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

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

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

---

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

Нужно заметить, что тип Date не является внутренним типом, используемым VBA для хранения даты и времени. Вместо него применяется тип Single (число с плавающей точкой). Целая часть этого числа – количество суток, прошедших с 30 декабря 1899 года, дробная – прошедшая часть текущих суток.

Тип данных Variant

В VBA предусмотрен один универсальный тип данных – Variant. Переменная этого типа может хранить значение любого поддерживаемого VBA типа (в том числе и ссылки на объекты, о которых будет рассказано ниже).

Однако при обработке переменных типа Variant тратится дополнительное время на определение и преобразование типа данных в этих переменных – самый универсальный тип данных VBA оказывается и самым медленным. Поэтому следует избегать слишком частого и неоправданного использования переменных этого типа (например, в качестве целочисленных итераторов, счетчиков и т. д.).

Когда переменная типа Variant пуста (ей не присвоено никакого значения), она заполняется специальным значением Empty.

Ссылки. Тип данных Object

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

Объекты, доступные из VBA, существуют, пока на них установлена хотя бы одна ссылка. Первая ссылка на объект устанавливается при его создании. В процессе работы можно как устанавливать новые ссылки на объект, так и удалять их с использованием специальной инструкции Set. Пусть objRef – ссылка на некоторый объект. Тогда операция установления новой ссылки будет иметь такой вид:

Set objNewRef = objRef

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

Set objRef = Nothing

Set objNewRef = Nothing

Если на объект не было других ссылок, кроме этих, то он будет удален.

Для доступа к объектам в VBA предусмотрен тип данных Object. Он является универсальным, так как может быть ссылкой на объект любого типа.

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

Для объявления переменных элементарных типов (не массивов) в блоке объявлений модуля используется следующая инструкция:

Public | Private [WithEvents] Имя_переменной [As [New] Имя_-

типа] _

[, Имя_переменной [As [New] Имя_типа]]…

Ключевые слова, записанные до первых квадратных скобок, задают область видимости переменных:

• Public – позволяет объявлять глобальные переменные и общие переменные-члены класса (о классах будет рассказано позже);

• Private – позволяет объявлять переменные, доступные только в одном модуле, и частные переменные-члены класса.

Для объявления переменных элементарных типов (не массивов) в процедурах или функциях используется такая инструкция (локальных переменных):

Dim | Static [WithEvents] Имя_переменной [As [New] Имя_типа] _

[, Имя_переменной [As [New] Имя_типа]]…

Ключевые слова, записанные до первых квадратных скобок, задают время жизни переменных:

• Dim – используется для объявления локальных переменных, которые уничтожаются после выхода из процедуры;

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

Ключевое слово WithEvents используется для объявления переменной-обработчика событий объекта.

Имя_переменной – это идентификатор объявляемой переменной.

Имя_типа – название типа данных объявляемой переменной.

Если конструкция [As [New] Имя_типа] не используется, то типом объявляемой переменной автоматически становится тип Variant.

Если используется ключевое слово New, то создается новый объект. New нельзя использовать совместно с WithEvents, а также при объявлении переменной типа Object и если тип Имя_типа не является объектным.

Ниже приведены примеры объявления переменных на VBA:

Public intData As Integer

Private intCount As Integer, varData

Dim strText As String

Static a, b, c

Dim objRef As Object

Dim objCtrl As New Control

Внимание!

При объявлении в одной строке нескольких переменных слово As относится только к переменной, непосредственно после идентификатора которой оно следует. Например, при объявлении Dim а, Ь, с As Integer переменные а и b будут иметь тип Variant, а переменная с – тип Integer.

Инициализация переменных

После того как переменная объявлена, VBA производит ее инициализацию указанным ниже образом.

• Переменным численных типов автоматически присваивается нулевое значение.

• Строки переменной длины после объявления являются пустыми (с нулевой длиной). Строки фиксированной длины заполняются нулевыми символами.

• Данные типа Date инициализируются значением 00:00:00 30.12.1899 (это равняется нулю при представлении даты в численном формате, о котором было рассказано выше).

• Все переменные типа Object и подобные (то есть ссылки на объекты определенного типа) принимают значение Nothing, если при их объявлении не создан новый объект (не использовалось New).

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

Рассмотренное выше объявление переменных называется явным.

VBA также поддерживает неявное объявление переменных. Под неявным объявлением подразумевается возможность использования переменной без ее объявления посредством инструкции Dim, Static, Private или Public. При первом обращении к такой переменной для нее автоматически выделяется память и происходит ее инициализация. Следует отметить, что все неявно объявленные переменные имеют тип Variant.

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

К примеру, можно очень долго разбираться, почему же после вычисления такого выражения, как dblSalaryAccount = dblSalaryAcount * 10. 5, значение переменной dblSararyAccount стало равным нулю, если до этого оно было равно 5.37 5. Все дело в небольшой ошибке в названии идентификатора переменной, в результате которой создается новая переменная dblSalaryAcount, которая инициализируется нулевым значением. В случае использования явного объявления переменных подобных ошибок в программе быть не может, потому что каждый раз при обнаружении необъявленного идентификатора VBA выдает ошибку.

Для включения требования обязательного объявления переменных используется директива Option Explicit. Чтобы данная директива добавлялась в каждый новый модуль автоматически, нужно с помощью меню Tools → Options (Сервис → Параметры) редактора VBA открыть диалоговое окно Options (Параметры) и на вкладке Editor (Редактор) установить флажок Require Variable Declaration (Явное описание переменных).

Константы

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

[Public | Private] Const Имя_константы [As Имя_типа] = Значение

Два ключевых слова в первых скобках задают область видимости константы:

• Public – используется для объявления глобальных констант;

• Private – используется для объявления констант, доступных только в том модуле, где они объявлены.

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

Имя_константы – задает идентификатор константы.

Значение – константное значение (например, «Строка1», 1.245 ит. д.) либо выражение, в число аргументов которого не входят переменные и функции.

Если тип константы не задан, то он автоматически выбирается VBA исходя из ее значения. Ниже приводятся примеры объявления констант:

Const PI As Double = 3.14159265359

Public Const MyConstString = «MyConst»

Private Const НазваниеТаблицы As String = «Отчеты»

Операторы

Язык VBA содержит большое количество встроенных операторов, которые позволяют выполнять разнообразные действия над всеми встроенными в VBA типами. Операторы и их операнды по определенным правилам составляются в выражения. Данный раздел посвящен описанию операторов, предоставляемых VBA-npoграммисту.

Операторы для работы с численными значениями

Информация обо всех операторах для работы с численными значениями приведена в табл. 1.3.

Таблица 1.3. Операторы для работы с численными значениями

Примечание

Оператор «+» может использоваться и для соединения строк. Однако bVBA существует специальный оператор «&», выполняющий эту функцию. Рекомендуется использовать для соединения строк именно оператор «&», так как это способствует легкому визуальному отделению операций над строками от операций над другими типами данных, что, в свою очередь, улучшает читаемость кода.