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

   ' Инструкции процедуры
   …
   End Sub
   Проведенную процедуру можно модифицировать так, чтобы вместо необязательного второго параметра процедура принимала произвольное количество аргументов, из которых формируется массив:

   Sub ProcedureExample(ByVal intNumber As Integer, ParamArray varArray())
   ' Инструкции процедуры
   …
   End Sub

   Внимание!
   Два приведенных примера процедур в программе на VBA одновременно присутствовать не могут. Это обусловлено тем, что язык VBA не поддерживает перегрузку процедур и функций (создание процедур и функций с одинаковыми именами, но с разными параметрами).

Вызов процедур

   Для вызова процедуры в программе на VBA предусмотрена инструкция Call, формат которой приведен ниже:

   [Call] Имя_процедуры [Список_аргументов]

   Здесь элемент Имя_процедуры представляет собой идентификатор вызываемой процедуры. Если процедура принимает аргументы, то они должны быть указаны на месте элемента Список_аргументов через запятую. В качестве аргументов в вызывающей процедуре или функции используются константные значения или идентификаторы переменных соответствующих типов.
   Эта инструкция позволяет также вызывать и функции, но при этом возвращаемое ими значение получить невозможно.

   Примечание
   Интересной особенностью инструкции Call является то, что само ключевое слово Call можно опускать. Если ключевое слово Call используется, то список аргументов процедуры необходимо заключать в скобки. В противном случае скобок быть не должно.

   Пусть имеется процедура:

   Sub ProcedureExample(ByVal intNumber As Integer, ParamArray varArray())
   ' Инструкции процедуры
   …
   End Sub

   Пусть также имеется процедура TestExample, в которой необходимо вызывать процедуру ProcedureExample. Процедуру TestExample можно реализовать следующим образом:

   Sub TestExample()
   ' Инструкции процедуры
   …
   ' Вызов ProcedureExample
   Call ProcedureExample(123, «Значение1», «Значение2», «Значение3»)
   ' Инструкции процедуры
   …
   End Sub

   Если в TestExample не использовать ключевое слово Call, то вызов процедуры будет выглядеть так:

   ' Вызов ProcedureExample
   ProcedureExample 123, «Значение1», «Значение2», «Значение3»

   Далее, перед тем как рассматривать особенности передачи значений в процедуры, целесообразно рассмотреть создание и вызов функций. Это связано с тем, что передача параметров в процедуры и функции происходит одинаково.

Объявление функций. Возврат значения

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

   [Private | Public] [Static] Function Имя_функции [(Список_аргументов)] _
   [As Имя_типа]
   [Инструкции]
   [Имя_функции = Значение]
   [Exit Functon]
   [Инструкции]
   [Имя_функции = Значение]
   End Function

   Приведенный формат объявления функции отличается от объявления процедуры использованием ключевого слова Function вместо Sub, возможностью указания типа возвращаемого функцией значения (после списка аргументов) и возможностью в теле функции присвоить значение переменной с идентификатором, соответствующим идентификатору этой функции (Имяфункции = Значение). При объявлении функций можно использовать все возможности, доступные при объявлении процедур.
   Если тип возвращаемого функцией значения не указан, то подразумевается возвращение значения типа Variant.
   Для возврата значения функцией необходимо в нужном ее месте присвоить соответствующее значение переменной с таким же идентификатором, как и идентификатор функции. Часто в функции может быть несколько точек, в которых возвращается значение. Если после получения результата нужно немедленно выходить из функции, то после присвоения Имяфункции = Значение используется инструкция Exit Function. Если на протяжении выполнения функции не было использовано присвоение Имяфункции = Значение, то возвращается значение по умолчанию для соответствующего типа данных (см. подраздел об инициализации переменных).
   Ниже приведен пример функции, которая вычисляет квадратный корень из переданного ей аргумента (если аргумент меньше нуля, то возвращается значение -1, сигнализирующее об ошибке):

   Function dhSQR(dblValue As Double) As Double
   If dblValue < 0 Then
   ' Недопустимый аргумент функции
   dhSQR = -1
   Else
   ' Вычисление квадратного корня
   dhSQR = Sqr(dblValue)
   End If
   End Function

Вызов функций

   Для вызова функций допускается также использовать инструкцию Call, например:

   Call dhSQR(16.324)

   или

   dhSQR 16.324

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

   dblRes = dhSQR(25)
   dblRes = 1 + dhSQR(16)

   Точно таким же образом вызываются все встроенные функции VBA, например

   IsArray, SQR и Array.

Особенности передачи параметров

   При создании и использовании процедур и функций необходимо учитывать некоторые особенности передачи параметров в них. Они общие для процедур и функций. Рассмотрим данные особенности.

Позиционная передача параметров

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

   Sub Procedure(Optional intA As Integer = 25, Optional intB As Integer)
   ' Инструкции процедуры
   …
   End Sub

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

   Procedure 12, 56

   или

   Call Procedure (12, 56)

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

   Procedure 12 Пропущен второй параметр
   Procedure, 12 Пропущен первый параметр
   Procedure Пропущены оба параметра

Использование именованных параметров

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

   Procedure intA:=12, intB:=56

   или

   Procedure intB:=56, intA:=12

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

   Procedure intB:=56

   В данном примере не очень заметны преимущества использования именованных параметров. Другое дело, если необходимо использовать следующую функцию, задав значения только параметров arg3 и arg8:

   Function dhManyArg(Optional arg1, Optional arg2, Optional arg3,
   _
   Optional arg4, Optional arg5, Optional arg6, Optional arg7, _
   Optional arg8)
   ' Инструкции функции
   …
   End Function

   Очевидно, что инструкция

   varRes = dhManyArg(,,"text",,,,,142.23)

   куда менее наглядна и понятна, чем инструкция

   varRes = dhManyArg(arg3:="text",arg8:=142.23)

Передача аргументов по значению или ссылке

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

   Sub TestByVal(ByVal intArg As Integer)
   ' Какие-то действия, во время которых значение переменной _
   intArg изменяется
   ...
   End Sub

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

   Sub TestByRef(ByRef intArg As Integer)
   ' Какие-то действия, во время которых значение переменной _
   intArg изменяется
   ...
   End Sub

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

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

   Данный раздел посвящен рассмотрению возможностей VBA для определения и изменения типа значений в переменных во время выполнения программы.

Определение типов переменных

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

Таблица 1.8. Значения, возвращаемые функцией VarType

   Примечание
   Функция VarType возвращает значение vbArray только в сумме со значением, идентифицирующим тип элементов массива. Например, для массива строк функция возвратит значение 8192 + 8 = 8200. Значение же vbVariant возвращается только в сумме со значением vbArray и только для массивов с элементами типа Variant.

   2. Для определения типа переменной можно использовать встроенную функцию TypeName. В качестве аргумента она принимает идентификатор переменной или константное значение некоторого типа. Возвращает данная функция строку (тип String) с именем типа аргумента, например «Integer», «String», «Workbook», «Object». Данная функция может вернуть некоторые специфические значения, описание которых приведено в табл. 1.9.

Таблица 1.9. Специфические значения, возвращаемые функцией TypeName

 

   3. Для того чтобы определить тип объекта, на который указывает ссылка, допустимо использовать инструкцию TypeOf, имеющую следующий формат: TypeOf Ссылка Is Идентификатор_типа. Данная инструкция возвращает значение True, если ссылка с именем Ссылка указывает на объект, имя типа которого соответствует параметру Идентификатор_типа. В противном случае возвращается значение False. Например, если obj – ссылка на объект Worksheet, то в результате выполнения инструкции TypeOf obj Is Worksheet появится значение True.

   Примечание
   Инструкция TypeOf работает только для ссылок, имеющих значение, отличное от Nothing. Если в качестве параметра Идентификатор_типа используется Object, то результатом выполнения инструкции будет значение True независимо от типа объекта, на который указывает ссылка.

Преобразование типов

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

   CBool(Выражение)
   CByte(Выражение)
   CCur(Выражение)
   CDate(Выражение)
   CDbl(Выражение)
   CDec(Выражение)
   CInt(Выражение)
   CLng(Выражение)
   CSng(Выражение)
   CStr(Выражение)
   CVar(Выражение)

   Далее приведены примеры использования этих функций (переменная varRes имеет тип Variant, а переменная strRes – тип String):

   varRes = CDec(12.4635246) / CDec(3.14169265359)
   strRes = CStr(12.3535)

   В результате выполнения приведенных инструкций переменная varRes будет содержать значение типа Decimal (использование функции CDec – это единственный способ оперировать с типом данных Decimal), а в переменную strRes будет записано значение «12.3535».

   Примечание
   При использовании функции CBool необходимо помнить, что к значению True преобразуется любое значение аргумента, не равное нулю. Передавать в функцию CBool разрешается только численные значения. Интересным образом также ведут себя инструкции преобразования к целочисленным типам Clnt, CLng и CByte при наличии дробной части в аргументе. Эти функции округляют дробное число до ближайшего целого четного числа.

Файловый ввод/вывод

   Язык VBA поддерживает некоторые возможности для организации файлового ввода/вывода, рассмотрению которых посвящается данный раздел.

Открытие файлов

   Для открытия файла в VBA существует специальная инструкция Open, формат которой приведен ниже:

   Open Имя_файла For Тип_доступа [Access Режим_доступа] [Блокировка] _
   As [#]Дескриптор [Len=Длина_записи]

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

Таблица 1.10. Элементы инструкции Open

 

   Ниже приведены примеры инструкций открытия файла D:\MyTextFile.txt для произвольного доступа, для последовательного чтения и записи:

   Open «D:\MyTextFile.txt» For Random Access Read Write As 1 Len = 100
   Open «D:\MyTextFile.txt» For Input As 2
   Open «D:\MyTextFile.txt» For Output As 3

Дескрипторы файлов. Функция FreeFile

   В среде программирования VBA открытые файлы идентифицируются номерами – дескрипторами. Дескриптор каждого открытого файла должен быть уникальным. Как было видно из примеров открытия файла, программист может сам назначать дескрипторы открываемым файлам (при этом необходимо учитывать, что допустимый диапазон значений дескриптора – 1-511).
   Для небольших и простых программ возможность назначать дескрипторы вручную очень удобна. Однако в больших проектах, в которых ведется работа с многими файлами, бывает достаточно сложно следить за правильностью назначения дескрипторов вручную. Для избавления программиста от необходимости контролировать правильность дескрипторов в VBA введена специальная функция FreeFile, имеющая следующий формат:

   FreeFile ([Диапазон])

   Данная функция возвращает значение типа Long, которое можно использовать в инструкции Open в качестве дескриптора открываемого файла. Единственным параметром данной функции является необязательный параметр Диапазон, который может иметь значение 1 или 0. Если значение параметра равно 0 (по умолчанию), то функция возвращает дескриптор файла из диапазона 1-255. Если же оно равно 1 – значение из диапазона 256–511. Если свободных дескрипторов в диапазоне нет, то функция возвращает нулевое значение.
   Ниже приведен пример использования функции FreeFile:

   Dim hFile As Long
   hFile = FreeFile ' Получение дескриптора для файла
   ' Открытие файла
   Open «D:\MyTextFile.txt» For Output As hFile

Закрытие файлов

   После того как с открытым с помощью инструкции Open файлом выполнены необходимые действия, его нужно закрыть. Операция закрытия (или освобождения) является обязательной для всех объектов операционной системы, а не только для файлов. При закрытии файла освобождается его дескриптор, а другие приложения получают возможность работать с этим файлом, если он был заблокирован при открытии.
   В VBA для закрытия файлов предусмотрены две инструкции: Reset и Close. Формат этих инструкций следующий:

   Reset
   Close [[#]Десктиптор [, [#]Дескриптор]...]

   Инструкция Reset закрывает все файлы, открытые ранее с помощью инструкции Open. Инструкция Close закрывает только файлы с указанными дескрипторами, например:

   Close 1, #3, hFile

   Если при использовании инструкции Close дескрипторы закрываемых файлов не указаны, то она закрывает все открытые ранее файлы.

Чтение из файлов и запись в файлы

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

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

   Описание инструкций последовательного доступа, используемых для работы с файлами, приведено в табл. 1.11.

Таблица 1.11. Инструкции последовательного доступа к файлу

 

   Ниже приведен пример использования данной функции для считывания из файла первых 10 символов:

   Sub WriteToFile()
   Open «D:\MyTextFile.txt» For Output As 1
   ' Запись данных в файл
   Write #1, «Значение», «Value», 154.32
   Print #1, «Слово1», «Слово2», 14.28464
   Close 1
   End Sub

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

   Sub ReadFromFile()
   Dim strVal1, strVal2, dblNumber
   Dim strString
   Open «D:\MyTextFile.txt» For Input As 1
   ' Чтение данных из файла
   Input #1, strVal1, strVal2, dblNumber
   Line Input #1, strString
   Close 1
   End Sub

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

   Input(Количество_символов, [#]Дескриптор)

   Ниже приведен пример использования данной функции для считывания из файла первых 10 символов:

   Sub TestInput()
   Dim strText As String
   Open «D:\MyTextFile.txt» For Input As 1
   " Чтение из файла первых 10 символов
   strText = input(10, 1)
   Close 1
   End Sub

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

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

   Put [#]Дескриптор, [Номер_записи], Переменная
   Get [#]Дескриптор, [Номер_записи], Переменная

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

   Type Record
   intVal As Integer
   strName As String * 100
   End Type
   Sub TestRandomAccess()
   Dim rec1 As Record, rec2 As Record
   ' Заполнение rec1 и rec2 значениями ...
   Open «D:\MyRandomAccessFile.txt» For Random Access Read Write _
   As 1 Len = Len(rec1)
   ' Запись данных в файл
   Put 1, , rec1
   Put 1, , rec2
   ' Теперь считывание данных из файла
   Get 1, 2, rec2
   Get 1, 1, rec1
   Close 1
   End Sub

Инструкции бинарного доступа

   Бинарный (Binary) доступ к файлу по своей сути идентичен произвольному доступу с тем лишь различием, что запись в файле имеет длину 1 байт. При бинарном доступе к файлу используются те же инструкции Put и Get, что и при произвольном доступе. Также при бинарном доступе для чтения определенного количества байт может быть использована функция Input, о которой было рассказано выше.

Определение конца файла

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

   EOF(Дескриптор)
   LOF(Дескриптор)

   Функция EOF возвращает значение True, если достигнут конец файла, заданного параметром Дескриптор, и False – в противном случае. Если функция EOF возвратила значение False, то читать из файла больше нельзя. Для файлов, открытых в режиме Output, функция EOF всегда возвращает значение True.
   Функция LOF позволяет узнать длину файла, заданного параметром Дескриптор. Эта функция возвращает значение типа Long, отражающее длину открытого файла в байтах.

Определение текущей позиции файла

   Для определения текущей позиции файла в VBA предусмотрены функции Loc и Seek, имеющие следующий формат:

   Loc(Дескриптор)
   Seek(Дескриптор)

   Обе функции возвращают значение текущей позиции файла, заданного параметром Дескриптор. Однако каждая из этих функций имеет свои особенности.
   Функция Loc для файлов, открытых в режиме Random, возвращает номер последней считанной или записанной записи. Для файлов, открытых в режиме Binary, – номер последнего считанного или записанного байта. Для файлов, открытых в режиме последовательного доступа, – текущую позицию в байтах, деленную на 128.
   Функция Seek для файлов, открытых в режиме Random, возвращает номер записи, которая будет считана из файла или записана в файл при следующей операции чтения/записи. Для остальных файлов эта функция возвращает номер байта, с которого будет начинаться следующая операция чтения или записи.

Стандартные окна сообщений

   Для вывода информации пользователю в арсенале VBA есть очень удобная функция MsgBox. Она позволяет отображать стандартное окно с сообщением (например, об ошибке). Функция MsgBox имеет следующий формат:

   MsgBox(Текст_сообщения[, Стиль] [, Заголовок] [, Файл_справки, Индекс_темы])

   Здесь Текстсообщения задает строку с текстом сообщения, Заголовок – строку с текстом, который отображается в строке заголовка окна, Файлсправки – имя справочного файла. Если задан аргумент Файлсправки, то должен быть задан аргумент Индекстемы, который идентифицирует тему из заданного файла справки, посвященную выводимому диалоговому окну.
   Особого рассмотрения заслуживает аргумент Стиль – он задает значок окна сообщения, отображаемые в этом окне кнопки и другие полезные параметры стиля окна. В табл. 1.12 приведено описание значений, которые объединяются при задании аргумента Стиль с помощью оператора Or.

Таблица 1.12. Значения, используемые для формирования стиля окна

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

Таблица 1.13. Значения, возвращаемые функцией MsgBox

 

Обработка ошибок времени выполнения

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

Перехват ошибок

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