Основы excel vba: Справочник по excel Visual Basic для приложений (VBA)

Содержание

Основы программирования на Visual Basic for Application (VBA) в Excel

Содержание:

Вкладка разработчика;
Редактор VBA;
Написание простой процедуры;
Браузер объектов;
Ссылка на процедуру.

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

Написание кода Ваших процедур и редактирование макросов производится в редакторе 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) к дереву выбранного проекта добавится вновь созданный модуль.

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

Sub Моя_процедура()
MsgBox "Привет пользователь!"
End Sub

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Начало_Процедуры
  Любой код
  Первая строка дублирующего кода
  Любой код
  Последняя строка дублирующего кода
  Любой код
  Первая строка дублирующего кода
  Любой код
  Последняя строка дублирующего кода
  Любой код
Конец_Процедуру

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

Начало_Главной_Процедуры
  Любой код
  Ссылка на Процедуру_дублирующегося_кода
  Любой код
  Ссылка на Процедуру_дублирующегося_кода
  Любой код
Конец_Главной_Процедуру
Начало_Процедуры_дублирующегося_кода
  Любой код
Конец_Процедуры_дублирующегося_кода

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

Call Макрос1
'Следующая строка идентична предыдущей
Макрос1

Новые статьи:

Критерий Манна-Уитни
Подключение MySQL в Excel
Подключение Excel к SQL Server

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

Добавить комментарий

Тренинг «Программирование макросов на VBA в Excel» (3 дня)

Автоматизируйте рутину в Excel с помощью макросов. Не хватает каких-то нужных вам функций в Excel? Напишите их сами!

Для кого этот тренинг

Этот тренинг будет интересен всем, кто по роду деятельности сталкивается в работе с однообразными повторяющимися операциями в Microsoft Excel и хочет автоматизировать их с помощью макросов, написанных на языке Visual Basic (VBA). Фактически, знание принципов и основ подобного программирования позволит решать в среде Excel и Office любые задачи. От простых, но эффективных макросов «на каждый день», помогающих рядовым пользователям в их работе, до серьезных проектов корпоративного уровня.

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

После этого тренинга вы…

Сможете самостоятельно писать с нуля макросы для автоматизации ваших типовых задач.

Будете знать и уметь использовать более сотни основных команд и функций языка Visual Basic.

Будете ясно понимать внутренне устройство Excel с точки зрения программирования, чтобы писать простой и понятный код.

Если придется, то будете способны разобраться в коде чужих макросов и редактировать их «под себя».

Программа тренинга

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

В этом модуле мы рассмотрим необходимые основы и те немногие теоретические знания, которые надо знать, чтобы начать программировать на VBA в Microsoft Excel.

Основные понятия и термины, чтобы говорить на одном языке.

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

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

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

Этот модуль посвящен подробному разбору основного инструмента программиста на VBA в Microsoft Excel — среды программирования и редактора кода Visual Basic Editor (VBE).

Основные элементы окна VBE — их назначение, использование, горячие клавиши и приемы ускорения работы.

Структура проекта VBA Project — как устроена книга Excel с точки зрения программирования, где и как может храниться код VBA, в чем различия и особенности использования модулей разного типа, что такое формы и т.д.

Два основных типа макросов (процедуры Sub и функции Function) и их особенности и различия.

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

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

Теоретический минимум (иерархия объектов, коллекции, свойства, методы и события).

Получение справки по объектам (встроенная справка, интернет).

Упрощенные ссылки на объекты (как использовать удобную сокращенную форму записи ссылок на объекты).

Подробный разбор свойств, методов и событий объектов Workbook и Worksheet.

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

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

Объявление переменных и констант (правила выбора имен для переменных, типы данных, области видимости переменных).

Команды ввода-вывода, то есть различные способы организации диалога с пользователем (MsgBox, InputBox, Application.InputBox, Debug.Print и т.д.)

Математические и текстовые операторы для обработки данных

Способы проверки условий (конструкции if…then, select case и т.д.)

Способы организации циклов (различные виды безусловных циклов for…next, циклы по условию do…loop и т.д.)

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

Безусловно, почти любой макрос создается для взаимодействия с отдельными ячейками и диапазонами листа Excel. В этом модуле мы подробно разберем все свойства и методы основных объектов почти любого макроса: Workbook, Worksheet и Range — и работу с ними на практических задачах из реальной жизни.

Объект Workbook, его свойства, методы и события. Создание, сохранение и открытие файлов.

Коллекции Worksheets и Sheets. Основные операции с листами с помощью кода VBA.

Объект Range, его свойства, методы и события. Выполнение различных операций над диапазонами.

Коллекция Cells, варианты и особенности ее использования в коде.

Свойство Selection, обработка выделенных областей на листе.

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

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

Концепция событий Microsoft Excel. Последовательность возникновения событий. Функции обработки событий.

События книги, примеры использования в реальных проектах.

События листа, примеры.

Сомневаетесь?

«Я лучше изучу все это по книгам»

Тогда придется прочитать много толстых книг. И изучить справку по Visual Basic (на английском). Если программирование для вас хобби и вы готовы полгода по вечерам ковыряться в книжках, собирая все грабли лично, то — ради бога (я сам такой и очень хорошо вас понимаю, поверьте). Если же макросы для вас — способ решить рабочие проблемы, то эффективнее тренинга еще ничего не придумали. За 3 дня вы научитесь использовать их в реальных офисных задачах.

«Я никогда не программировал(а), это все очень сложно, вряд ли у меня получится :(»

На самом деле, прошлый опыт программирования (или его отсутствие) не играет большой роли. Даже если бы вы когда-то программировали в школе, то к сегодняшнему дню наверняка бы уже все забыли 🙂 Visual Basic — один из самых простых в освоении языков, не требующий предварительной подготовки. А у нас отточенная годами преподавания проверенная методика, файлы-примеры из реальной офисной жизни, наглядные конспекты, слайды и шпаргалки, а за плечами — богатый опыт реальных задач и проектов.

«У меня нет времени проходить этот тренинг — очень много работы»

А свободного времени и не появится, если продолжать в том же духе. Объем работы и ее сложность будут только расти. Чтобы ее выполнять нужно менять подход, инструменты, логику. Тупым топором много не наработаешь. Наш тренинг как раз и нужен, чтобы «заточить топор» и автоматизировать рутину раз и навсегда.

Бонусы

Каждый участник тренинга получит:

подробную презентацию-конспект по всем темам курса

готовые образцы всех макросов, примеры и функции, которые вы легко сможете адаптировать к своим задачам

удобные шпаргалки по горячим клавишам и всем функциям Excel

полную версию надстройки PLEX, которая добавит в ваш Excel больше сотни новых полезных функций

Заинтересовались? Хотите получить более подробную информацию о условиях проведения такого тренинга? Тогда выберите ваш вариант:

← Назад к списку тренингов

Рабочая книга и объект рабочего листа в Excel VBA (в простых шагах)

Иерархия объектов | Коллекции | Свойства и методы

Узнайте больше об объекте Workbook и Worksheet в Excel VBA .

Иерархия объектов

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

Мать всех объектов — сам Excel. Мы называем это объектом приложения. Объект приложения содержит другие объекты. Например, объект Workbook (файл Excel). Это может быть любая рабочая книга, которую вы создали. Объект Workbook содержит другие объекты, например объект Worksheet. Объект Worksheet содержит другие объекты, такие как объект Range.

В главе «Создание макроса» показано, как запустить код, нажав кнопку команды. Мы использовали следующую строку кода:

Range(«A1»).Value = «Hello»

но на самом деле мы имели ввиду:

Application.Workbooks(«create-a-macro»).Worksheets(1).Range(«A1»). Значение = «Привет»

Примечание: объекты соединены точкой. К счастью, нам не нужно добавлять строку кода таким образом. Это потому, что мы разместили нашу командную кнопку в файле create-a-macro.xlsm на первом рабочем листе. Имейте в виду, что если вы хотите что-то изменить на разных листах, вы должны включить объект Worksheet. Читай дальше.

Коллекции

Возможно, вы заметили, что рабочие книги и рабочие листы имеют множественное число. Это потому, что они коллекции. Коллекция Workbooks содержит все открытые в данный момент объекты Workbook. Коллекция Worksheets содержит все объекты Worksheet в книге.

Вы можете ссылаться на элемент коллекции, например, на отдельный объект Worksheet, тремя способами.

1. Использование имени листа.

Рабочие листы («Продажи»). Диапазон («A1»). Значение = «Здравствуйте»

2. Использование порядкового номера (1 — это первый рабочий лист, начинающийся слева).

Рабочие листы(1).Range(«A1»).Value = «Hello»

3. Использование CodeName.

Sheet1.Range(«A1»).Value = «Hello»

Чтобы увидеть кодовое имя листа, откройте редактор Visual Basic. В Project Explorer первым именем является CodeName. Второе имя — это имя рабочего листа (Продажи).

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

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

Теперь давайте рассмотрим некоторые свойства и методы коллекции Workbooks и Worksheets. Свойства — это то, что есть у коллекции (они описывают коллекцию), а методы что-то делают (выполняют действие с коллекцией).

Поместите кнопку на рабочий лист и добавьте строки кода:

1. Метод Add коллекции Workbooks создает новую рабочую книгу.

Workbooks.Add

Примечание. Метод Add коллекции Worksheets создает новый рабочий лист.

2. Свойство Count коллекции Worksheets подсчитывает количество листов в книге.

MsgBox Worksheets.Count

Результат при нажатии командной кнопки на листе:

Примечание. Свойство Count коллекции Workbooks подсчитывает количество активных книг.

Объект диапазона в Excel VBA (в простых шагах)

Объект Range , который является представлением ячейки (или ячеек) на вашем рабочем листе, является наиболее важным объектом Excel VBA . В этой главе дается обзор свойств и методов объекта Range. Свойства — это то, чем обладает объект (они описывают объект), а методы что-то делают (выполняют действие с объектом).

Примеры диапазонов

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

Диапазон («B3»). Значение = 2

Результат при нажатии командной кнопки на листе:

Код:

Диапазон («A1:A4»). Значение = 5

Результат:

Код:

Диапазон(«A1:A2,B3:C4»).Значение = 10

Результат:

Примечание. Чтобы сослаться на именованный диапазон в коде Excel VBA, используйте строку кода, например это:

Диапазон («Цены»). Значение = 15

Ячейки

Вместо Диапазона вы также можете использовать Ячейки. Использование ячеек особенно полезно, когда вы хотите перебирать диапазоны.

Код:

Ячейки(3, 2).Значение = 2

Результат:

Объяснение: Excel VBA вводит значение 2 в ячейку на пересечении строки 3 и столбца 2.

Код:

Range(Cells(1, 1), Cells(4, 1)).Value = 5

Результат:

Объявление объекта Range

Вы можете объявить объект Range, используя ключевые слова Dim и Set.

Код:

Размерный пример Как диапазон
Установите пример = Диапазон («A1: C4»)

пример. Значение = 8

Результат:

Select

Важным методом объекта Range является метод Select. Метод Select просто выбирает диапазон.

Код:

Размерный пример Как диапазон
Set example = Range(«A1:C4»)

example.Select

Результат:

Примечание: чтобы выбрать ячейки на другом листе, вы должны сначала активировать этот лист. Например, следующие строки кода выбирают ячейку B7 на третьем рабочем листе слева.

Рабочие листы(3).Активировать
Worksheets(3).Range(«B7»).Select

Rows

Свойство Rows предоставляет доступ к определенной строке диапазона.

Код:

Размерный пример Как диапазон
Set example = Range(«A1:C4»)

example.Rows(3).Select

Результат:

Примечание: граница только для иллюстрации.

Столбцы

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

Код:

Размерный пример Как диапазон
Set example = Range(«A1:C4»)

example. Columns(2).Select

Результат:

Примечание: рамка только для иллюстрации.

Копировать/Вставить

Метод Копировать и Вставить используется для копирования диапазона и вставки его в другое место на рабочем листе.

Код:

Диапазон («A1:A2»). Выберите
Selection.Copy

Range(«C3»).Select
ActiveSheet.Paste

Результат:

Хотя это разрешено в Excel VBA, гораздо лучше использовать приведенную ниже строку кода, которая делает то же самое.

Range(«C3:C4»).Value = Range(«A1:A2»).Value

Clear

Чтобы очистить содержимое диапазона Excel, можно использовать метод ClearContents.

Range(«A1»).ClearContents

или просто используйте:

Range(«A1»).Value = «»

Примечание: используйте метод Clear для очистки содержимого и формата диапазона. Используйте метод ClearFormats только для очистки формата.

Подсчет

С помощью свойства Подсчет можно подсчитать количество ячеек, строк и столбцов диапазона.