Алексей Гладкий - Excel. Трюки и эффекты

• Название: Excel. Трюки и эффекты
• Автор: Алексей Гладкий
• Жанр: Программы

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

---

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).