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

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

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

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

Таблица 12.33

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

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

Символ строки

?

Любой одиночный символ

*

Любое количество символов или отсутствие символа

#

Любая одиночная цифра (0–9)

[список]

Любой одиночный символ, входящий в список

[!список]

Любой одиночный символ, не входящий в список

Для создания функции перейдите в редактор 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.

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

Рис. 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).

 
< Пред   СОДЕРЖАНИЕ     След >