Основы vba: Самоучитель по Excel VBA — ОфисГуру

Циклы в VBA — ОфисГуру

Автор Антон Андронов На чтение 6 мин Опубликовано 12.05.2020

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

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

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

Далее мы подробно рассмотрим каждый из этих циклов.

Содержание

1. Оператор цикла «For» в Visual Basic
2. Цикл «For … Next»
3. Цикл «For Each»
4. Оператор прерывания цикла «Exit For»
5. Цикл «Do While» в Visual Basic
6. Цикл «Do Until» в Visual Basic

Оператор цикла «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 по умолчанию используется приращение 1. Однако, в некоторых случаях требуется использовать другие значения приращения для цикла. Это можно сделать при помощи ключевого слова 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))

Оцените качество статьи. Нам важно ваше мнение:

Оформление кода VBA — ОфисГуру

Главная » Уроки MS Excel » Макросы (VBA)

Автор Антон Андронов На чтение 4 мин Опубликовано 12.11.2015

Начиная практиковаться в написании кода VBA, очень важно с самого начала выработать хорошие привычки в оформлении кода, чтобы в дальнейшем написанный код было легко читать и понимать, как он работает.

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

Эта статья посвящена комментариям, отступам в коде и переносам строк – элементам, которые делают код аккуратным и понятным.

Содержание

1. Комментарии в VBA
2. Отступы в коде VBA
3. Переносы строк в VBA

Комментарии в VBA

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

Комментарии не участвуют в процессе выполнения программы и не влияют на результат работы макроса. Каждая строка, начинающаяся апострофом (‘), будет считаться в VBA комментарием. Редактор VBA в Excel выделит такую строку зелёным цветом шрифта, чтобы с первого взгляда было понятно, что это комментарий, который не будет выполняться.

Ниже продемонстрировано, как при помощи комментариев поясняется работа простой процедуры Sub:

' процедура Sub для просмотра диапазона ячеек A1-A100 активного
' листа  и поиска ячейки, содержащей переданную процедуре строку 

Sub Find_String(sFindText As String)

   Dim i As Integer   ' переменная типа Integer для цикла 'For'
   Dim iRowNumber As Integer   ' переменная типа Integer для хранения результата 

   iRowNumber = 0

   ' последовательно посматриваем ячейки A1-A100, пока не будет найдено значение 'sFindText'

   For i = 1 To 100
      If Cells(i, 1). Value = sFindText Then

         ' найдено совпадение с переданной строкой
         ' сохраняем номер текущей строки и выходим из цикла
         iRowNumber = i
         Exit For

      End If
   Next i

   ' сообщение во всплывающем окне сообщает пользователю,
   ' найдена ли строка, и если найдена – сообщает номер строки 

   If iRowNumber = 0 Then
      MsgBox "Строка " & sFindText & " не найдена"
   Else
      MsgBox "Строка " & sFindText & " найдена в ячейке A" & iRowNumber
   End If

End Sub

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

Часто программисты ленятся добавлять подробные комментарии к своему коду, но, поверьте, затраченные усилия оправдают себя с избытком! Несколько минут, потраченных на написание понятного комментария, могут сэкономить Вам долгие часы в будущем.

Отступы в коде VBA

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

Переносы строк в VBA

Ещё один способ сделать код более читаемым и облегчить работу с ним – делать переносы и разбивать одну длинную строку кода на несколько коротких. В VBA, чтобы разбить строку, нужно вставить символы » _» (пробел+подчёркивание) непосредственно перед переносом строки. Это сообщает компилятору VBA, что текущая строка кода продолжается на следующей строке.

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

Посмотрите на этот оператор If:

If (index = 1 And sColor1 = "красный") Or (index = 2 And sColor1 = "синий") Or (index = 3 And sColor1 = "зеленый") Or (index = 4 And sColor1 = "коричневый") Then

При помощи переносов строк тот же оператор If может быть записан вот так:

If (index = 1 And sColor1 = "красный") Or _
   (index = 2 And sColor1 = "синий") Or _
   (index = 3 And sColor1 = "зеленый") Or _
   (index = 4 And sColor1 = "коричневый") Then

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excelfunctions.net/VBA-Code-Presentation.html
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Рабочая книга и объект рабочего листа в Excel VBA (простые макросы)

Иерархия объектов | Коллекции | Свойства и методы

Узнайте больше об объекте Workbook и Worksheet в Excel VBA .

Иерархия объектов

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

Мать всех объектов — сам Excel. Мы называем это объектом приложения. Объект приложения содержит другие объекты. Например, объект Workbook (файл Excel). Это может быть любая рабочая книга, которую вы создали. Объект Workbook содержит другие объекты, например объект Worksheet. Объект Worksheet содержит другие объекты, такие как объект Range.

В главе «Создание макроса» показано, как запустить код, нажав кнопку команды. Мы использовали следующую строку кода:

Range(«A1»).Value = «Hello»

но на самом деле мы имели ввиду:

Application.Workbooks(«create-a-macro»).Worksheets(1).Range(«A1»). Значение = «Привет»

Примечание: объекты соединены точкой. К счастью, нам не нужно добавлять строку кода таким образом. Это потому, что мы разместили нашу командную кнопку в файле create-a-macro.xlsm на первом рабочем листе. Имейте в виду, что если вы хотите что-то изменить на разных листах, вы должны включить объект Worksheet. Читать дальше.

Коллекции

Возможно, вы заметили, что рабочие книги и рабочие листы имеют множественное число. Это потому, что они коллекции. Коллекция Workbooks содержит все открытые в данный момент объекты Workbook. Коллекция Worksheets содержит все объекты Worksheet в книге.

Вы можете ссылаться на элемент коллекции, например, на отдельный объект Worksheet, тремя способами.

1. Использование имени рабочего листа.

Рабочие листы («Продажи»). Диапазон («A1»). Значение = «Здравствуйте»

2. Использование порядкового номера (1 — это первый рабочий лист, начинающийся слева).

Рабочие листы(1).Range(«A1»).Value = «Hello»

3. Использование CodeName.

Sheet1.Range(«A1»).Value = «Hello»

Чтобы увидеть кодовое имя листа, откройте редактор Visual Basic. В Project Explorer первым именем является CodeName. Второе имя — это имя рабочего листа (Продажи).

Примечание. CodeName остается прежним, если вы меняете имя рабочего листа или порядок ваших рабочих листов, поэтому это самый безопасный способ ссылки на рабочий лист. Нажмите «Вид», «Окно свойств», чтобы изменить кодовое имя рабочего листа. Есть один недостаток: вы не можете использовать CodeName, если ссылаетесь на рабочий лист в другой книге.

Свойства и методы

Теперь давайте рассмотрим некоторые свойства и методы коллекции Workbooks и Worksheets. Свойства — это то, что есть у коллекции (они описывают коллекцию), а методы что-то делают (выполняют действие с коллекцией).

Поместите кнопку на рабочий лист и добавьте строки кода:

1. Метод Add коллекции Workbooks создает новую рабочую книгу.

Workbooks.Add

Примечание. Метод Add коллекции Worksheets создает новый рабочий лист.

2. Свойство Count коллекции Worksheets подсчитывает количество листов в книге.

MsgBox Worksheets.Count

Результат при нажатии командной кнопки на листе:

Примечание. Свойство Count коллекции Workbooks подсчитывает количество активных книг.

Объект диапазона в Excel VBA (простые макросы)

Примеры диапазона | Клетки | Объявить объект Range | Выберите | Ряды | Столбцы | Копировать/Вставить | Очистить | Граф

Объект Range , который является представлением ячейки (или ячеек) на вашем рабочем листе, является наиболее важным объектом Excel VBA . В этой главе дается обзор свойств и методов объекта Range. Свойства — это то, чем обладает объект (они описывают объект), а методы что-то делают (выполняют действие с объектом).

Примеры диапазонов

Поместите командную кнопку на рабочий лист и добавьте следующую строку кода:

Диапазон («B3»). Значение = 2

Результат при нажатии командной кнопки на листе:

Код:

Диапазон («A1:A4»). Значение = 5

Результат:

Код:

Диапазон(«A1:A2,B3:C4»).Значение = 10

Результат:

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

Диапазон («Цены»). Значение = 15

Ячейки

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

Код:

Ячейки(3, 2).Значение = 2

Результат:

Объяснение: Excel VBA вводит значение 2 в ячейку на пересечении строки 3 и столбца 2.

Код:

Range(Cells(1, 1), Cells(4, 1)).Value = 5

Результат:

Объявление объекта Range

Вы можете объявить объект Range, используя ключевые слова Dim и Set.

Код:

Размерный пример Как диапазон
Установите пример = Диапазон («A1: C4»)

пример. Значение = 8

Результат:

Select

Важным методом объекта Range является метод Select. Метод Select просто выбирает диапазон.

Код:

Размерный пример Как диапазон
Set example = Range(«A1:C4»)

example.Select

Результат:

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

Рабочие листы(3).Активировать
Worksheets(3).Range(«B7»).Select

Rows

Свойство Rows предоставляет доступ к определенной строке диапазона.

Код:

Размерный пример Как диапазон
Set example = Range(«A1:C4»)

example.Rows(3).Select

Результат:

Примечание: рамка только для иллюстрации.

Столбцы

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

Код:

Размерный пример Как диапазон
Set example = Range(«A1:C4»)

example.Columns(2).Select

Результат:

Примечание: рамка только для иллюстрации.

Копировать/Вставить

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

Код:

Диапазон («A1:A2»). Выберите
Selection.Copy

Range(«C3»).Select
ActiveSheet.Paste

Результат:

Хотя это разрешено в Excel VBA, гораздо лучше использовать приведенную ниже строку кода, которая делает то же самое.