Применение VBA для решения практических задач

Рассмотрим несколько примеров, иллюстрирующих возможности применения VBA для решения практических задач.

Пример 12.1. Создадим в Microsoft Excel функцию пользователя, которая в указанном диапазоне подсчитывает количество ячеек, значения которых соответствуют заданному шаблону.

Шаблон будем задавать в виде текстовой строки. В строке шаблона можно использовать специальные символы шаблона, приведенные в табл. 12.33.

Таблица 12.33

Символы шаблона

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

Public Function СчетШаблон (Диапазон As Range, _.

Шаблон As String).

Dim k As Integer, r.

k = 0.

For Each r In Диапазон.

If r Like Шаблон Then k = k + 1.

Next.

СчетШаблон = k.

End Function.

В нашей функции мы воспользовались операцией сравнения строк Like, имеющейся в VBA. Шаблон задается в соответствии с синтаксисом V? A. В результате получилась простая, но очень полезная для статистической обработки текстовых данных функция.

Чтобы добавить описание функции, которое выводится в окне Мастер функций, откройте окно Макрос (вкладка Разработчик), введите название функции (СчетШаблон) в поле Название макроса, щелкните на кнопке Параметры. Введите описание функции в поле Описание.

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

На рис. 12.1 показано использование функции на рабочем листе. Строка шаблона в данном случае имеет вид «Мо*17» «*». Среди наименований товаров мы ищем мониторы с длиной диагонали 17″. Чтобы задать символ «» «внутри текстовой константы, потребовалось указать его дважды, чтобы было попятно, что это не конец строки.

Рис. 12.1. Использование функции пользователя.

Пример 12.2. Пусть на рабочем листе Excel начиная с ячейки А1 расположена таблица с результатами экзаменов. Напишем макрос в Excel, который создаст новый документ Word, поместит в него заголовок «Ведомость» и таблицу, скопированную из Excel.

Чтобы создать такой макрос, перейдите в редактор Visual Basic (в Excel), откройте существующий или создайте новый программный модуль, в котором будет находиться макрос. Поместите в этот модуль тест следующей процедуры:

Public Sub PrintToWord ().

Dim wrd As Word.Application.

Set wrd = CreateObjectfWord. Application").

With wrd.

.Visible = True.

.Documents. Add.

End With.

With wrd.Selection.

.ParagraphFormat.Alignment = wdAlignParagraphCenter.

Font.Bold = wdToggle.

.Font.Size = 16.

.TypeText Text:= «Ведомость» .

.TypeParagraph.

End With.

Range («A1»).CurrentRegion.Copy.

With wrd.Selection.

.TypeParagraph.

.Paste.

.Tables (1).AutoFormat Format:=wdTableFormatGrid3.

.Tables (1).Select.

.Font.Size = 12.

.ParagraphFormat.Alignment = wdAlignParagraphLeft.

.ParagraphFormat.FirstLinelndent = _.

CentimetersToPoints (0.44).

.Columns.Width = lnchesToPoints (1.5).

.Rows (1).Select.

.Font.Bold = True.

End With.

Set wrd = Nothing.

End Sub.

Особенностью этого макроса является то, что мы в Excel используем объекты другого приложения (Word). Чтобы это стало возможным, выполните команду Tools/Refercnces, в открывшемся окне найдите строку Microsoft Word: 14.0 Object Library (вместо 14.0 на вашем компьютере может быть другая версия), поставьте рядом с ней галочку (щелкните мышью) и нажмите кнопку ОК.

Все обращения к объектам Word должны начинаться с главного объекта Word.Application. В нашем случае его опускать нельзя, так как по умолчанию будет использоваться объект Excel. Application с другими свойствами и методами. При работе с объектами Excel объект Excel. Application можно не указывать, так как макрос создан в Excel. Объект Word. Application в макросе создает функция CreateObject. Она запускает новый экземпляр приложения Word и возвращает ссылку на созданный объект, которая сохраняется в переменной wrd. Именно эта переменная используется в программе для доступа к объектам Word.

Информация передается из рабочей книги в документ Word, как обычно, через буфер обмена. В макросе для этой цели используются методы Сору и Paste соответствующих объектов.

Методы TypeText и TypeParagraph объекта Selection вставляют текст и пустой абзац в выделенный фрагмент документа.

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

Чтобы выполнить макрос, перейдите на лист с данными и выполните команду Разработчик/Макросы. Найдите в списке имя макроса PrintToWord и нажмите Выполнить. Если в этом окне выбрать Параметры, то макросу можно назначить сочетание клавиш, которое будет использоваться для вызова макроса.

Пример 12.3. В рабочей книге имеется таблица с информацией о получателях, состоящая их двух столбцов: Получатель и Адрес (Лист1), и таблица, содержащая названия всех улиц (Лист2). Данные, находящиеся на этих листах, показаны на рис. 12.2.

Создадим диалоговое окно для ввода информации в таблицу получателей. Вид окна приведен на рис. 12.3.

Рис. 12.2. Данные, находящиеся на листах Лист1 и Лист2.

Рис. 12.3. Диалоговое окно для ввода данных в таблицу.

Чтобы создать такое окно, перейдите в редактор Visual Basic. Добавьте к проекту модуль формы. В нашем проекте имя этой формы — UserForml. Разместите в форме элементы управления: четыре поля для ввода фамилии (TextBoxl), дома (TextBox2), корпуса (TextBox3) и квартиры (TextBox4); поле со списком для ввода названия улицы (ComboBoxl); пять элементов Label, которые используются для вывода поясняющих надписей рядом с предыдущими элементами; кнопки Добавить (Command Button 1) и Выход (CommandButton2).

В окно кода формы поместите следующий текст:

Private Sub CommandButton1_Click ().

ActiveCell.Value = TextBoxl. Text If Right (ComboBox1 .Text, 4) = «nep.» Or _.

Right (ComboBox1.Text, 6) = «бульв.» Then.

S = ComboBoxl .Text.

Else.

S = «ул. «& ComboBoxl .Text.

End If.

S = S &", дом «& TextBox2.Text.

If TextBox3. Text «» Then S = S &", корп. «& TextBox3.Text.

S = S &", kb. «& TextBox4.Text.

ActiveCell.Next. Value = S.

Cells (ActiveCell.Row + 1, ActiveCell. Column).Activate.

TextBoxl .Text ="" .

TextBox2.Text = «» .

TextBox3.Text =.

TextBox4.Text = «» .

End Sub.

Private Sub CommandButton2_Click ().

UserForml.Hide.

End Sub.

Процедура CommandButtonl_Click выполняется, когда будет нажата кнопка Добавить. Она переносит данные из формы на рабочий лист. Содержимое поля TextBoxl записывается в активную ячейку рабочего листа, сформированный адрес — в соседнюю с ней ячейку справа (для доступа к этой ячейке используется свойство Next). Курсор устанавливается в ячейку, находящуюся в следующей строке, и все ноля очищаются.

Процедура CommandButton2_Click выполняется, когда будет нажата кнопка Выход. С помощью метода Hide форма убирается с экрана.

Чтобы форму было удобно вызывать при работе с таблицей, поступим следующим образом. В программном модуле (не в модуле формы!) создадим следующий макрос:

Public Sub Adrlnput ().

UserForml .ComboBoxI .RowSource = _.

" Лист2! «& Лист2. Range («A3»).CurrentRegion.Address.

UserForml.Show.

EndSub.

В макросе Adrlnput сначала элемент ComboBoxI заполняется значениями, а затем форма выводится на экран. Для заполнения списка ComboBox1 используется свойство RowSource, которому присваивается ссылка на диапазон рабочего листа Лист2 с названиями улиц (в виде текстовой строки). Так как макрос находится не в модуле формы, то для доступа к элементу мы сначала должны указать имя формы, а затем, после точки, имя элемента. По условию задачи известно, что названия улиц располагаются начиная с ячейки А3. Используя для этого диапазона свойство CurrentRegion, получаем новый диапазон, содержащий все названия улиц. Такой прием позволяет менять список улиц (добавлять, удалять), программа не зависит от его размера. Свойство Address возвращает текстовую строку, соответствующую ссылке на вычисленный диапазон, например «А3:А15». Добавив с помощью операции конкатенации к полученной строке имя листа, мы получили нужное значение свойства. Метод Show выводит форму на экран.

Для вызова макроса на рабочем листе используйте команду Разработчик/Макросы. Можно назначить макросу комбинацию клавиш (Разработчик/Макросы/Параметры), тогда вызывать его при вводе данных будет еще удобнее.

Подробно вопросы практического использования VBA освещены в книге «Информатика для экономистов: практикум» под ред. В. П. Полякова, В. П. Косарева (, 2012).