Table Of Content

1. ХАРАКТЕРИСТИКА ЯЗЫКА И РАБОТА С ОБЪЕКТАМИ 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