Александр Анатольевич Чиртик. Excel. Трюки и эффекты

   val = col(«Key1»)
   val = col(2)

Определяемые пользователем типы данных

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

Структуры

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

Описание структур

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

   [Public | Private] Type Имя_структуры
   Поле1
   Поле2
   …
   ПолеN
   End Type

   Ключевые слова Public и Private задают область видимости структуры (по умолчанию используется Public):
   • Publiс – применяется для описания структуры, которую можно использовать (объявлять переменные этого типа) во всех модулях проекта; недопустимо в модулях класса;
   • Private – применяется для описания структуры, которую можно использовать только в том модуле, где эта структура описана.
   После ключевого слова Туре следует имя описываемой структуры. Внутри блока Type-End Туре помещаются объявления переменных-членов структуры. Эти объявления аналогичны объявлениям обычных переменных и отличаются только отсутствием в начале ключевых слов Dim, Static, Private или Public и тем, что в одной строке можно объявлять только одну переменную.
   Пусть некоторой компании необходимо хранить данные об имени, фамилии, адресе, телефоне и дате рождения своих сотрудников. Совместно с этими данными нужно хранить информацию о проекте, в котором принимает участие каждый сотрудник. Ниже представлено описание структур, с помощью которых можно реализовать хранение требуемых данных.

   Type ПроектИнформация
   Название As String
   Срок_завершения As Date
   End Type
   Type Сотрудник
   Имя As String
   Фамилия As String
   Адрес As String
   Телефон As String
   Дата_рождения As Date
   Проект As ПроектИнформация
   End Type

   Далее на этом примере рассмотрим особенности работы со структурами в программах на VBA.

Объявление переменных, содержащих структуры

   Объявления переменных, содержащих структуры, выглядят точно так же, как объявления переменных другого типа. Ниже приведен пример объявления одной переменной, содержащей структуру Сотрудник:

   Dim worker As Сотрудник

   VBA позволяет создавать массивы любых типов данных, в том числе и структур:

   Dim workers (15) As Сотрудник

   В данном случае будет создан массив из 15 структур типа Сотрудник.

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

Доступ к полям структур

   Для доступа к содержимому полей структуры необходимо использовать символ «.» (точка). Ниже приведен пример получения значений полей с именем и телефоном сотрудника:

   strFirstName = worker.Имя
   strPhone = worker.Телефон

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

   strFirstName = workers(15). Имя
   strPhone = workers(15). Телефон

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

   strName = worker.Проект. Название
   datDate = worker.Проект. Срок_завершения

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

   worker.Имя = «Иван»
   worker.Фамилия = «Иванов»
   worker.Адрес = «ул. Первомайская, д. 100, кв. 5»
   worker.Телефон = «(095) 200 00 00»
   worker.Дата_рождения = «12.03.1978»
   worker.Проект. Название = «План здания»
   worker.Проект. Срок_завершения = «15.09.2005»

   Содержимое полей структур можно использовать в любых корректных выражениях, например:

   strFullName = "Имя: " & worker.Имя & ", фамилия: " & worker.Фамилия

   При использовании заполненной чуть выше структуры (с информацией о сотруднике Иванове) строка strFullName в результате обработки выражения получит значение" Имя: Иван, фамилия: Иванов".

Перечисления

   VBA позволяет определять целочисленные типы данных с ограниченным количеством значений – перечисления. Каждому значению перечисления соответствует идентификатор.
   Использование перечислений, во-первых, позволяет оградить программиста от ошибок (не нужно знать значения элементов перечислений), а во-вторых, может повысить читаемость программного кода, так как вместо малоинформативных значений типа 167353b программе используются идентификаторы типа Actions ave. Использование перечислений также избавляет от необходимости создания глобальных целочисленных констант, которые используются только как значения параметров функций и процедур.

Описание перечислений

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

   [Public | Private] Enum Имя_перечисления
   Идентификатор1 [= Значение1]
   Идентификатор2 [= Значение2]
   …
   Идентификатор1 [= ЗначениеN]
   End Enum

   Ключевые слова Public и Private задают область видимости перечисления точно так же, как для структуры (см. выше).
   После ключевого слова Enum следует имя описываемого перечисления. Внутри блока Enum—End Enum задаются идентификаторы значений перечисления и, если нужно, сами значения, которые сопоставляются с идентификаторами.
   Если значение элемента перечисления явно не указывается, то оно автоматически формируется следующим образом:
   • если элемент перечисления первый, то тогда ему присваивается нулевое значение;
   • если элемент не первый, то его значение равняется значению предыдущего элемента, увеличенному на единицу.
   Ниже приведен пример описания перечисления:

   Enum MyEnum
   value1
   value2 = 100
   value3
   End Enum

   В приведенном примере создается перечисление, содержащее три идентификатора и значения. При этом с идентификаторами значения сопоставлены следующим образом: value1 имеет значение 0, value2 – значение 100, a value3 – 101.

Использование перечислений

   Объявление переменных для перечислений ничем не отличается от объявления переменных других типов. Ниже приведены примеры объявления переменной и массива переменных для перечисления MyEnum:

   Dim EnumValue As MyEnum
   Dim EnumValues(255) As MyEnum

   Таким переменным можно присваивать любые численные значения, но можно (и даже нужно) использовать идентификаторы этих значений. Например:

   EnumValue = value1
   EnumValues(100) = value3

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

   If EnumValue = value2 Then…

   Здесь значением выражения EnumValue = value2 является True, если EnumValue имеет значение value2 (или 100), и False – в противном случае.

Управление выполнением программы

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

Циклы

   В VBA реализовано несколько способов организации циклов. Их разнообразие и гибкость играют существенную роль в упрощении программ на языке VBA, а также во многом способствуют повышению наглядности программного кода.
   VBA поддерживает четыре вида циклов: обычный цикл For-Next, цикл For Each-Next для просмотра элементов массивов и коллекций, циклы While-Wend и Do-Loop. Циклы различных видов могут быть вложены друг в друга. Рассмотрим подробно каждый из приведенных циклов.

Цикл For-Next

   Цикл For-Next в VBA является самым простым и очень часто используемым. Формат данного цикла следующий:

   For Счетчик = Начальное_значение To Конечное_значение [Step Шаг]
   [Инструкции]
   [Exit For]
   [Инструкции]
   Next [Счетчик]

   Здесь Счетчик – это переменная-итератор любого численного типа. Началъное_значение, Конечное_значение, Шаг – численные значения или идентификаторы переменных численного типа. После ключевого слова Next можно (но не обязательно) указывать идентификатор итератора цикла, конец тела которого обозначает данное ключевое слово. Указывать идентификатор переменной-итератора после Next особенно удобно при организации сложных вложенных циклов.
   В начале выполнения цикла итератору присваивается значение элемента Начальное_значение. Инструкции, записанные в теле цикла, выполняются до тех пор, пока значение итератора не превзойдет значение элемента Конечное_значение (станет больше или меньше его в зависимости от направления изменения итератора). Шаг и направление изменения итератора (увеличение или уменьшение) задаются элементом Шаг. Если шаг изменения итератора равен единице, то данный элемент можно опустить.
   Для преждевременного выхода из цикла предусмотрена инструкция Exit For. При ее встрече в теле цикла выполнение программы переходит на следующую инструкцию после ключевого слова Next.
   Ниже приведен пример трех вложенных циклов For-Next, итераторами которых являются целочисленные переменные i, j и к:

   For i = 10 To 1 Step -1
   For j = 1 To 20
   For k = 10 To -10 Step -2
   ' Выполнение каких-то действий
   …
   Next k
   Next j
   Next i

Цикл For Each-Next

   Цикл For Each-Next используется для просмотра всех элементов массива или коллекции. Формат данного цикла следующий:

   For Each Элемент In Контейнер
   [Инструкции]
   [Exit For]
   [Инструкции]
   Next [Элемент]

   Здесь Элемент – это идентификатор переменной-итератора, а Контейнер – идентификатор массива или коллекции. Для цикла For Each-Next допустимый тип итератора зависит от того, просматривается массив или коллекция. При просмотре массива итератор должен иметь тип Variant. При просмотре коллекции итератор может иметь тип Variant или быть ссылкой на объект.
   После ключевого слова Next можно (но не обязательно) указывать идентификатор итератора цикла, конец тела которого обозначает данное ключевое слово.
   Чтобы преждевременно выйти из цикла, можно использовать такую же инструкцию Exit For, как и для цикла For-Next.
   Ниже приведен пример использования цикла For Each-Next для просмотра массива astrStrings:

   For Each varItem In astrStrings
   ' Выполнение каких-то действий над элементом varItem
   …
   Next

Цикл While-Wend

   While-Wend являeтcя самым простым циклом, с помощью которого можно осуществлять определенные действия до тех пор, пока выполняется заданное условие. Формат данного цикла следующий:

   While Условие
   [Инструкции]
   Wend

   Инструкции в теле цикла While-Wend выполняются до тех пор, пока логическое выражение Условие имеет значение True (значение этого выражения вычисляется при каждой итерации).
   Ниже приведен пример организации цикла While-Wend:

   While i < 100
   ' Действия в цикле
   …
   i = i + 3
   Wend

   Следует отметить, что цикл While-Wend является значительно упрощенным и ограниченным с точки зрения разнообразности способов его использования.

Цикл Do-Loop

   Цикл Do-Loop предоставляет гораздо больше возможностей при организации циклических действий с проверкой логического условия, чем цикл While-Wend. Проверка логического условия окончания цикла может происходить в начале каждой итерации цикла, при этом формат цикла следующий:

   Do [While | Until Условие]
   [Инструкции]
   [Exit Do]
   [Инструкции]
   Loop

   Проверка условия может также происходить в конце каждой итерации цикла (тогда выполняется как минимум одна итерация цикла):

   Do
   [Инструкции]
   [Exit Do]
   [Инструкции]
   Loop [While | Until Условие]

   В приведенных форматах Условие – любое логическое выражение. При использовании ключевого слова While цикл выполняется до тех пор, пока Условие имеет значение True, а при использовании ключевого слова Until – пока Условие имеет значение False. Для выхода из цикла предусмотрена инструкция Exit Do.
   Ниже приведен пример использования цикла Do-Loop:

   Do While i < 100
   i = i + 1
   Do
   j = j + 5
   ' Действия
   …
   Loop Until j > 200
   Loop

Инструкции выбора

   Язык VBA поддерживает инструкции, позволяющие осуществлять различные действия в зависимости от выполнения или невыполнения заданных условий, – инструкции выбора If-Then-Else и Select.

Инструкция If-Then-Else

   Инструкция VBA If-Then-Else предоставляет возможность выбора одного из действий в зависимости от значений заданных логических выражений. Формат данной инструкции следующий:

   If Выражение1 Then
   [Инструкции1]
   [ElseIf Выражение2 Then
   [Инструкции2]]
   …
   [ElseIf ВыражениеN Then
   [ИнструкцииN]]
   [Else
   [Инструкции]]
   End If

   Здесь Выражение1 – ВыражениеN – логические выражения. Если какое-либо из них истинно, то выполняются инструкции, находящиеся после соответствующего ключевого слова If или Elself. Если ни одно из выражений не является истинным, то выполняются инструкции, записанные после ключевого слова Else (если, конечно, это ключевое слово используется).
   Рассмотрим пример использования инструкции If-Then-Else:

   If intAction = 1 Then
   ' Выполнение сложения
   res = a + b
   ElseIf intAction = 2 Then
   ' Выполнение вычитания
   res = a – b
   ElseIf intAction = 3 Then
   ' Выполнение умножения
   res = a * b
   Else
   ' Заданное действие не поддерживается
   '…
   End If

   В приведенном примере с помощью инструкции If-Then-Else выбирается одно из трех поддерживаемых действий для переменных а и Ь: сложение, вычитание или умножение. Действие, которое необходимо выполнять, определяется по содержимому переменной intAction. Если она имеет значение, отличное от 1, 2 и 3, то выполняются инструкции, следующие непосредственно после ключевого слова Else.
   Язык программирования VBA также поддерживает упрощенный вариант инструкции If-Then-Else:

   If Выражение Then [Инструкции1] [Else Инструкции2]

   Здесь Выражение – это логическое выражение, при истинном значении которого выполняются инструкции после ключевого слова Then. Если Выражение не истинно, то выполняются инструкции после ключевого слова Else (если это ключевое слово используется). При использовании этой формы инструкции If-Then-Else следует учитывать, что она записывается в одну строку (или в несколько строк, но с использованием символа подчеркивания). Также необходимо учитывать, что Инструкции и Инструкции1 представляют собой либо одну инструкцию VBA, либо несколько инструкций, разделенных двоеточием.
   Если ключевое слово Else используется, то элемент Инструкции1 может отсутствовать.
   Ниже приведены несколько примеров использования сокращенного варианта инструкции If-Then-Else:

   If a = 1 Then a = 2 Else a = 1
   If a = 1 Then a = 2 Else a = 1: b = b + 1
   If a = 1 And b = 0 Then Else a = 1: b = b + 1

Инструкция Select Case

   Select Case позволяет, подобно инструкции If-Then-Else, делать выбор выполняемых программой действий в зависимости от значения заданного аргумента. При большом количестве альтернатив данная инструкция работает быстрее инструкции If-Then-Else, так как значение проверяемого выражения вычисляется только один раз. Формат инструкции Select Case приведен ниже:

   Select Case Проверяемое_выражение
   [Case Список_выражений
   [Инструкции]]…
   [Case Else
   [Инструкции]]
   End Select

   Здесь Проверяемое_выражение – это любое численное или строковое выражение. Список_выражений содержит неограниченное количество выражений, диапазонов значений и условий. Для более детального пояснения ниже приведен формат элемента Список_выражений:

   Выражение | Мин_значение To Макс_значение | Is Оператор Выражение _
   [, Выражение | Мин_значение To Макс_значение | Is Оператор Выражение]…

   Значения элементов приведенной конструкции следующие.
    Выражение – это любое численное или строковое выражение (тип элемента Выражение должен соответствовать типу элемента Проверяемое_выражение).
    Мин_значение То Макс_значение – используется для задания диапазона значений. Элементы Мин_значение и Макс_значение задают минимальное и максимальное значения диапазона соответственно.
   • Is Оператор Выражение – используется для задания условий. Позволяет использовать в инструкции Select Case операторы сравнения. Элемент Оператор – это любой оператор сравнения VBA, кроме Is и Like. Элемент Выражение – это любое выражение, тип которого соответствует типу элемента Проверяемое_выражение.
   При соответствии значения элемента Проверяемое_выражение одному из заданных выражений, при попадании значения этого элемента в один из диапазонов или при выполнении одного из заданных условий происходит выполнение инструкций, записанных после соответствующего ключевого слова Case. Если ни одна Case-конструкция не сработала, то выполняются инструкции после сочетания ключевых слов Case Else.
   Допустим, что в программе необходимо проверять значение численной переменной intTestValue и выполнять одни действия, когда эта переменная имеет значение 1, 2, 3 или 5, и другие действия – в противном случае. Приведенный ниже фрагмент программы позволяет решить поставленную задачу:

   Select Case intTestValue
   Case 1 To 3, 5
   ' Действия при значении переменной intTestValue,_
   равном 1, 2, 3 или 5
   Case Is < 1, Is > 3
   ' Действия при значении переменной intTestValue _
   меньше 1 или больше 3
   End Select

   В данном примере необходимо обратить внимание на то, что значение 5 удовлетворяет обеим Case-конструкциям. При обработке инструкции Select Case VBA просматривает конструкции с ключевым словом Case в том порядке, в котором они следуют в программе. Поэтому в приведенном примере при значении переменной intTestValue, равном 5, выполняются инструкции после первого ключевого слова Case.
   Не менее просто с помощью инструкции Select Case можно обрабатывать и строковые значения. Ниже приведен пример, в котором выполняются различные действия при значениях строковой переменной strTestValue, начинающихся со строчной и прописной букв латинского алфавита:

   Select Case strTestValue
   Case «a» To "z"
   " Действия, если строка strTestValue начинается _
   со строчной буквы латинского алфавита
   Case «A» To "Z"
   " Действия, если строка strTestValue начинается _
   с прописной буквы латинского алфавита
   Case Else
   " Действия, если строка не начинается с символа _
   латинского алфавита
   End Select

Инструкции безусловного перехода

   С помощью инструкций безусловного перехода можно приступать к выполнению части заданной программы без проверки каких-либо условий. К таким инструкциям относятся GoTo и пара GoSub-Return. Однако перед их рассмотрением необходимо ознакомиться еще с одним элементом языка VBA, без которого данные инструкции использоваться не могут, – с метками.

Метки

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

   100:
   DoSomeAction:
   Перерасчет:

   После перехода на метку выполняются все инструкции, расположенные после нее до конца процедуры, функции, следующих инструкций GoTo, GoSub или до инструкции Return (см. далее).

Инструкция GoTo

   Инструкция GoTo используется для простого перехода к выполнению программы после нужной метки. Формат инструкции следующий:

   GoTo Имя_метки

   Инструкции, расположенные после GoTo, выполняются только в том случае, если в программе существуют соответствующие инструкции GoTo или GoSub. Рассмотрим пример использования GoTo:

   a = 15 + b
   If a < 0 Then GoTo 10
   ' Выполнение действий для значения переменной a больше нуля
   10:
   ' Выполнение действий для значения переменной a меньше нуля

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

Пара инструкций GoSub-Return

   Во времена старого доброго языка Basic инструкции GoSub и Return были незаменимы для программиста. Это было связано с тем, что Basic не был даже процедурным языком программирования: в нем не было процедур и функций, все инструкции записывались в виде единой программы. Чтобы не реализовывать несколько раз одинаковые действия, в этой большой программе выделялись отрезки кода, выполняющие типичные действия, – подпрограммы. Подпрограмма начиналась некоторой меткой и оканчивалась инструкцией Return.
   При достижении инструкции Go Sub осуществлялся переход на указанную метку (аналогично инструкции GoTo) – начинала выполняться подпрограмма. При достижении инструкции Return происходил возврат из подпрограммы – выполнение программы продолжалось после последней инструкции Go Sub.
   Пара инструкций GoSub-Return в языке VBA работает точно таким же образом, но переходы осуществляются только в пределах процедуры или функции. Формат инструкций GoSub-Return такой:

   GoSub Имя_метки
   [Инструкции]
   Имя_метки:
   [Инструкции подпрограммы]
   Return

   Ниже приведен пример использования инструкций GoSub-Return (в подпрограмме вычисляется квадрат длины гипотенузы прямоугольного треугольника):

   a = 5
   b = 4
   GoSub Calculate
   ' Другие действия
   …
   Calculate:
   ' Подпрограмма
   c2 = a ^ 2 + b ^ 2
   Return

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

Процедуры и функции

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

Объявление процедур

   Для объявления процедуры в VBA используется следующая конструкция:

   [Private | Public] [Static] Sub Имя_процедуры [(Список_аргументов)]
   [Инструкции]
   [Exit Sub]
   [Инструкции]
   End Sub

   Ключевые слова Private и Public данной конструкции задают область видимости процедуры.
   • Public – применяется по умолчанию, позволяет создать процедуру, которую можно вызывать из любого места проекта VBA. При использовании в модуле класса она дает возможность создавать общую процедуру (метод) этого класса.
   • Private – позволяет создать процедуру, которую можно вызывать только в том модуле VBA, где данная процедура объявлена. При использовании в модуле класса дает возможность создавать личную процедуру (метод) этого класса.
   Если в объявлении процедуры используется ключевое слово Static, то значения всех локальных переменных данной процедуры сохраняются между ее вызовами. Это эквивалентно использованию инструкции Static вместо Dim при объявлении каждой локальной переменной внутри процедуры.
   Имя_процедуры – это любой корректный идентификатор VBA, который будет употребляться в программе в случае необходимости вызова данной процедуры.
   Список_аргументов – содержит описания аргументов, которые принимаются процедурой. Описания аргументов разделяются запятой и имеют следующий формат:

   [Optional] [ByVal | ByRef] [ParamArray] Имя_аргумента[()] [As Имя_типа] _
   [= Значение_по_умолчанию]

   Пояснения элементов, используемых в данной конструкции, приведены в табл. 1.7.

Таблица 1.7. Элементы описания аргумента процедуры

   Для выхода из процедуры предусмотрена инструкция Exit Sub. При ее достижении выполнение программы немедленно переходит к инструкции, следующей за вызвавшей процедуру инструкцией.
   Ниже приведен пример процедуры, имеющей два аргумента, при этом второй аргумент необязательный и передается по ссылке:

   Sub ProcedureExample(ByVal intNumber As Integer, Optional fFlag = True)