Как сделать модуль в vba

Добавил пользователь Валентин П.
Обновлено: 10.09.2024

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

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

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

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.

В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)

:

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

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

Давайте разберем приведенный выше в качестве примера макрос Zamena:

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

Способ 2. Запись макросов макрорекордером

Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:

Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.

Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:

Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.

После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :

Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.

Создание кнопки для запуска макросов

Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:

В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)

Выберите объект Кнопка (Button) :

Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.

Создание пользовательских функций на VBA

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

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

Visual Basic поддерживает три типа модулей, которые могут быть включены в проект и использованы:

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

Структура модуля VBA включает два неявных (т.е. не требующих специального описания) раздела: общий (General) и объявлений (Declarations). В общем разделе задаются параметры среды (Option Base, Option Explicit), приводятся описания глобальных переменных, констант и типов. Раздел объявлений предназначен для описания процедур и функций.

Области видимости

VBA поддерживает две области видимости для переменных и подпрограмм: локальную и глобальную.

Локальные переменные определены на уровне подпрограммы с помощью ключевых слов Dim или Static. Они доступны только внутри этой подпрограммы и по выходу из нее уничтожаются. Глобальные переменные объявляются на уровне модуля. Такие переменные доступны:

для всех подпрограмм модуля, в котором они объявлены (при объявлении с ключевым словом Private, либо Dim);
для всего приложения – при объявлении с ключевым словом Public;

По умолчанию используется уровень проекта.

При описании локальных переменных можно использовать ключевое слово Static. Такие переменные являются статическими и сохраняют значения между вызовами.

Встроенные функции VBA

В языке программирования VBA предусмотрено несколько десятков встроенных функций. Они доступны в любой программе на языке VBA, при этом безразлично, в среде какого программного продукта мы находимся — Excel, Word, Access или, к примеру, AutoCAD. Используются они очень активно, и во многих ситуациях без них не обойтись. Встроенные функции обычно группируют по назначению: математические, строковые, преобразования типов, логические и т.п. В справке по VBA имеется подробная информация о всех встроенных функциях. Здесь же приведем краткое описание только некоторых.

Функции приведения типов

Используются для конвертации типов данных. Вот перечень этих функций: CBool(), CByte(), CCur(), CDate(), CDbl(), CDec(), CInt(), CLng(), CSng(), CStr(), CVar(), CVDate(), CVErr().

Просмотреть, что в итоге получилось, можно при помощи функции TypeName(), например:

Кроме того, еще несколько полезных для конвертации функций:

Str() — позволяет перевести числовое значение в строковое, при этом вставляет пробел впереди для положительных чисел.
Val() — извлекает из строки числовое значение (до первого нечислового символа, кроме точки). Очень удобно, когда вперемежку с числовыми данными прописываются единицы измерения или валюта.

Строковые функции

Left(), Right(), Mid() — получить фрагмент строки слева, справа или из середины исходной строки соответственно.
Len() — получить число символов в строке.
LCase() и UCase() — перевести строку в нижний и верхний регистры соответственно.
LSet() и RSet() — заполнить строку символами без изменения длины (соответственно слева и справа). Лишние символы обрезаются, на место недостающих подставляются пробелы.
LTrim(), RTrim(), Trim() — убрать пробелы соответственно слева, справа или и слева, и справа.
Replace() — заменить в строке одну последовательность символов на другую.
StrComp() — сравнить две строки.
StrReverse() — "перевернуть" строку, разместив ее символы в обратном порядке.

Математические функции

Функций для работы с числовыми значениями в VBA очень много. Приведем только некоторые из них.

ABS() — эта функция возвращает абсолютное значение переданного ей числа.
Int(), Fix() и Round() позволяют по разному округлять числа: Int возвращает ближайшее меньшее целое, Fix() отбрасывает дробную часть, Round() округляет до указанного количества знаков после запятой.
Rnd() и команда Randomize используются для получения случайных значений. Обычный синтаксис при применении Rnd выглядит так:

Перед вызовом функции Rnd() следует выполнить команду Randomize для инициализации генератора случайных чисел.

Функции для работы с датой и временем

Основные функции VBA для работы с датой/временем:

Date() — возвращает текущую системную дату.
Time() возвращает текущее системное время, а Now() — дату и время вместе.
DateAdd() — возможность добавить к дате указанное количество лет, кварталов, месяцев и так далее — вплоть до секунд.
DateDiff() — возможность получить разницу между датами.
DatePart() — возвращает указанную часть даты (например, только год, только месяц или только день недели).
DateSerial() — формирует значение даты на основе передаваемых символьных значений. То же самое делает DateValue(), отличия — в формате принимаемых значений. Аналогичным образом (для времени) работают TimeSerial() и TimeValue().
Day() (а также Year(), Month(), Weekday(), Hour(), Minute(), Second()) — специализированные заменители функции DatePart(), которые возвращают нужную часть даты.
MonthName() — возвращает имя месяца словами по его номеру. Возвращаемое значение зависит от региональных настроек. Если они русские, то вернется русское название месяца.
Timer() — возвращает количество секунд, прошедших с полуночи.

Функции взаимодействия с пользователем

Формат функции MsgBox:

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

Листинг 17. Использование MsgBox

При выполнении этого макроса, когда пользователь выбирает кнопку Yes или No в переменной res сохраняется число, соответствующее выбранной кнопке.

Вместо возвращаемых функцией MsgBox целочисленных значений удобнее пользоваться предопределенными константами VBA. В таб. 11 приведены возвращаемые значения констант функции MsgBox.

Таблица 11. Возвращаемые значения функции MsgBox

Константа	Означает, что пользователь нажал кнопку
vbAbort	Стоп (Abort)
vbCancel	Отмена (Cancel)
vbIgnore	Пропустить (Ignore)
vbNo	Нет (No)
vbOk	Ок
vbRetry	Повтор (Retry)
vbYes	Да (Yes)

Дополним код листинга 17 проверкой возвращенного значения (листинг 18).

Листинг 18. Проверка возращаемого значения MsgBox

Приведем пример использования функции InputBox для получения имени пользователя.

В результате выполнения этого макроса на экран последовательно выводятся диалоговые окна ввода и вывода (рис. 5).

Цена на разработку базы данных типа Microsoft Access составляет определяется техническим заданием, где указан предварительный бюджет работы. Для работы приложения потребуется установить Microsoft Office c Access.

Разработка программы на Windows

Под таким приложением понимается программа, которая не требует при запуске установки дополнительных программ. Цена на разработку настольных приложений для Microsoft Windows также определяется по техническому заданию. Если задание состоит из общих фраз, то разработка идет по этапам

Многие наверняка слышали про модули классов, но не все их используют. На самом деле довольно многие программирующие на VBA за все время программирования прекрасно обходятся без применения модулей классов. Т.к. VBA не является языком объектно-ориентированного программирования(ООП) в строгом смысле слова, то пользовательские классы здесь не обязательны и как следствие не так уж и часто используются при разработке. Это не значит, что VBA не содержит модулей классов: модули книги, листов, пользовательские формы - все это модули классов. Многие, кстати, используют их даже не зная того, что используют именно модули классов. Т.к. модуль листа, книги и формы - это модуль класса, то почти каждый, кто работал с формой работал с модулем класса. В чем их большая польза - с их помощью можно отслеживать различные события объектов. Для форм это события самой формы или любого её элемента - например CommandButton_Click или TextBox_Change. Но мы сейчас рассмотрим лишь тот тип модулей, который в VBA обычно называют модулем класса - Class Module.

Модуль класса(Class Module) – это модуль, содержащий программные коды, которые реализуют работу пользовательских классов. В подавляющем большинстве случаев создается специально для отслеживания событий различных объектов. Создается так же, как и любой другой объект проекта: в окне проводника объектов щелкаем правой кнопкой мыши на нужном проекте-Insert-Class Module

Но прежде чем создать модуль, необходимо понять, что мы будем в нем хранить и для чего он нам. Возьмем для примера самую распространенную проблему: на форме создано несколько ТекстБоксов и необходимо отследить событие ввода данных в эти ТекстБоксы. Обычно делается все просто - для каждого ТекстБокса прописывается отслеживание события:

Private Sub TextBox1_Change() MsgBox "Изменено значение TextBox1" End Sub Private Sub TextBox2_Change() MsgBox "Изменено значение TextBox2" End Sub Private Sub TextBox3_Change() MsgBox "Изменено значение TextBox3" End Sub 'и т.д.

С одной стороны - все верно. А с другой: что если таких текстбоксов у нас не 3, а 43? Не очень удобно для каждого событие прописывать. Да и читабельность такой "портянки" кода тоже значительно падает.
Или другая ситуация - необходимо "на ходу" создать ТекстБоксы на форме и в дальнейшем отслеживать их события. Как тут быть? Ведь раз ТексБоксов еще нет - то и события в форме для них не создать. Создание для них кодов обработки событий заранее ничего не даст - они не будут связаны с самими объектами, поэтому и пытаться даже не стоит. Почему так - при создании элемента вручную VBE делает за нас всю грязную работу - он сам ассоциирует созданный объект с событиями, предназначенные для него заранее. Если же создать объект программно - то часть грязной работы придется делать самим. И создание модуля класса, с описанием в нем объекта ТекстБокс и его событий, как раз очень даже подойдет.
Рассмотрим сразу оба случая. Что нам для этого потребуется:

для начала создать модуль класса с именем clsmTxtBxes(Insert-Class Module)
создать стандартный модуль с именем mMain(Insert-Module)
ну и сама форма тоже не лишняя(Insert-UserForm). У меня форма называется frmTest.
очень желательно наличие у вас опыта написания хотя бы простейших процедур. Иначе может показаться все очень сложным и непонятным.

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

Tips_Macro_UseClassModules.xls (63,5 KiB, 5 458 скачиваний)

Для начала создадим на нашей форме frmTest 4 ТекстБокса, не меняя их имена(по умолчанию они будут TextBox1, TextBox2, TextBox3, TextBox4). Это для того, чтобы понять как применить модули класса к уже созданным ранее на форме элементам.
Далее в стандартный модуль mMain поместим следующий код:

Option Explicit Public aoTxtBxes(1 To 8) As New clsmTxtBxes Sub Show_Form() frmTest.Show End Sub

aoTxtBxes - массив, который будет содержать до 8 ТекстБоксов. Объявляется как Public (чтобы был доступен из любого модуля проекта. Подробнее в статье: Что такое переменная и как правильно её объявить?). Обращаю внимание, что данный массив объявлен как созданный нами модуль класса - As clsmTxtBxes. Это обязательное условие. Если у вас модуль класса называется ClassModule1, то и объявлять aoTxtBxes следует соответственно:

Public aoTxtBxes(1 To 8) As New ClassModule1

но я не приветствую подобный подход, т.к. имя ClassModule1 ни о чем нам не говорит, в то время как clsmTxtBxes сразу дает понять, что там мы обрабатываем ТекстБоксы. Хотя это дело вкуса. Если в одном модуле класса собраны различные событийные процедуры для разных типов( TextBox , ComboBox , ListBox и т.д.) - то конечно, имя лучше дать более общее.
Теперь в созданный модуль класса clsmTxtBxes запишем создание объекта и код, который хотим применить для всех наших ТекстБоксов:

Option Explicit Public WithEvents oTxtBx As MSForms.TextBox 'событие изменения текста в TextBox-ах Private Sub oTxtBx_Change() MsgBox "Вы изменили значение " & oTxtBx.Name, vbInformation, "Информационное окно" End Sub

Завершающий этап - создаем код в модуле формы frmTest , который создаст недостающие ТекстБоксы и свяжет их и ранее созданные с модулем класса:

Option Explicit Private Sub UserForm_Initialize() Dim i As Integer 'Присваиваем последовательно значениям массива aoTxtBxes значения объектов, существующих на форме For i = 1 To 4 Set aoTxtBxes(i).oTxtBx = Me.Controls("TextBox" & i) Next i 'создаем 4 своих TrxtBox-а помимо имеющихся на форме и так же заносим в массив aoTxtBxes For i = 5 To 8 Set aoTxtBxes(i).oTxtBx = Me.Controls.Add("Forms.TextBox.1", "TextBox" & i) 'задаем позицию нового TextBox aoTxtBxes(i).oTxtBx.Left = 100 aoTxtBxes(i).oTxtBx.Top = Me.Controls("TextBox" & i - 4).Top Next i End Sub

Кратко описать, что делает эта процедура, можно так:

Если необходимо больше ТекстБоксов обработать - увеличиваем верхнюю границу массива aoTxtBxes(если хотим вместить 20 текстбоксов - Public aoTxtBxes(1 To 20) As New clsmTxtBxes ). Если заранее неизвестно количество - либо задаем с запасом, либо объявляем aoTxtBxes как динамический массив( Public aoTxtBxes() As New clsmTxtBxes ), а границы определяем в процессе(посредством ReDim Preserve ). Но это уже совершенно другая тема.

Me.Controls.Add("Forms.TextBox.1", "TextBox" & i)

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

Читайте также: