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

   On Error GoTo Метка
   On Error Resume Next
   On Error GoTo 0

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

Обработка перехваченных ошибок

   Если в программе используется инструкция вида On Error GoTo Метка, то при возникновении ошибки после этой инструкции выполнение программы продолжается с метки Метка. Программный код, который начинается с данной метки и заканчивается (обычно, но не всегда и не обязательно) инструкцией Resume, называется обработчиком ошибок. В обработчике ошибок программист помещает действия, которые либо исправляют ошибку, либо информируют о ней пользователя. В конец обработчика ошибок обычно помещается один из вариантов инструкции Resume:

   Resume [0]
   Resume Next
   Resume Метка

   При использовании Resume [0] выполнение программы продолжается с той инструкции, в которой произошла ошибка. Если использовать вариант Resume Next, то выполнение программы продолжается со следующей инструкции после той, в которой произошла ошибка. Использование же варианта Resume Метка позволяет продолжить выполнение программы с указанной после Resume метки.
   При обработке ошибок важно знать, что в распоряжении программиста всегда имеется глобальная ссылка с именем Err на объект ErrObject. Этот объект хранит подробную информацию о возникшей ошибке (номер ошибки, текст сообщения об ошибке и т. д.). В обработчике эту ссылку можно использовать для уточнения типа, источника ошибки, а также для получения других сведений.
   Ниже приведен пример функции с обработчиком ошибок (она пытается записать текст в файл на гибком диске А:):

   Function dhWriteToFloppy(strText As String) As Boolean
   ' Включение обработчика ошибок
   On Error GoTo ErrHandler
   ' Выполнение операций с дискетой
   Open «A:\Text.txt» For Output As 1
   Write #1, strText
   Close 1
   ' Действия выполнены успешно
   dhWriteToFloppy = True
   ExitFunc:
   ' Выход из функции до обработчика ошибок
   Exit Function
   ErrHandler:
   ' Закрытие файла, если его все-таки удалось открыть
   Close 1
   Dim strErrMessage As String
   ' Идентификация ошибки и формирование текста сообщения
   Select Case Err.Number
   Case 71
   strErrMessage = «Нет диска в дисководе»
   Case 70
   strErrMessage = «Диск защищен от записи»
   Case 61
   strErrMessage = «Нет места на диске»
   Case Else
   strErrMessage = Err.Description
   End Select
   ' Отображение сообщения об ошибке
   MsgBox strErrMessage, vbExclamation, «Ошибка»
   ' Продолжение выполнения программы
   dhWriteToFloppy = False
   Resume ExitFunc
   End Function

   Если запись удается, то функция возвращает значение True. Если возникает ошибка, то выдается соответствующее сообщение, после чего функция возвращает значение False. На примере функции dhWriteToFloppy следует заметить, что при нормальном выполнении программы (без возникновения ошибок) обработчик ошибок выполняться не должен, что достигается выходом из функции до обработчика с помощью инструкции Exit Function.

Классы в VBA

   Язык программирования VBA является объектно-ориентированным, хотя и не поддерживает наследование и полиморфизм. VBA-программист может работать с встроенными классами, а также создавать и использовать свои собственные классы.

Создание класса на VBA

   Создание класса на VBA отличается от других языков программирования (таких как C++), в которых описание классов во многом аналогично описанию структур.
   В VBA для каждого класса в проект должен быть добавлен отдельный модуль, в который помещается код, реализующий работу класса, – модуль класса. Добавление нового модуля класса осуществляется с помощью команды меню Insert → Class Module (Вставить → Модуль класса) редактора Visual Basic. Имя модулю класса присваивается с помощью окна Properties (Свойства), которое показано на рис. 1.3.
   Имя, которое присвоено добавленному модулю, и будет являться именем нового класса. В данном случае имя созданного класса – Class1. В качестве примера с помощью этого класса будет реализовано хранение ссылки на объект, а также хранение некоторой информации об объекте.

   Рис. 1.3. Назначение имени классу

Свойства класса

   Свойства для классов в VBA могут быть реализованы двумя способами. Первый способ – это использование в модуле класса общих переменных-членов (объявленных с атрибутом Public). Добавим таким способом свойство в созданный ранее класс Class1, в котором будет храниться строка с описанием данных, содержащихся в объекте-экземпляре этого класса:

   Public strTag As String

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

   [Public | Private] [Static] Property Get Имя_свойства ([Аргументы]) _
   [As Имя_типа]
   [Инструкции]
   [Имя_свойства = Выражение]
   [Exit Property]
   [Инструкции]
   [Имя_свойства = Выражение]
   End Property

   Для присвоения значения свойству, не являющемуся ссылкой на объект, предназначена процедура, объявленная с использованием Property Let:

   [Public | Private] [Static] Property Let Имя_свойства ([Аргументы,]Значение)
   [Инструкции]
   [Exit Property]
   [Инструкции]
   End Property

   Для присвоения значения свойству, являющемуся ссылкой на объект, предназначена процедура, объявленная с использованием Property Set:

   [Public | Private] [Static] Property Set Имя_свойства ([Аргументы,]Значение)
   [Инструкции]
   [Exit Property]
   [Инструкции]
   End Property

   Использование процедур и функций с ключевым словом Property очень удобно для создания свойств только для чтения (для этого свойства не реализуются Property Let и Property Set) и свойств только для записи (не реализуется Property Get).
   Разберем реализацию свойств ObjectRef и ObjectType для рассматриваемого класса Class1 (частная переменная-член objRef используется для хранения установленной ссылки на объект):

   Private objRef As Object
   Property Set ObjectRef(objNewRef As Object)
   ' Задание ссылки хранимого объекта
   Set objRef = objNewRef
   End Property
   Property Get ObjectRef() As Object
   ' Возврат ссылки на хранимый объект
   Set ObjectRef = objRef
   End Property
   Property Get ObjectType() As String
   ' Возврат имени типа хранимого объекта
   ObjectType = TypeName(objRef)
   End Property

Методы класса

   Любая функция или процедура, описанная в модуле класса, является методом этого класса. Методы делятся на общие (описаны с использованием Public) и частные (описаны с использованием Private).
   Ниже приведена реализация метода для созданного нами класса Class1, при обращении к которому на экран выводится сообщение со значениями атрибутов класса:

   Sub ShowInfo()
   ' Отображение окна со значением свойства strTag и именем типа _
   объекта, на который хранится ссылка
   MsgBox "strTag = " & strTag & vbCrLf & _
   "Object type = " & ObjectType
   End Sub

Использование класса в программе

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

   Dim obj As Class1

   После создания ссылки сам объект создается с помощью инструкции Set:

   Set obj = New Class1

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

   Dim obj As New Class1

   Для доступа к свойствам и методам объекта используется точка, например:

   obj.strTag = «Некоторый текст»
   Set obj.ObjectRef = Nothing
   MsgBox obj.ObjectType
   obj.ShowInfo

   Ниже приведен пример процедуры, которая использует реализованный класс

   Class1:
   Sub TestClass()
   ' Создание объекта
   Dim obj As New Class1
   ' Установка свойств
   Set obj.ObjectRef = New Collection
   obj.strTag = "В этом объекте хранится ссылка на объект
   Collection"
   ' Вызов метода
   obj.ShowInfo
   End Sub

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

   Рис. 1.4. Окно с информацией о свойствах объекта
 
   Как можно заметить, в процедуре TestClass не происходит явного уничтожения ссылки на объект класса Class1. Дело в том, что ссылка obj – локальная переменная процедуры. А при выходе из процедуры данные всех локальных (не статических) переменных уничтожаются, в том числе удаляются и локальные ссылки на объекты.

Использование API-функций в VBA

   Иногда даже при программировании на таком языке, как VBA, возникает необходимость использовать API-функции Windows. Эти стандартные функции действительно предоставляют программисту поистине огромные возможности – от управления отображением окон и кнопок до организации сетевого взаимодействия. Всего Windows API (Application Programming Interface) насчитывает около 1000 различных функций.

Объявление API-функций

   Чтобы API-функцию можно было вызывать из программы на VBA, ее нужно объявить с использованием инструкции Declare:

   [Public | Private] Declare Function Имя Lib «Библиотека» _
   [Alias «Псевдоним»] [([Аргументы])] [As Имя_типа]

   или, если API-функция не возвращает значения:

   [Public | Private] Declare Sub Имя Lib «Библиотека» [Alias «Псевдоним»] _
   [([Аргументы])]

   Данная инструкция помещается в блоке объявлений модуля. Ключевые слова Public и Private задают область видимости объявляемой API-функции аналогично обычной процедуре или функции. Единственной особенностью является то, что при объявлении API-функции в модуле класса нужно использовать Private. Назначение остальных элементов инструкции Declare поясняется в табл. 1.14.

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

   Ниже приведен пример объявления API-функции получения имени текущего пользователя без использования псевдонима:

   Declare Function GetUserNameA Lib «advapi32.dll» _
   (ByVal lpBuffer As String, nSize As Long) As Long

   а также с использованием псевдонима:

   Declare Function GetUserName Lib «advapi32.dll» Alias
   «GetUserNameA» _
   (ByVal lpBuffer As String, nSize As Long) As Long

   При использовании первой из приведенных инструкций для вызова функции нужно использовать имя GetUserNameA. При использовании второй – имя GetUserName.

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

   Вызов API-функций, объявленных с помощью инструкции Declare Function, ничем не отличается от вызова других функций: программист волен использовать инструкцию Call или употреблять функцию в выражениях. Если API-функция объявлена с использованием Declare Sub, то для вызова может применяться только инструкция Call (аналогично процедуре).
   Для закрепления изложенного выше рассмотрим пример использования API-функции GetUserName для получения имени текущего пользователя компьютера:

   ' Объявление API-функции с использованием псевдонима
   Declare Function GetUserName Lib «advapi32.dll» Alias
   «GetUserNameA» _
   (ByVal lpBuffer As String, nSize As Long) As Long
   Sub UserName()
   Dim strBuffer As String
   ' Создание строкового буфера для возврата значения функцией
   strBuffer = Space(100)
   ' Получение имени пользователя (ВЫЗОВ API-ФУНКЦИИ). _
   Функция возвращает ненулевое значение, если имя пользователя _
   записано в strBuffer
   If GetUserName(strBuffer, 100) Then
   ' Вывод имени пользователя
   MsgBox RTrim(strBuffer)
   Else
   MsgBox «Не удалось получить имя пользователя»
   End If
   End Sub

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

   Программирование на VBA в Microsoft Office чаще всего представляет собой управление объектами соответствующего приложения. Не является исключением и программирование в Excel. Данный раздел ознакомит читателя с основными объектами, встроенными в Excel. Эти объекты используются в подавляющем большинстве примеров (трюков), приведенных в дальнейших главах книги.

Объектная модель Excel

   На рис. 1.5 представлена значительно упрощенная структура объектов, доступ к которым имеет программист на VBA.
   Как видно из приведенного рисунка, корневым (главным) объектом, доступным в VBA, является Application. Используя ссылку на этот объект, можно манипулировать как самим запущенным приложением Excel, так и такими объектами, как рабочие книги, листы, диаграммы, окна, меню, панели инструментов, – Application предоставляет доступ ко всем объектам Excel.
   Объект Application содержит большое количество вложенных объектов. Они могут быть и объектами, с которыми можно взаимодействовать непосредственно (как Assistant – объект для работы с помощником), и представлять собой коллекции, содержащие другие объекты.

   Рис. 1.5. Структура объектов Microsoft Excel
 
   Ниже приведено описание некоторых особенно часто используемых коллекций:
   • Cells – коллекция, содержащая все ячейки рабочего листа;
   • CommandBars – коллекция, содержащая все меню и панели инструментов;
   • Comments – коллекция, содержащая все примечания рабочего листа;
   • ChartObjects – коллекция, содержащая все объекты-контейнеры внедренных в рабочий лист диаграмм (по одному объекту на каждую внедренную диаграмму);
   • Charts – коллекция, содержащая все листы диаграмм рабочей книги;
   • Dialogs – коллекция стандартных диалоговых окон Excel;
   • Sheets – коллекция, содержащая все листы книги;
   • Windows – коллекция всех отображаемых в Excel окон;
   • Workbooks – коллекция, содержащая все открытые в Excel рабочие книги;
   • Worksheets – коллекция, содержащая все рабочие листы книги.
   Объект Selection (а вернее, свойство объекта Application) предоставляет доступ к данным, выделенным на активном листе рабочей книги. В Selection могут содержаться ссылки на объекты различного типа. Тип зависит от того, что именно выделено на листе (например, если выделены ячейки, то тип объекта Selection – Range).
   Особого рассмотрения заслуживает объект Range. Он может содержать одну ячейку, диапазон ячеек или несколько диапазонов ячеек. Этот объект используется при необходимости получения или изменения значений в ячейках таблицы.
   Подробная информация о наиболее часто используемых в книге объектах Excel приведена в приложении.

Доступ к объектам Excel из программы

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

   Set objSel = Application.Selection

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

   Set objSel = Selection

   Аналогичным образом осуществляется доступ к остальным объектам. При этом с коллекциями Excel, такими как Workbooks, Worksheets и пр., работают как с обычными коллекциями VBA, содержащими ссылки на объекты:

   Worksheets(1).Name = «Sheet 1»

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

   ActiveCell.Value = 15

   или

   ActiveSheet.Name = «This sheet is now activated»

Глава 2
Рабочая область Microsoft Excel

   В данной главе мы рассмотрим порядок работы с основными элементами рабочей области Microsoft Excel – рабочей книгой, рабочим листом и ячейкой (диапазоном). Кроме того, здесь же поговорим о работе с формулами и пользовательскими функциями.

Рабочая книга

   Как отмечалось ранее, рабочая книга представляет собой файл Microsoft Excel (обычно с расширением XLSX), в котором хранится и обрабатывается необходимая информация. Используя некоторые несложные приемы, можно расширить возможности рабочей книги. Об этом будет рассказано в текущем разделе.

Автозапуск любимого файла при загрузке Excel

   Возможности программы предусматривают автоматический запуск требуемого файла одновременно с открытием Excel. Иначе говоря, при открытии Excel на экране отобразится не пустая рабочая книга (как обычно), а содержимое конкретного файла. Для достижения такого эффекта необходимо поместить требуемый файл в каталог автоматической загрузки – XLStart. Этот каталог расположен в папке с файлами Microsoft Office (например, по адресу С: \ Program Files\Microsoft Office\Office12\XLSTART). При необходимости можно поместить в указанный каталог несколько файлов – в результате при запуске Excel они автоматически будут открыты в разных окнах. Однако для настройки автоматического запуска нескольких файлов удобнее выполнить следующие действия.
   1. Открыть все файлы, которые должны автоматически открываться вместе с запуском Excel.
   2. На вкладке Вид в группе Окно выбрать команду Сохранить рабочую область и в открывшемся окне по обычным правилам Windows указать путь к каталогу автоматической загрузки (в нашем примере – С: \Program Files\Microsoft Office\Office12\XLStart), после чего нажать кнопку ОК.
   В результате в каталог автозагрузки будет помещен файл с расширением XLW (это расширение файла рабочей области). Теперь при запуске Excel будут автоматически запускаться файлы, включенные в эту рабочую область.

Восстановление важной информации из испорченного файла

   Использование трюка, описание которого приводится в данном подразделе, позволяет извлечь данные из испорченного файла с помощью встроенного в Excel механизма специальной вставки. Для этого необходимо выполнить следующие действия.
   1. Создать две новые пустые книги.
   2. В первой книге выделить диапазон ячеек и скопировать его в буфер.
   3. Перейти ко второй книге.
   4. Во второй книге выделить ячейку А1. На вкладке Главная выбрать из раскрывающегося списка кнопки Вставить (группа Буфер обмена) пункт Вставить связь.