Лекция 7. Процедуры и функции в vba

1. Процедуры, функции и макросы

Использование языка VBA отличается от использования других языков, в частности, языка Паскаль. На языке Паскаль обычно пишутся программы, которые преобразуются в исполняемый файл и запускаются по требованию пользователя. Программа на языке Паскаль соответствует приложению MicrosoftExcelв целом. Язык VBA (VisualBasicforApplication) предназначен для написания кода, работающего внутри приложенияMicrosoftExcel. Поэтому код на языке VBA оформляется не в виде самодостаточной программы, а в видеподпрограмм.

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

Можно также сказать, что подпрограмма – это логическиобъединённый набор действий, оформленный по правилам языка программирования.

Подпрограммы делятся на процедуры и функции.

Процедура– это подпрограмма, которая выполняет некоторые действия, но не возвращает никакого значения.

Функция– это подпрограмма, которая возвращает значение.

В офисных пакета используется также термин «макрос». Макрос– это процедура, записанная с помощью специального средства, встроенного в офисное приложение.

2. Вставка процедур и функций

Процедуры, функции и макросы хранятся в модулях. Модуль– это специальная часть рабочей книги, предназначенная для хранения процедур и функций. До написания процедур и функций в рабочую книгу необходимо вставить модуль с помощью пункта менюVBEInsert  Module.

Затем можно вставить процедуру или функцию с помощью пункта меню VBEInsert  Procedure…. В появившемся диалоговом окне необходимо указать имя подпрограммы и тип подпрограммы – процедура (Sub) или функция (Function). Остальные параметры можно оставить без изменения. Однако список параметров и тип результата функции придётся вписывать вручную. В принципе, использовать пункт меню вставки подпрограммы и соответствующий диалог совсем не обязательно. Можно просто набрать в тексте модуля строкуPublic Sub …илиPublic Function …, и после нажатия клавишиВводредактор VBA вставит завершающую строкуEnd SubилиEnd Functionи отделит новую подпрограмму чертой.

3. Процедуры

1. Определение процедуры

---

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

Sub<имя> (<список параметров>)

<инструкции>

[Exit Sub]

<инструкции>

End Sub

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

Список параметров определяет параметры, которые надо передать в процедуру. Эти параметры будут управлять поведением процедуры. Список параметров может быть пустым.

Public Sub CountOfSheets()

MsgBox "Всего листов - " & Sheets.Count & vbNewLine & _

"Рабочих листов - " & Worksheets.Count & vbNewLine & _

"Листов диаграмм - " & Charts.Count

End Sub

Public Sub ChangeNegatives()

Dim cell As Range, n As Integer

n = 0

For Each cell In Selection

If cell.Value < 0 Then

cell.Value = -cell.Value

n = n + 1

End If

Next cell

MsgBox "Обработано ячеек - " & Selection.Count & vbNewLine & "Изменено ячеек - " & n

End Sub

---

2. Вызов процедуры

Однако одного определения процедуры недостаточно. Следующее важное действие – вызовпроцедуры. Без вызова никакая процедура работать не будет, а будет лишь лежать мёртвым грузом в модуле рабочей книги.

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

Для вызова процедуры, написанной на языке VBA, в приложении MicrosoftExcelсуществуют следующие возможности.

• Команда Run  Run Sub/UserForm в VBE. Этот способ используется преимущественно для тестирования процедуры в процессе её разработки.

• Диалоговое окно Макрос.

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

• Элементы управления (будут рассмотрены позже).

• Вызов процедуры из другой процедуры или функции.

• Пользовательский элемент управления, добавленный на ленту (сложно).

• Пользовательский пункт контекстного меню (будет рассмотрено позже).

• Связь процедуры с определённым событием.

3. Обработчики событий

Событие– это изменение в состоянии объекта.Процедура обработки события– это специальная процедура, которая запускается приложениемMicrosoftOfficeпри наступлении определённого события. Такие процедуры должны иметь определённое имя, состоящее из имени объекта и имени события, и определённый набор параметров.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)

MsgBox "The range " & Source.Address(False, False) & _

" on the worksheet " & Sh.Name & " has been changed"

EndSub

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

Рассмотрим некоторые события основных объектов приложения MicrosoftExcel– рабочей книги и рабочего листа.

События объекта Workbook

---

Событие	Событие происходит
Activate	При активации рабочей книги
BeforeClose	Перед закрытием рабочей книги (если книга была изменена, событие происходит перед запросом на сохранение)
BeforePrint	Перед печатью рабочей книги или любой её части
BeforeSave	Перед сохранением рабочей книги
Deactivate	При деактивации рабочей книги
NewSheet	При добавлении нового листа в рабочую книгу
Open	При открытии рабочей книги
SheetCalculate	При пересчёте формул или изменении диаграммы
SheetChange	При изменении ячейки любого рабочего листа
SheetSelectionChange	При изменении выделенного диапазона любого рабочего листа

События объекта Worksheet

Событие	Событие происходит
Activate	При активации рабочего листа
Calculate	При пересчёте формул рабочего листа
Change	При изменении любой ячейки рабочего листа
Deactivate	При деактивации рабочего листа
SelectionChange	При изменении выделенного диапазона рабочего листа

'Активация первого рабочего листа при открытии книги

Private Sub Workbook_Open()

Worksheets(1).Activate

EndSub

'Вводим в ячейку А1 дату и время создания листа, запрашиваем имя рабочего листа

Private Sub Workbook_NewSheet(ByVal sh As Object)

Dim s As String

If TypeName(sh) = "Worksheet" Then

sh.Range("A1") = "Лист добавлен " &Now()

s=InputBox("Введите имя нового рабочего листа")

If s <> "" Then sh.Name = s

---

EndIf

EndSub

'Скрытие столбцов B:D перед печатью

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim sheet As Worksheet

For Each sheet In Worksheets

sheet.Columns("B:D").Hidden = True

Nextsheet

EndSub

'Отображение столбцов B:D перед закрытием книги

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim sheet As Worksheet

For Each sheet In Worksheets

sheet.Columns("B:D").Hidden = False

Nextsheet

EndSub

'Выделение жирным шрифтом ячеек с формулами на конкретном рабочем листе

Private Sub Worksheet_Change(ByVal target As Range)

Dim cell As Range

Set target = Intersect(target, target.Parent.UsedRange)

If target Is Nothing Then Exit Sub

For Each cell In target

cell.Font.Bold = cell.HasFormula

Nextcell

EndSub

'Выделение строки и столбца, на пересечении которых находится активная ячейка

Private Sub Worksheet_SelectionChange(ByVal target As Range)

Cells.Interior.ColorIndex = xlColorIndexNone

With ActiveCell

.EntireRow.Interior.Color = RGB(219, 229, 241)

.EntireColumn.Interior.Color = RGB(219, 229, 241)

End With

End Sub

---

Смотрите также файлы

• Lection6.docx

• Lection5.docx

• Lection4.docx

• Lection3.docx

• Lection2.docx