Table Of Content1. ХАРАКТЕРИСТИКА ЯЗЫКА И РАБОТА С ОБЪЕКТАМИ EXCEL
1.1. ОСНОВНЫЕ ПОНЯТИЯ
1.1.1. Введение
Visial Basic for Applications (VBA) - система визуального программи-
рования для создания прикладных программ в среде Microsoft (MS) Office.
VBA является общей языковой платформой для всех приложений MS
Office. Язык VBA является производным от языка Visual Basic (VB). На-
значением VBA является совместная работа с прикладными программа-
ми, а с помощью VB можно разрабатывать самостоятельные приложения.
Таким образом, проекты VBA выполняются только с помощью приложе-
ний, поддерживающих VBA: обычно программы на VBA встраиваются в
другое приложение и расширяют его функциональные возможности. В це-
лом, языки VB и VBA очень похожи.
Языки VB и VBA относятся к объектно-ориентированным языкам.
Многие данные в программах на этих языках представлены в виде объек-
тов - комбинаций предварительно разработанного программного кода и
данных, обрабатываемых, как единое целое. Каждый объект имеет свойст-
ва и методы, позволяющие управлять данными и использовать их в про-
грамме. Основное преимущество работы с объектами состоит в том, что
объекты обеспечивают программный код, который не требуется писать
разработчику программы. Для использования объекта достаточно устано-
вить свойства объекта и вызвать методы объекта, разработанные при соз-
дании языка программирования и его библиотек. Общая методика про-
граммирования на VBA включает: 1) создание объектов управления и кон-
троля (диалоговых окон, экранных форм, меню и других); 2) разработку
процедур, используемых при вызове объектов.
В VBA используются следующие понятия:
объекты управления и контроля - экранные формы, графические
элементы внутри форм и документов (элементы управления): текстовые
окна, полосы прокрутки, окна-списки, командные кнопки и другие;
свойство (параметр) - характеристика объекта управления, прини-
мающая определенное значение свойства;
событие - действие, которое распознается объектом управления;
метод (доступа) - способ воздействия на объект;
процедуры - подпрограммы и функции, состоящие из операторов
VBA; процедуры делятся на событийные (запускаются автоматически
только при наступлении определенных событий) и общие (требуют явного
вызова в программе для своего запуска).
Свойства и методы объектов не используются по отдельности: они
указываются вместе с объектом. Чтобы отделить имя объекта от его свой-
ства или метода, используется инструкция с точкой:
имя_объекта.свойство_объекта, имя_объекта.метод_объекта
3
Для выполнения некоторых методов требуется задать дополнитель-
ную информацию, которую называют аргументами или параметрами ме-
тода. Методы могут иметь произвольное число параметров. При этом ис-
пользуется синтаксис:
объект.метод параметр1, параметр2,…, параметрN
Некоторые методы возвращают значения. При этом возвращаемое
значение присваивается переменной, которая должна иметь тот же тип
данных, что и значение:
переменная = объект.метод
Далее для определенности будем рассматривать версию VBA 5, ра-
ботающую в среде MS Office 97, а также отражающую основные свойства
и более поздних версий языка VBA.
1.1.2. Использование объектов Excel
Программы на VBA для приложений, работающих в среде Excel,
создаются двумя способами: 1) в автоматическом режиме как результат
построения макрокоманды; 2) в неавтоматическом режиме путем создания
программного кода. Способ 2 является наиболее общим и рассматривается
в настоящем пособии. Основные наборы (классы или семейства объек-
тов), с которыми работает программа на VBA в Excel, описывает таблица:
Таблица 1.1
Имя набора Вид объектов в наборе
Workbooks Все текущие открытые рабочие книги; набор
содержит наборы Worksheets, Sheets, Charts
Worksheets Все рабочие листы (кроме листов диаграмм) в
рабочей книге
Sheets Все листы любых типов в рабочей книге
Charts Все листы диаграмм в рабочей книге
Доступ к компоненту набора выполняется одним из способов:
1) по номеру компонента, например,
Workbooks(″Имя_книги″).Worksheets(2)
- ссылка на рабочий лист 2 рабочей книги с именем Имя_книги;
2) по имени компонента:
Workbooks(″Имя_книги″).Worksheets(″Имя_листа″)
- ссылка на рабочий лист с именем Имя_листа рабочей книги Имя_книги.
Чаще всего используемым объектом Excel является объект Range,
который не входит ни в один набор и представляет ячейку или диапазон
ячеек (как смежных, так и несмежных), а также трехмерные выделения.
Рассмотрим некоторые свойства, связанные с объектом Range.
4
1) Cells – свойство объекта Range, используемое для ссылки на ячей-
ку, зная положение ячейки относительно заданного объекта Range_объект.
Использование этого свойства без указания объекта подразумевает ячейки
активного рабочего листа. В качестве аргументов свойства указываются
два целых числа, определяющих положение отдельной ячейки таблицы:
Range_объект.Cells(число1, число2)
где число1 - номер строки, число2 - номер столбца. Это свойство возвраща-
ет объект Range, который включает одну ячейку.
Например, при инструкциях:
Cells(1, 2).Value = 5
ячейке В1 текущего рабочего листа активной рабочей книги присваивается
значение 5 путем задания свойства Value (значение) объекта Range, соот-
ветствующего этой ячейке;
Cells(2, 1).Formula = ″=SUM(В1:В5)″
- в ячейку А2 записывается формула суммирования значений в ячейках
В1:В5 путем задания свойства Formula соответствующего объекта Range.
2) Range – свойство, которое возвращает объект Range и использует-
ся для ссылки на прямоугольные области ячеек. В качестве аргументов ис-
пользуется одна или несколько ссылок на ячейки таблицы, например, для
активного рабочего листа: Range("A1") - ячейка A1, Range(''A:A'') – 1-й
столбец, Range(''2:2'') – 2-я строка, Range(''A1:B2, D1:E3'') - несмежные
блоки ячеек A1:B2, D1:E3, Range(''A:A, C:C, F:F'') - столбцы A, C, F.
При инструкциях
Worksheets(''Prim'').Range(''A10:C10, F12'').Value = 10
блокам ячеек A10:C10, F12 рабочего листа с именем Prim присваивается
значение 10;
Range(''D15'').Value = ''Test''
ячейке D15 текущего рабочего листа активной рабочей книги присваивает-
ся строковое значение Test (при этом строка заключается в кавычки);
Range(''Prim'').ClearContents
выполняется очистка с помощью метода ClearContents содержимого име-
нованного блока ячеек Prim текущего рабочего листа.
3) Offset - свойство, возвращающее объект Range и используемое для
относительной ссылки на ячейку. В качестве аргументов передаются два
числа, указывающие положение ячейки относительно верхнего левого угла
заданной области (в частном случае, относительно одной ячейки). Напри-
мер, если текущей активной ячейкой является А1, то после инструкции
Selection.Offset(2, 5).Select
5
активной станет ячейка F3 (активная ячейка сместится на 2 ячейки вниз и
на 5 ячеек вправо; используются свойство Selection, возвращающее выде-
ленный в активном окне объект, и метод выбора объектов Select).
При написании программ на VBA используются следующие правила:
1) Для пояснений в программах используются комментарии, начинаю-
щиеся с апострофа (′); при этом программой игнорируется все, что нахо-
дится между апострофом и символом конца строки; комментарий может
начинаться с произвольного места программы.
2) Длинные операторы разбиваются на несколько строк, используя в
конце строки признак продолжения строки - пробел в сочетании со знаком
подчеркивания ( _ ). Признак продолжения строки недопустим в строковом
выражении - при разбиении строковой константы на несколько строк ис-
пользуется оператор конкатенации (&), знак подчеркивания и кавычки, на-
пример:
MsgBox ''Эту строку прерываем, '' & _
''чтобы продолжить на следующей''
(встроенная функция MsgBox служит для вывода сообщений). За призна-
ком продолжения строки нельзя ставить комментарий.
3) Логические уровни в программе выделяются отступами слева (ана-
логично программам на языке Паскаль).
1.1.3. Встроенные типы данных VBA
Основные типы данных VBA характеризует таблица:
Таблица 1.2
Тип Размер (в Разрядность Диапазон значений
данных байтах) (цифр)
Boolean 2 1 True, False
Integer 2 5 -32768; 32767
Long 4 10 -2147483648; 2147483647
Single 4 7 -3.402823E+38; -1.401298E-45
1.401298E-45; 3.402823E+38
Double 8 15 -1.797693E+308; -4.940656E-324
4.940656E-324; 1.797693E+308
Currency 8 19 -922337203685477.5808
+922337203685477.5807
Date 8 01.01.100; 31.12.9999
String Зависит от длины строки 0; 65400 или до 2⋅109 символов
Object 4 Ссылка на любой определённый
ранее объект
Variant Зависит от самих данных Универсальный тип
Byte 1 3 0 - 255
6
Переменные типов Byte, Integer и Long не могут иметь дробных значений,
а типа Byte – отрицательных. Тип Currency используется для расчетов, при
которых важна точность: для денежных расчётов, расчетов с десятичными
дробями с фиксированной точностью. Этот тип данных требует меньше
памяти по сравнению с типами Single и Double. Тип String используется
для строк (длина строки – до 2⋅109 символов). Переменные типа Variant
могут содержать данные любого типа, кроме строк фиксированной длины
и типов, определяемых пользователем. Этот тип данных применяется по
умолчанию: если переменная используется без описания типа, то она по-
лучает тип Variant. Без явной необходимости такие переменные лучше не
использовать, так как они занимают много машинной памяти и могут вы-
зывать дополнительные трудности при отладке программы. Данные типа
Boolean хранят логические значения True (Истина), False (Ложь), которые
обычно используются в инструкциях управления порядком выполнения
команд программы. Данные типа Date имеют диапазон значений от 1 янва-
ря 100 года до 31 декабря 9999 года.
1.1.4. Переменные
Переменной называется поименованная область в памяти компьюте-
ра во время выполнения программы. Переменная требует явного объявле-
ния своего имени, которое образуют алфавитно-цифровые символы и спе-
циальные знаки. Имя начинается с буквы и представляет собой непрерыв-
ную последовательность символов, но не более 255 и не содержит пробе-
лов, точек и специальных символов: #, $, %, &, !, @. Имена переменных
можно набирать как в латинице, так и в кириллице, при этом нет различия
между прописными и строчными буквами: ПриМЕР, пример, ПРИМЕР -
одно и то же имя переменной. Имена переменных могут состоять из от-
дельных слов, соединенных символом подчеркивания: Ставка_1_разряда.
Программа на VBA имеет модульную структуру - состоит из главно-
го модуля, в составе которого могут быть вложенные модули, содержащие
одну или более вложенных процедур. Каждая переменная имеет опреде-
лённый тип значений и сферу действия. Переменные, объявленные только
в процедуре, действуют в пределах процедуры. Объявление переменной на
уровне модуля делает её доступной для всех процедур модуля. Для ис-
пользования переменной во всех вложенных процедурах и модулях она
объявляется глобальной на уровне внешнего модуля.
Переменные в модуле объявляются с помощью операторов различ-
ных типов: локальные переменные, действуют только внутри своего моду-
ля и объявляются инструкцией
Dim var1 As тип, var2 As тип,…,
глобальные переменные, объявляются инструкцией
Public var1 As тип, var2 As тип,…,
7
где var1, var2, … - имена, тип - встроенный тип данных переменных.
Сферы действия переменных иллюстрирует схема программы на
VBA, приведенная на рис. 1.1. На схеме переменная var0 доступна всем
вложенным модулям и процедурам, переменные var4, var5 используются
только в самих процедурах. Переменная var2 объявлена в процедурах 1 и
2. Это соответствует двум различным переменным с одинаковым именем и
своей сферой действия (они имеют разные значения и/или типы данных и
независимы друг от друга). Переменные на уровне модуля, var1 – в модуле
1 и var3 – в модуле 2, используются во всех вложенных в модуль процеду-
рах. Переменная var1 переопределена в процедуре 1, но сохраняет своё
значение в процедуре 2; переменная var3 переопределена в процедуре 3.
МОДУЛЬ
Public var0 As Currency
МОДУЛЬ1 МОДУЛЬ 2
Dim var1 As Boolean Dim var3 As Byte
Процедура 1 Процедура 2 Процедура 3
Dim var1 As Dim var2 As Dim var3
Integer String*1 As Integer
Dim var2 As Dim var5 As Dim var4
Date String*2 As Long
Рис. 1.1. Сферы действия переменных в программе на языке VBA.
Для объявления переменных используются также ключевые слова:
Private - для локальных, Static - для статических переменных, например:
Private A As Integer, Static B As Integer
Если переменная не объявлена статической, то ее значение после выпол-
нения процедуры не сохраняется, а занятая переменной область памяти ос-
вобождается. Если переменная явно не объявлена, то по умолчанию ей
присваивается тип Variant. Переменные разных типов можно объявить од-
ним оператором, например:
Private A, B, I As Integer
- А и В получают тип Variant (они явно не объявлены); I - тип Integer;
Private A As Integer , B As Integer , I As Integer
- у всех переменных – тип Integer.
8
При присваивании значений переменным и константам типа Date
используется символ #:
Var = #17.11.2000#
Объявление объектной переменной
Для работы с различными объектами используются объектные пе-
ременные, дающие ссылку на объекты и объявляемые одним из способов:
1) указанием универсального типа Object, например,
Dim Лист As Object
2) указанием зарезервированного имени объекта набора, например,
Dim Лист As Worksheet
По возможности, рекомендуется использовать второй способ, что упроща-
ет отладку программы. Для закрепления объекта за объявленной объект-
ной переменной используется оператор Set:
Set Лист = Worksheets.Add
- при помощи метода Add создается новый рабочий лист и ссылка на него
присваивается объектной переменной Лист.
1.1.5. Константы
Константы - это величины, сохраняющие постоянное значение во
время выполнения программы, но изменяемые по желанию пользователя.
Константы объявляются с помощью оператора:
Const константа = значение
где константа - имя константы, значение - ее значение.
Как и для переменных, для констант существует понятие сфера дей-
ствия. Глобальные константы объявляются с помощью ключевого слова
Public, например:
Public Const Date1 = #12.12.99#
Для объявления локальных констант используется инструкция, например:
[Private] Const Date2 = #13.12.99#
где ключевое слово Private - необязательный параметр (здесь и ниже не-
обязательные параметры синтаксиса указываются в квадратных скобках).
Локальные константы объявляются в пределах процедуры или модуля,
глобальные - на уровне внешнего модуля и доступны для всех вложенных
модулей и процедур. По умолчанию (если нет ключевых слов Public или
Private), константа считается локальной. Если при объявлении константы
тип данных явно не указан, ей назначается тип, соответствующий выраже-
нию, присвоенному константе, например:
9
Private Const var1 = 1 ′В обоих случаях объявляется одна и та
Const var1 = 1 ′же локальная константа var1
Public Const var2 = ″Help″ ′Глобальной константе var2
′присваивается тип String
Private Const var3 As Integer = 5 ′Тип локальной константы указан явно
Язык VBA имеет встроенные константы, которые применяются без
специального объявления и начинаются с символов vb. Excel использует
встроенные константы, начинающиеся символами xl.
1.2. ОПЕРАТОРЫ ЯЗЫКА VBA
1.2.1. Oператор присваивания
Оператор присваивания служит для присвоения начальных значений,
записи результата вычисления в переменную, изменения значений пере-
менной. Структура оператора:
var = формула
где var - имя переменной, формула – формула. Переменная слева от знака
равно может быть простой переменной, элементом массива или свойством
объекта. Формула состоит из переменных, констант, операций и функций.
C помощью оператора присваивания под именем переменной сохраняются
результаты вычислений по формуле в памяти машины.
Раccмотрим операции VBA:
операции для построения формул (в скобках указаны приоритеты
операций в порядке убывания): ^ - возведение в степень (1), - -отрицание
(2), * - умножение (3), / - деление (3), \ - деление нацело (4), Mod - остаток
от деления (5), + - сложение (6), - - вычитание (6), & - объединение (конка-
тенация) строк (7);
операции сравнения: = - равно, <> - не равно, < - меньше, <= - мень-
ше или равно, > - больше, >= - больше или равно, Is - идентично (только
для объектов, вместе с зарезервированным словом Null используется для
проверки, является ли значение пустым), Like - использование шаблонов
для поиска и сравнения, Between…And… - проверка принадлежности вы-
ражения указанному диапазону значений, In - проверка выражения на сов-
падение с любым из элементов в списке.
Для изменения указанного порядка выполнения операций использу-
ются круглые скобки.
1.2.2. Процедуры
Процедура– последовательность совместно выполняемых инструк-
ций, имеющая имя. В процедурах содержится код исполняемых программ.
Рассмотрим 2 типа процедур:
1. Процедура Sub - набор команд, с помощью которого решается оп-
ределенная задача. При запуске Sub выполняются команды этой процеду-
10
ры, а затем управление передается в приложение или процедуру, которая
вызвала процедуру Sub.
2. Процедура Function или функция пользователя - также набор ко-
манд, решающий определенную задачу. Различие Sub и Function заключа-
ется в том, что Function возвращает одно вычисленное значение, которое
может участвовать в выражениях оператора присваивания, а Sub значения
не возвращает.
Процедура Sub
Синтаксис процедуры:
Sub имя ([аргументы])
тело_процедуры
End Sub
где Sub - оператор объявления процедуры, параметр имя определяет имя
процедуры, необязательные параметры аргументы позволяют передать в
процедуру из программы требуемые значения, тело_процедуры - набор
последовательно выполняемых операторов на VBA. Каждому оператору
Sub должен соответствовать оператор завершения процедуры End Sub.
Есть несколько вариантов вызова процедур (при этом все необходимые
ссылки должны быть заранее установлены):
1) Из другой процедуры того же модуля - по имени процедуры, на-
пример:
Sub Proc_A( ) ′Вызывающая процедура
. . .
Proc_B( ) ′Вызов процедуры Proc_B( )
. . .
End Sub ′Завершение работы процедуры Proc_A( )
. . .
Sub Proc_B( ) ′Вызываемая процедура
. . .
End Sub ′Завершение работы процедуры Proc_B( )
2) В пределах одного приложения из другой процедуры другого
(подключенного) модуля – по имени модуля и имени процедуры, разде-
ленным точкой, например:
Sub Proc_A( ) ′Вызывающая процедура
. . .
[Модуль2].Proc_B( ) ′Вызов процедуры Proc_B( ) модуля Модуль2
. . .
End Sub ′Завершение работы процедуры Proc_A( )
3) Из другого приложения, например, другой рабочей книги Excel, -
по именам рабочей книги, модуля и процедуры, разделенным точкой:
Sub Proc_A( ) ′Вызывающая процедура
11
. . .
[Book2.xls].Модуль3.Proc_B( ) ′Вызов процедуры Proc_B( ) модуля
′с именем Модуль3 рабочей книги Book2.xls
. . .
End Sub ′Завершение работы вызывающей процедуры
Если в момент вызова процедуре передаются какие-то величины или
значения, необходимые для ее выполнения, то определяют список аргу-
ментов, который содержит данные обмена с вызывающей процедурой. В
теле процедуры могут быть объявлены внутренние переменные, которые
не являются предметом обмена с вызывающей процедурой; такие пере-
менные не могут изменяться вне пределов вызываемой процедуры.
Объявление списка аргументов в заголовке процедуры имеет вид:
Sub Proc_B (Val1 As Single, Val2 As Byte)
где Val1, Val2 – аргументы процедуры. Если аргумент необязательный, то
есть может не задаваться при вызове процедуры, перед ним указывается
ключевое слово Optional.
Рассмотрим пример - процедуру вычисления площади прямоуголь-
ника по двум сторонам:
Sub Square(L As Single, H As Single, S As Single, Optional F)
If IsMissing (F) Then F=100
S=L*H ′Вычисление площади
End Sub
(встроенная функция IsMissing проверяет наличие значения у аргумента F;
если F не задано, то по умолчанию F присваивается значение 100).
Вызвать процедуру, например, процедуру Square из примера, можно
несколькими способами.
Способ 1 - явное задание значений аргументов:
Sub Proc_(A) ′Вызывающая процедура
Dim Sq As Single ′Описание переменной Sq
Square 10,20,Sq ′Вызов процедуры Square
End Sub
При этом процедура вызывается с передачей значений длин 10, 20 сторон
прямоугольника в явном виде, результат вычисления будет присвоен пе-
ременной Sq.
Способ 2 - через имена переменных, которым присвоены значения:
Sub Proc_A( )
Dim Sq As Single, LL As Single, HH As Single
LL=10
HH=20
Square LL, HH, Sq
End Sub
12
