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

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

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

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

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

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

Sub

<имя> (<список параметров>) <инструкции> [

Exit Sub

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

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

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

        Команда Run  Run Sub/UserForm в VBE.

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

Комбинация клавиш. Элементы управления.

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

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

Пользовательский пункт контекстного меню.

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

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

Событие – это изменение в состоянии объекта.

Процедура обработки события – это специальная процедура, которая запускается приложением Microsoft Office при наступлении определённого события.

Private Sub Workbook_SheetChange(ByVal Sh As Object, " has been changed" ByVal Source As Range) MsgBox "The range " & Source.Address(False, False) & _ " on the worksheet " & Sh.Name & _ End Sub

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

Workbook

Событие

Activate BeforeClose BeforePrint BeforeSave Deactivate NewSheet

Событие происходит

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

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

Worksheet

Событие

Activate Calculate Change Deactivate SelectionChange

Событие происходит

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

Примеры обработчиков событий

'Активация первого рабочего листа 'при открытии рабочей книги Private Sub Workbook_Open() Worksheets(1).Activate

End Sub

Примеры обработчиков событий

'Вводим в ячейку А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 End If End Sub

Примеры обработчиков событий

'Скрытие столбцов B:D перед печатью Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim sheet As Worksheet For Each sheet In Worksheets sheet.Columns("B:D").Hidden = True Next sheet End Sub

Примеры обработчиков событий

'Отображение столбцов B:D перед закрытием книги Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sheet As Worksheet For Each sheet In Worksheets sheet.Columns("B:D").Hidden = False Next sheet End Sub

Примеры обработчиков событий

'Выделение жирным шрифтом ячеек с формулами 'на конкретном рабочем листе 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

Next cell End Sub

Примеры обработчиков событий

'Выделение строки и столбца, 'на пересечении которых находится активная ячейка 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

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

Function

<имя> (<список параметров>) As <тип> <инструкции> <имя> = <выражение> [

Exit Function

] <инструкции> <имя> = <выражение>

End Function

Примеры функций

Public Function Average(mas As Range, h As Double) As Variant Dim s As Double, n As Integer, cell As Range s = 0 n = 0 For Each cell In mas If cell.Value > h Then s = s + cell.Value

n = n + 1 End If Next cell If n = 0 Then Average = CVErr(xlErrDiv0) Else Average = s / n End If End Function

Примеры функций

Public Function Check(mas As Range) As Boolean Dim cell As Range For Each cell In mas If cell.Value = "" Then Check = True Exit Function End If Next cell Check = False End Function

Вызов функции

  Функция может быть использована как формула (или часть формулы) ячейки рабочего листа.

Функция может быть вызвана из другой процедуры или функции.

Вызов функции

=Average(A1:D5;10) =Average(B2:F7;E14) Public Function Average(mas As Range, h As Double) As Variant Dim s As Double, n As Integer, cell As Range If Check(mas) Then Average = CVErr(xlErrNull) Exit Function End If ...

End Function

Функции, возвращающие массивы

Public Function GreaterThanAverage(m As Range) As Variant Dim r() As Integer, n As Integer, i As Integer Dim j As Integer, av As Double ReDim r(1 To m.Rows.Count, 1 To 1) av = 0 For i = 1 To m.Rows.Count

For j = 1 To m.Columns.Count

av = av + m.Cells(i, j) Next j Next i

av = av / m.Rows.Count / m.Columns.Count

For i = 1 To m.Rows.Count

n = 0 For j = 1 To m.Columns.Count

If m.Cells(i, j) > av Then n = n + 1 Next j r(i, 1) = n Next i GreaterThanAverage = r End Function

Параметры

Формальные параметры – это параметры, записанные в заголовке процедуры или функции.

Фактические параметры – это параметры, записанные в вызове процедуры или функции.

Список фактических параметров должен соответствовать списку формальных параметров по следующим критериям:  по количеству;   по типу; по порядку следования.

Необязательные параметры

Public Function RangePart(r As Range, Optional row As Integer = 0, Optional column As Integer = 0) As Variant If row = 0 And column = 0 Then RangePart = r ElseIf row = 0 Then RangePart = r.Columns(column) ElseIf column = 0 Then RangePart = r.Rows(row) Else RangePart = r.Cells(row, column) End If End Function

Необязательные параметры

'Функция возвращает весь диапазон, 'переданный в качестве первого параметра r = RangePart(Worksheets(5).Range("A1:C4")) 'Функция возвращает одну ячейку, 'находящуюся в 1 строке 3 столбце r = RangePart(Worksheets(5).Range("A1:C4"), 1, 3) 'Функция возвращает одну строку r = RangePart(Worksheets(5).Range("A1:C4"), 5) 'Функция возвращает один столбец r = RangePart(Worksheets(5).Range("A1:C4"), , 4) r = RangePart(Worksheets(5).Range("A1:C4"), column:=4)

Необязательные параметры

Public Sub Change(source As Range, Optional replace, _ Optional dest) Dim i As Integer, j As Integer If IsMissing(dest) Then Set dest = source Else If TypeName(dest) <> "Range" Then Exit Sub End If If Not IsMissing(replace) And TypeName(replace) <> "Range" Then Exit Sub End If

For i = 1 To source.Rows.Count

For j = 1 To source.Columns.Count

If source.Cells(i, j) < 0 Then If IsMissing(replace) Then dest.Cells(i, j) = -source.Cells(i, j) Else dest.Cells(i, j) = replace.Cells(i, j) End If Else dest.Cells(i, j) = source.Cells(i, j) End If Next j Next i End Sub

Необязательные параметры

'Замена отрицательных чисел диапазона A1:C2 на их модули 'Результат записывается в исходный диапазон A1:C2 Change Worksheets(4).Range("A1:C2") 'Замена отрицательных чисел диапазона A1:C2 'на числа из диапазона E1:G2 'Результат записывается в исходный диапазон A1:C2 Change Worksheets(4).Range("A1:C2"), _ Worksheets(4).Range("E1:G2")

Необязательные параметры

'Замена отрицательных чисел диапазона A1:C2 'на числа из диапазона E1:G2 'Результат записывается в диапазон I1:K2 Change Worksheets(4).Range("A1:C2"), _ Worksheets(4).Range("E1:G2"), _ Worksheets(4).Range("I1:K2")

Необязательные параметры

'Замена отрицательных чисел диапазона A1:C2 на их модули 'Результат записывается в диапазон I1:K2 Change Worksheets(4).Range("A1:C2"), , _ Worksheets(4).Range("I1:K2") 'Явное указание имён параметров – данный вызов процедуры 'аналогичен предыдущему Change dest:=Worksheets(4).Range("I1:K2"), _ source:=Worksheets(4).Range("A1:C2")

Передача параметров по значению и по ссылке

Public Sub ParameterByValue(ByVal x As Integer) x = x * 10 End Sub Public Sub ParameterByReference(ByRef x As Integer) x = x * 10 End Sub Dim n As Integer n = 8 ParameterByValue n ParameterByReference n 'n = 8 'n = 80