Excel vba приёмы программирования


Содержание

Язык визуального программирования приложений Visual Basic for Applications

ЧАСТЬ I. Основные средства и возможности VBA

ЧАСТЬ II. Практические приемы программирования на VBA

Настоящий курс является с одной стороны, подробным справочником по Visual Basic for Applications (VBA), а с другой стороны, самоучителем по составлению и разработке приложений, написанных на этом языке. Это удобное сочетание, которое, следуя рекламному подходу, можно назвать «два в одном», обеспечивает большую гибкость при решении читателем своих собственных задач. Курс на большом количестве примеров доступно обучает, как можно быстро и эффективно решать разнообразные задачи. В справочнике приводится подробное описание возможностей VBA, имея такие сведения под рукой у читателя исчезнет необходимость бегать по магазинам в поиске дополнительной литературы при написании самостоятельных приложений, что несомненно сбережет время и кошелек.

Курс состоит из уроков. В каждом из уроков разрабатывается пример пользовательского приложения и дается подробный анализ. Тексты всех программ снабжены доскональными комментариями. Можно сказать, что все рассматриваемые программы разложены буквально по маленьким разжеванным кусочкам, которые читателю только и остается проглотить. По завершению урока предлагается самостоятельное задание, выполнение которого поможет лучше закрепить разобранный материал.

С помощью VBA можно легко и быстро создавать пользовательские приложения, используя единую для всех офисных программ среду и язык. Научившись разрабатывать приложения для одной офисной программы, например Excel (которой, как наиболее популярной офисной программе, в основном и посвящен данный курс), можно создавать приложения и для других офисных программ, например Access. Внимательно изучая этот курс, можно стать искусным разработчиком и научиться пользоваться мощными средствами разработки приложений Excel для того, чтобы конструировать эффективные и применимые к реальной жизни приложения. Кроме того, по своей структуре, интерфейсу и синтаксису VBA образует ядро Visual Basic. Поэтому тот, кто изучит программирование на VBA очень быстро может освоить и Visual Basic.

В данном курсе уделяется огромное внимание программированию на языке VBA, но это совсем не требует от читателя быть профессиональным программистом. VBA обладает мощными встроенными интеллектуальными средствами, которые позволяют даже начинающему пользователю быстро самостоятельно разрабатывать профессиональные приложения. Например, при написании кода программы редактор VBA сам предлагает пользователю возможные продолжения составляемых им инструкций. Другим примером встроенных интеллектуальных средств VBA является макрорекордер, который переводит все выполняемые вручную пользователем действия в основном приложении на язык VBA. Таким образом, макрорекордер позволяет пользователю поручать VBA самому создавать большие куски кода разрабатываемого приложения.

Краткий обзор материала курса

Курс состоит из двух частей, первая из которых имеет 15 глав со следующим кратким содержанием:

  • Во введении на простейших примерах объясняется, зачем нужен VBA.
  • Глава 1 отвечает на вопрос: «Что такое VBA?». В ней также дано описание основных структурных элементов VBA.
  • В главе 2 рассматриваются основные элементы объектной иерархической структуры VBA.
  • В главах 3 и 4 дан обзор методов VBA, программирующих команды для работы с рабочим листом, которые позволяют строить прогрессии, создавать фильтры и консолидировать данные, организовывать сценарии и структуры, решать уравнения, подводить промежуточные итоги и сортировать данные.
  • Глава 5 описывает, как строятся диаграммы в VBA.
  • В главе 6 обсуждается, как программировать одно из наиболее мощных средств по анализу данных — сводные таблицы.
  • В глава 7 приведен обзор элементов управления VBA и описание того, как в VBA создаются и программируются диалоговые окна.
  • Глава 8 продолжает начатый в предыдущей главе разговор по созданию пользовательского интерфейса. В ней объясняется, как создать пользовательское меню и панели инструментов.
  • Глава 9 посвящена вопросу создания очень полезного и наглядного средства Microsoft Office — помощника.
  • В главе 10 дано обзорное описание процесса создания графических объектов.
  • В главе И приводится краткая информация по основным понятиям языка VBA: какими типами данных оперирует VBA, что такое переменная, константа, массив и динамический массив, как создается пользовательский тип переменной. В ней перечислены операции, встроенные функции, операторы и процедуры VBA, а также типы процедур.
  • В главе 12 обсуждаются принципы создания процедур обработки ошибок, а также встроенные в редактор VBA мощные средства по отладке программ.
  • В главе 13 описаны типы файлов и способы работы с ними в VBA.
  • Глава 14 объясняет, как в VBA можно создавать пользовательские объекты.
  • В главе 15 дан обзор методов по работе с внешними базами данных, использовании Microsoft Query, открытой связи с базой данных (ODBC) и объектов доступа к данным (DAO).

Во второй части приведено 14 уроков самоучителя по созданию пользовательских приложений:

  • В уроке 1 на примере создания приложение по игре в орел и решку показывается: как в редакторе VBA создается программа и как она запускается на выполнение; создание пользовательского диалогового окна; программный контроль за вводом в поле чисел, а не строковой информации; программное управление запретом ввода данных в поле; работа с функцией генератора случайных чисел; вывод числовой информации в поле.
  • В уроке 2 на примере разработки приложения по расчету маргинальной процентной ставки объясняется: как программно решаются уравнения; программный ввод формулы в ячейку рабочего листа; финансовые функции; проверка корректности ввода данных из диалогового окна; назначение клавишам и функций кнопок диалогового окна; создание всплывающих подсказок у элементов управления; использование MacroRecorder для упрощения и убыстрения написания кода; программное форматирование ячеек рабочего листа.
  • В уроке 3 на рассмотренном примере работы со списком показывается: как заполняется список; управление выбором нескольких элементов из списка; как выполнить специфицированную операцию над выбранными элементами из списка с помощью переключателей.
  • В уроке 4 на примере разработки приложения по расчету амортизации объясняются: финансовые функции расчета амортизации; управление видимостью отдельных элементов управления в окне диалога; программный вывод объектов WordArt на рабочий лист.
  • В уроке 5 на рассмотренном примере показывается: ввод формул при помощи элемента управления RefEdit; нахождение корня уравнения зависящего от параметра; установка параметров метода GoalSeek; создание прогрессий на рабочем листе; программирование протаскивания маркера заполнения выделенного диапазона на рабочем листе; построение диаграмм.
  • В уроке 6 на обсужденных примерах показывается: программное управление размерами диалогового окна и элементов управления; задание последовательности элементов управления в виде массива объектов; определение текущего объема вклада; задание параметров счетчика; как можно программно или при помощи drag-and-drop операции перемещать элементы управления по поверхности диалогового окна.
  • В уроке 7 на примере конструируемого приложения демонстрируется: как при помощи диалогового окна можно заполнить базу данных на рабочем листе; программирование примечаний и текстовых полей на рабочем листе; использование переключателя и флажков; создание пользовательского заголовка окна приложения и программное закрепление области.
  • В уроке 8 на примере разработки приложения по построению поверхности объясняется: как табулируются функции, зависящие от двух аргументов; преобразование формулы с аргументами х и у в формулу рабочего листа; программное построение поверхности; запись диаграммы в графический файл; считывание графического файла в элемент управления image; программное управление углом зрения, под которым смотрят на поверхность, и углом поворота поверхности вокруг оси Z.
  • В уроке 9 на примере конструируемого приложения по расчету периодических выплат показывается: как используется финансовая функция пплдт (РМТ); вывод результатов табулирования функции в элемент управления ListBox (список); построение диаграммы, тип которой выбирается в группе переключателей; программная проверка наличия файла на диске.
  • В уроке 10 на примере разработки приложения по работе с базой данных демонстрируется: конструирование пользовательского интерфейса; создание приложения, работающего с несколькими диалоговыми окнами; поиск информации в базе данных; редактирование записей в базе данных; удаление ненужных записей из базы данных; архивация данных; программирование фильтрации и сортировки данных; создание сводных таблиц; добавление пользователем новых элементов в список с полем во время выполнения программы.
  • В уроке И на примере игры в крестики и нолики объясняется: удаление рисунка из элемента управления; учет количества щелчков по элементу управления; управление видимостью границы элемента управления; создание игрового поля.
  • В уроке 12 на примере приложения по построению линии тренда показывается: конструирование многостраничных диалоговых окон и линии тренда; применение метода offset для вывода данных на рабочем листе; считывание данных из каждой отдельной ячейки диапазона.
  • В уроке 13 на примере приложения по составлению расписания обсуждается: передача информации между элементами управления при обработке события click; управление видимостью рисунков и цветом элементов управления.
  • В уроке 14 на примере показана работа с текстовыми файлами: считывание и запись в файл последовательного доступа; считывание и запись записей в файла прямого доступа; создание и работа с пользовательскими типами данных; создание простейшего текстового редактора и заставки приложения.

Литература

  1. «Советы тем кто программирует на Visual Basic»
  2. «Советы тем кто программирует на Visual Basic и MS Office VBA» Ч.1 — [pdf]
  3. «Советы тем кто программирует на Visual Basic и MS Office VBA» Ч.2 — [pdf]

Релятивисты и позитивисты утверждают, что «мысленный эксперимент» весьма полезный интрумент для проверки теорий (также возникающих в нашем уме) на непротиворечивость. В этом они обманывают людей, так как любая проверка может осуществляться только независимым от объекта проверки источником. Сам заявитель гипотезы не может быть проверкой своего же заявления, так как причина самого этого заявления есть отсутствие видимых для заявителя противоречий в заявлении.

Это мы видим на примере СТО и ОТО, превратившихся в своеобразный вид религии, управляющей наукой и общественным мнением. Никакое количество фактов, противоречащих им, не может преодолеть формулу Эйнштейна: «Если факт не соответствует теории — измените факт» (В другом варианте » — Факт не соответствует теории? — Тем хуже для факта»).

Максимально, на что может претендовать «мысленный эксперимент» — это только на внутреннюю непротиворечивость гипотезы в рамках собственной, часто отнюдь не истинной логики заявителя. Соответсвие практике это не проверяет. Настоящая проверка может состояться только в действительном физическом эксперименте.

Эксперимент на то и эксперимент, что он есть не изощрение мысли, а проверка мысли. Непротиворечивая внутри себя мысль не может сама себя проверить. Это доказано Куртом Гёделем.

Понятие «мысленный эксперимент» придумано специально спекулянтами — релятивистами для шулерской подмены реальной проверки мысли на практике (эксперимента) своим «честным словом». Подробнее читайте в FAQ по эфирной физике.

НОВОСТИ ФОРУМА
Рыцари теории эфира
01.10.2020 — 05:20: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ — Upbringing, Inlightening, Education ->
[center][Youtube]69vJGqDENq4[/Youtube][/center]
[center]14:36[/center]
Osievskii Global News
29 сент. Отправлено 05:20, 01.10.2020 г.’ target=_top>Просвещение от Вячеслава Осиевского — Карим_Хайдаров.
30.09.2020 — 12:51: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ — Upbringing, Inlightening, Education ->
[center][Ok]376309070[/Ok][/center]
[center]11:03[/center] Отправлено 12:51, 30.09.2020 г.’ target=_top>Просвещение от Дэйвида Дюка — Карим_Хайдаров.
30.09.2020 — 11:53: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ — Upbringing, Inlightening, Education ->
[center][Youtube]VVQv1EzDTtY[/Youtube][/center]
[center]10:43[/center]

интервью Раввина Борода https://cursorinfo.co.il/all-news/rav.
мой телеграмм https://t.me/peshekhonovandrei
мой твиттер https://twitter.com/Andrey54708595
мой инстаграм https://www.instagram.com/andreipeshekhonow/

[b]Мой комментарий:
Андрей спрашивает: Краснодарская синагога — это что, военный объект?
— Да, военный, потому что имеет разрешение от Росатома на манипуляции с радиоактивными веществами, а также иными веществами, опасными в отношении массового поражения. Именно это было выявлено группой краснодарцев во главе с Мариной Мелиховой.

[center][Youtube]CLegyQkMkyw[/Youtube][/center]
[center]10:22 [/center]

Доминико Риккарди: Россию ждёт страшное будущее (хотелки ЦРУ):
https://tainy.net/22686-predskazaniya-dominika-rikardi-o-budushhem-rossii-sdelannye-v-2000-godu.html

Завещание Алена Даллеса / Разработка ЦРУ (запрещено к ознакомлению Роскомнадзором = Жид-над-рус-надзором)
http://av-inf.blogspot.com/2013/12/dalles.html

[center][b]Сон разума народа России [/center]

[center][Youtube]CLegyQkMkyw[/Youtube][/center]
[center]10:22 [/center]

Доминико Риккарди: Россию ждёт страшное будущее (хотелки ЦРУ):
https://tainy.net/22686-predskazaniya-dominika-rikardi-o-budushhem-rossii-sdelannye-v-2000-godu.html

Завещание Алена Даллеса / Разработка ЦРУ (запрещено к ознакомлению Роскомнадзором = Жид-над-рус-надзором)
http://av-inf.blogspot.com/2013/12/dalles.html

[center][b]Сон разума народа России [/center]

Знакомство с программированием в Excel

Добавление на ленту вкладки разработчика

Написание кода Ваших процедур и редактирование макросов производится в редакторе Visual Basic, который доступен с вкладки «Разработчик». Данная вкладка по умолчанию скрыта. Для ее отображения необходимо выполнить следующие действия:

Для Excel 2007. Зайдите в параметры Excel, используя кнопку Office, и в «основных параметрах работы с Excel» установите галочку на пункте «Показывать вкладку Разработчик на ленте».

Для Excel 2010 и 2013. В параметрах Excel нужно выбрать пункт «Настроить ленту» и в категории основные вкладки установить галочку для вкладки «Разработчик».

Знакомство с редактором Visual Basic в Excel

Чтобы попасть в редактор кода, кликните на вкладке разработчика в области «Код» по кнопке «Visual Basic».

После этого на экране появится новое окно редактора VBA. Рассмотрим ниже некоторые его элементы, которые могут понадобиться в самом начале.

№1 на изображении. Кнопка сохранить. Сохраняет текущую книгу Excel. Необходимо быть внимательнее при работе с несколькими книгами, так как текущей считается та, которая выделена в окне проектов (№3 на изображении).

№2 на изображении. Кнопки запуска, остановки и прерывания выполнения кода. Во время запуска начинает выполняться код той процедуры, на которой размещен курсор. Данные кнопки дублируются вкладкой «Run» основного меню редактора.

№3 на изображении. Окно управления проектами VBA открытых книг (Project Explorer) и кнопка, отображающая это окно.

№4 на изображении. Кнопка, отображающая окно свойств объектов (Properties Window), выбранных в окне №3, и элементов пользовательских форм (работа с объектами, их свойствами и методами будет рассмотрена в отдельной статье этой категории).

№5 на изображении. Кнопка вызова окна объектов (Object Browser). В нем описаны все доступные для работы объекты, их свойства и методы.


Кнопки №3, №4 и №5 так же доступны на вкладке «View» главного меню редактора. Там же можно вызвать другие полезные для работы окна, которые не будут рассмотрены в этом материале.

Написание простой процедуры на Visual Basic for Application

Код любой процедуры (подпрограммы) располагается в модуле, поэтому необходимо его добавить, чтобы приступить к программированию. Выберите пункт «Module» на вкладке «Insert» основного меню редактора VBA. В основной области редактора (на изображении имеет серый фон) должно появиться новое окно кода – Имя_книги – имя_модуля (Code), а в окне управления проектами (№3) к дереву выбранного проекта добавится вновь созданный модуль.

Добавьте в модуль следующий код:

Данная процедура выводит на экран диалоговое окно с сообщением «Привет пользователь!». Протестируйте ее работу, нажав кнопку Rub Sub (№2 на изображении) или вызвав как обычный макрос.

Теперь более подробно разберем приведенный код.

Любая процедура начинается со строки объявления, состоящей из оператора Sub (сокращение от Subprogram), после которого следует имя подпрограммы. За именем следует перечень аргументов, заключенный в скобки.

Для имени действуют некоторые ограничения: оно должно начинаться с буквы и не должно содержать пробелы и большинство спецсимволов.

В этом примере аргументы не используются, поэтому указываются пустые скобки.

Следующая строка выводит диалог на экран с помощью функции MsgBox. Сообщение, которое в нее передается должно содержать текст, поэтому оно заключается в двойные кавычки.

Последняя строка содержит оператор End с последующим ключевым словом Sub и сообщает о завершении процедуры.

Учимся пользоваться Object Browser

Выше была упомянута функция MsgBox, но ее возможности полностью не рассмотрены. Это хороший случай, чтобы научиться использовать браузер объектов (№5 на изображении окна редактора VBA).

Нажмите кнопку на панели или клавишу F2, чтобы отобразить Object Browser:

Выпадающий список №1 содержит перечень всех подключенных библиотек. Используйте его только в том случае, если точно знаете, к какой библиотеке относится изучаемый класс, функция и т.д. (подключить или отключить любую из доступных библиотек можно, выбрав пункт «References» на вкладке «Tools» главного меню редактора VBA).

Примечание: Библиотеки предоставляют стандартные и дополнительные возможности работы с объектами Excel (ячейки, листы, книги и т.д.), файловой системы, изображениями, базами данных и другими.

Поле №2 предназначено для поиска свойств, методов, классов подключенных библиотек.

Результаты поиска отображаются в поле №3 и представляют из себя таблицу с тремя столбцами: библиотека, класс и член класса.

Поле №4 содержит перечень всех классов, выбранных в поле №1 библиотек.

В области №5 содержится список всех членов класса, выбранного в поле №4.

Поле №6 содержит краткую справку о выделенном результате поиска, классе или члене класса.

Примечание: библиотеки, классы, функции и процедуры (методы), свойства и т.п. в Object Browser имеют различные обозначения, представленные набором пиктограмм. Краткая справка (поле №6) всегда указывает какой тип элемента выделен. Обращайте на это внимание, чтобы в дальнейшем лучше ориентироваться в работе с объектами.

Примечание: Понятие объекта не относится к теме данной статьи и будет рассмотрено в следующих материалах.

Теперь найдем функцию MsgBox с помощью браузера объектов. В поле поиска (№2) впишите ее название и кликните по кнопке с изображением бинокля. В поле результатов поиска выделите строку со значением MsgBox в столбце «Member». В поле №6 появилась краткая информация, сообщающая нам, что выбранный член является функцией, имеет перечень аргументов, возвращает результат типа VbMsgBoxResult, относиться в классу Interaction библиотеки VBA.

Для получения полной справки кликните по кнопке с изображением вопросительного знака. В зависимости от версии Excel, Вас перенаправить на файл справки или сайт с документацией от Microsoft.

Изучите самостоятельно возможности функции MsgBox.

Ссылка на процедуру VBA

По мере углубления в язык VBA Ваш код будет усложняться и иметь более гибкую логику. Вам часто придется сталкиваться с тем, что некоторые части программы будут дублировать код частично или полностью. Таких ситуаций нужно избегать, так как они усложняют понимание процедур и затрудняют их отладку.

Для лучшего понимая рассмотрим пример (в примере используется псевдокод):

Выносите дублирующие участки кода в отдельные процедуры и указывайте ссылки на них из главной процедуры:

Для указания ссылки на процедуру используется оператор Call, который состоит из необязательного ключевого слова Call и имени вызываемой подпрограммы:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

VBA для чайников — Камминг С.

Название: VBA для чайников.

Автор: Камминг С.

Эта книга поможет освоить интегрированную среду разработки VBA и научиться программировать в VBA с использованием объектов, их свойств, методов и событий. Обсуждаемые приемы программирования иллюстрируются примерами, которые можно сразу же опробовать на практике. Полученные знания вы сможете применить как для настройки и усовершенствования популярных офисных приложений, включая приложения Office XP, так и для создания собственных приложений.
Книга рассчитана на тех, кто собирается быстро и без лишних усилий научиться программировать для Windows, используя VBA.

Да, это именно та книга, которая необходима для того, чтобы начать освоение VBA (аббревиатура от Visual Basic for Applications, что означает Visual Basic для приложений). Благодаря этой книге вы узнаете об основных принципах программирования в VBA и получите необходимые навыки для создания полезных программ. А самое главное, вы сможете сделать это без лишних усилий.
В книге использовано много примеров, написана она легко и понятно, поскольку, кто знает, сколько скучных томов уже издано в этом мире! Я попытался изложить все самым обычным языком, по возможности исключив из употребления режущий ухо нормального человека специальный жаргон. И с этого момента я начинаю отпускать (иногда не самые лучшие) шуточки, чтобы вам было на что направить свое раздражение.
С другой стороны, обсуждение рассматриваемых вопросов не слишком упрощено, иначе оно было бы лишено всякой ценности. Если отбросить шутки в сторону, то эта книга — полноценное справочное пособие, охватывающее все основные разделы VBA.

Оглавление
Введение
ЧАСТЬ I. НАЧАЛЬНЫЕ СВЕДЕНИЯ О VBA
Глава 1 .Уважайте теорию, теория — это все!
Глава 2. Не пишите программу, когда можно записать макрос
Глава 3. Основы программирования в VBA
Глава 4. Выполнение VBA-программ
Глава 5. Редактор Visual Basic к вашим услугам
ЧАСТЬ II. КУРС ПРОГРАММИРОВАНИЯ НА VBA
Глава 6. Анатомия выдающейся VBA-программы
Глава 7. Хранение и обработка информации
Глава 8. Управление потоком
Глава 9. «Бронированный» программный код: отладка и устранение ошибок
Глава 10. Создание интерактивных VBA-форм
ЧАСТЬ III. ПРАКТИКУЕМСЯ В ПРОГРАММИРОВАНИИ НА VBA
Глава 11. Инструменты встроенного оркестра VBA
Глава 12. Объектно-ориентированное программирование
Глава 13.Тонкости хранения данных: массивы и коллекции
ЧАСТЬ IV. ПРОФЕССИОНАЛЬНАЯ РАБОТА С VBA
Глава 14. VBA для Office
Глава 15. Программирование на VBA в Word
Глава 16- VBA-программирование в Excel
Глава 17. Программирование баз данных
Глава 18. Работа с файлами на диске
Глава 19. Еще о VBA-формах
ЧАСТЬ V. ВЕЛИКОЛЕПНЫЕ ДЕСЯТКИ
Глава 20. Десятка (без трех) эффектных решений с помощью VBA
Глава 21 .Три десятка ресурсов VBA
Предметный указатель

Бесплатно скачать электронную книгу в удобном формате, смотреть и читать:
Скачать книгу VBA для чайников — Камминг С. — fileskachat.com, быстрое и бесплатное скачивание.

Скачать pdf
Ниже можно купить эту книгу по лучшей цене со скидкой с доставкой по всей России. Купить эту книгу

Создание макросов в редакторе Visual Basic

Для создания макроса в Excel используется специальное окно — редактор программ на языке программирования Visual Basic for Applications (VBA).

По умолчанию, вкладка, отвечающая за работу с макросами не отображается в ленте Excel, поэтому ее надо активировать.
Для этого в ленте переходим на вкладку «Файл» -> «Параметры» -> «Основные» и включаем в правой части окна флажок Показывать вкладку «Разработчик» на ленте:

Добавление вкладки «Разработчик» на ленту

Вкладка «Разработчик» на панели вкладок

Интерфейс редактора VBA

Виды модулей для макросов


  1. Модуль листа — модуль используется для макросов, которые выполняются при наступлении определенных условий на данном листе (например, пересчет формул, копирование листа и т.д.);
  2. Модуль «ЭтаКнига» — модуль используется для макросов, которые выполняются при наступлении определенных условий в данной книге (например, при окрытии книги, при сохранении и т.д.);
  3. Обычные модули — модуль предназначен для любых макросов.

Рассмотрим стандартный вид макроса из любого модуля:

Код макроса на языке программирования VBA

  1. Начало макроса — любой макрос начинается с оператора Sub, за которым следует имя макроса и список аргементов;
  2. Тело макроса — команды, которые будут выполняться при запуске макроса (в данном конкретном примере макрос каждой ячейке из выделенной области присваивает случайное число от 1 до 100 и заменяет формулы на значения);
  3. Конец макроса — любой макрос заканчивается оператором End Sub.

Без знаний основ программирования и наличия опыта работы с VBA могут возникнуть определенные трудности при создании макросов. В этом случае можно начать с более простого инструмента и создать макрос в Excel с помощью макрорекордера.

ПРОГРАММИРОВАНИЕ В VISUAL BASIC FOR APPLICATIONS

ЦЕЛЬ ТРЕНИНГА

Научиться эффективно применять Visual Basic for Applications в Microsoft Excel для выполнения задач

КРАТКОЕ ОПИСАНИЕ

В ходе тренинга будут освоены навыки программирования в Visual Basic for Applications, необходимые для самостоятельного написания кода с нуля, отладки кода, анализа чужого кода, а также приобретены навыки и знания по ориентированию в редакторе Visual Basic Editor, понимание объектной модели Excel «изнутри»

ПРЕИМУЩЕСТВА ТРЕНИНГА

• Разработка и создание в ходе проведения тренинга файлов Microsoft Excel, используемых участниками для решения своих рабочих задач;
• Учет специфики работы и корпоративной культуры Заказчика;
• Обеспечение консультационной поддержки участников тренинга после его проведения.

РЕЗУЛЬТАТ

Сокращение времени, затрачиваемого на выполнение различных задач в Microsoft Excel, повышение качества и эффективности работы

ПРОДОЛЖИТЕЛЬНОСТЬ ТРЕНИНГА: 24 академических часа

ПРОГРАММА ТРЕНИНГА

Модуль 1. Введение

• Необходимые основы и теоретические знания, которые надо знать, чтобы начать программировать на Visual Basic for Applications (VBA) в Microsoft Excel.
• Основные понятия и термины, чтобы говорить на одном языке.
• Плюсы и минусы использования макросов, когда они могут быть вам полезны, а когда лучше воспользоваться иными возможностями Excel.
• Способы создания макросов (макрорекордер, самостоятельное написание кода, копирование и адаптация фрагментов чужого кода и т.д.)

Модуль 2. Редактор VBE

• Этот модуль посвящен подробному разбору основного инструмента программиста на VBA в Microsoft Excel — среды программирования и редактора кода Visual Basic Editor (VBE).
• Основные элементы окна VBE — их назначение, использование, горячие клавиши и приемы ускорения работы
• Структура проекта VBA Project — как устроена книга Excel с точки зрения программирования, где и как может храниться код VBA, в чем различия и особенности использования модулей разного типа, что такое формы и т.д.
• Процедуры (Sub) и функции (Function) – что из себя представляет каждый тип и в чем их различия и особенности.

Модуль 3. Объектная модель Microsoft Excel

• Рассматривается набор объектов (листов, ячеек, диаграмм и т.д.) со своими свойствами и специфическими методами и событиями. Знание и понимание этих свойств и взаимодействия объектов позволяет легко манипулировать любыми встроенными инструментами в Excel, привлекая их для решения задач. Этот модуль даст понимание устройства общей объектной структуры Excel, принципов и подходов для работы с ней, свойств ключевых объектов книги.
• Теоретический минимум (иерархия объектов, коллекции, свойства, методы и события)
• Получение справки по объектам
• Сокращенное обращение к объекту (для упрощения и упорядочивания ссылок на объекты)
• Подробный разбор свойств и методов объектов Workbook и Worksheet

Модуль 4. Синтаксис языка VBA

• В этом модуле на живых примерах разберем все основные программные конструкции и синтаксические особенности языка VBA, нюансы их использования в реальных задачах и проектах.
• Объявление переменных и констант (правила выбора имен для переменных, типы данных, области видимости переменных).
• Обзор способов организации взаимодействия с пользователем (MsgBox, InputBox, Application.InputBox, Debug.Print и т.д.)
• Математические и текстовые операторы для обработки данных
• Способы проверки условий (конструкции if. then, select case и т.д.), операторы сравнения.
• Способы организации циклов (различные виды безусловных циклов for. next, циклы по условию do. loop и т.д.)
• Обработка ошибок: завершение кода при возникновении ошибки; переход в определенное место в коде; сброс ошибок. Почему стоит избегать переходов при ошибках.

Модуль 5. Работа с диапазонами

• Безусловно, почти любой макрос создается для взаимодействия с отдельными ячейками и диапазонами листа Excel. В этом модуле подробно рассматриваются все свойства и методы объекта Range и работа с ним на практических задачах из реальной жизни.
• Свойство Selection, обработка выделенных областей на листе
• Объект Range, его свойства, методы и события. Выполнение различных операций над диапазонами
• Коллекция Cells, варианты и особенности ее использования в коде

Модуль 6. Обработка событий

• События в Microsoft Excel — это некие специальные ситуации, возникающие в определенный момент (сохранение книги, изменение данных на листе, печать документа и т.д.) Умение перехватывать и обрабатывать события открывает перед разработчиком очень широкий набор мощных возможностей для применения в проектах. Этот модуль посвящен детальному разбору принципов и алгоритмов обработки событий в Excel.
• Концепция событий Microsoft Excel. Последовательность возникновения событий. Отключение и включение событий. Почему события необходимо отключать.
• События книги, примеры использования в реальных проектах
• События листа, примеры
• Перехват событий любой открытой книги, листа.

Модуль 7. Пользовательские формы

• Для многих программ часто необходимо взаимодействие с пользователем: запрос данных, отображение, визуализация. Все это не сделать при помощи одних Msgbox и InputBox, поэтому нельзя представить более-менее объемную программу на VBA без пользовательских форм — UserForms. Данный модуль поможет научиться создавать пользовательские формы и применять их в проекте.
• Добавление формы в проект
• Использование элементов управления: кнопка, надпись, текстовое поле, выпадающий список
• Обработка событий элементов управления

Модуль 8. Варианты запуска процедур. Защита проекта VBA

• Создание кнопок на листе для запуска процедур
• Создание своей панели на ленте с кнопками для запуска процедур. Использование событий для создания кнопок на панели при открытии книги.
• Защита проекта VBA.

Модуль 9. Функции API

• Краткое введение в понятие функций API
• Когда имеет смысл применять данные функции, а когда лучше воздержаться.
• Объявление функций API, особенности объявления в различных версиях OS Windows.

Скачать программу тренинга:

ПРОГРАММИРОВАНИЕ В VBA (480,7 KiB, 5 729 скачиваний)

ОРГАНИЗАЦИЯ ТРЕНИНГА

Для проведения тренинга на вашей территории необходимо:

• подходящее помещение(достаточное для комфортного размещения всех участников тренинга);
• проектор и экран(или что-то, что может данный экран заменить);
• компьютеры для каждого участника с установленным на них Microsoft Office Excel 2007 или старше(2003 так же подойдет, но желательно все же 2007 или старше).

Если у вас нет подходящего помещения, то тренинг может быть проведен на нашей территории, где уже есть все необходимое.

Глава 16. VBA-программирование в Excel.

Глава 16. VBA-программирование в Excel.

Что такое объектная модель Excel

Управление ячейками с использованием объектов диапазонов


Создание собственных функций для использования в формулах рабочих листов

Использование встроенных функций Excel в VBA-коле

Работа с событиями Excel

Каждого, кто когда-либо писал формулы рабочих листов, можно считать в некотором роде программистом; поэтому не следует относиться к VBA, как к чему-то запредельному. Скорее всего, после некоторой работы с редактором Visual Basic вы обнаружите, что писать VBA-код, на самом деле, удобнее, чем составлять формулы непосредственно в рабочем листе; VBA предоставляет больший простор для деятельности в окне редактирования кода и позволяет вносить комментарии, что бывает очень полезным.

Кроме того, VBA может служить мощным инструментом для создания настраиваемых приложений, базирующихся на таблицах, способных выполнять вычисления и форматирования, которые без них просто не реальны. Но прежде чем стать профессионалом в использовании кодов Excel, чему способствует представленный ниже материал, следует ознакомиться с основами VBA, которые даются в первых трех разделах.

Знакомство с объектной моделью Excel

Прежде чем приступить к написанию VBA-кода для Excel, необходимо составить представление об иерархии объектов Excel и о том. как определять их в написанном коде. В самом верху иерархии находится объект Application (Приложение), отвечающий за функционирование всей программы Excel.

Для увеличения скорости работы многих VBA-программ следует отключить обновление экрана. По умолчанию Excel отображает каждое изменение в рабочей книге, сделанное в ходе выполнения VBA-кода. Это приводит к существенному снижению быстродействия. Для отключения такого режима работы Excel необходимо воспользоваться функцией ScreenUpdating (Обновление экрана) объекта Application:

Application . ScreenUpriatirig = False

Не забудьте в конце процедуры восстановить значение True для свойства ScreenUpdating, в противном случае пользователь не сможет увидеть на экране полученный результат.

При использовании свойства Screer.Upcating необходимо явно указывать имя объекта Application. Однако в большинстве случаев свойства объекта Application можно использовать напрямую. Например, свойство ActiveSheet объекта Application относится к рабочему листу или диаграмме, активной в текущий момент (безусловно, в книге, активной сейчас). Для обращения в коде к такому листу вместо записи Application .ActiveSheet можно использовать просто ActiveSheet. Коллекция Workbooks объекта Application содержит все рабочие книги, открытые в данный момент.

Для идентификации определенной рабочей книги используйте ее имя, заключенное в кавычки. Вот пример активизации отдельной рабочей книги:

Workbooks(«Сводная статистика продаж.xls»).Activate

Конечно же, рабочие листы также являются полноправными объектами. Они принадлежат коллекции Worksheets, и их идентификация должна проводиться таким же образом. Выражение Worksheets ( «ЛистЗ» ) указывает на лист с именем ЛистЗ. Аналогично, отдельный объект Chart для диаграммы, находящейся на отдельном листе, является членом коллекции Charts. Обращение к ней выполняется с помощью выражения вроде Charts («Parts Chart») . Приемы работы с диаграммами, внедренными в рабочий лист, несколько отличаются, и приемы написания кода для них являются объектом рассмотрения данной книги.

Отображение рабочих книг можно изменить с помощью объектов Window (Окно). Объекты Window, которые иногда используются и для изменения содержимого рабочей книги, являются членами коллекции Windows объекта Application. Ссылка на окно осуществляется указанием в качестве индекса в коллекции Windows имени файла, содержащего рабочую книгу:

Windows(«Кривая продаж игрушек.xls»)

Если для одной рабочей книги открыто несколько окон, необходимо после имени рабочей книги через двоеточие указать номер окна, например: Windows («Кривая продаж иг ру ше к.x l s:2»).

Самоучитель по Excel VBA

Данный учебник является введением в язык программирования Excel VBA (Visual Basic for Applications). Изучив VBA, Вы сможете создавать макросы и выполнять в Excel практически любые задачи. Вы очень скоро поймёте, что макросы могут сэкономить уйму времени благодаря автоматизации повторяющихся задач и обеспечить гибкое взаимодействие с другими пользователями.

Этот учебник не является исчерпывающим руководством по языку программирования Excel VBA. Его цель – помочь начинающему специалисту освоить написание макросов в Excel при помощи кода VBA. Для желающих изучить этот язык программирования более глубоко существуют отличные книги по Excel VBA. Далее приведено содержание самоучителя по Excel Visual Basic. Для начинающих программистов настоятельно рекомендуется начать с первого раздела учебника и изучать их по порядку. Те, кто имеет опыт в программировании на VBA, могут сразу же перейти к интересующим темам.

Более подробное описание по Excel VBA можно найти на сайте Microsoft Office.

Основы программирования на языке Visual Basic for Applications (VBA). Модель объектов MS Excel

В модели объектов Excel имеется более 100 объектов. Наиболее часто используемыми объектами Excel являются объекты Application, Workbooks и Workbook, Worksheets и Worksheet, Range, Selection.

Коллекция представляет собой объект, содержащий несколько других объектов, как правило, одного и того же типа.

Объект Workbooks содержит все открытые объекты Workbook (рабочая книга).

Доступ к заданному элементу коллекции осуществляется либо по номеру, либо по имени.

Например, Worksheets(1) обозначает первый рабочий лист активной книги, а Worksheets(“Лист1”) — рабочий лист с именем Лист1.

Методы и Свойства

Примеры: Application.Caption = “Пример”

Полная ссылка на объект состоит из ряда имен вложенных последовательно друг в друга объектов. Их имена в этом ряду разделяются точками. Ряд начинается с объекта Application и заканчивается именем самого объекта.

В неявной ссылке, в отличие от полной, активный в данный момент объект, как правило, можно опускать.

• Если программа выполняется в Excel, то Workbooks(“Архив”).Worksheets(“Продажа”).Range(“A1”)

• Если рабочая книга Архив является активной, то ссылку можно сократить:
Worksheets(“Продажа”).Range(“A1”)

• Если рабочий лист Продажа активен, то Range(“A1”)

Объект Application – это главный (корневой) объект в иерархии объектов Excel, представляет само приложение Excel. Он имеет огромное число свойств и методов, позволяющих установить общие параметры приложения Excel.

Свойства объекта Application

ActiveWorkbook (активная рабочая книга)

ActiveSheet (активный рабочий лист)

ActiveCell (активная ячейка)

ActiveChart (активная диаграмма)

.Font.Bold = True

.Value = “Отчет за Май”

Caption – Возвращает или устанавливает текст из заголовка главного окна Excel. Установка значения свойства равным Empty возвращает заголовок, используемый по умолчанию.

  1. Application.Caption = “Отчет за 2000 год”
  2. Application.Caption = Empty

Метод Quit – Закрывает приложение.

После объекта Application в иерархии объектов Excel следует коллекция Workbooks, которая содержит объекты Workbook. Объект Workbook представляет собой рабочую книгу Excel. Все открытые в Excel рабочие книги включены в коллекцию Workbooks.

Свойства коллекции Workbooks и объекта Workbook


Count — возвращает число объектов коллекции Workbooks.

Worksheets – возвращает коллекцию всех рабочих листов книги.

Charts – возвращает коллекцию всех диаграмм книги (которые не внедрены в рабочие листы)

Методы коллекции Workbooks и объекта Workbook

Activate — активизирует рабочую книгу.

Add – создает новый объект в коллекции Workbooks.

Close –закрывает книги.

Обработка событий объекта Workbook

  1. Перейти в редактор VBA;
  2. В окошке Project Explorer выбрать объект ЭтаКнига и перейти в окошко кода для этого объекта;
  3. В верхнем левом списке выбрать объект Workbook;
  4. В верхнем правом списке выбрать необходимое событие.

Коллекция Worksheets включает в себя множество всех объектов Worksheet (рабочий лист) в рабочей книге. В иерархии Excel объект Worksheets идет сразу после объекта Workbook.

Свойства коллекции Worksheets и объекта Worksheet

Name – возвращает или устанавливает имя рабочего листа.

Visible – возвращает True, если объект – видимый.

Cells – возвращает коллекцию всех ячеек рабочего листа.

Columns, Rows – возвращает коллекцию всех столбцов и строк рабочего листа.

Методы коллекции Worksheets и объекта Worksheet

Activate – активизирует рабочий лист.

Add – создает новый рабочий лист.

Delete – удаляет рабочий лист.

Activate – активизирует рабочий лист.

Add – создает новый рабочий лист.

Delete – удаляет рабочий лист.

Обработка событий объекта Worksheet

  1. Перейти в редактор VBA;
  2. В окошке Project Explorer выбрать объект Лист1 (или другой лист) и перейти в окошко кода для этого объекта;
  3. В верхнем левом списке выбрать объект Worksheet;
  4. В верхнем правом списке выбрать необходимое событие.

События объекта Worksheet

Activate – при активизации рабочего листа.

BeforeDoubleClick – при двойном щелчке по рабочему листу.

Calculate – при пересчете рабочего листа.

Change – при изменении содержимого ячейки пользователем.

Deactivate – когда рабочий лист теряет фокус.

SelectionChange – при изменении выделенного диапазона ячеек.

В иерархии Excel объект Range (диапазон), один из ключевых объектов VBA,следует сразу после объекта Worksheet. Объект Range может представлять собой ячейку, строку, столбец или диапазон ячеек.

Объект Selection – это любые выделенные ячейки на рабочем листе. При работе с объектом Selection можно использовать свойства и методы объекта Range. Для определения объекта Selection можно использовать метод Select.

Задание группы строк и столбцов

  1. Range(“A:C”) – задает диапазон, состоящий из столбцов A, B и C.
  2. Range(“2:2”) – задает диапазон состоящий из второй строки.
  3. Range(“2:5”) – задает диапазон состоящий из 2, 3, 4, 5 строки.
  4. Rows(2) – задает вторую строку.
  5. Columns (1) – задает столбец А.

Связь объекта Range и свойства Cells объекта Worksheet

  1. Range(“A2”) – задает ячейку A2.
  2. Cells(1,2) – задает ячейку B1.
  3. Range(“A2:C3”) – задает диапазон ячеек A2:C3.
  4. Range(“A2:C3, A5:C6”) – задает диапазон ячеек A2:C3 и A5:C6.
  5. Range(Cells(1,2), Cells(3,3)) – задает диапазон ячеек B1:C3.
  6. Range(“B2:D4”).Select
    Selection.Cells(2,2).Value = 2 – значение 2 вводится в ячейку C3.

Свойства объекта Range

Value – возвращает или устанавливает значение в ячейках диапазона.

Interior – возвращает объект, представляющий собой фон ячейки. Свойство ColorIndex этого объекта задает цвет фона (от 1 до 56: 1-черный, 2-белый, 3-красный, 4-зеленый, 5-синий, 6-желтый, 7-фиолетовый ).

Font – возвращает объект, представляющий собой шрифт. Свойства: Name, FontStyle (Regular (обычный), Bold (жирный), Italic (курсив)), Size, ColorIndex.

Formula – возвращает или устанавливает формулу в формате A1.

Address – возвращает адрес ячейки.

X = Range(“C1”).Value ‘X — переменная


With Range (“A1:B2”).Font
.Size = 14 : .FontStyle = “Bold” : .ColorIndex = 3
End With

• Range(“A1”).Formula = “=$A$4 + $A$10”

Методы объекта Range

Activate – активизирует ячейку.

Clear – очищает диапазон.

Copy – копирует диапазон в другой диапазон или в буфер обмена.

Delete – удаляет диапазон.

Select – выделяет диапазон.

Range (“A1:D4”).Copy Worksheets(“Лист2”).Range(“E5”)

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Да какие ж вы математики, если запаролиться нормально не можете. 8426 — | 7330 — или читать все.

188.64.174.135 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)

очень нужно

Excel vba: приёмы программирования

Range(“�����”)

Cells(i, j)

Rows(� ������)

Columns(� �������)

Sheets(“���”)

Sheets(� �����)

WorkSheet

Range(“A5:A10”). Value = 0 ��� Range(“A5:A10”) = 0 – � �������� ����� A5:A10 ��������� �������� 0.

Cells(2, 4). Value = n ��� Cells(2, 4) = n – � ������, ����������� �� ����������� 2-� ������ � 4-�� ������� (������ � ������� “D2”), ��������� �������� ���������� n.

Xn = Cells(1, 2).Value ��� Xn = Range(“B1”).Value – ���������� Xn ������������� �������� �� ������ B1 �������� �������� �����.

Sheets(2).Activate ������� �������� ���� � �2.

Sheets(“���������”).Delete ������� ���� � ������ “���������”.

Range(«A5:A10»).Clear – �������� �������� ����� A5:A10.

Range(«A2:B10»).Select – �������� �������� ����� A2:B10.

Application.Quit — ���������� ������ � Excel.

Атанас Йонков Блоггер, Веб-разработчик
yonkov.atanas@gmail.com

В этом уроке я покажу Вам самые популярные макросы в VBA Excel, которые вы сможете использовать для оптимизации своей работы. VBA — это язык программирования, который может использоваться для расширения возможностей MS Excel и других приложений MS Office. Это чрезвычайно полезно для пользователей MS Excel, поскольку VBA может использоваться для автоматизации вашей работы и значительно увеличить Вашу эффективность. В этой статье Вы познакомитесь с VBA и я вам покажу некоторые из наиболее полезных, готовых к использованию примеров VBA. Вы сможете использовать эти примеры для создания собственных скриптов, соответствующих Вашим потребностям.

Вам не нужен опыт программирования, чтобы воспользоваться информаций из этой статьи, но вы должны иметь базовые знания Excel. Если вы еще учитесь работать с Excel, я бы рекомендовал Вам прочитать статью 20 формул Excel, которые вам нeобходимо выучить сейчас, чтобы узнать больше о функциональных возможностях Excel.

Я подготовил для вас несколько самых полезных примеров VBA Excel с большой функциональностью, которую вы сможете использовать для оптимизации своей работы. Чтобы их использовать, вам необходимо записать их в файл. Следующий параграф посвящен установке макроса Excel. Пропустите эту часть, если вы уже знакомы с этим.

Table of Contents

Как включить макросы в Excel

В Excel нажмите комбинацию клавиш alt + F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку Microsoft Excel Objects слева и выберите Insert => Module. Это место, где сохраняются макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel как макрос. Из табуляции File => Save as, выберите Save as macro-enabled Workbok (расширение .xlsm) Теперь пришло время написать свой первый макрос!

1. Копирование данных из одного файла в другой.

Очень полезный макрос, поскольку он показывает, как скопировать ряд данных изнутри vba и как создать и назвать новую книгу. Вы можете изменить этот макрос в соответствии с вашими собственными требованиями:

2. Отображение скрытых строк

Иногда большие файлы Excel можно содержать скрытые строки для большей ясности И для лучшего удобства пользователей. Вот один макрос, который отобразит все строки из активной рабочей таблицы:

3. Удаление пустых строк и столбов

Пустые строки в Excel — может быть проблемой для обработки данных. Вот как избавиться от них:

4. Нахождение пустых ячеек

13. Создание сводной таблицы

14. Отправка активного файла по электронной почте

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

15. Вставка всех графиков Excel в презентацию PowerPoint

Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:

16. Вставка таблицы Excel в MS Word

Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

17. Извлечение слов из текста

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

18. Защита данных в MS Excel

Иногда мы хотим защитить данных нашего файла, чтобы только мы могли его изменять. Вот как это сделать с VBA:

Поздравления! Поскольку вы все еще читаете это, вы действительно заинтересованы в изучении VBA. Как вы уже сами видели, язык программирования VBA чрезвычайно полезен и может сэкономить нам много времени. Надеюсь, вы нашли эту информацию полезной и использовали ее, чтобы стать мастером MS Excel, VBA и компьютерных наук в целом.

Илон Маск рекомендует:  Группирование элементов форм
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL