1. Процедуры, функции и макросы - shikardos.ru o_O
Главная
Поиск по ключевым словам:
страница 1
Похожие работы
1. Процедуры, функции и макросы - страница №1/1

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

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


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

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

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

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

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

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

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


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


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

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


3.Процедуры

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


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

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


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

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

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


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

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

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

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

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

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

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

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

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


Событие – это изменение в состоянии объекта. Процедура обработки события – это специальная процедура, которая запускается приложением Microsoft Office при наступлении определённого события. Такие процедуры должны иметь определённое имя, состоящее из имени объекта и имени события, и определённый набор параметров.
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"

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

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

События объекта 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


4.Функции

Определение функции


Как было сказано, функция – это подпрограмма, которая возвращает значение. Функция имеет следующий синтаксис:
Function <имя> (<список параметров>) As <тип>

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

<имя> = <выражение>

[Exit Function]



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

<имя> = <выражение>

End Function
Первая строка, содержащая имя функции, список параметров и тип результата, называется заголовком функции. Имя и параметры функции создаются и анализируются компилятором по тем же правилам, что и имя и параметры процедуры. Тип, указываемый после списка параметров, задаёт тип результата функции.

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

Рассмотрим для примера разработку функции, вычисляющей в массиве среднее арифметическое значений, больших заданного числа.
Public Function Average(mas As Range, h As Double) As Variant

Dim s As Double, n As Integer

Dim 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
Обратите внимание на то, что тип параметров указан как Range и Double, т.е. использованы конкретные типы данных. Параметры этой функции должны иметь именно такие типы – иначе не логично. При передаче в функцию параметров, типы которых не соответствуют указанным, а также если диапазон содержит не числа, функция будет возвращать признак ошибки #ЗНАЧ!. Проверка соответствия типов производится приложением Microsoft Excel. Если бы тип параметров был указан как Variant, разработчику функции пришлось бы добавлять инструкции для проверки типов параметров.

Тип результата функции указан как Variant, поскольку функция может вернуть как число, так и признак ошибки.

Объект Range обычно представляет собой двумерный массив. В данной функции, однако, используется только один цикл For Each, который позволяет перебрать все ячейки массива, в том числе и для двумерного массива. Другие языки программирования, в частности, Паскаль и С++ не имеют подобный циклов и для обработки двумерных массивов необходимо использовать два вложенных параметрических цикла. Однако, обратите внимание, что в данной функции расположение элементов по строкам и столбцам не принципиально и на результат не влияет. Поэтому можно использовать один цикл For Each. Но в других случаях, возможно, также придётся использовать два параметрических цикла.

Рассмотрим разработку функции, проверяющей в диапазоне наличие пустых ячеек.


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)


Можно вызвать функцию Check из функции Average.
Public Function Average(mas As Range, h As Double) As Variant

Dim s As Double, n As Integer

Dim cell As Range

If Check(mas) Then

Average = CVErr(xlErrNull)

Exit Function

End If

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

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


В качестве результата функция VBA может возвращать массив значений. Такая функция вставляется не в одну ячейку, а в диапазон, и вставка завершается нажатием клавиш Ctrl + Shift + Ввод.
Public Function GreaterThanAverage(m As Range) As Variant

Dim r() As Integer

Dim n As Integer, i As Integer, j As Integer

Dim 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
Обратите внимание, что одномерный массив соответствует строке, поэтому в данном случае не может быть использован. Одномерный массив, который надо расположить в виде столбца, должен быть объявлен как двумерный массив с одним столбцом.

5.Параметры


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

Между этими двумя списками существует разница, во-первых, в синтаксисе, а во-вторых, что более важно, в семантике. Список формальных параметров – это список неких условных переменных. Он описывает данные, которые должны быть переданы в подпрограмму, в общем виде. Например, в функцию Average необходимо передавать диапазон и число.

Список фактических параметров – это список вполне конкретных значений, которые реально передаются в подпрограмму и которые она обрабатывает. Мы рассматривали примеры вызова функции Average, в которых в функцию передавались конкретные диапазоны (A1:D5, B2:F7) и конкретные числа (10, число из ячейки E14). Формальные параметры – это, в общем-то, абстракция. Фактические параметры должны реально существовать, т.е. это должны быть конкретные диапазоны, константы, числа, содержащиеся в конкретной ячейке. Можно провести аналогию с математическим выражением, записанным в общем виде с использованием переменных, например, x2 + y2. Можно построить график функции, исследовать свойства этого выражения, оперировать с ним в общем виде, но нельзя вычислить значение этого выражения, пока мы не подставим конкретные числа вместо переменных x и y. Формальные параметры соответствуют переменным математического выражения, а фактические параметры – конкретным числам.

Список формальных параметров определяется количество, порядок и типы параметров, которые должны быть переданы в подпрограмму при вызове.

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

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



  1. По количеству.

  2. По типу.

  3. По порядку следования.

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


Язык VBA позволяет объявлять параметр как необязательный, а также задавать так называемые значения по умолчанию.

Для указания того, что параметр является необязательным, используется ключевое слово Optional, которое ставится перед именем параметра.

Для задания значения по умолчанию после описания формального параметра ставится знак равенства и значение, которое подставляется в подпрограмму, в случае отсутствия в вызове соответствующего фактического параметра.

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


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)
Параметры с типом Variant можно просто опускать, без задания значения по умолчанию, т.к. тип Variant позволяет в самом параметре указать факт отсутствия параметра. Для проверки, был ли параметр задан, используется функция IsMissing. Рассмотрим для примера процедуру, которая в заданном диапазоне заменяет отрицательные числа. Кроме исходного диапазона можно также задать диапазон, откуда берутся числа для замены (в случае отсутствия этого параметра отрицательные числа заменяются модулями), и диапазон, куда копируется исходный диапазон с изменёнными значениями.
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

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")



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


В списке формальных параметров перед описанием параметра может быть указано одно из ключевых слов ByVal или ByRef. Эти ключевые слова задают способ передачи параметра – по значению или по ссылке.

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

Передача параметра по ссылке означает, что переменная, являющаяся фактическим параметром, может быть изменена инструкциями подпрограммы.
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 'n = 8

ParameterByReference n 'n = 80


В языке VBA по умолчанию используется способ передачи «по ссылке», что, вообще-то, опасно, т.к. может привести к нежелательному изменению переменных.