• ПРЕДИСЛОВИЕ
• КРАТКИЙ ОБЗОР МАТЕРИАЛА КНИГИ
• ВВЕДЕНИЕ
• ЗАЧЕМ НУЖЕН VBA
• СОЗДАНИЕ ФУНКЦИЙ ПОЛЬЗОВАТЕЛЯ

Предисловие

Настоящая книга является с одной стороны, подробным справочником по Visual Basic for Applications (VBA), а с другой стороны, самоучителем по составлению и разработке приложений, написанных на этом языке. Это уникальное сочетание, которое, следуя рекламному подходу, можно назвать "два в одном", обеспечивает большую гибкость при решении читателем своих собственных задач. Самоучитель на большом количестве примеров умело и доступно обучает, как можно быстро и эффективно решать разнообразные задачи. В справочнике приводится подробное описание возможностей VBA, имея такие сведения под рукой у читателя исчезнет необходимость бегать по магазинам в поиске дополнительной литературы при написании самостоятельных приложений, что несомненно сбережет время и кошелек.

Самоучитель состоит из уроков. В каждом из уроков разрабатывается пример пользовательского приложения и дается подробный анализ. Тексты всех программ снабжены доскональными комментариями. Можно сказать, что все рассматриваемые программы разложены буквально по маленьким разжеванным кусочкам, которые читателю только и остается проглотить. По завершению урока предлагается самостоятельное задание, выполнение которого поможет лучше закрепить разобранный материал.

С помощью VBA можно легко и быстро создавать пользовательские приложения, используя единую для всех офисных программ среду и язык. Научившись разрабатывать приложения для одной офисной программы, например Excel (которой, как наиболее популярной офисной программе, в основном и посвящена данная книга), можно создавать приложения и для других офисных программ, например Access. Внимательно читая эту книгу, можно стать искусным разработчиком и научиться пользоваться мощными средствами разработки приложений Excel для того, чтобы конструировать эффективные и применимые к реальной жизни приложения. Кроме того, по своей структуре, интерфейсу и синтаксису VBA образует ядро Visual Basic. Поэтому тот, кто изучит программирование на VBA очень быстро может освоить и Visual Basic.

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

Краткий обзор материала книги

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

• Во введении на простейших примерах объясняется, зачем нужен VBA.
  
• Глава 1 отвечает на вопрос: "Что такое VBA?". В ней также дано описание основных структурных элементов VBA.
  
• В главе 2 рассматриваются основные элементы объектной иерархической структуры VBA.
  
• В главах 3 и 4 дан обзор методов VBA, программирующих команды для работы с рабочим листом, которые позволяют строить прогрессии, создавать фильтры и консолидировать данные, организовывать сценарии и структуры, решать уравнения, подводить промежуточные итоги и сортировать данные.
  
• Глава 5 описывает, как строятся диаграммы в VBA.
  
• В главе 6 обсуждается, как программировать одно из наиболее мощных средств по анализу данных — сводные таблицы.
  
• В глава 7 приведен обзор элементов управления VBA и описание того, как в VBA создаются и программируются диалоговые окна.
  
• Глава 8 продолжает начатый в предыдущей главе разговор по созданию пользовательского интерфейса. В ней объясняется, как создать пользовательское меню и панели инструментов.
  
• Глава 9 посвящена вопросу создания очень полезного и наглядного средства Microsoft Office — помощника.
  
• В главе 10 дано обзорное описание процесса создания графических объектов.
  
• В главе И приводится краткая информация по основным понятиям языка VBA: какими типами данных оперирует VBA, что такое переменная, константа, массив и динамический массив, как создается пользовательский тип переменной. В ней перечислены операции, встроенные функции, операторы и процедуры VBA, а также типы процедур.
  
• В главе 12 обсуждаются принципы создания процедур обработки ошибок, а также встроенные в редактор VBA мощные средства по отладке программ.
  
• В главе 13 описаны типы файлов и способы работы с ними в VBA.
  
• Глава 14 объясняет, как в VBA можно создавать пользовательские объекты.
  
• В главе 15 дан обзор методов по работе с внешними базами данных, использовании Microsoft Query, открытой связи с базой данных (ODBC) и объектов доступа к данным (DAO).
  

Во второй части приведено 14 уроков самоучителя по созданию пользовательских приложений:

• В уроке 1 на примере создания приложение по игре в орел и решку показывается: как в редакторе VBA создается программа и как она запускается на выполнение; создание пользовательского диалогового окна; программный контроль за вводом в поле чисел, а не строковой информации; программное управление запретом ввода данных в поле; работа с функцией генератора случайных чисел; вывод числовой информации в поле.
  
• В уроке 2 на примере разработки приложения по расчету маргинальной процентной ставки объясняется: как программно решаются уравнения; программный ввод формулы в ячейку рабочего листа; финансовые функции; проверка корректности ввода данных из диалогового окна; назначение клавишам <Enter> и <Esc> функций кнопок диалогового окна; создание всплывающих подсказок у элементов управления; использование MacroRecorder для упрощения и убыстрения написания кода; программное форматирование ячеек рабочего листа.
  
• В уроке 3 на рассмотренном примере работы со списком показывается: как заполняется список; управление выбором нескольких элементов из списка; как выполнить специфицированную операцию над выбранными элементами из списка с помощью переключателей.
  
• В уроке 4 на примере разработки приложения по расчету амортизации объясняются: финансовые функции расчета амортизации; управление видимостью отдельных элементов управления в окне диалога; программный вывод объектов WordArt на рабочий лист.
  
• В уроке 5 на рассмотренном примере показывается: ввод формул при помощи элемента управления RefEdit; нахождение корня уравнения зависящего от параметра; установка параметров метода GoalSeek; создание прогрессий на рабочем листе; программирование протаскивания маркера заполнения выделенного диапазона на рабочем листе; построение диаграмм.
  
• В уроке 6 на обсужденных примерах показывается: программное управление размерами диалогового окна и элементов управления; задание последовательности элементов управления в виде массива объектов; определение текущего объема вклада; задание параметров счетчика; как можно программно или при помощи drag-and-drop операции перемещать элементы управления по поверхности диалогового окна.
  
• В уроке 7 на примере конструируемого приложения демонстрируется: как при помощи диалогового окна можно заполнить базу данных на рабочем листе; программирование примечаний и текстовых полей на рабочем листе; использование переключателя и флажков; создание пользовательского заголовка окна приложения и программное закрепление области.
  
• В уроке 8 на примере разработки приложения по построению поверхности объясняется: как табулируются функции, зависящие от двух аргументов; преобразование формулы с аргументами х и у в формулу рабочего листа; программное построение поверхности; запись диаграммы в графический файл; считывание графического файла в элемент управления image; программное управление углом зрения, под которым смотрят на поверхность, и углом поворота поверхности вокруг оси Z.
  
• В уроке 9 на примере конструируемого приложения по расчету периодических выплат показывается: как используется финансовая функция пплдт (РМТ); вывод результатов табулирования функции в элемент управления ListBox (список); построение диаграммы, тип которой выбирается в группе переключателей; программная проверка наличия файла на диске.
  
• В уроке 10 на примере разработки приложения по работе с базой данных демонстрируется: конструирование пользовательского интерфейса; создание приложения, работающего с несколькими диалоговыми окнами; поиск информации в базе данных; редактирование записей в базе данных; удаление ненужных записей из базы данных; архивация данных; программирование фильтрации и сортировки данных; создание сводных таблиц; добавление пользователем новых элементов в список с полем во время выполнения программы.
  
• В уроке И на примере игры в крестики и нолики объясняется: удаление рисунка из элемента управления; учет количества щелчков по элементу управления; управление видимостью границы элемента управления; создание игрового поля.
  
• В уроке 12 на примере приложения по построению линии тренда показывается: конструирование многостраничных диалоговых окон и линии тренда; применение метода offset для вывода данных на рабочем листе; считывание данных из каждой отдельной ячейки диапазона.
  
• В уроке 13 на примере приложения по составлению расписания обсуждается: передача информации между элементами управления при обработке события click; управление видимостью рисунков и цветом элементов управления.
  
• В уроке 14 на примере показана работа с текстовыми файлами: считывание и запись в файл последовательного доступа; считывание и запись записей в файла прямого доступа; создание и работа с пользовательскими типами данных; создание простейшего текстового редактора и заставки приложения.
  

Введение

Зачем нужен VBA

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

Рис. В.1. Таблица ежемесячных расходов

Для обучения компьютера отлично подходит MacroRecorder - транслятор, создающий программу (макрос) на языке VBA, которая является результатом перевода на язык VBA действий пользователя с момента запуска MacroRecorder до окончания записи макроса.

Итак, для активизации MacroRecorder выберите команду Сервис, Макрос, Начать запись (Tools, Macro, Record New Macro). Появится диалоговое окно Запись макроса (Record Macro) (рис. В.2). Это диалоговое окно позволяет задать параметры макроса.

Поля Имя макроса (Macro Name) и Описание (Description) предназначены для задания имени макроса и его описания. Последнее важно для многократно используемых макросов. Наша память не долговечна и, не имея подсказки в виде описания, через некоторое время бывает трудно вспомнить, для чего тот или иной макрос создавался. По умолчанию макросам присваиваются имена макрос1, Макрос2 и т. д. Чтобы было легче разпознать макрос, лучше не оставлять стандартное, а присвоить ему какое-нибудь уникальное имя, поясняющее его назначение. В данном случае присвоим макросу, например, имя Расходы. Поле Сочетание клавиш (Shortcut Key) позволяет назначить макросу комбинацию клавиш, т. е. указать клавишу, которая в сочетании с клавишей <Ctrl> будет служить для запуска его на выполнение. Назначать комбинацию клавиш макросу совсем не обязательно. Это стоит делать только для постоянно используемых макросов, для быстрого доступа к ним. Макрос всегда можно вызвать командой Сервис, Макрос (Tools, Macro). Раскрывающийся список Сохранить в книге (Store Macro in) предназначен для выбора книги, в которой будет сохранен макрос. Если выбрать Личная книга макросов (Personal Macro Workbook), то макрос будет сохранен в специальной скрытой книге. Эта книга хотя и скрыта, но является открытой, и записанные в ней макросы доступны для других рабочих книг. Команда Окно, Отобразить (Window, Unhide) позволяет отобразить личную книгу макросов. Если в раскрывающемся списке выбрать Эта книга (This Workbook) (этот выбор по умолчанию предлагает компьютер), то макрос сохранится на новом листе модуля в активной рабочей книге, а если выбрать Новая книга (New Workbook) — в новой рабочей книге.

Рис. В.2. Диалоговое окно Запись макроса

Итак, в диалоговом окне Запись макроса (Record Macro) в поле Имя макроса (Macro Name) введем Расходы, а в поле Описание (Description) — Расчет месячных расходов и нажмем кнопку ОК. Появится плавающая панель инструментов с кнопкой Остановить запись (Stop Recording). Теперь все производимые действия будут записываться до тех пор, пока не будет нажата эта кнопка. Построим шаблон таблицы расходов по следующему алгоритму:

1. Активизируем ячейку B1 и введем в нее Расходы.
   
2. Активизируем ячейку А2 и введем в нее транспорт.
   
3. Активизируем ячейку A3 и введем в нее коммунальные.
   
4. Активизируем ячейку А 4 и введем в нее Еда.
   
5. Активизируем ячейку А5 и введем в нее Развлечения.
   
6. Активизируем ячейку Aб и введем в нее Одежда.
   
7. Активизируем ячейку A7 и введем в нее компьютер.
   
8. Активизируем ячейку А8 и введем в нее машина.
   
9. Активизируем ячейку A9 и введем в нее прочие.
   
10. Активизируем ячейку A10 и введем в нее итого.
    
11. Активизируем ячейку вю и введем в нее формулу =СУМM(В2:В9), вычисляющую суммарные расходы.
    
12. Выберем диапазон В2:В9 и при помощи раскрывающегося списка Границы (Borders) панели инструментов Форматирование (Formatting) создадим рамку, окаймляющую этот диапазон.
    
13. Выберем диапазон дю:вю и при помощи раскрывающегося списка Цвет заливки (Fill Color) панели инструментов Форматирование (Formatting) окрасим этот диапазон в желтый цвет.
    
14. Выберем ячейку Bi и при помощи раскрывающегося списка Цвет заливки (Fill Color) панели инструментов Форматирование (Formatting) окрасим эту ячейку в желтый цвет.
    
15. Выберем диапазон А2:Д9 и при помощи раскрывающегося списка Цвет заливки (Fill Color) панели инструментов Форматирование (Formatting) окрасим этот диапазон в светло-бирюзовый цвет.
    
16. Выберем столбец д, изменим его ширину так, чтобы введенный в диапазон А2: Д9 текст помещался в этом столбце.
    
17. Выберем диапазон А2:В9 и при помощи мастера диаграмм, вызываемого кнопкой Мастер диаграмм (Chart Wizard) панели инструментов Стандартная (Standard), создадим диаграмму.
    

Рабочий лист теперь будет выглядеть так, как показано на рис. В.1. Остановим запись макроса, нажав кнопку Остановить запись (Stop Recording).

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

Для просмотра записанной процедуры необходимо выбрать команду Сервис, Макрос, Макросы (Tools, Macro, Macros), которая вызовет диалоговое окно Макрос (Macro) (рис. В.З).

В этом диалоговом окне в списке выделим макрос и нажмем кнопку Изменить (Edit). Это вызовет появление главного окна редактора VBA (рис. В.4). Ниже приведен полный текст записанного макроса.

Рис. В.З. Диалоговое окно Макрос

Рис. В.4. Главное окно редактора VBA

Sub Расходы()

'

' Расходы Макрос

' Расчет месячных расходов '

'

Range("Bl").Select

ActiveCell.FormulaRlCl = "Расходы"

Range("A2").Select

ActiveCell.FormulaRlCl = "Транспорт"

Range("A3").Select

ActiveCell.FormulaRlCl = "Коммунальные"

Range("A4").Select

ActiveCell.FormulaRlCl = "Еда"

Range("A5").Select

ActiveCell.FormulaRlCl = "Развлечения"

Range("A6").Select

ActiveCell.FormulaRlCl = "Одежда"

Range("A7").Select

ActiveCell.FormulaRlCl = "Компьютер"

Range("A8").Select

ActiveCell.FormulaRlCl = "Машина"

Range("A9").Select

ActiveCell.FormulaRlCl = "Прочие"

Range("A10").Select

ActiveCell.FormulaRlCl = "Итого"

Range("BIO").Select

ActiveCell.FormulaRlCl = "=SUM(R[-8]C:R[-1]C)"

Range("A10:BIO").Select

Selection.Interior.Colorlndex = 36

Range("B1").Select

Selection.Interior.Colorlndex = 36

Range("A2:A9").Select

Selection.Interior.Colorlndex = 34

Columns("A:A").ColumnWidth = 13.86

Range("A2:B9").Select

Charts.Add

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData

Source:=Sheets("Лист1").Range("A2:B9"), PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Nаmе:="Лист1"

With ActiveChart

.HasTitle = False

.Axes(xlCategory, xlPrimary)

.HasTitle = False

.Axes(xlValue, xlPrimary)

.HasTitle = False

End With

End Sub

На первый взгляд полученный макрос выглядит довольно устрашающе, но прочитав данную книгу вы поймете, что его очень легко создавать и читать. Еще более замечательным является то, что в настоящий момент эта программа сама по себе совершенно не нужна и с ней можно работать даже не понимая записанные в ней коды. Пока о программе надо знать только ее имя -Расходы и то, что рабочий лист, на котором при помощи этого макроса будет строиться шаблон таблицы с диаграммой, должен иметь имя лист1. Ограничение на выбор имени рабочего листа не столь обременительно. Изучив эту книгу, вы легко сможете создавать универсальные приложения без каких-либо ограничений на среду. Для того чтобы воспользоваться макросом, надо перед его выполнением переименовать рабочий лист, присвоив ему имя лист1. После построения таблицы можно изменить имя рабочего листа на новое, например, на имя месяца, для которого строится текущий отчет по расходам.

Итак, активизируем новый рабочей лист, временно присвоим ему имя лист1. Выберем команду Сервис, Макрос, Макросы (Tools, Macro, Macros), которая вызовет диалоговое окно Макрос (Macro). В этом окне в списке выделим исходный макрос и нажмем кнопку Выполнить (Run). Диалоговое окно закроется и выполнится процедура, создающая на активном рабочем листе шаблон таблицы. Теперь в нее остается ввести новые данные, а расчет суммарных расходов и построение диаграммы будет происходить автоматически.

Создание функций пользователя

VBA предоставляет также возможность пользователю создавать собственные функции, работать с которыми на рабочем листе можно при помощи мастера функций точно так же, как и с любой встроенной функцией. Рассмотрим один пример построения функции пользователя. На минуту представьте себе, что вы менеджер издательства по оптовой продаже книг. Для привлечения покупателей в вашем издательстве введена прогрессивная шкала цен. Если продается от 100 до 200 экземпляров книги, то скидка от ее отпускной цены составляет 7%, если продается от 201 до 300 экземпляров, то скидка составляет 10%, а если свыше 300 экземпляров — 15%. Кроме того, для постоянных клиентов предусмотрена дополнительная скидка в размере 5%. Создадим функцию пользователя с именем стоимость для расчета стоимости Партиикниг. Аргументы этой функции назовем ЦенаОднойКниги, Количество и Скидка. Для аргумента Скидка предусмотрим только два допустимых значения: 1 для постоянных клиентов и 0 в противном случае. Построим пользовательскую функцию стоимость следующим образом:

1. Выполните команду Сервис, Макрос, Редактор Visual Basic (Tools, Macro, Visual Basic Editor), чтобы открыть окно редактора Visual Basic.
   
2. Выполните команду Вставка, Модуль (Insert, Module) для создания листа модуля.
   
3. Выберите значок модуля в окне Проект (Project), чтобы активизировать окно редактора кода на листе модуля.
   
4. Наберите на листе модуля приведенную ниже процедуру.
   

Function Стоимость(ЦенаОднойКниги, Количество, Скидка)

'

' Вычисление стоимости без учета скидки для постоянных клиентов '

If Количество < 100 Then

'

' Продажа до 99 экземпляров

'

СтоимостьБезСкидки = ЦенаОднойКниги * Количество Else

If Количество <= 200 Then

'

' Продажа от 100 до 200 экземпляров

'

СтоимостьБезСкидки = ЦенаОднойКниги * Количество * 0.93

Else '

' Продажа от 201 до 300 экземпляров

'

If Количество <= 300 Then

СтоимостьБезСкидки = ЦенаОднойКниги * Количество * 0.9 Else

' Продажа свыше 300 экземпляров

'

СтоимостьБезСкидки = ЦенаОднойКниги * Количество * 0.85

End If

End If

End If

'

' Корректировка стоимости с учетом скидки для постоянных клиентов

If Скидка = 0 Then

Стоимость = СтоимостьБезСкидки Else

Стоимость = СтоимостьБезСкидки * 0.95

End If

End Function

Итак, функция пользователя стоимость создана. Она включена в категорию функций, определенных пользователем, мастера функций. Благодаря тому что в VBA допустимо применение русскоязычных имен, текст написанной программы ясен и прозрачен для понимания. Кроме того, это обеспечивает и простоту использования диалогового окна мастера функций для данной функции (рис. В.5). Названия всех параметров функции стоимость в окне мастера функций выводятся также на русском языке. Доступная для понимания структура диалогового окна позволяет использовать функцию стоимость любому пользователю, даже не владеющему VBA. Для ее применения достаточно знать только имя этой функции.

Рис. В.5. Диалоговое окно для заполнения параметров функции Стоимость