Модули, процедуры, функции

Приложение, созданное на VBA, имеет модульную структуру. Это означает, что процедуры, входящие в приложение, группируются в модули. Когда в подразделе 10.2 мы создавали интерфейс приложения, размещая все элементы управления на листе Excel, приложение содержало только один модуль, который, подобно рамке, включал в себя все процедуры приложения (см. рис. 10.2). Этот модуль относился к объекту Excel, имеющему имя Лист1. Когда был применен второй вариант конструирования интерфейса (вариант с диалоговым окном Dialog1), наш проект (разрабатываемое приложение) стал содержать два модуля – переключаться на их тексты удобно с помощью пункта меню Окно (см. рис. 10.2, 10.3).

Первый модуль по-прежнему относится к объекту Лист1, а второй к форме Dialog1. В составе первого модуля осталась событийная процедура CommandButton1_Click(), не имеющая параметров (в скобках – пусто), и общая процедура Mrows(I1 As Integer, J1 As Integer, M As Integer), которая определяет номер M последней занятой ячейки в столбце J1, причем проверки ячеек столбца начинаются с ячейки I1. При вызове этой процедуры для конкретизации ее функций используются 3 параметра. Параметр I1 введен, чтобы расширить область применимости процедуры: ведь в общем случае столбцы чисел могут размещаться не с первой, а с любой другой строки таблицы Excel.

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

Private Sub SpinButton1_Change()

TextBox1.Value = SpinButton1.Value

End Sub

Смысл этой процедуры в том, что при изменении свойства Value счетчика, это изменение надо отобразить в поле TextBox1, изменив и его свойство Value. Вторая событийная процедура диалоговой формы реагирует на нажатие кнопки Ok:

Private Sub ButtonOk_Click()

Dialog1.Hide

End Sub

Эта процедура делает диалоговую форму скрытой, невидимой.

Те процедуры, которые начинаются со слова Private, являются собственностью модуля, в который они включены: к ним нельзя обратиться из других модулей. Процедуры, перед названием которых указано только слово Sub (сокращение от Subroutine – подпрограмма), доступны и из других модулей. Таким образом, процедура – это подпрограмма, имеющая заголовок, содержащая команды VBA (тело процедуры) и завершающаяся инструкцией End Sub. Заголовок процедуры определяет ее тип, имя и параметры, конкретизирующие ее функции. Поясним синтаксис процедуры на примере общей процедуры Mrows - ее параметры были пояснены выше. Эта процедура имеет вид:

Sub Mrows(I1 As Integer, J1 As Integer, M As Integer)

‘ J1 – номер столбца,

‘M – определяемый номер последней непустой ячейки в столбце

M = I1 ‘ I1 – номер (по вертикали) первой проверяемой в столбце J1 ячейки

Do Until IsEmpty(Cells(M, J1)) ‘ начало цикла

‘ цикл, завершающийся инструкцией Loop, выполняется, пока ячейка (M,J1) не пуста

M = M + 1 ‘ наращивание M – это переход к следующей ячейке столбца

Loop ‘ конец цикла

M = M - 1

End Sub

Параметры процедуры описываются в скобках, в строке заголовка, с указанием типов через ключевое слово As (как, в качестве). В эту процедуру вставлены комментарии – начинаются с апострофа. В каждой строке записывается одна или несколько инструкций. В последнем случае инструкции разделяются двоеточием (см. первую процедуру на рис. 10.2). Общую процедуру можно вызывать из других процедур. Так, с помощью процедуры Mrows событийная процедура CommandButton1_Click (см. рис. 10.2) определяет количество чисел в столбце, подлежащем сортировке.

Кроме подпрограмм-процедур, обозначаемых ключевым словом Sub, применяются подпрограммы- функции, обозначаемые ключевым словом Function. Так, вместо процедуры Mrows можно было написать функцию Mrows:

Function Mrows(I1 As Integer, J1 As Integer) As Integer

Dim i As Integer

i = I1 - 1

Do

i = i + 1

Loop Until IsEmpty(Cells(i, J1))

Mrows = i - 1

End Function

Подпрограмму оформляют в виде функции, если результатом является единственное значение. Если же надо сразу получить более одного результата, например, найти число непустых строк и столбцов таблицы, то можно использовать только процедуру Sub. Функция отличается от процедуры не только первой и последней инструкциями, но и присваиванием результата ей самой (см. предпоследнюю инструкцию функции Mrows). В функцию мы включили описание переменной i, а цикл (инструкции от Do до Loop) записали для разнообразия иначе, чем в процедуре. Вызов функции Mrows из событийной процедуры CommandButton1_Click (см. рис. 10.2) имел бы вид: M = Mrows(I1, J). Здесь обязательно надо подчеркнуть различие между формальными параметрами процедуры (или функции) и фактическими параметрами, используемыми при ее вызове. Так при написании функции Mrows второй параметр обозначен J1 – это формальный параметр, ему соответствует адрес ячейки, содержащей номер столбца. В момент написания функции этот адрес не известен и не важно, как он обозначен в тексте функции. Реальное значение этого адреса – фактический параметр - передается как второй параметр функции в момент ее вызова процедурой CommandButton1_Click. В этой процедуре номер столбца обозначен через J. Таким образом, важно не обозначение параметра при вызове функции, а его тип (здесь – целое число) и порядковый номер в списке передаваемых фактических параметров.

Обратим также внимание на то, что функция Mrows, в свою очередь, обращается к встроенной функции IsEmpty, а ее параметром является значение встроенной функции Cells. Функция Cells(i,j) предоставляет свойства, в том числе и значение, ячейки, расположенной на пересечении i-ой строки и j-го столбца того листа Excel, к которому относится модуль. Если потребуется ячейка на листе с именем Лист2, то надо написать: Лист2.Cells(i,j) или Лист2.Cells(i,j).Value - при доступе к конкретному свойству ячейки (i,j) – к хранимому в ней значению. Функция IsEmpty проверяет, является ли ячейка пустой. Если это так (ячейка не инициализирована – в ней хранится условный редкий код), то функция возвращает логическое значение (типа Boolean), равное True (истина), иначе – False (ложь). Другими словами, в функции Mrows значение переменной i наращивается в цикле до тех пор, пока не встретится пустая ячейка в столбце.

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

MsgBox M,,”Номер последней строки”

В этом примере выводится диалоговое окно с заголовком ”Номер последней строки”. Окно содержит число M и одну кнопку Ok. Эту процедуру можно вызвать немного иначе:

MsgBox Str(M), 0,”Номер последней строки”

Результат будет тем же самым. Просто во втором варианте число M перед выводом преобразовано в строку символов (с помощью встроенной функции STR), и явно задан второй параметр. Вообще говоря, первый параметр этой процедуры должен быть именно строкой символов, а не числом, но VBA “понимает”, что M – это число, и сам преобразует его к строковому типу данных. Второй параметр – это код размещения в окне определенных кнопок. Код 0 – это значение по умолчанию, его можно не указывать между запятыми на месте второго параметра, и будет выведена только одна кнопка Ok. Второй параметр играет важную роль при организации диалога, т.е. при использовании MsgBox в качестве функции:

Dim Sort As Boolean

Sort=True ‘Сортировка по возрастанию

IF MsgBox(“По возрастанию?”, 4, “Вариант сортировки”)=7 Then Sort=False

В этом примере переменная Sort описана как логическая - она может принимать одно из двух значений: True (истина) или False (ложь). Сначала принимаем вариант сортировки по возрастанию (Sort=True), а затем выводим стандартное диалоговое окно с заголовком “Вариант сортировки”. В окне помещаем строку “По возрастанию?” и две кнопки: “Да” и “Нет”. Код 4 (второй параметр функции) задает вариант вывода в окне именно этих двух кнопок. Результатом функции является код выбранной кнопки. Код кнопки “Да” равен 6, а кнопки “Нет” – 7. Если пользователь выбрал “Нет”, то значение переменной Sort изменяется на False. Программисту трудно запомнить все эти коды вариантов вывода и выбора кнопок. Поэтому VBA хранит константы, имена которых запомнить легче - их можно указывать вместо числовых кодов. Приведем некоторые из них:

VbOkOnly=0, VbOkCancel=1, VbYesNoCancel=3, VbYesNo=4 – варианты вывода кнопок,

VbOk=1, VbCancel=2, VbYes=6, VbNo=7 – возвращаемые значения.

Процедура-функция MsgBox довольно удобна во многих случаях. Для получения подробной справки о ней или о другой встроенной функции (Таблица 10.1) достаточно ввести название функции в строку окна редактора VBA, поместить на это название курсор и нажать функциональную клавишу F1. В открывшемся окне справки полезно обратить внимание на ссылки См. также и Пример.

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

Кроме модулей, которые автоматически создаются при размещении элементов управления на листах книги Excel и на формах, можно создавать дополнительные модули с помощью пункта меню Вставка/Модуль в окне Visual Basic. Такой модуль удобно использовать в крупных проектах для размещения общих процедур и глобальных (доступных всем процедурам проекта) переменных.

Таблица 10.1