Visual Basic for Applications Visual Basic for Applications (VBA)

Visual Basic for Applications

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

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

создание объектов управления и контроля (диалоговые окна, пиктограммы, меню);
разработка процедур, используемых при вызове объектов.

Прикладные программы на языке VBA оперируют со следующими понятиями:

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

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

в автоматическом режиме как результат построения клавишной макрокоманды;
в неавтоматическом режиме путем создания программного кода. Ниже рассматривается вариант традиционного программирования на языке VBA применительно к Excel.

Основные наборы (классы объектов), с которыми работает программа на языке VBA в среде Excel, представлены в таблице 1.

Таблица 1

Sheets

Workbooks

Worksheets

Charts

Range

Все листы любых типов в рабочей книге

Все текущие открытые рабочие книги

Все таблицы в рабочей книге

Все листы диаграмм в рабочей книге

Диапазон ячеек таблицы (строк, столбцов, несмежных ячеек, трехмерных выделений)

Доступ к компоненту набора осуществляется двумя способами:

по номеру компонента

Пример: Workbooks("Имя- книги").Worksheets(2)

по имени компонента

Пример: Workbooks("Имя- книги.").Worksheets("Имя- листа")

Наборы обладают следующими свойствами:

Count — определяет число компонентов в наборе;
Name — определяет имя компонента;
Parent — определяет имя объекта, включающего набор.

Для добавления и удаления компонентов набора используют методы Add к Delete. Отдельные ячейки таблицы доступны через объект Range и следующие методы Cell, Offset, Union.

VBA использует 11 встроенных типов данных:

Boolean использует 2 байта, или 16 бит, памяти для хранения логических данных; имеет два возможных значения: True и False.

Integer использует 2 байта памяти для хранения целых чисел, в том числе 1 бит для хранения знака числа, 15 бит — для числа.

Long использует 4 байта, или 32 бита, памяти для хранения целого числа двойной точности, в том числе 1 бит —- для хранения знака числа, 31 бит —для числа

Single использует 4 байта для раздельного хранения мантиссы (цифровая часть числа — максимум 7 знаков) и порядка (расположение десятичной точки),

Double использует 8 байтов для раздельного хранения мантиссы (максимум 15 знаков) и порядка.

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

Date использует 8 байтов и предназначен для хранения дат и времени. Даты записываются в виде целых чисел дней, а время — как дробная часть дня.

String использует 1 байт для хранения 1 символа плюс 1 байт для отметки конца ки. Символы хранятся в виде кодов ANSI.

Object использует 4 байта памяти для ссылки на любой объект Visual Basic.

Array определяет список данных любого типа и любой размерности.

Variant используется всегда, когда явно не указан какой-либо другой тип; может соответствовать любому другому разрешенному типу данных.

Основные типы данных VBA

Integer (целое число)

Long (длинное целое число)

Single (число с плавающей точкой обычной точности)

Double (число с плавающей точкой двойной точности)

Boolean (логический)

String (строка символов)

Currency (денежный)

Date (дата)

Object (объект)

Variant (строковые или числовые подтипы)

Целые числа от 0 до 255

Целые числа от –32768 до 32767

Целые числа двойной длины (от
–2147483648 до 2147483647)

От -3.402823E38 до -1.401298E-45 для отрицательных значений;
От 1.401298E-45 до 3.402823E38 для положительных значений;

От -1.79769313486232E308 до
-4.94065645841247E-324 для отрицательных значений;
От 4.94065645841247E-324 до 1.79769313486232E308 для положительных значений;

Логическое значение True или False

От 0 до приблизительно 2 миллиардов

От –922337203685477,5808 до 922337203685477,5807

От 1 января 100 г. до 31 декабря 9999 г.

Любой указатель объекта

Любое числовое или строковое значение

1 на каждый символ

 16

byt

int

lng

sng

dbl

bln

str

cur

dtm

obj

vnt

–

Обозначения типов данных является ключевыми словами языка (и выделяется после набора в редакторе VBA).

Над различными типами данных допустимы различные операции. В VBA имеются три основных типа операций:

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

Операции VBA

Математические операции

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

Приоритеты операций

Вызов функции и скобки

- (смена знака)

*, /

Mod

+, –

>, <, >=, <=, <>, =

Not

And

Xor

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

длина имени не должна превышать 255 символов;
имя не может содержать стандартные разделители (точку, запятую, двоеточие, дефисов, пробелов и т.п.) и следующих символов: %, &, !, @, #, $;
имя может содержать любую комбинацию не запрещенных символов, но начинаться должно с буквы;
имена должны быть уникальны внутри области, в которой они определены;
запрещено использовать имена, совпадающие с ключевыми словами VBA и именами встроенных функций и процедур.

Замечание. Хотя регистр букв (верхний или нижний) в имени не имеет значения, его умелое использование может существенно облегчить понимание содержательной стороны переменной. Вместо плоских и невыразительных имен предпочтительнее использовать имена, которые легче воспринимаются благодаря выделению некоторых входящих в них символов разумным использованием верхнего регистра. Например, представляется более удачным вместо имен “процентнаяставка”, “x-начзнач” использовать “ПроцентнаяСтавка”, “x-НачЗнач”.

Для того чтобы начать оперировать с любой величиной (постоянной или переменной) ее необходимо соответствующим образом описать. Важно, чтобы не только разработчик программы понимал, величины какого типа используются в программе, но и исполнитель программы (компьютер). Второе даже более важно, т.к. если компьютер не будет знать, величина какого типа используется в программе, он будет считать ее величиной универсального типа Variant и отведет для ее хранения в памяти 16 и более ячеек. Это будет приводить к неэффективному использованию памяти и замедлению работы программы.

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

Блок описания переменных имеет следующий синтаксис:

Dim <Имя> [As <Тип>]

Здесь Dim и As – ключевые слова VBA;

<Имя> – имя переменной, удовлетворяющее стандартным правилам именования переменных;

<Тип> – тип данных переменной.

Назначение этого оператора – объявить переменную, т.е. задать ее имя и тип, однако объявление типа может отсутствовать. В этом случае по умолчанию переменная получает тип Variant.

Переменные, описанные с помощью слова Dim на уровне модуля, доступны для всех процедур в данном модуле. Переменные, описанные на уровне процедуры, доступны только в данной процедуре.

Примеры:

Dim N As Integer
– инструкция описывает переменную N типа Integer.
Можно объявить сразу несколько переменных:
Dim Строка As String, Число As Single
– инструкция описывает переменную Строка типа String, переменную Число типа Single.
Dim K1, K2 As Integer
– инструкция описывает переменную K2 типа Integer, переменную K1, тип которой не задан (по умолчанию будет приписан тип Variant).

Альтернативным способом описания переменных некоторых типов может быть использование суффиксов. Например, инструкция

Dim A%, Text$

служит для описания переменной A типа Integer и переменной Text типа String. Последующее использование этих переменных в тексте программы не требует использования суффикса.

Описание констант осуществляется следующим образом:

[Public | Private] Const <Имя> [As <Тип>] = <Выражение>

Public – ключевое слово, используемое на уровне модуля для описания констант, доступных всем процедурам во всех модулях; не допускается в процедурах;

Private – ключевое слово, используемое на уровне модуля для описания констант, доступных только внутри модуля, в котором выполняется описание; не допускается в процедурах;

As – ключевое слово VBA;

<Имя> – имя константы, удовлетворяющее стандартным правилам именования;

<Тип> – один из поддерживаемых типов данных. Для каждой описываемой константы следует использовать отдельное предложение As <Тип>.

<Выражение> – запись, определяющая последовательность действий над величинами; может содержать константы, переменные, знаки операций (за исключением Is), функции.

Примеры:

Const L As Integer = 12345
– инструкция описывает константу L типа Integer, значение которой равно 12345, доступную на уровне процедуры.
Private Const Строка As String = “Большая перемена”
– инструкция описывает константу Строка типа String, доступную на уровне модуля, со значением “Большая перемена”.
Const K1 As Integer = 350, K2 As Integer = 750, K3 = 3678 \ 57
– инструкция описывает константы K1 и K2 типа Integer, константу K3, тип которой не задан (по умолчанию будет приписан тип Variant); все константы доступны на уровне процедуры.

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

[Let] <Имя> =<Выражение>

<Имя> – имя переменной, удовлетворяющее стандартным правилам именования;

<Выражение> – произвольное выражение любого типа;

Let –ключевое слово, которое не является обязательным и чаще всего опускается.

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

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

Переменная требует явного объявления своего имени, которое образуют алфавитно-цифровые символы и специальные знаки. Имя всегда начинается с буквы и представляет собой непрерывную последовательность символов, но не более 255 и не содержит специальных символов.

Операторы:

Оператор присваивания. Данный тип оператора служит для присвоения начальных значений, записи результата вычисления в переменную, изменения значений. Оператор присваивания имеет следующую структуру построения:

= , где - имя переменной; - формула.

Переменная слева от знака "=" может быть простой переменной, элементом массива или свойством объекта. Формула состоит из переменных, констант, операций и функций.

Процедуры:

Программные модули VBA состоят из процедур (одной или более). Процедура - минимальный модуль в составе прикладной программы на языке VBA. Процедуры имеют стандартное оформление:

Sub <имя_процедуры> (аргументы)

тело процедуры (операторы)

End Sub

Оператор Sub - объявление процедуры, задается имя, указывается состав аргументов, передаваемых при вызове процедуры из программы. Каждому оператору Sub обязательно соответствует End Sub.

Тело процедуры - набор последовательно выполняемых операторов на языке VBA.

Различают 4 типа процедур:

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

Запустить редактор VBA в Excel позволяет следующая команда Сервис /Макрос /Редактор Visual Basic.

После запуска на экране появляется интегрированная среда разработки Visual Basic (рис.2.1), она состоит из:

[pic]

Рисунок 1 - Среда разработки VBA

Окно проекта(Project) показывает состав вашего проекта. Все объекты, составляющие приложение, объединяются в проект. Простейший проект содержит одну форму.

Конструктор форм (View Object). В это окно выводится либо изображение формы, либо окно программы (кода).

Форма(UserForm). По сути дела она представляет собой окно, в котором можно размещать различные управляющие элементы при создании приложения.

Панель элементов(ToolBox) содержит компоненты, из которых складывается интерфейс приложения. Каждый элемент переносится на форму простым перетаскиванием или двойным щелчком мыши. Затем можно изменять его размеры и перемещать.

Окно свойств(Properties Window) отражает свойства объектов. Каждый элемент – форма, кнопка и т.д. – считается объектом и имеет свойства. Список свойств разделен на две колонки. В левой находятся свойства объекта, а в правой – значения свойств, установленные по умолчанию (их можно выбрать и изменить).

При работе с модулем на экран выводится новая панель инструментов Visual Basic (рис.2.2):

Р [pic]
исунок 2 - Панель инструментов Visual Basic

Организация ввода-вывода. Структура программы

Редактор VBA

Будем осуществлять написание собственного кода на языке VBA в среде редактора Visual Basic Excel.

Для входа в среду VBA Excel можно воспользоваться одним из следующих способов:

выполнить команду Tool (Сервис) / Macro (Макрос) / Visual Basic Editor (Редактор Visual Basic);
воспользоваться комбинацией клавиш +;
нажать кнопку Visual Basic Editor (Редактор Visual Basic) [pic] панели инструментов Visual Basic.

Возвратиться из редактора VBA в рабочую книгу Excel можно с помощью команды Viev (Вид) / Microsoft Excel (Microsoft Excel), комбинации клавиш + или нажатием соответствующей кнопки [pic] панели инструментов Стандарт.

Структурным элементом программы, написанной на языке VBA, является модуль – совокупность объявлений и процедур, объединенных в единое целое. По своему предназначению модули делятся на два типа: стандартные модули и модули объектов. К стандартным модулям относятся те, которые содержат макросы. К модулям объектов относятся модули, связанные с рабочей книгой, рабочими листами, формами и модули класса. Мы будем использовать только стандартные модули.

Чтобы осуществить вставку модуля необходимо находясь в среде VBA выполнить команду Insert (Вcтавить) / Module (Модуль).

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

модуль;
область описания;
процедура;
код;
оператор.

При создании и редактировании кода удобно пользоваться командой Insert (Вcтавить) / Procedure (Процедуру). В появившемся окне необходимо выбрать функцию или подпрограмму и задать ее имя. После этого будут автоматически сформированы операторы начала и конца процедуры и можно переходить непосредственно к набору операторов процедуры. Для набора следующей процедуры в том же модуле необходимо повторить команду Insert (Вcтавить) / Procedure (Процедуру). Если нужно создать новый модуль повторяется команда Insert (Вcтавить) / Module (Модуль).

Проверка правописания осуществляется на этапе компиляции командой Debug (Отладка) / Compile VBAProject (компилировать). Для запуска программы требуется выполнить команду Run (Запуск) / Run Sub/UserForm (Запуск подпрограммы/UserForm) или нажать клавишу или соответствующую кнопку [pic] панели инструментов.

В появившемся окне следует выбрать имя нужной программы и щелкнуть по кнопке Run (Выполнить).

Для того чтобы сохранить или открыть созданную ранее программу используют стандартные средства Excel, например, соответствующие пункты меню Файл: Сохранить как… , Открыть.

Запуск или редактирование существующего программного кода после запуска Excel может быть осуществлен после последовательного выполнения команд: Tool (Сервис) / Macro (Макрос) / Макросы. В появившемся диалоговом окне следует указать имя процедуры и нажать кнопку, отвечающую за выполнение требуемого действия (выполнить или изменить).

Структура программного кода

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

[Option Explicit]

[Private | Public] [Static] Sub <Имя> ([<Список аргументов>])

[Dim <Имя> [As <Тип>]]

[Const <Имя> [As <Тип>] = <Выражение>]

[Инструкции]

[Exit Sub]

[Инструкции]

End Sub

В этой записи:

Option Explicit – инструкция, предписывающая явное описание всех переменных, встречающихся в программе;

Public – ключевое слово, которое указывает, что процедура доступна для всех других процедур во всех модулях;

Private – ключевое слово, указывающее, что процедура доступна для других процедур только того модуля, в котором она описана;

Static – ключевое слово, указывающее, что локальные переменные процедуры сохраняются в промежутках времени между вызовами этой процедуры;

<Имя> – имя процедуры, удовлетворяющее стандартным правилам именования;

<Список аргументов> – список переменных, представляющий аргументы, которые передаются в процедуру при ее вызове. Имена переменных разделяются запятой.

Dim <Имя> [As <Тип>] –блок описания переменных;

Const <Имя> [As <Тип>] = <Выражение> –блок описания констант;

Инструкции – любой набор любых команд VBA;

Exit Sub – инструкция, выполнение которой приводит к немедленному выходу из процедуры;

Sub, End Sub –служебные слова VBA.

Замечание. Использование инструкции Option Explicit является простейшим средством предотвращения случайных ошибок. В частности, она позволяет избежать следующей трудно отслеживаемой ошибки. Предположим, что в программе используется переменная с именем “Ссуда”, а при наборе имени этой переменной где-то в программе вместо русской буквы “с” по ошибке набрана латинская буква “с”. Визуально эти имена ничем не отличаются друг от друга, но воспринимаются компилятором VBA как имена разных переменных. Если используется инструкция Option Explicit, а значит, имеет место явное описание переменной “Ссуда”, то компилятор укажет на переменную “Ссуда” с латинской буквой “с”, как на не описанную и ошибка будет найдена.

Встроенные диалоговые окна

В программном коде на VBA ввод и вывод организуется с помощью встроенных диалоговых окон: окон ввода и окон сообщений. Окно сообщений (процедура MsgBox) выводит простейшие сообщения для пользователя, а окно ввода (Функция InputBox) обеспечивает ввод информации.

Функция InputBox выводит на экран диалоговое окно, содержащее сообщение и поле ввода, устанавливает режим ожидания ввода текста пользователем или нажатия кнопки. Затем возвращает значение типа String, содержащее текст, введенный в поле. Синтаксис:

InputBox (promt, [, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Аргументы:

prompt – строковое выражение, отображаемое как сообщение в диалоговом окне.

title – строковое выражение, отображаемое в строке заголовка диалогового окна. Если этот аргумент опущен, в строку помещается имя приложения.

default – строковое выражение, отображаемое в поле ввода как используемое по умолчанию, если пользователь не введет другую строку. Если этот аргумент опущен, поле ввода изображается пустым.

xpos – числовое выражение, задающее расстояние по горизонтали между левой границей диалогового окна и левым краем экрана. Если этот аргумент опущен, диалоговое окно выравнивается по центру экрана по горизонтали.

ypos – числовое выражение, задающее расстояние по вертикали между верхней границей диалогового окна и верхним краем экрана. Если этот аргумент опущен, диалоговое окно помещается по вертикали примерно на одну треть высоты экрана.

helpfile – строковое выражение, определяющее имя файла справки, содержащего справочные сведения о данном диалоговом окне. Если этот аргумент указан, необходимо также наличие аргумента context.

context – числовое выражение, определяющее номер соответствующего раздела справочной системы. Если этот аргумент указан, необходимо также наличие аргумента helpfile.

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

Синтаксис:

MsgBox (prompt, [, button][, title] [, helpfile, context])

Аргументы:

prompt – строковое выражение, отображаемое как сообщение в диалоговом окне.

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

Значения аргумента, определяющие отображаемые кнопки:

VbOKCancel

VbAbortRetryIgnore

VbYesNoCancel

VbYesNo

VbRetryCancel

OK, Отмена

Стоп, Повтор, Пропустить

Да, Нет, Отмена

Да, Нет

Повтор, Отмена

Значения аргумента, определяющие отображаемые значки

VbQuestion

VbExclamation

VbInformation

[pic]

Значения аргумента, определяющие основную кнопку окна

VbDefaultButton2

VbDefaultButton3

VbDefaultButton4

256

512

768

Демонстрационные примеры

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

Sub Тест()

Dim msg As String, style As Integer, title As String, x

Dim default

msg = “Мои первые шаги в VB!”

title = “Cообщение с одной кнопкой”

MsgBox msg, , title

title = “Cообщение с двумя кнопками”

style = vbYesNo

MsgBox msg, style, title

title = “Cообщение с двумя кнопками и значком”

style = vbYesNo + vbCritical

MsgBox msg, style, title

msg = “Введите число, кратное 16, не больше 64!”

title = ”Формируем окно для ввода информации ”

default = 16

x = InputBox (msg, title, default)

msg = “ Мои первые шаги в VB!”

title = “ Мое сообщение”

style = vbYesNo + x

MsgBox msg, style, title

End Sub

В результате выполнения приведенной ниже программы на экране последовательно будут появляться окна. В таблице отражены окна, соответствующие определенным участкам кода. Отметим, что значок, который будет изображен в последнем окне, определяется значением переменной x, вводимым с клавиатуры при появлении окна “Формируем окно для ввода информации”. В нашем случае было введено значение, равное 48.

title = “Cообщение с одной кнопкой”

MsgBox msg, , title

[pic]

title = “Cообщение с двумя кнопками”

style = vbYesNo

MsgBox msg, style, title

[pic]

title = “Cообщение с двумя кнопками и значком”

style = vbYesNo + vbCritical

MsgBox msg, style, title

[pic]

msg = “Введите число, кратное 16, не больше 64!”

title = ”Формируем окно для ввода информации ”

default = 16

x = InputBox (msg, title, default)

[pic]

msg = "Мои первые шаги в VB!"

title = "Мое сообщение"

style = vbYesNo + x

MsgBox msg, style, title

[pic]

Приведенная программа может быть переписана следующим образом:

Sub Тест1()

Dim x as Byte

MsgBox “Мои первые шаги в VB!”, , “Cообщение с одной кнопкой”

MsgBox “Мои первые шаги в VB!”, vbYesNo, “Cообщение с двумя кнопками”

MsgBox “Мои первые шаги в VB!”, vbYesNo + vbCritical, “Cообщение с двумя кнопками и значком”

x = InputBox (“Введите число, кратное 16, не больше 64!”, ”Формируем окно для ввода информации ”, 16)

MsgBox “ Мои первые шаги в VB!”, vbYesNo + x, “ Мое сообщение”

End Sub