- •Лекция 7. Процедуры и функции в vba
- •Процедуры, функции и макросы
- •Вставка процедур и функций
- •Процедуры
- •Определение процедуры
- •Вызов процедуры
- •Обработчики событий
- •Функции
- •Определение функции
- •Вызов функции
- •Функции, возвращающие массивы
- •Параметры
- •Необязательные параметры
- •Передача параметров по значению и по ссылке
Вызов процедуры
Однако одного определения процедуры недостаточно. Следующее важное действие – вызовпроцедуры. Без вызова никакая процедура работать не будет, а будет лишь лежать мёртвым грузом в модуле рабочей книги.
Проведём такую аналогию. Офисные приложения знают, как сделать шрифт текста жирным. Кто-то когда-то написал процедуру, которая делает буквы толще, и эта процедура лежит в недрах офисных приложений. Однако буквы сами по себе не становятся жирными. Для этого надо выделить часть текста и нажать кнопку на панели инструментов. Нажатие кнопки соответствует вызову процедуры.
Для вызова процедуры, написанной на языке VBA, в приложении MicrosoftExcelсуществуют следующие возможности.
Команда Run Run Sub/UserForm в VBE. Этот способ используется преимущественно для тестирования процедуры в процессе её разработки.
Диалоговое окно Макрос.
Комбинация клавиш. Перед записью макроса выводится диалоговое окно, в котором макросу можно назначить некоторую комбинацию клавиш. Процедуре (а также макросу) можно назначить комбинацию клавиш после разработки (записи) с помощью команды Разработчик Код Макросы Параметры.
Элементы управления (будут рассмотрены позже).
Вызов процедуры из другой процедуры или функции.
Пользовательский элемент управления, добавленный на ленту (сложно).
Пользовательский пункт контекстного меню (будет рассмотрено позже).
Связь процедуры с определённым событием.
Обработчики событий
Событие– это изменение в состоянии объекта.Процедура обработки события– это специальная процедура, которая запускается приложением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