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

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

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

---

Операторы сравнения

Результатом выполнения всех операторов сравнения является значение типа Boolean. Если операнды какого-либо оператора удовлетворяют его условию, то возвращается значение True, иначе возвращается значение False. Все операторы сравнения, поддерживаемые VBA, приведены в табл. 1.4.

Таблица 1.4. Операторы сравнения

Описанные операторы сравнения могут принимать в качестве операндов значения выражений любого типа, то есть фактически оперируют с типом данных Variant. Если один из операндов равен Empty, то результатом выполнения операторов будет специальное значение NULL. Если операнды несравнимы, то при выполнении описанных выше операторов генерируется ошибка: «Несоответствие типа».

Режим сравнения строковых значений можно задать с помощью директивы Option Compare. Для Excel работают два варианта данной директивы: Option Compare Text (текст сравнивается без учета регистра символов) и Opt ion Compare Binary (сравниваются бинарные коды символов, при этом автоматически учитывается их регистр). По умолчанию сравнение строк происходит согласно директиве Option Compare Binary.

В VBA реализованы два специфических оператора Like и Is, которые тоже относятся к операторам сравнения. Они также возвращают значение типа Boolean как результат сравнения.

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

Результат = Ссылка1 Is Ссылка2

Оператор Like используется для проверки, удовлетворяет ли текст в строке заданному шаблону. Формат этого оператора следующий:

Результат = Строка Like Шаблон

Перечень символов, которые могут употребляться в строке шаблона, приведен в табл. 1.5.

Таблица 1.5. Перечень возможных символов в строке шаблона

В качестве примера определим, является ли строка «15 26 ОА» номером автомобиля серии ОА или ОО. Значение строки-шаблона для этого случая будет равно «## ## О[AО]», а результатом применения оператора «15 26 ОА» Like «## ## О[АО]» будет значение True.

Для задания непрерывного диапазона символов в квадратных скобках можно воспользоваться знаком «минус» (-). При этом символы необходимо указывать в возрастающем порядке (по номеру в алфавите): [A-Z], а не [Z-A].

Примечание

Если нужно, чтобы в шаблоне присутствовали специальные символы, приведенные в табл. 1.5, то необходимо заключить соответствующие знаки в скобки: ([), (]), (#), (?), (*).

Логические операторы

В VBA введены операторы, которые используются в составе логических выражений (например, условие в инструкции If-Then-Else, которая будет рассмотрена позже). Формат логических операторов VBA (кроме импликации) и их описание приведены в табл. 1.6 (все выражения, используемые в операторах, – логические, принимающие значение True или False).

Таблица 1.6. Логические операторы VBA

Массивы

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

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

Объявление массива

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

Public | Private Имя_массива ([Размерность])[As Имя_типа] _

[, Имя_массива ([Размерность]) [As Имя_типа]]…

или

Dim | Static Имя_массива ([Размерность])[As Имя_типа] _

[, Имя_массива ([Размерность]) [As Имя_типа]]…

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

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

При указании размерности массива необходимо учитывать, что элемент Размерность имеет следующий формат:

Нижняя_граница To Верхняя_граница | Количество_элементов _

[,Нижняя_граница To Верхняя_граница | Количество_элементов]…

В VBA разрешено создавать многомерные массивы с количеством измерений не более 60. Размерности измерений массива разделяются запятой.

При задании размерности в виде Нижняягранща То Верхняягранща нужно явно указывать нижнюю и верхнюю границы измерения массива (например, 50 То 100).

При задании размерности можно также просто указывать требуемое количество элементов в данном измерении массива. При таком задании в качестве нижней границы измерения используется значение по умолчанию (об изменении этого значения будет рассказано далее).

Ниже приведены примеры объявлений массивов (переменного размера, двух одномерных и двух многомерных):

Dim avarValues()

Dim astrValues(1 To 10) As String, astrValues2(10) As String

Dim aintValues(1 To 10, 1 To 3) As Integer, aintValues(10, 3)

As Integer

Задание нижней границы по умолчанию

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

Option Base 0

и

Option Base 1

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

Изменение размера массива

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

ReDim [Preserve] Имя_массива ([Размерность])[As Имя_типа] _

[, Имя_массива ([Размерность]) [As Имя_типа]]…

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

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

ReDim astrValues(1 To 10), aintValues(10, 20)

ReDim varArray(2 To 4) As Boolean

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

• нельзя изменять количество измерений массива;

• нельзя изменять размерности измерений массива, кроме размерности последнего измерения;

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

Давайте рассмотрим пример использования инструкции ReDim с ключевым словом Preserve:

' Первая инструкция ReDim для динамического массива

ReDim astrValues(1 To 5, 1 To 10)

…

' Увеличение размера массива

ReDim Preserve astrValues(1 To 5, 1 To 25)

…

' Уменьшение размера массива

ReDim Preserve astrValues(1 To 5, 1 To 15)

Определение границ массива

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

LBound(Имя_массива[, Номер_измерения])

RBound(Имя_массива[, Номер_измерения])

Функция LBound позволяет получить нижнюю границу массива, a RBound – верхнюю. Обе функции принимают в качестве аргументов идентификатор массива и номер измерения, границу которого нужно получить. Нумерация измерений начинается с единицы. Если параметр Номеризмерения опущен, то его значение принимается равным единице. Обе функции возвращают значение типа Long.

Ниже приведен пример получения нижней и верхней границ первого измерения массива avarValues (значения сохраняются в переменных типа Long):

lngLBound = LBound(avarValues)

lngRBound = RBound(avarValues)

Доступ к элементам массива

Для доступа к элементам массива в VBA используется указание номера этого элемента в круглых скобках после идентификатора переменной массива. При этом номера измерений массива разделяются запятыми. Например (для одномерного и трехмерного массивов):