В начало

Приложение. Ответы

4-й час. Переменные и константы

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

В этом часе будут рассмотрены следующие вопросы.

• Что такое модули.
• Процедуры и способы их создания.
• Как использовать переменные.
• Как использовать константы.
• Какую роль играет область видимости переменных.

Модуль: дом для кода VBA

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

Официально модуль определяется как совокупность объявлений (описательная часть) и процедур, хранящихся как единое целое.

VBA позволяет создавать собственные объекты. Модули классов содержат описание таких объектов.

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

Обзор процедур

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

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

Листинг 4.1. Пример подпрограммы

1: Sub cmdБольшойШрифт_Click ()

2:  With Selection.Font

3:   .Name = "Arial"

4:   .FontStyle = "обычный"

5:   .Size = 16

6:  End With

7: End Sub

Часто выполнение процедуры является реакцией на какое-либо событие (event). В этом случае говорят, что процедура обрабатывает событие. Чтобы узнать, что это за событие, надо посмотреть на имя процедуры. Имя процедуры состоит из названия объекта, cmdБольшойШрифт, и события, Click (щелчок), которое отделяется от названия объекта символом подчеркивания. Для тех, кто еще не догадался, сообщаю, в данном случае процедура выполняется в ответ на щелчок на командной кнопке, которая имеет имя cmdБольшойШрифт.

Процедура-функция (напомню, что мы договорились называть ее просто функцией) в отличие от подпрограммы возвращает какое-либо значение. Это значение часто является результатом вычислений или результатом тестирования чего-либо (в этом случае возвращается логическое значение ИСТИНА или ЛОЖЬ). Как уже упоминалось ранее, вы можете использовать VBA для создания собственных функций. Затем эти функции можно применить при создании формул в ячейках рабочего листа. Листинг 4.2. содержит пример простой функции, вычисляющей торговую наценку в размере 10% от цены.

Листинг 4.2. Пример простой функции

1: Public Function Наценка(Цена)

2:  Наценка = Цена * 0.1

3: End Function

Отметим, что функция использует аргумент Цена, который определяет величину наценки. (Аргументы могут иметь как функции, так и подпрограммы.) Аргумент Цена может быть числом или ссылкой на ячейку рабочего листа. Величина наценки является тем значением, которое возвращает функция. Применение этой функции показано на рис. 4.1.

Рис. 4.1. Использование созданной функции Наценка

Создание процедур

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

1. Откройте новую рабочую книгу.
2. Выполните команду Сервис > Макрос > Редактор Visual Basic. Откроется окно редактора Visual Basic.
3. В левой части окна редактора Visual Basic вы видите окно проектов Project Explorer. Откройте проект VBAProject (Книга1) и щелкните правой кнопкой мыши на элементе ЭтаКнига. В контекстном меню выберите команду Insert > Module (Вставка > Модуль). В ваше приложение будет добавлен модуль (рис. 4.2).

Если вы не видите окно проектов, нажмите комбинацию клавиш <Ctrl+R>.

В верхней части окна кода модуля вы увидите оператор Option Explicit (явное описание опций), который принуждает явно описывать все переменные с помощью операторов Dim, Private, Public, ReDim и Static. Если вы попытаетесь использовать переменную без ее объявления, то при компиляции кода будет выдана ошибка. Этот оператор в новом модуле появляется не всегда, а только тогда, когда в диалоговом окне Options на вкладке Editor установлен флажок опции Require Variable Declaration (Требовать объявления переменных). Диалоговое окно Options открывается командой Tools > Options (Сервис > Параметры).

Рис. 4.2. Окно проектов позволяет следить за компонентами приложений

4. Выполните команду Insert > Procedure (Вставка > Процедура). Откроется диалоговое окно Add Procedure (Добавить процедуру) (рис. 4.3).
5. Введите имя процедуры, например ПерваяПроцедура. В секции Туре (Тип) установите переключатель Sub (Подпрограмма). Щелкните на кнопке ОК. Как показано на рис. 4.4, новая процедура будет добавлена в модуль.
6. В пустой строке находится текстовый курсор, предлагая начать ввод кода процедуры. Нажмите клавишу <Таb> и введите следующий текст:

MsgBox "Это моя 1-я процедура VBA"

После того как вы введете MsgBox и нажмете клавишу пробела, появится окно с информацией о данном операторе.

Делать отступ текста с помощью клавиши <Таb> не обязательно. Отступ делается для более легкого чтения кода.

7. Нажмите клавишу <Enter>. Завершенная процедура показана на рис. 4.5.

Рис. 4.3. Это диалоговое окно помогает в создании подпрограмм и функций

Рис. 4.4. Новая процедура начинается оператором Public Sub и заканчивается оператором End Sub

Рис. 4.5. Ваша первая процедура имеет всего три строчки кода VBA

VBA требует выполнения определенных правил для имен подпрограмм и функций. Эти правила таковы.

• Первые три символа имени должны быть буквами.
• Имя может содержать буквы, цифры и знаки подчеркивания.
• Имя не может содержать пробелы, точки, запятые, восклицательные знаки (!) и символы @, &, $, #.
• Имя не должно иметь более 255 символов.

Выполнение процедуры

После создания процедуры вы можете ее сразу выполнить. Для этого существует несколько путей. Можно использовать команду Run Sub/UserForm (Выполнить подпрограмму/экранную форму) из меню Run (Выполнить) или щелкнуть на кнопке Run Sub/UserForm стандартной панели инструментов, или нажать клавишу <F5>. Итак, чтобы выполнить нашу процедуру, сделайте следующее.

1. Щелкните на кнопке Run Sub/UserForm стандартной панели инструментов редактора Visual Basic. Процедура выполнится и отобразит окно сообщения (рис. 4.6).
2. Щелкните на кнопке ОК. Вы вернетесь в окно редактора Visual Basic.

Рис. 4.6. Всего одна строка кода процедуры - в результате симпатичное окно сообщения!

Сохранение изменений в модуле

Чтобы сохранить новую процедуру, надо сохранить рабочую книгу, где находится эта процедура. Можно сохранить рабочую книгу из редактора Visual Basic. Для этого выполните следующие действия.

1. Выполните команду File > Save Книга (Файл > Сохранить книгу). Если рабочая книга еще ни разу не сохранялась, дайте ей имя.
2. Введите Первая в качестве имени книги и нажмите клавишу <Enter>. Рабочая книга, модуль и процедура будут сохранены.

Переменные

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

1. Создайте новую процедуру с именем СкажиСвоеИмя.
2. Введите следующий код процедуры:

InputBox "Введите Ваше имя"

Не беспокойтесь о синтаксисе оператора InputBox. Более подробно этот оператор будет рассмотрен в 6-м часе "Логика условных операторов".

3. Нажмите клавишу <F5> для выполнения процедуры. Появится окно ввода, предлагающее ввести ваше имя.
4. Введите свое имя и щелкните на кнопке ОК. Процедура закончится.

Где теперь ваше имя? Как найти и сохранить то, что пользователь ввел с помощью окна ввода? Вот для того и необходима переменная, чтобы сохранить все, что введет пользователь.

Типы данных

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

Таблица 4.1. Типы данных VBA

Отметим, что хороший программист всегда выбирает тип данных по возможности наименьшего размера. Например, если надо сохранить небольшое целое число, такое как число студентов в академической группе, то рационально выбрать тип Byte. Интересно, почему в этой ситуации Excel по умолчанию использует расточительный тип Single?

Объявление переменных

Теперь, когда вы знакомы с типами данных, которые могут принимать переменные, можно приступать к созданию переменных. Для этого используется оператор Dim. Создание переменной называется объявлением переменной (declaring variable).

Оператор Dim имеет следующий синтаксис:

Dim имяпеременной As типданных

Здесь имяпеременной обозначает имя создаваемой переменной. Правила именования переменных совпадают с правилами задания имен процедурам, типданных - один из типов данных, приведенных в табл. 4.1.

Имя переменной должно начинаться с буквы и может содержать буквы, цифры и другие символы. Имя не может содержать пробелы, точки, запятые, восклицательные знаки (!) и символы @, &, $, #. Имя должно состоять не более чем из 255 символов.

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

1. Создайте новую процедуру под именем ЗнаюВашеИмя.
2. Введите код процедуры

Dim sName As String

sName = InputBox(“Введите ваше имя: “)

MsgBox “Привет, “ & sName

Полный код процедуры должен иметь следующий вид

Public Sub ЗнаюВашеИмя()

 Dim sName As String

 sName = InputBox(“Введите ваше имя: “)

 MsgBox “Привет, “ & sName

End Sub

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

3. Нажмите клавишу <F5> для выполнения процедуры. На экране появится окно ввода (рис. 4.7).
4. Введите свое имя и нажмите клавишу <Enter>. Окно ввода закроется, и появится окно сообщения, содержащее ваше имя (рис. 4.8).
5. Щелкните на кнопке ОК. Вы вернетесь в редактор Visual Basic.

Рис. 4.7. Теперь переменная сохранит введенное имя

Рис. 4.8. Окно сообщения использует значение переменной, содержащей ваше имя

Вы не обязаны указывать тип данных в операторе Dim. Если не указан тип данных, то по умолчанию VBA применит тип Variant. Обычная реакция на такое сообщение: "Здорово! Пусть VBA делает всю работу - я могу не беспокоиться о типе данных". Большое заблуждение! Вы должны определять типы данных. И для этого существует много причин. Первая очевидная причина заключается в расточительстве ресурсов памяти данными типа Variant. Ни один тип данных не требует 16 или 22 байт для сохранения значений переменной - убедитесь в этом, просмотрев табл. 4.1. Другая, не совсем очевидная, но не менее важная, причина состоит в том, что VBA по-разному обрабатывает данные разных типов. Поэтому, не объявляя тип данных, вы можете получить результат, отличающийся от ожидаемого. Кроме того, в добавок к сказанному, время выполнения процедур VBA также зависит от объявленных (или необъявленных) типов данных. В качестве значимости объявления типов данных я часто привожу своим студентам следующий наглядный пример.

1. Создайте новую процедуру с названием ПримерПеременных.
2. Введите следующий код процедуры:

Dim StartTime

Dim EndTime

Dim i

Dim j

StartTime = Now()

For i = 1 To 5000000

 j = i + 1

Next i

EndTime = Now()

MsgBox “Начало выполнения: “ & StartTime & _

 vbNewLine & "Конец выполнения: “ & EndTime

Так как для переменных не объявлен тип данных, то все они по умолчанию имеют тип Variant.

3. Нажмите клавишу <F5> для выполнения процедуры. Выполнение этой процедуры займет несколько секунд. На моем компьютере эта процедура выполнялась не менее 4 секунд. Возможно, ваш компьютер более быстрый, чем мой, и выполнение процедуры занимает менее 4 секунд, в этом случае замените число 5000000 на 10000000. Запомните длительность выполнения процедуры.
4. Щелкните на кнопке ОК, чтобы закрыть окно сообщения.
5. Измените код процедуры следующим образом:

Dim StartTime As Date

Dim EndTime As Date

Dim i As Long

Dim j As Long

StartTime = Now()

For i = 1 To 5000000

 j = i + 1

Next i

EndTime = Now()

MsgBox “Начало выполнения: “ & StartTime & _

 vbNewLine & “Конец выполнения: “ & EndTime

6. Снова нажмите клавишу <F5> для выполнения процедуры. Сравните время выполнения процедуры теперь и ранее - оно уменьшилось по крайней мере в два раза.
7. Щелкните на кнопке ОК, чтобы закрыть окно сообщения.

Соглашения об именах переменных

В 3-м часе "Работа с элементами управления" вы познакомились с соглашениями, применяемыми к именам элементов управления. Многие разработчики придерживаются аналогичных соглашений для имен переменных. В табл. 4.2 представлены рекомендуемые префиксы для имен переменных.

Таблица 4.2. Префиксы для имен переменных

Листинг 4.3 показывает примеры объявления переменных различных типов. Обратите внимание на объявление строковых переменных - если вам известно максимальное число символов в строке, то это число можно указать в объявлении переменной.

В листинге 4.3 присутствуют строки, которые начинаются с одинарной кавычки ('). Это строки комментариев, которые используются для пояснения кода процедур. При выполнении процедур комментарии игнорируются.

Листинг 4.3. Объявления переменных

1: 'Следующие строки объявляют переменные

2: Dim StudentID

3: Dim iNumberOfStudents as Integer

4: Dim dTestDate as Date

5: 'Далее объявляется строковая переменная произвольной длины

6: Dim sLastName as String

7: 'Следующая строка объявляет строковую переменную

8: 'фиксированной длины в 2 символа

9: Dim sState as String * 2

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

Массивы

Если вы работали с другими языками программирования, то, вероятно, знакомы с массивами. Массив - это совокупность переменных, имеющих одинаковый тип данных и объединенных под общим именем. Элементы массива идентифицируются порядковым номером. Синтаксис объявления массива следующий:

Dim имя_массивa(n) As тип

где n - количество элементов в массиве

Например, если вы хотите создать массив, содержащий имена 10-ти студентов, используйте следующий оператор:

Dim sStudents(9) As String

Отметьте, что в скобке стоит число 9, а не 10. Это потому, что первый порядковый номер в перечислении элементов массива по умолчанию равен 0. Массивы эффективно применять при работе с однотипной информацией. Предположим, вам надо обработать 15 тестовых оценок. Для этого можно создать 15 переменных с помощью 15 операторов Dim. Но можно создать массив, содержащий все тестовые оценки, и использовать при этом только один оператор Dim:

Dim iTestScores(14) As Integer

В основном вы будете использовать одномерные массивы, подобные приведенным выше, но VBA поддерживает и многомерные массивы. Двумерный массив, например, по своей структуре напоминает обычную таблицу. Чтобы создать двумерный массив-таблицу размером 4×4, примените следующий оператор:

Dim iTable(3, 3) As Integer

Существует возможность при объявлении массива не указывать его размер. Это позволяет задать размер массива после начала выполнения программы. Например, ваше приложение позволяет пользователю создавать собственные таблицы, предлагая ввести количество столбцов и строк для новой таблицы. Применив динамически изменяемый массив, вы не только создадите такую таблицу, но и позволите пользователю добавлять или удалять строки и столбцы в этой таблице в процессе выполнения приложения. Если при объявлении массива не указаны его размеры, то такой массив называется динамическим. Синтаксис объявления динамического массива имеет следующий вид:

Dim имя_массива() As тип

Для задания размера динамического массива во время выполнения приложения используется оператор ReDim:

ReDim имя_массива(размер)

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

ReDim Preserve имя_массива(размер)

Присвоение значений переменным

После объявления переменных им можно присвоить значения. В листинге 4.4 показаны примеры операторов присвоения. Отметим использование порядковых номеров при присвоении значений элементам массива.

Листинг 4.4. Присвоение значений переменным

1: Dim iNumber As Integer

2: Dim iTestScore() As Integer

3: Dim i As Integer

4:

5: iNumber = InputBox(“Введите число студентов: “)

6: ReDim Preserve (iNumber)

7:

8: For i =1 To iNumber

9:  iTestScore(i) = InputBox(“Введите оценку “ & i)

10: Next

Использование констант

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

Const ИМЯ_КОНСТАНТЫ As тип_данных = значение

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

Const SALESTAX As Long = .06231

Область видимости переменных и констант

До сих пор мы изучали, как объявлять переменные и константы, но не говорили о том, где их объявлять. Их можно объявить в двух местах: внутри процедуры и в верхней части модуля, которая называется областью общих объявлений. Область объявления переменных определяет область их видимости (scope). Если переменные объявлены в процедуре, то только эта процедура их видит и знает. Другие процедуры не могут использовать эти переменные и их значения. Такие переменные называются локальными, так как они определены (локализованы) в конкретной процедуре. В этом случае также говорят, что переменные видимы на уровне процедуры.

Что нужно сделать, чтобы значения определенных переменных и констант были доступны всем процедурам данного модуля? Для этого надо их объявлять в области общих объявлений модуля. Тогда все процедуры, определенные в данном модуле, могут использовать эти переменные и константы. Такие переменные и константы видимы на уровне модуля.

Концепция видимости также применима и к процедурам.

Существует еще третий уровень видимости, который называется глобальным (public). Переменные и константы этого уровня видимости можно использовать в любой процедуре любого модуля вашего приложения. Применение глобальных переменных очень эффективно во многих практических ситуациях, но необходимо знать, что они находятся в памяти компьютера все время выполнения приложения и, следовательно, потребляют системные ресурсы. Для создания глобальных переменных используется оператор Public, который имеет следующий синтаксис:

Public имя_переменной As тип_данных

Для объявления глобальной константы применяется оператор вида

Public Const ИМЯ_КОНСТАНТЫ тип_данных = значение

Глобальные переменные и константы следует объявлять в верхней части модуля в области общих объявлений.

Резюме

В этом часе основное внимание было уделено использованию и созданию переменных и констант. Была показана необходимость правильного определения типа данных. В заключение часа кратко рассмотрено такое важное понятие, как область видимости переменных и констант.

Вопросы и ответы

Вопрос. Где надо определить переменную, чтобы можно было бы воспользоваться ею не только в текущей процедуре?

Ответ. Такую глобальную переменную надо объявить в области общих объявлений модуля. Как объявить - зависит от расположения ваших процедур. Если все процедуры находятся в одном модуле, то можно использовать оператор Dim. Если процедуры локализованы в нескольких модулях, следует воспользоваться оператором Public.

Вопрос. Модули хранятся отдельно от рабочих книг?

Ответ. Нет, модули являются частью рабочих книг. При сохранении рабочих книг сохраняются все изменения, сделанные в модуле.

Вопрос. Почему нельзя объявить все переменные как Variant?

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

Практикум

С помощью тестов и упражнений вы проверите, насколько хорошо усвоили изложенный материал. Ответы на вопросы смотрите в Приложении.

Тесты

1. Назовите три уровня видимости переменных.
2. Переменную какого типа необходимо использовать для хранения целых чисел из диапазона от 0 до 100?
3. Какое максимальное количество символов можно использовать при задании имен процедур, переменных и констант?
4. Истинно или ложно следующее утверждение: имя процедуры может начинаться с числа?
5. Где объявляются глобальные переменные?
6. Истинно или ложно следующее утверждение: константы можно определить только в процедуре?
7. Какую функциональную клавишу следует нажать для выполнения процедуры в редакторе Visual Basic?

Упражнение

Создайте новую процедуру с именем ПеремИКонст. Создайте переменную с именем sTest типа String. Создайте константу с именем iNumber типа Integer и положите ее равной 2. Установите значение переменной sTest как “Это простой тест”. Добавьте в процедуру следующие две строчки кода, чтобы вывести на экран значения переменной sTest и константы iNumber:

MsgBox “Значение переменной sTest: “ & sTest

MsgBox “Значение константы iNumber: “ & iNumber

Выполните процедуру.