Приемы программирования в среде Visual basic for application MS office (стр. 9 )

При создании макроса можно существенно упростить процесс программирования, воспользовавшись режимом записи макроса по команде Excel Сервисð Начать Запись….

Попробуем создать макрос, который будет форматировать фрагмент листа, как показано на таблицах выше: наружная рамка будет нарисована двойной линией, а внутренние линии – одинарными линиями.

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

Начнем запись нашего макроса по команде Excel Сервисð Начать Запись…. Появляется окно, в котором должны быть заполнены исходные данные по записываемому макросу (рис.22). Здесь можно задать имя макроса, сочетание горячих клавиш, которыми он будет вызываться, и внести небольшой комментарий к подпрограмме.

После нажатия кнопки Ok появляется панель инструментов с элементами управления записью, и начинается запись. Теперь все команды, которые мы выполняем, записываются в кодах VBA. Выполним следующие действия на новом листе книги:

· выделим несколько ячеек в виде прямоугольника, например, прямоугольник из 3 столбцов и 5 строк;

· вызовем команду форматирования ячеек с помощью горячих клавиш Ctrl+1 или по команде ФорматðЯчейки. В открывшемся окне выбираем закладку Границы (рис.23);

Рис.23. Оформление границ ячеек

· на ней выбираем тип линии «двойная» и щелкаем по кнопке Внешние на панели Все;

· потом выбираем тип линии «одинарная» и щелкаем по кнопке Внутренние на панели Все;

· заканчиваем запись макроса, нажимая кнопку Остановить запись.

Переходим в VBA и смотрим изменения в нашем проекте. Там появился новый модуль2, и в нем подпрограмма Макрос1, которая имеет следующее содержание

Sub Макрос1()

' Макрос1 Макрос

' Макрос записан 02.09.2007 (Ves)

'

Range("B3:D7").Select

Selection. Borders(xlDiagonalDown).LineStyle = xlNone

Selection. Borders(xlDiagonalUp).LineStyle = xlNone

With Selection. Borders(xlEdgeLeft)

.LineStyle = xlDouble

.Weight = xlThick

.ColorIndex = xlAutomatic

End With

With Selection. Borders(xlEdgeTop)

.LineStyle = xlDouble

.Weight = xlThick

.ColorIndex = xlAutomatic

End With

With Selection. Borders(xlEdgeBottom)

.LineStyle = xlDouble

.Weight = xlThick

.ColorIndex = xlAutomatic

End With

With Selection. Borders(xlEdgeRight)

.LineStyle = xlDouble

.Weight = xlThick

.ColorIndex = xlAutomatic

End With

With Selection. Borders(xlInsideVertical)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

With Selection. Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = xlAutomatic

End With

End Sub

Рассмотрим полученный код. В первой строке процедуры выделяется область фрагмента таблицы, и дальше задаются свойства для границ ячеек в следующей последовательности – диагонали сверху вниз и снизу вверх, потом левая граница, верхняя, нижняя и правая и в конце внутренние вертикальные границы и внутренние горизонтальные. Для диагоналей тип линии (LineStyle) задается отсутствие линии (xlNone), для наружных границ – двойной линией (xlDouble) и для внутренних границ – одинарной линией (xlContinuous).

Теперь надо эту программу сделать универсальной. В самом простом варианте надо удалить строку, где выделяется область таблицы, и считать, что запускать макрос будем только после выделения заданной области таблицы. Попробуем это сделать. Удаляем строку – Range("B3:D7").Select. Затем на листе книги выделяем новый фрагмент таблицы, вызываем макрос (Alt+F8) и выполняем его. Получаем тот же результат, что и при записи макроса.

Вот мы и создали свою макрокоманду. Попробуем оптимизировать программный код. В нем имеется четыре одинаковых фрагмента, которые отличаются только параметрами. Попробуем заменить их внутренней подпрограммой. Сначала создадим внутреннюю подпрограмму (рис.24), например, Draw_Line с четырьмя формальными параметрами:

· Typ_Border – тип бордюра в который мы передадим константу, определяющую месторасположения границы (xlDiagonalDown, xlDiagonalUp, xlEdgeLeft, xlEdgeTop, xlEdgeBottm, xlEdgeRight);

· Line_Style – тип линии, который будет задаваться константой (xlNone, xlDouble, xlContinuous);

· Weight – толщина линия, в нашем случае это только xlThin, хотя имеется гораздо больше вариантов;

· Color_Index –цвет линии, который задается его индексом, для нас это только xlAutomatic.

Она должна быть внутренней (Private), как отмечено в окне вставки процедуры, показанной на рис.24. Ее программный код показан ниже.

Private Sub Draw_Line(Typ_Border, _
Line_Style, _
Optional Weight, _
Optional Color_Index)

With Selection. Borders(Typ_Border)

.LineStyle = Line_Style

If Line_Style <> xlNone Then

.Weight = Weight

.ColorIndex = Color_Index

End If

End With

End Sub

Часть кода можно просто скопировать из записанного макроса с исправлением констант, чтобы уменьшить вероятность ошибок при наборе служебных слов. Список формальных параметров введен построчно только для удобства представления данных на листе. Однако надо помнить, что строка кода может занимать несколько строк текста, и для указания этого используется символ подчеркивания, который перед собой имеет пробел, а за ним идет символ конца строки. Разрывать текст кода можно около разделителей и знаков операций. Служебное слово Optional, которое встречается перед двумя последними параметрами, говорит о том, что они необязательные к передаче, и могут быть просто опущены в списке фактических параметров, что вы увидите дальше. Теперь напишем код нашего макроса. Дадим ему имя «Мой_макрос», это будет подпрограмма общего типа. Попробуйте создать ее самостоятельно, и потом введите необходимый код, который показан ниже

Public Sub Мой_макрос()

Draw_Line xlDiagonalDown, xlNone

Draw_Line xlDiagonalUp, xlNone

Draw_Line xlEdgeLeft, xlDouble, xlThick, xlAutomatic

Draw_Line xlEdgeTop, xlDouble, xlThick, xlAutomatic

Draw_Line xlEdgeBottom, xlDouble, xlThick, xlAutomatic

Draw_Line xlEdgeRight, xlDouble, xlThick, xlAutomatic

Draw_Line xlInsideVertical, xlContinuous, xlThin, _
xlAutomatic

Draw_Line xlInsideHorizontal, xlContinuous, xlThin,_
xlAutomatic

End Sub

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

Любое окно, которое открывается по командам программ, представляет собой форму. Такая же форма может быть создана и нами. Чтобы форма и элементы на ней работали, для их событий пишутся программы, которые должны выполняться при наступлении этих событий. Попробуем создать самую простую форму с двумя элементами на ней – текстовой меткой, которая выводит текст, и кнопкой, которая может нажиматься и отжиматься мышкой. В эти моменты текстовая метка должна менять свой текст.

Сначала создадим форму, используя команду Insert ð UserForm. В рабочей области экрана появляется форма (рис.25), которая по умолчанию имеет имя UserForm1. С помощью курсоров по углам и сторонам формы можно изменять ее размеры. В окне свойств объекта имеется набор всех свойств, которые имеются у формы. Разберем их более подробно. Если перейти в режим расположения свойств по категориям, то их легче рассмотреть:

· в первой группе свойств задают заголовок формы (Caption). Имеются свойства для оформления формы – цвета фона (BackColor), цвета текста (ForeColor), который будет по умолчанию применяться в любых ее элементах, и цвета рамки (BorderColor), которая будет видна вокруг рабочей области объекта, если вид рамки (BorderStyle) ‑ одинарная линия (fmBorderStyleSingle). Для обрамления можно выбрать и другой вид рамки, используя свойство специальных эффектов (SpecialEffect);

· во второй группе обычно интерес представляет свойство показа окна (ShowModal). При установке этого свойства в состояние True окно захватывает все события и выполняет только свою программу. Это приводит к тому, что передача управления самой программе произойдет только после закрытия окна. Если в этом нет необходимости, то устанавливаем значение False;

Рис.25. Форма пользователя на рабочей области VBA

· следующая группа содержит настройки шрифта по умолчанию для всех элементов формы;

· в четвертой группе основные характеристики формы, из них нам могут понадобиться ее имя (Name), и наличие на форме дополнительных кнопок (WhatsThisButton) и (WhatsThisHelp), которые выводят в ее заголовок кнопки контекстной помощи по элементам формы;

· затем идет группа команд, позволяющих расположить на форме рисунок, и задать параметры его расположения;

· в пятой группе свойства, определяющие размер и место расположения окна на экране, а так же место его расположения при начальном запуске;

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

Справа расположена панель элементов формы (ToolBox), на которой мы будем выбирать нужные элементы. Сначала в верхней части формы расположим текстовую метку и зададим для нее следующие свойства:

· изменим шрифт (Font), увеличив размер до 16 и выбрав написание жирное с наклоном, сменим цвет на красный (ForeColor);

· для метки определим центровку текста (TextAlign) по центру, с переносом слов (WordWrap).

Теперь под этой меткой располагаем кнопку (ToggleButton), и зададим для нее заголовок (Caption) – «Нажмите кнопку». Остается вписать для формы программный код, и она будет готова. Надо выбрать событие, которое нам нужно – это нажатие на кнопку. Чтобы создать эту процедуру достаточно просто щелкнуть на этой кнопке левой кнопкой мышки. После этого форма скрывается и на рабочей области появляется процедура обработки события, которая зовется ToggleButton1_Click. Нам остается вписать необходимы код.

Private Sub ToggleButton1_Click()

If ToggleButton1.Value = True Then

Label1.Caption = "Кнопка нажата"

Else

Label1.Caption = "Кнопка отжата"

End If

End Sub

Суть кода заключается в том, что если кнопка нажата (ToggleButton1.Value = True) то в текстовую метку вписывается текст – «Кнопка нажата», в противном случает текст – «Кнопка отжата».

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

Private Sub UserForm_Initialize()

ToggleButton1_Click

End Sub

Мы познакомились с основными приемами работы в среде программирования VBA. Теперь познакомимся с приемами управления данными книги. Данные приемы можно использовать только в подпрограммах (Sub), так как при обращении к данным листа при выполнении функции происходит пересчет всех данных, что может зациклить систему.

Для получения данных с листа и переноса результатов на лист можно прямо обращаться к ячейкам листа по их адресам через объект .Range(“A1”) или по номерам строки и столбца через объект .Cells(1,1). Обе записи ссылаются на первую ячейку активного листа. Для работы с различными листами надо данные объекты привязать к нужным листам, добавив объект .Sheets(“Лист1”) с нужным именем листа. Так же можно продолжить цепочку объектов и работать с различными книгами, добавив коллекцию открытых книг объектом Workbooks("Имя_книги”). Существуют специальные объекты, которые позволяют обращаться к активным (открытым в данные момент листам, книгам и ячейкам), они имеют следующие имена: ActiveWorkBook – активная (видимая в данный момент) книга Excel; ActiveSheet – активный лист; ActiveCell – выделенная ячейка (в ней в данный момент находится курсор ввода). Пользуясь этими объектами, мы можем попадать в объекты, которые мы только что активировали. Например, новая открытая книга всегда становится – ActiveWorkBook. Если нам надо выполнить какие-либо действия на листе, мы можем написать программный код с использованием объекта ActiveSheet и запускать его на исполнение после выбора нужного листа и т. п. Часто надо бывает работать с двумя книгами – одна содержит сам код программы, а в другой находятся данные, которые надо оттуда забрать, обработать и сохранить в основной книге, где записан код программы. В этом случае мы может использовать два объекта: ThisWorkBook – книга с программным кодом и ActiveWorkBook – книга, которую мы открываем, обрабатываем и снова закрываем. В результате мы можем обработать несколько книг, которые мы будем открывать и закрывать друг за другом, все они будут отвечать объекту ActiveWorkBook.

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

Рассмотрим несколько примеров реализации этих задач:

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

Сначала готовим лист, с которым мы будем работать. Для этого создаем новую книгу, переименовываем первый лист на «Данные» и заполняем следующую информацию на нем (рис.26.). Светло-зеленым цветом отмечены ячейки, куда будет вводиться информация о интервале графика. В ячейку функции записываем формулу, например =SIN(B4). Заполняем любыми данными таблицу в 10 строк, которая является исходным материалом для графика, и после на отдельном листе с именем «График» строим сам график, и настраиваем его параметры.

Теперь разберем алгоритм, который должен быть реализован в программе. Сначала делаем активным лист с данными, и определяем активную ячейку «А1» (первая ячейка листа), после очищаем данные таблицы. Теперь можем готовить новые данные, считываем начало и конец интервала, определяем шаг при его делении на 10 точек и в цикле вычисляем эти данные, помещая их таблицу. Переходим на лист «График».

Теперь все готово для создания программы. Открываем редактор VBA (Alt+F11), создаем модуль (InsertðModule) и в него вставляем подпрограмму (InsertðProcedure) с именем «Make_Graf» и записываем следующий код:

Public Sub MakeGraf()

Sheets("Данные").Activate

ActiveSheet. Range("A1").Activate

N = 10: Ir = 6

For i = 0 To N

ActiveCell. Offset(Ir + i, 0) = ""

ActiveCell. Offset(Ir + i, 1) = ""

Next i

xl = ActiveCell. Offset(1, 1)

xr = ActiveCell. Offset(1, 3)

H = (xr - xl) / N

For i = 0 To N

ActiveCell. Offset(3, 1) = xl + H * i

y = ActiveCell. Offset(3, 3)

ActiveCell. Offset(Ir + i, 0) = xl + H * i

ActiveCell. Offset(Ir + i, 1) = y

Next i

Sheets("График").Activate

End Sub

В двух первых строках определяем активный лист и активную ячейку. Потом задаем число интервалов (N) и смещение (Ir) для таблицы данных. Потом очищаем таблицу, считываем границы интервалов, определяем шаг (H) и далее в цикле вычисляем значения (Y) и помещаем их в таблицу для графика. Для вычисления функции мы сначала помещаем текущее значение параметра в ячейку «В4» командой ActiveCell.Offset(3, 1) = xl + H * i, потом получаем значение функции командой y = ActiveCell.Offset(3, 3), полученные данные заносим в таблицу. Последняя команда делает активным лист графика. Результаты работы показаны на (рис.27).

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

Рис.27. Результаты работы программы: лист данных и сам график

Чтобы выполнить последнее действие, надо на графике выделить нужные заголовки для редактирования, перейти в строку редактирования и вписать туда формулу вида «=Адрес_Ячейки». В представленном выше примере ячейки с текстами заголовков находятся в столбце «G» с 3 по 5 строки. Для них формулы будут иметь следующий вид «=Данные!$G$3», «=Данные!$G$4» и «=Данные!$G$5». Для ввода этих формул достаточно в строке формулы при выделении нужного заголовка написать «=» и потом с помощью мышки выбрать нужную ячейку на листе «Данные».

2. Реализуем вариант программы для сбора данных из других книг Excel. Например, предположим, что мы проводим эксперименты на установке, которая записывает результаты на первый лист книги Excel. Нам надо указать программе нужную книгу, открыть ее, считать два столбца данных до первой пустой ячейки в любом из двух столбцов. Затем эти данные мы должны сохранить в книге Excel на листе «Эксперименты» по следующей схеме: находим первый свободный столбец, в первой строке заносим текущие дату и время получения данных, во второй – имя файла данных, который был открыт для чтения данных, в третьей – число пар данных и далее сами данные.

Сначала определим основные этапы программы:

- на первом этапе мы должны получить файл для обработки, здесь можно воспользоваться стандартным диалоговым окном «Открыть файл…»;

- если файл указан и открыт, проверяем наличие данных на первом листе в открытом файле, если они есть тогда:

o готовим место для сохранения данных – записываем текущие дату, время и имя файла, находим свободный столбец на листе «Эксперимент»;

o начинаем считывать и сохранять данные, иначе просто выходим из программы;

o заносим число пар данных в третью строку;

o закрываем файл с данными.

- иначе просто завершаем программу.

Теперь начнем создавать саму книгу. Нам нужна новая книга с одним листом с именем «Эксперименты». Создаем ее, удаляем лишние листы и переименовываем первый с «Лист1» на «Эксперимент». Теперь переходим в редактор VBA (Alt+F11) и начинаем писать саму программу.

Создаем модуль и вставляем в него процедуру «Sub Get_Dates».

Теперь вызываем стандартный диалог

R = Application.Dialogs(xlDialogOpen).Show

Для этого определяем переменную, которая вернет нам результат операции, т. е. при открытии файла переменная R будет иметь значение True. После знака равенства вводим служебное слово Application, которое определят саму программу Excel. Ставим точку и выбираем из появившегося списка Dialogs. В этот момент можно начинать вводить данное слово, пока курсор сам не найдет нужную позицию списка. Вводим скобку и в следующем списке находим тип окна диалога. Ставим точку и выбираем метод «Show». После можно задать набор дополнительных аргументов типа маски файла, типа файла и т. п.

В следующей строке проверяем, открыт ли файл, если нет, сообщаем в окне «MsgBox» об этом

MsgBox "Для работы укажите файл с данными", _

vbInformation + vbOKOnly

Если файл открыт, определяем два объекта для упрощения записей в коде программы для листа с данными и листа экспериментов соответственно

Set AW = ActiveWorkbook. Sheets(1)

Set TW = ThisWorkbook. Sheets("Эксперимент")

Теперь проверяем наличие данных в первой строке открытого файла

If Not IsEmpty(AW. Cells(1, 1)) And _

Not IsEmpty(AW.Cells(1, 2)) Then

Если их (данных) нет, то выводим сообщение об этом и закрываем книгу без сохранения сделанных изменений

MsgBox "На листе открытой книги данных нет", _

vbInformation + vbOKOnly