Основы программирования на языке VBA. Процедуры «Function» и «Sub» в VBA

9.3.1. Подробнее о точном соответствии Самый простой случай возникает тогда, когда типы фактических аргументов совпадают с типами формальных параметров. Например, есть две показанные ниже перегруженные функции max(). Тогда каждый из вызовов max() точно соответствует одному

Немного подробнее о Buganizer Самый старый баг, зарегистрированный в Google, создан 18 мая 2001 года в 15:33 и существует до сих пор. Он называется «Test Bug», а его описание выглядит как «First Bug!». Смешно, что этот баг до сих пор случайно всплывает, когда разработчики привязывают

Для начала в качестве примера рассмотрим простую процедуру VBA типа Sub. Она хранится в модуле VBA и вычисляет сумму первых ста положительных целых чисел. По окончании вычислений процедура отображает сообщение с результатом.

Sub VBA_Demo()
" Пример простой процедуры VBA
Dim Total As Long, i As Long
Total = 0
For i = 1 To 100
Total = Total + i
Next i
MsgBox Total
End Sub

Скачать заметку в формате или

В этой процедуре применяются некоторые популярные элементы языка:

• комментарий (строка, начинающаяся апострофом);
• оператор объявления переменной (строка, начинающаяся ключевым словом Dim);
• две переменные (Total и i);
• два оператора присваивания (Total = 0 и Total = Total + i);
• циклическая структура (For–Next);
• функция VBA (MsgBox).

Комментарии

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

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

Переменные, типы данных и константы

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

В VBA используется очень много зарезервированных слов, которые не допускается применять в качестве названий переменных или процедур.

Интерпретатор VBA облегчает жизнь программистам, автоматически обрабатывая любые типы данных . Однако это чревато негативными последствиями - медленным выполнением операций и менее эффективным использованием памяти. В результате, позволяя VBA самостоятельно определять типы данных, вы можете столкнуться с проблемами выполнения больших или сложных приложений.

Рекомендуется выбирать тот тип данных, в котором используется минимальное количество байтов для хранения значений. Для проведения математических вычислений в рабочих листах Excel использует тип данных Double. Его рекомендуется применять и в процессе обработки чисел в VBA для обеспечения той же точности вычислений.

Существуют три типы областей действия переменных:

Работа с константами

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

Для объявления констант используется оператор Const. Например,

Const NumQuarters as Integer = 4

Как и переменные, константы имеют область действия. При попытке изменения значения константы в коде VBA вы получите сообщение об ошибке (чего и следовало ожидать). Константа - это постоянное значение, а не переменная. В Excel и VBA существует целый ряд предопределенных констант, которые можно использовать без объявления. Вам даже необязательно знать значение этих констант для их применения. При записи макросов обычно используются константы, а не значения. В следующей процедуре для изменения ориентации страницы активного листа на альбомную применена встроенная константа:

Sub SetToLandscape ()
ActiveSheet.PageSetup.Orientation = xlLandscape
End Sub

Фактическое значение переменной xlLandscape равно 2. Окно Object Browser содержит список всех констант Excel и VBA. Чтобы открыть Object Browser в VBE, нажмите клавишу .

В VBA дата и время определяются как значения, заключенные между знаками #

Const FirstDay As Date = #1/1/2007#
Const Noon = #12:00:00#

Даты всегда определяются в формате " месяц/день/год " , даже если система настроена на отображение данных в другом формате.

Операторы присваивания

Оператор присваивания - это инструкция VBA, выполняющая математическое вычисление и присваивающая результат переменной или объекту. В справочной системе Excel выражение определяется как комбинация ключевых слов, операторов, переменных и констант. Эта комбинация возвращает в результате строку, число или объект. Выражение может выполнять вычисление, обрабатывать символы или тестировать данные.

Большое количество операций, выполняемых в VBA, связано с разработкой (и отладкой) выражений. Если вы знаете, как создавать формулы в Excel, то у вас не будет возникать проблем с созданием выражений в VBA. В формуле рабочего листа Excel результат отображается в ячейке. С другой стороны, выражение VBA может присваивать значение переменной или использоваться как значение свойства. В VBA оператором присваивания выступает знак равенства (=).

Массивы

Массив - это группа элементов одного типа, которые имеют общее имя; на конкретный элемент массива ссылаются, используя имя массива и индекс. Например, можно определить массив из 12 строк так, чтобы каждая переменная соответствовала названию месяца. Если вы назовете массив MonthNames, то можете обратиться к первому элементу массива как MonthNames (0), ко второму - как MonthNames (1) и так до MonthNames (11).

Объявить массив, содержащий ровно 100 целых чисел, можно следующим образом:

Dim MyArray(1 То 100) As Integer

По умолчанию в массивах VBA в качестве первого элемента используется нуль. Если вы хотите, чтобы в качестве первого индекса всех массивов использовалась единица, то перед первой процедурой модуля нужно сделать следующее объявление: Option Base 1

Динамический массив не имеет предопределенного количества элементов. Он объявляется с незаполненными значениями в скобках: Dim MyArray() As Integer. Тем не менее, прежде чем динамический массив можно будет использовать в программе, необходимо обратиться к оператору ReDim, указывающему VBA, сколько элементов находится в массиве. Для этого часто применяется переменная, значение которой неизвестно до тех пор, пока процедура не будет запущена на выполнение. Например, если переменной х присвоено число, размер массива определяется с помощью следующего оператора: ReDim MyArray (1 to х).

Объектные переменные

Объектая переменная - это переменная, представляющая целый объект, например, диапазон или рабочий лист: Dim InputArea As Range. Для присваивания объекта переменной воспользуйтесь ключевым словом Set: Set InputArea = Range(«С16:Е16»).

Встроенные функции

В VBA есть ряд встроенных функций, упрощающих вычисления и операции. Например, функция VBA UCase, преобразующая строку в верхний регистр, эквивалентна функции Excel ПРОПИСН. Чтобы использовать функцию Excel в операторе VBA, перед названием функции введите следующее выражение:

Application.WorksheetFunction

Важно понимать, что вы не можете использовать функции Excel, для которых в VBA представлены эквивалентные функции. Например, VBA не позволяет получить доступ к функции Excel КОРЕНЬ (SQRT), так как в VBA имеется собственная версия этой функции: Sqr. Таким образом, следующий оператор выдает ошибку:

MsgBox Application.WorksheetFunction.Sqrt(123)

Функция MsgBox - одна из самых полезных в VBA. Кроме прочего, это превосходный инструмент отладки, поскольку вы можете в любое время вставить функцию MsgBox, чтобы приостановить программу и отобразить результат вычисления или присваивания. Функция MsgBox не только возвращает значение, но и отображает диалоговое окно, в котором пользователь может выполнить определенные действия. Значение, возвращаемое функцией MsgBox, является ответом пользователя на отображенный запрос. Функция MsgBox может применяться даже в том случае, когда ответ пользователя не требуется, а нужно отобразить сообщение. Синтаксис функции MsgBox:

MsgBox(сообщение[, кнопки] [, заголовок] [, файл_справки, контекст])

• Сообщение (обязательный аргумент) - сообщение, которое отображается в диалоговом окне.
• Кнопки (необязательный аргумент) - значение, определяющее, какие кнопки и пиктограммы (если нужно) отображаются в окне сообщения. Применяйте встроенные константы (например, vbYesNo).
• Заголовок (необязательный аргумент) - текст, который отображается в строке заголовка окна сообщения. По умолчанию отображается текст Microsoft Excel.
• Файл_справки (необязательный аргумент) - название файла справки, соответствующего окну сообщения.
• Контекст (необязательный аргумент) - контекстный идентификатор раздела справки. Представляет конкретный раздел справки для отображения. Если используется аргумент контекст, следует также задействовать аргумент файл_справки.

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

Ans = MsgBox(" Продолжить? " , vbYesNo + vbQuestion, " Сообщи ")
If Ans = vbNo Then Exit Sub

Обратите внимание, что в качестве значения аргумента кнопки используется сумма двух встроенных констант (vbYesNo + vbQuestion). Благодаря константе vbYesNo в окне сообщения отображаются две кнопки: одна с меткой Yes, а вторая - с меткой No. Добавление vbQuestion в состав аргумента также приведет к отображению значка вопроса. Как только будет выполнен первый оператор, переменная Ans получит одно из двух значений, представленных константами vbYes и vbNo. В этом примере процедура завершает свою работу после щелчка на кнопке No.

Управление объектами и коллекциями

VBA предлагает две конструкции, которые помогут вам упростить управление объектами и коллекциями. Конструкция With — End With позволяет выполнять несколько операций над одним объектом. Чтобы понять, как она работает, проанализируйте следующую процедуру, которая изменяет шесть свойств выделенного объекта (подразумевается, что выделен объект Range).

Sub ChangeFontl()
Selection.Font.Name = " Cambria "
Selection.Font.Bold = True Selection.Font.Italic = True
Selection.Font.Size = 12
Selection.Font.Underline = xlUnderlineStyleSingle
Selection.Font.ThemeColor = xlThemeColorAccentl
End Sub

Эту процедуру можно переписать с помощью конструкции With — End With. Процедура, показанная ниже, работает точно так же, как и предыдущая.

Sub ChangeFont2 ()
With Selection.Font
.Name = " Cambria "
.Bold = True
.Italic = True
.Size = 12
.Underline = xlUnderlineStyleSingle
.ThemeColor = xlThemeColorAccentl
End With
End Sub

Предположим, вы решили выполнить действие над всеми объектами коллекции или вам необходимо оценить все объекты коллекции и совершить действие при выполнении определенных условий. Это идеальная ситуация для применения конструкции For Each — Next. Синтаксис конструкции:

For Each элемент In коллекция
[инструкции ]
[инструкции ]
Next [элемент ]

Например:

Sub CountSheets()
Dim Item as Worksheet
For Each Item In ActiveWorkbook.Worksheets
MsgBox Item.Name
Next Item
End Sub

В следующем примере закрываются все окна, за исключением активного:

Sub Closelnactive()
Dim Book as Workbook
For Each Book In Workbooks
If Book.Name <> ActiveWorkbook.Name Then Book.Close
Next Book
End Sub

Контроль за выполнением кода

Некоторые процедуры VBA начинают выполняться с первых строк кода. Однако иногда необходимо контролировать последовательность операций, пропуская отдельные операторы, повторно выполняя некоторые команды и проверяя условия для определения следующего действия, выполняемого процедурой.

Оператор GoTo перенаправляет ход выполнения программы на новую инструкцию, которая помечена специальным образом (текстовая строка, заканчивающаяся двоеточием, или число, заканчивающееся пробелом, указанные перед инструкцией). В приведенной ниже процедуре применена функция VBA InputBox для получения имени пользователя. Если имя пользователя отличается от Ховард, то процедура переходит к метке WrongName, на чем заканчивает свою работу. В противном случае процедура выполняет дополнительные операции. Оператор Exit Sub заканчивает выполнение процедуры.

Sub GoToDemo()
UserName = InputBox(" Введите свое имя: ")
If UserName <> " Ховард " Then GoTo WrongName
MsgBox (" Привет, Ховард… ")
" — [Здесь вводится дополнительный код] —
Exit Sub
WrongName:
MsgBox " Извините, эту процедуру может запускать только Ховард. "
End Sub

В действительности оператор GoTo необходим только для перехват ошибок (код выше является неудачным примером, который не следует использовать).

Вероятно, конструкция If-Then чаще остальных используется для группирования инструкций VBA:

If условие Then инструкции_истина

Например

Sub GreetMe()
If Time < 0.5 Then
MsgBox " Доброе утро "
Elself Time >= 0.5 And Time < 0.75 Then
MsgBox " Добрый день "
Else
MsgBox " Добрый вечер "
End If
End Sub

VBA использует систему дат и времени, похожую на задействованную в Excel. Время дня выражается дробным числом, например, полдень представлен как 0.5. Значение 0.75 представляет время 18:00 - три четверти суток и тот момент, когда день переходит в вечер. Вложенные структуры If-Then достаточно громоздкие. Поэтому рекомендуется использовать их только для принятия простых бинарных решений. Если же необходимо выбрать между тремя и более вариантами, то целесообразно обратиться к конструкции Select Case.

В следующей процедуре используется функция VBA WeekDay, с помощью которой определяется, является ли текущий день субботой либо воскресеньем (функция Weekday возвращает значение 1 либо 7). Затем отображается соответствующее сообщение.

Sub GreetUserlO
Select Case Weekday(Now)
Case 1, 7
MsgBox " Это выходные "
Case Else
MsgBox " Это не выходные "
End Select
End Sub

Интерпретатор VBA осуществляет выход из конструкции Select Case, как только найдено условие True. Следовательно, для максимальной эффективности, в первую очередь, следует выполнить проверку наиболее вероятного случая.

Цикл - это процесс повторения набора инструкций. Возможно, вы заранее знаете, сколько раз должен повториться цикл, или это значение определяется переменными в программе. Простейший пример хорошего цикла - For-Next:

For счетчик = начало То конец
[инструкции ]
[инструкции ]
Next [счетчик ]

Следующая процедура суммирует квадратные корни первых 100 целых чисел:

Sub SumSquareRoots()
Dim Sum As Double
Dim Count As Integer
Sum = 0
For Count = 1 To 100
Sum = Sum + Sqr(Count)
Next Count
MsgBox Sum
End Sub

Значение переменной Step в цикле For-Next может быть отрицательным. Приведенная ниже процедура удаляет строки 2, 4, 6, 8 и 10 в активном листе:

Sub DeleteRows ()
Dim RowNum As Long
For RowNum = 10 To 2 Step -2
Rows(RowNum).Delete
Next RowNum
End Sub

Циклы For-Next могут также содержать один или более операторов Exit For. Когда программа встречает этот оператор, то сразу же выходит из цикла:

Sub ExitForDemo()
Dim MaxVal As Double
Dim Row As Long
MaxVal = Application.WorksheetFunction.Max(Range(" A:A "))
For Row = 1 To 1048576
If Cells(Row, 1).Value = MaxVal Then
Exit For
End If
Next Row
MsgBox " Максимальное значение в строке " & Row
Cells(Row, 1).Activate
End Sub

Максимальное значение в столбце вычисляется с помощью функции Excel МАХ. Затем это значение присваивается переменной MaxVal. Цикл For-Next проверяет каждую ячейку в столбце. Если определенная ячейка равна MaxVal, оператор Exit For заканчивает процедуру. Однако перед выходом из цикла процедура сообщает пользователю о расположении искомой ячейки и активизирует ее.

Цикл Do While выполняется до тех пор, пока удовлетворяется заданное условие. Цикл Do While может иметь один из двух представленных ниже синтаксисов.

Do
[инструкции ]
[инструкции ]
Loop

Do
[инструкции ]
[инструкции ]
Loop

Процедура EnterDates1 вводит даты текущего месяца в столбец рабочего листа, начиная с активной ячейки:

Sub EnterDatesl ()
" цикл Do While, условие проверяется в начале
Dim TheDate As Date
TheDate = DateSerial(Year(Date), Month(Date), 1)
Do While Month(TheDate) = Month(Date)
ActiveCell = TheDate
TheDate = TheDate + 1
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

В этой процедуре используется переменная TheDate, которая хранит даты, записанные в рабочем листе. Для инициализации переменной используется первый день текущего месяца. В процессе выполнения цикла значение переменной TheDate было введено в активную ячейку, затем это значение было увеличено на единицу, после чего активизируется следующая ячейка. Цикл выполняется до тех пор, пока значение месяца, присвоенное переменной TheDate, совпадет со значением месяца текущей даты.

Циклы Do While также могут включать один или более операторов Exit Do. По достижении оператора Exit Do цикл завершается, а управление передается оператору, следующему за оператором Loop.

Структура цикла Do Until имеет много общего с конструкцией Do While. Разница заключается лишь в том, как проверяется условие цикла. В варианте Do While цикл выполняется до тех пор, пока выполняется условие. В цикле Do Until цикл выполняется, пока условие не станет выполняться. Структура Do Until также может быть представлена двумя видами синтаксиса.

По материалам книги . – М: Диалектика, 2013. – С. 211–251.

Встречаются ситуации, когда от программы VBA требуется совершить несколько раз подряд один и тот же набор действий (то есть повторить несколько раз один и тот же блок кода). Это может быть сделано при помощи циклов VBA.

К циклам VBA относятся:

• Цикл For
• Цикл Do While
• Цикл Do Until

Оператор цикла «For» в Visual Basic

Структура оператора цикла For в Visual Basic может быть организована в одной из двух форм: как цикл For … Next или как цикл For Each .

Цикл «For … Next»

Цикл For … Next использует переменную, которая последовательно принимает значения из заданного диапазона. С каждой сменой значения переменной выполняются действия, заключённые в теле цикла. Это легко понять из простого примера:

For i = 1 To 10 Total = Total + iArray(i) Next i

В этом простом цикле For … Next используется переменная i , которая последовательно принимает значения 1, 2, 3, … 10, и для каждого из этих значений выполняется код VBA, находящийся внутри цикла. Таким образом, данный цикл суммирует элементы массива iArray в переменной Total .

В приведённом выше примере шаг приращения цикла не указан, поэтому для пошагового увеличения переменной i от 1 до 10 по умолчанию используется приращение. Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова Step , как показано в следующем простом примере.

For d = 0 To 10 Step 0.1 dTotal = dTotal + d Next d

Так как в приведённом выше примере задан шаг приращения равный 0.1 , то переменная dTotal для каждого повторения цикла принимает значения 0.0, 0.1, 0.2, 0.3, … 9.9, 10.0.

Для определения шага цикла в VBA можно использовать отрицательную величину, например, вот так:

For i = 10 To 1 Step -1 iArray(i) = i Next i

Здесь шаг приращения равен -1 , поэтому переменная i с каждым повторением цикла принимает значения 10, 9, 8, … 1.

Цикл «For Each»

Цикл For Each похож на цикл For … Next , но вместо того, чтобы перебирать последовательность значений для переменной-счётчика, цикл For Each выполняет набор действий для каждого объекта из указанной группы объектов. В следующем примере при помощи цикла For Each выполняется перечисление всех листов в текущей рабочей книге Excel:

Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox "Найден лист: " & wSheet.Name Next wSheet

Оператор прерывания цикла «Exit For»

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

Применение оператора Exit For продемонстрировано в следующем примере. Здесь цикл перебирает 100 записей массива и сравнивает каждую со значением переменной dVal . Если совпадение найдено, то цикл прерывается:

For i = 1 To 100 If dValues(i) = dVal Then IndexVal = i Exit For End If Next i

Цикл «Do While» в Visual Basic

Цикл Do While выполняет блок кода до тех пор, пока выполняется заданное условие. Далее приведён пример процедуры Sub , в которой при помощи цикла Do While выводятся последовательно числа Фибоначчи не превышающие 1000:

"Процедура Sub выводит числа Фибоначчи, не превышающие 1000 Sub Fibonacci() Dim i As Integer "счётчик для обозначения позиции элемента в последовательности Dim iFib As Integer "хранит текущее значение последовательности Dim iFib_Next As Integer "хранит следующее значение последовательности Dim iStep As Integer "хранит размер следующего приращения "инициализируем переменные i и iFib_Next i = 1 iFib_Next = 0 "цикл Do While будет выполняться до тех пор, пока значение "текущего числа Фибоначчи не превысит 1000 Do While iFib_Next < 1000 If i = 1 Then "особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

В приведённом примере условие iFib_Next < 1000 проверяется в начале цикла. Поэтому если бы первое значение iFib_Next было бы больше 1000, то цикл бы не выполнялся ни разу.

Другой способ реализовать цикл Do While – поместить условие не в начале, а в конце цикла. В этом случае цикл будет выполнен хотя бы раз, не зависимо от того, выполняется ли условие.

Схематично такой цикл Do While с проверяемым условием в конце будет выглядеть вот так:

Do ... Loop While iFib_Next < 1000

Цикл «Do Until» в Visual Basic

Цикл Do Until очень похож на цикл Do While : блок кода в теле цикла выполняется раз за разом до тех пор, пока заданное условие выполняется (результат условного выражения равен True ). В следующей процедуре Sub при помощи цикла Do Until извлекаются значения из всех ячеек столбца A рабочего листа до тех пор, пока в столбце не встретится пустая ячейка:

IRow = 1 Do Until IsEmpty(Cells(iRow, 1)) "Значение текущей ячейки сохраняется в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop

В приведённом выше примере условие IsEmpty(Cells(iRow, 1)) находится в начале конструкции Do Until , следовательно цикл будет выполнен хотя бы один раз, если первая взятая ячейка не пуста.

Однако, как было показано в примерах цикла Do While , в некоторых ситуациях нужно, чтобы цикл был выполнен хотя бы один раз, не зависимо от первоначального результата условного выражения. В таком случае условное выражение нужно поместить в конце цикла, вот так:

Do ... Loop Until IsEmpty(Cells(iRow, 1))

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Цикл For Loop в VBA – один из самых популярных циклов в Excel. Данный цикл имеет две формы – For Next и For Each In Next. Данные операторы используются для последовательного перемещения по списку элементов или чисел. Для завершения цикла мы можем в любой момент использовать команду выхода. Давайте подробнее рассмотрим каждый из этих циклов.

VBA цикл For Next

Цикл For Next имеет следующий синтаксис:

То что мы делаем здесь, по существу, это создаем цикл, который использует переменную счетчик как хранитель времени. Устанавливаем его значение равным начало_счетчика , и увеличиваем (или уменьшаем) на 1 во время каждого витка. Цикл будет выполняться до тех пор, пока значение счетчик не станет равным конец_счетчика. Когда оба эти значения совпадут, цикл выполнится последний раз и остановится.

Пример цикла

счетчик будет равным 11

VBA обратный цикл For Loop с инструкцией STEP

Если у вас появилась необходимость перемещаться от большего значения к меньшему – вы можете использовать цикл в обратном направлении. Вот пример обратного цикла:

Последнее значение переменной счетчик будет равным 1.

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

VBA цикл For Each … Next

Цикл For Each … Next имеет следующий цикл:

Здесь переменная элемент_группы принадлежит к группе_элементов (железная логика!!!). Я имею в виду, что объект группа_элементов должен быть коллекцией объектов. Вы не сможете запустить цикл For Each для отдельно объекта (Microsoft сразу оповестит вас об этом 438-й ошибкой).

Данный цикл перебирает все элементы какой-либо коллекции, начиная с самого первого. Вы можете использовать данный цикл, если вам необходимо, например, обойти все листы в книге, объекты на листе, сводные таблицы и т.д.

Ниже представлен пример, как можно воспользоваться циклом For Each для просмотра всех листов книги:

… либо всех сводных таблиц на листе

Прерывание цикла VBA

Если вам необходимо выйти из цикла до момента, как будет достигнуто условие завершения цикла, воспользуйтесь командой End For в связке с инструкцией IF . В примере, приведенном ниже, мы выйдем из цикла до момента достижения условия завершения, в данном цикле выход будет осуществлен при условии, когда переменная счетчик будет равна 3.

Пропуск части цикла в For Each

Пропускать часть цикла, а затем возвращаться назад – плохая практика. Тем не менее, давайте рассмотрим пример:

Здесь мы пропустили одну итерацию (когда j = 3). Как вы думаете, какой результат выдаст программа? 3? 5? Ну… на самом деле, ни один из вариантов не верный. Цикл будет выполняться бесконечно, пока память компьютера не переполнится.

Однако возможность пропустить шаг цикла без последствий существует. Вы можете увеличить значение счетчика на 1 (или другое значение), что приведет к пропуску операций, находящихся между этими значениями. Вот пример:

Но опять же, это плохая практика написания кода, и может привести к нежелательным последствиям при написании кода в будущем. Вместо этого, при необходимости пропуска некоторых итераций, попробуйте использовать функцию If или Select Case.

Excel VBA. Цикл в макросе

Создадим самый простой цикл используя VBA в Excel.

В этот раз напишем всё своими руками:

Sub Заполнение() "Запускаем цикл от 1 до 10 "Начало цикла For i = 1 To 10 "В первый столбец записываем порядковый номер Cells(i + 1, 1) = i "Во второй столбец записываем произведение порядкового номера и значения указанного в ячейке "B1" Cells(i + 1, 2) = i * Range("b1").Value "Конец цикла Next End Sub

Расшифрую, приведённый выше код VBA:

• Весь код макроса заключается между Sub и End Sub, после Sub пишется название макроса.
• Текст после ‘ означает комментарий
• Цикл заключается между For … to и Next
• i - в моём случае переменная, Вы можете выбрать любую другую, в том числе поддерживаются и русские обозначения (например: For переменная=1 To 10 …)
• Cells(строка, столбец) – ячейка в которую хотим что-то записать или из которой хотим извлечь информацию, в нашем случае столбец мы указали константу (1 и 2), а строку сделали переменной (i+1).
• Range(“b1”) возвращает значение ячейки “B1”

А ещё, можно сразу дописать минимакрос по очистке диапазона от значений

Sub Очистка() Range("A2:B11").ClearContents End Sub

Доброго времени суток! Данную статью я решил посвятить рубрике по основам программирования в Visual Basic for Application . И сегодня мы поговорим о циклах в VBA, разберём их синтаксис и рассмотрим несколько примеров, которые часто встречаются программисту.

Сначала напомню, что цикл - процесс повторения части кода, выполняемый, пока не будет выполнено или нарушено заданное нами условие.

В данной статье мы разберём синтаксис и примеры следующих циклов в VBA :

• For each
• While
• Until

Цикл For в VBA

Цикл for в VBA обычно используется при зацикливании фрагмента кода, если нам известно конечное значение counter - счетчика, при котором мы выйдем из цикла.
Возьмём для примера самый распространённый пример:

Сгенерировать массив из 5 целых значений

Dim mas(5) As Integer For i% = 0 To 4 mas(i) = Int((10 * Rnd) + 1) Next i

Обратите ваше внимание, что в этом примере используется неявное объявление при работе с циклами в VBA. i% - означает неявное объявление переменной i в формате integer. Такая конструкция по сути заменяет следующую: dim i as integer . Это используется для сокращения кода и для удобства написания и чтения. В старых версиях VBA необходимо указывать знак формата после каждого использования неявной переменной. В более поздних версиях достаточно всего один раз.

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

Найти пересечение графика функции y = 5*x + 5 с осью ординат

Function expr(x As Integer) As Integer expr = 5 * x + 5 End Function Sub CodeTown() Dim i As Integer For i = -10 To 10 Step 1 If expr(i) = 0 Then MsgBox "При Y = 0, Х = "+ CStr(i) Next i End Sub

Теперь представим, что у нас достаточно большой диапазон и мы не хотим заставлять компьютер считать лишние итерации. На этот случай существует команда Exit For . Перепишем наш последний пример с новой командой.

Function expr(x As Integer) As Integer expr = 5 * x + 5 End Function Sub CodeTown() Dim i As Integer For i = -10 To 10 Step 1 If expr(i) = 0 Then MsgBox "При Y = 0, Х = "+ CStr(i) Exit For End If Next i End Sub

C помощью команды Exit можно закончить выполнение любого цикла в VBA. Достаточно указать после Exit название используемого цикла. Также им возможно завершить работу любой процедуры или функции.

Цикл For Each в VBA

For Each в VBA основан на переборе всех элементов, указанного типа в массиве, объекте или группе.
Самый популярный вариант его использования - перебор страниц в рабочей книге.

Вывести названия всех листов в рабочей книге

For Each ws In ThisWorkbook.Worksheets MsgBox ws.Name Next ws

И ещё один интересный пример:

Изменить размер шрифта и выравнить по центру текст в label

For Each element In UserForm1.Controls If InStr(1, UserForm1.Controls.Item(i%).Name, "Label") > 0 Then UserForm1.Controls.Item(i%).TextAlign = fmTextAlignCenter UserForm1.Controls.Item(i%).Font.Size = 20 i% = i% + 1 End If Next element

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

Цикл While в VBA

Циклы в VBA, которые используют структуру Do..Loop (это while и until циклы) можно записывать с разным расположением фрагмента условия. Как видите на картинке выше, условие может проверяться после выполнения одной итерации, а может перед запуском цикла.
Самый популярный пример:

Отсортируйте по возрастанию сгенерированный массив методом пузырька

Dim mas(5) As Integer For i% = 0 To 4 mas(i%) = Int((10 * Rnd) + 1) Next i Dim count As Integer, temp As Integer count = 1 Do While count > 0 count = 0 For i% = 0 To 3 If mas(i) > mas(i + 1) Then temp = mas(i) mas(i) = mas(i + 1) mas(i + 1) = temp count = count + 1 End If Next i% Loop

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

Цикл Until в VBA

Как видите, отличия от while крайне несущественные. Цикл Until в VBA можно реализовать с помощью конструкции while NOT (condition) . Тем не менее, приведу пример:

Заставить пользователя ввести число

Dim temp As Variant Do temp = InputBox("Введите число") Loop Until IsNumeric(temp)

Почему заставить? Потому, что если пользователь закроет окно ввода, это его не спасёт, оно будет появляться вновь и вновь пока он не введёт любое число.

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

Цикл For…Next в VBA Excel, его синтаксис и описание отдельных компонентов. Примеры использования цикла For…Next.

Цикл For…Next в VBA Excel предназначен для выполнения группы операторов необходимое количество раз, заданное управляющей переменной цикла - счетчиком. При выполнении цикла значение счетчика после каждой итерации увеличивается или уменьшается на число, указанное выражением оператора Step, или, по умолчанию, на единицу. Когда необходимо применить цикл к элементам, количество которых и индексация в группе (диапазон, массив, коллекция) неизвестны, следует использовать цикл For Each… Next.

1. Синтаксис цикла For…Next
2. Компоненты цикла For…Next
3. Примеры циклов For…Next
   • Простейший цикл
   • Простейший цикл с шагом
   • Цикл с отрицательными аргументами
   • Вложенный цикл
   • Выход из цикла
   • Цикл с дробными аргументами

Синтаксис цикла For…Next

В квадратных скобках указаны необязательные атрибуты цикла For…Next.

Компоненты цикла For…Next

*Если атрибут Step отсутствует, цикл For…Next выполняется с шагом по умолчанию, равному.

**Если не использовать в цикле свой код, смысл применения цикла теряется.

Примеры циклов For…Next

Вы можете скопировать примеры циклов в свой модуль VBA, последовательно запускать их на выполнение и смотреть результаты.

Простейший цикл

Заполняем десять первых ячеек первого столбца активного листа цифрами от 1 до 10:

Sub test1() Dim i As Long For i = 1 To 10 Cells(i, 1) = i Next End Sub

Простейший цикл с шагом

В предыдущий цикл добавлен оператор Step со значением 3, а результаты записываем во второй столбец:

Sub test2() Dim i As Long For i = 1 To 10 Step 3 Cells(i, 2) = i Next End Sub

Цикл с отрицательными аргументами

Этот цикл заполняет десять первых ячеек третьего столбца в обратной последовательности:

Sub test3() Dim i As Long For i = 0 To -9 Step -1 Cells(i + 10, 3) = i + 10 Next End Sub

Увеличиваем размер шага до -3 и записываем результаты в четвертый столбец активного листа:

Sub test4() Dim i As Long For i = 0 To -9 Step -3 Cells(i + 10, 4) = i + 10 Next End Sub

Вложенный цикл

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

Sub test5() Dim i1 As Long, i2 As Long For i1 = 1 To 10 "Пятой ячейке в строке i1 присваиваем 0 Cells(i1, 5) = 0 For i2 = 1 To 4 Cells(i1, 5) = Cells(i1, 5) + Cells(i1, i2) Next Next End Sub

Выход из цикла

В шестой столбец активного листа запишем названия десяти животных, конечно же, с помощью цикла For…Next:

Sub test6() Dim i As Long For i = 1 To 10 Cells(i, 6) = Choose(i, "Медведь", "Слон", "Жираф", "Антилопа", _ "Крокодил", "Зебра", "Тигр", "Ящерица", "Лев", "Бегемот") Next End Sub

Следующий цикл будет искать в шестом столбце крокодила, который съел галоши. В ячейку седьмого столбца цикл, пока не встретит крокодила, будет записывать строку «Здесь был цикл», а когда обнаружит крокодила, запишет «Он съел галоши» и прекратит работу, выполнив команду Exit For. Это будет видно по ячейкам рядом с названиями животных ниже крокодила, в которых не будет текста «Здесь был цикл».

Sub test7() Dim i As Long For i = 1 To 10 If Cells(i, 6) = "Крокодил" Then Cells(i, 7) = "Он съел галоши" Exit For Else Cells(i, 7) = "Здесь был цикл" End If Next End Sub

Результат работы циклов For…Next из примеров:

Результат работы циклов For…Next

Такие данные на активном листе Excel вы получите, если последовательно запустите на выполнение в редакторе VBA все семь подпрограмм из примеров, демонстрирующих работу циклов For…Next.

Цикл с дробными аргументами

Атрибуты start, end и step могут быть представлены числом, переменной или числовым выражением:

For i = 1 To 20 Step 2 For i = a To b Step c For i = a - 3 To 2b + 1 Step c/2

В результате вычисления значения переменной вне цикла или выражения внутри его может получиться дробный результат. VBA Excel округлит его до целого числа, используя бухгалтерское округление:

"Значения атрибутов до округления For i = 1.5 To 10.5 Step 2.51 "Округленные значения атрибутов For i = 2 To 10 Step 3

Старайтесь не допускать попадания в тело цикла For…Next неокругленных значений аргументов, чтобы не получить непредсказуемые результаты его выполнения. Если без дробных чисел не обойтись, а необходимо использовать обычное округление, применяйте функцию рабочего листа WorksheetFunction.Round для округления числа перед использованием его в цикле For…Next.

В это статье мы кратко познакомимся с языком VBA, который позволяет создавать макросы в программных продуктах Microsoft Office. Макросы – некий программный код, который позволяет реализовывать за вас поставленную задачу. В отличии от прикладных программ, макросы VBA как таковые не могут выполняться самостоятельно. Язык VBA – Visual Basic for Applications является родственником таких языков как VBScript, Visual Basic или простой Basic. Синтаксис и общие правила программирования практически идентичны.

По сути, зная язык сервера Windows Script Host, вы с легкостью освоите все премудрости VBA.

Отличия VBA от VBScript

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

Внешние объекты – оба языка позволяют подключать функциональные возможности внешних объектов. Объект – в данном контексте это внешний библиотечный файл с набором методов (функций) и свойств (переменных). Однако, если вы создаете макрос в Word, то объект Word.Application будет доступен автоматически, если макрос работает в документе Excel, то автоматически доступен компонент Excel.Application. В сценариях эти объекты нужно подключать самостоятельно (сделать ссылку).

Обработка событий – в VBA компоненты формы поддерживают не только методы и свойств, но и события, например, нажатие кнопки мыши. В WSH обработка событий не предусмотрена по определению, так как, там нужно отдельно подключать классы для создания графического интерфейс.

Практическое применение VBA

И так, открыв любой документ Microsoft Office, например, Word, мы может вызвать встроенный редактор Visual Basic (комбинация клавиш Alt + F11) и приступить к созданию макроса. Как упоминалось выше, в макросах можно подключать функциональные возможности внешних объектов, тем самым, получать доступ к элементам операционной системы, например, файловая система, реестр и так далее. Желательно, что бы в настройках был отключен автоматический запуск макроса при открытии документа, дабы дырявый или зловредный код не натворил лиха.

Однако, на сегодняшний момент, макросы и язык VBA как элементы администрирования теряют свое преимущество. Тот же Microsoft Access, который предназначен для работы с базами данных также практически не используется. Более актуальным является пакет 1С.

Общие синтаксические принципы языка VBA

• Регистр не играет роли – вы можете прописать оператор как в верхнем, так и в нижнем регистре, или вообще в вперемешку, например, dim, DIM, Dim, разницы нет.
• Комментарии – для комментирования строки, перед ней прописывается одинарная кавычка или ключевое слово REM.
• Максимальная длина любого имени (будь то , или ) составляет 255 знаков.
• В отличии от JavaScript, или С языков, в VBA в конце строки с операторами не нужно ставить запятой.
• Двоеточие – чтобы не прописывать каждый оператор в отдельной строке, их можно с помощью двоеточия прописать в одну строку, например: MsgBox "Строка1" : MsgBox "Строка2"
• Перенос операторов – если строка с операторами слишком длинная, то ее можно разделить на несколько при помощи символа подчеркивания и знака &, например:
  MsgBox "Это простая" _
  & "строка"

Для демонстрации работы с макросами напишем такой пример. Откройте документ Microsoft Word и запустите редактор Visual Basic. Вам нужно создать новую форму и новый модуль (макрос). Цель – решение квадратного уравнения с изменяемыми значениями.

На поверхность формы надо добавить три полосы прокрутки и две текстовые метки. В редакторе кода для формы пропишите:

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

Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.

Пользовательская функция - это процедура VBA, которая производит заданные вычисления и возвращает полученный результат. Используется для вставки в ячейки рабочего листа Excel или для вызова из других процедур.

Объявление пользовательской функции

Синтаксис функции

Компоненты функции

• Static - необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
• Имя - обязательный компонент, имя пользовательской функции.
• СписокАргументов - необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
• Операторы - необязательный компонент, блок операторов (инструкций).
• Имя = выражение - необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
• Exit Function - необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.

*Один из компонентов Имя = выражение следует считать обязательным, так как если не присвоить функции значения, смысл ее использования теряется.

Видимость функции

Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).

Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public , отображается в диалоговом окне Мастера функций.

Ключевое слово Private указывает на то, что функция будет доступна для вызова из других процедур только в пределах программного модуля, в котором она находится. Функция, объявленная как Private , не отображается в диалоговом окне Мастера функций, но ее можно ввести в ячейку вручную.

Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.

Чтобы пользовательская функция всегда была доступна во всех открытых книгах Excel, сохраните ее в без объявления видимости или как Public. Но если вы планируете передать рабочую книгу с пользовательской функцией на другой компьютер, код функции должен быть в программном модуле передаваемой книги.

Пример пользовательской функции

Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции - Делимое и Делитель - тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.

Function Деление(Делимое As Variant, Делитель As Variant) As Variant If IsNumeric(Делимое) = False Or IsNumeric(Делитель) = False Then Деление = "Ошибка: Делимое и Делитель должны быть числами!" Exit Function ElseIf Делитель = 0 Then Деление = "Ошибка: деление на ноль!" Exit Function Else Деление = Делимое / Делитель End If End Function

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

• Если делимое или делитель не являются числом, функция возвращает значение: «Ошибка: Делимое и Делитель должны быть числами!», и производится принудительный выход из функции оператором Exit Function.
• Если делитель равен нулю, функция возвращает значение: «Ошибка: деление на ноль!», и производится принудительный выход из функции оператором Exit Function.

Если проверяемые условия не выполняются (возвращают значение False) производится деление чисел и функция возвращает частное (результат деления).

Вы можете скопировать к себе в стандартный модуль эту функцию и она станет доступна в разделе «Определенные пользователем» Мастера функций. Попробуйте вставить функцию «Деление» в ячейку рабочего листа с помощью Мастера и поэкспериментируйте с ней.

Практического смысла функция «Деление» не имеет, но она хорошо демонстрирует как объявляются, создаются и работают пользовательские функции в VBA Excel. А еще она поможет продемонстрировать, как добавлять к функциям и аргументам описания. С полноценной пользовательской функцией вы можете ознакомиться .

Добавление описания функции

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

• Запустите Мастер функций, посмотрите, как отображается имя нужной функции и закройте его.
• Откройте и в поле «Имя макроса» впишите имя пользовательской функции.
• Нажмите кнопку «Параметры» и в открывшемся окне добавьте или отредактируйте описание.
• Нажмите кнопку «OK», затем в окне списка макросов - «Отмена». Описание готово!

Добавление описания на примере функции «Деление»:

Описание функции «Деление» в диалоговом окне Мастера функций «Аргументы функции»:

С помощью окна «Список макросов» можно добавить описание самой функции, а ее аргументам нельзя. Но это можно сделать, используя метод Application.MacroOptions.

Метод Application.MacroOptions

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

Пример кода с методом Application.MacroOptions:

Sub ИмяПодпрограммы() Application.MacroOptions _ Macro:="ИмяФункции", _ Description:="Описание функции", _ Category:="Название категории", _ ArgumentDescriptions:=Array("Описание 1", "Описание 2", "Описание 3", ...) End Sub

• ИмяПодпрограммы - любое уникальное имя, подходящее для наименования процедур.
• ИмяФункции - имя функции, параметры которой добавляются или изменяются.
• Описание функции - описание функции, которое добавляется или изменяется.
• Название категории - название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию - «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
• "Описание 1", "Описание 2", "Описание 3", ... - описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.

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

Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.

Sub ИзменениеОписания() Application.MacroOptions _ Macro:="Деление", _ Description:="Описание функции Деление изменено методом Application.MacroOptions", _ ArgumentDescriptions:=Array("- любое числовое значение", "- числовое значение, кроме нуля") End Sub

После однократного запуска этой подпрограммы получаем следующий результат:

Метод Application.MacroOptions не работает в , но и здесь можно найти решение. Добавьте описания к пользовательским функциям и их аргументам в обычной книге Excel, затем экспортируйте модуль с функциями в любой каталог на жестком диске и оттуда импортируйте в Личную книгу макросов. Все описания сохранятся.