Оператор Option Base

Оператор Option Base позволяет установить начальный индекс по умолчанию для всех массивов, объявленных в модуле. Вы можете выбрать:

Option Base 0 — массивы начинаются с 0 (установлено по умолчанию).
Option Base 1 — массивы начинаются с 1.

Если вы не используете Option Base, массивы по умолчанию начинают нумерацию с 0, поэтому нет смысла использовать Option Base 0.

Option Base 1 устанавливает нумерацию массивов с 1 по умолчанию. Этот оператор должен быть написан в самом начале модуля, перед всеми процедурами.

Если вы явно задаёте диапазон индексов при объявлении массива (Dim myArray(1 To 5)), Option Base не влияет на объявленную размерность.

Пример с Option Base 1

В этом примере оператор Option Base используется для переопределения значения базового индекса массива, равного 0. Функция LBound возвращает наименьший доступный индекс для указанного измерения массива. Оператор Option Base используется только на уровне модуля.

Option Base 1 'Устанавливает нижний индекс массива по умолчанию равным 1.
 
Sub Primer()
Dim MyArray(20), TwoDArray(3, 4) 'Объявление переменных массива.
Dim ZeroArray(0 To 5) 'Переопределение базового индекса массива.
Dim NewArray(4 To 5) 'Переопределение базового индекса массива.
 
'Используем функцию LBound для проверки нижней границы массивов.
MsgBox LBound(MyArray) 'Возвращает 1.
MsgBox LBound(TwoDArray, 2) 'Возвращает 1 (для второго измерения массива).
MsgBox LBound(ZeroArray) 'Возвращает 0.
MsgBox LBound(NewArray) 'Возвращает 4.
End Sub

#VBA #ExcelVBA #OptionBase

Модуль в VBA Excel

Модуль в VBA Excel — это место, где хранится ваш код. Он помогает структурировать макросы и функции, упрощая управление вашим проектом.

Есть три основных типа модулей:

1️⃣ Стандартные (обычные) модули – для хранения общих макросов и функций.
2️⃣ Модули объектов – модули книг, листов, диаграмм и пользовательских форм.
3️⃣ Модули пользовательских классов – используются для создания собственных объектов и более сложных структур (это для продвинутых программистов, нам же вряд ли пригодятся).

Повторю, как создать стандартный модуль:

1️⃣ Откройте редактор VBA (Alt + F11).
2️⃣ В меню выберите Insert → Module.
3️⃣ Новый модуль появится в разделе Modules в окне Project Explorer.

Открыть существующий стандартный модуль можно, дважды кликнув по его имени в проводнике, или открыв на нем правой кнопкой мыши контекстное меню и выбрав «View Code».

Модули объектов создавать не надо, они уже есть. Чтобы открыть модуль объекта, дважды кликните в проводнике на его имени, или откройте на нем правой кнопкой мыши контекстное меню и выберите «View Code».

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

Между открытыми окнами форм и модулей можно переходить, нажав в главном меню вкладку «Window», где перечислены все открытые модули и формы (активный объект отмечен галочкой).

#Модуль #Module #СтандартныйМодуль #МодульКниги #МодульЛиста #МодульФормы #МодульДиаграммы

Процедуры в VBA Excel

Процедура — это блок кода, который выполняет определенную задачу. В VBA Excel процедуры делятся на два типа:

1️⃣ Sub (процедура без возврата результата): выполняет действия, но не возвращает значения.
2️⃣ Function (функция): возвращает результат, который можно использовать в расчетах или других действиях. Может быть вызвана в ячейке Excel, как встроенная функция.

Есть ещё и третий тип процедуры - Property (пользовательские свойства) - но он вряд ли вам пригодится.

Вот пример простой процедуры Sub:

Sub ShowMessage()
    MsgBox "Добро пожаловать в VBA Excel!"
End Sub

▶️ Чтобы запустить, откройте редактор VBA (Alt + F11), вставьте код в модуль и нажмите F5.

#Sub #SubVBA #Function #FunctionVBA #Процедура #Подпрограмма #Функцияа

Формы в VBA Excel

Формы в VBA Excel (UserForms) — это инструмент для создания удобного интерфейса пользователя. UserForm — это графическая форма, которая позволяет взаимодействовать с пользователем через элементы управления (текстовые поля, кнопки, списки и т.д.).

Как создать форму?

1️⃣ Откройте редактор VBA (Alt + F11).
2️⃣ В меню выберите: Insert → UserForm.
3️⃣ Перед вами откроется конструктор форм.

На панели инструментов (Toolbox) вы найдете стандартные элементы:

Label — надпись;
TextBox — поле ввода;
CommandButton — кнопка;
ComboBox и ListBox — выпадающие списки и т.д.

Просто перетащите нужный элемент на форму и настройте его свойства (щелчок правой кнопкой → Properties).

При создании UserForm окно Toolbox обычно открывается автоматически. Если этого не произошло, в главном меню выберите: View → Toolbox.

Вот пример простой формы для ввода имени:

1️⃣ Добавьте TextBox и CommandButton на форму.
2️⃣ В модуле формы напишите:

Private Sub CommandButton1_Click()
    MsgBox "Привет, " & TextBox1.Text & "!"
    Unload Me
End Sub

3️⃣ Для отображения формы создайте процедуру в модуле:

Sub ShowForm()
    UserForm1.Show
End Sub

▶️ Запустите макрос ShowForm — и форма откроется!

Напишите своё имя в TextBox1 и нажмите кнопку CommandButton1.

#UserForm #Форма #ПользовательскаяФорма #UserFormVBA #ФормаVBA

Личная книга макросов

Личная книга макросов — это специальный скрытый файл Excel (PERSONAL.XLSB), в котором хранятся ваши макросы. Этот файл автоматически загружается при открытии Excel и позволяет использовать макросы во всех рабочих книгах на вашем компьютере.

Если вы не видите в вашем редакторе VBA Личную книгу макросов, создайте её:

1️⃣ Откройте любой файл Excel.
2️⃣ Перейдите на вкладку "Разработчик" → нажмите "Запись макроса".
3️⃣ В окне записи макроса выберите "Сохранить в: Личная книга макросов".
4️⃣ Нажмите кнопку 🆗 для начала записи макроса.
5️⃣ Запишите любой простой макрос, например, выделите несколько ячеек и остановите запись.
6️⃣ Закройте Excel и подтвердите сохранение Личной книги макросов.

Теперь файл PERSONAL.XLSB создан и будет автоматически открываться при запуске Excel.

#VBA #ExcelVBA #КнигаМакросов #PERSONALXLSB #MacroWorkbook

Редактор VBA без Личной книги макросов

Редактор VBA с Личной книгой макросов

Условные операторы VBA Excel

Условные операторы в VBA — это конструкции, которые проверяют условия и управляют выполнением кода в зависимости от результата (истина или ложь).

Основные операторы:
1️⃣ Оператор If...Then...Else
2️⃣ Оператор Select Case
3️⃣ Функция IIf

#VBA #ExcelVBA #УсловныеОператоры

Оператор If…Then…Else

Оператор If…Then…Else предназначен для передачи управления одному из блоков операторов в зависимости от результатов проверяемых условий.

Однострочная конструкция If…Then…

Синтаксис

If [условие] Then [операторы]

✳️ условие — числовое или строковое выражение, возвращающее логическое значение True или False;
✳️ операторы — блок операторов кода VBA Excel, который выполняется, если компонент условие возвращает значение True.

Если компонент условие возвращает значение False, блок операторов конструкции If…Then… пропускается и управление программой передается следующей строке кода.

Пример 1

Sub Primer1()
Dim d As Integer, a As String
d = InputBox("Введите число от 1 до 20", "Пример 1", 1)
   If d > 10 Then a = "Число " & d & " больше 10"
MsgBox a
End Sub

Многострочная конструкция If…Then…Else

Синтаксис

If [условие] Then
[операторы]
ElseIf [условие] Then
[операторы]
----------------
Else
[операторы]
End If

✳️ условие — числовое или строковое выражение, следующее за ключевым словом If или ElseIf и возвращающее логическое значение True или False;
✳️ операторы — блок операторов кода VBA Excel, который выполняется, если компонент условие возвращает значение True;
✳️ пунктирная линия обозначает дополнительные структурные блоки из строки ElseIf [условие] Then и строки [операторы].

Самый простой вариант многострочной конструкции If…Then…Else:

If [условие] Then
[операторы]
Else
[операторы]
End If

Пример 2

Sub Primer2()
Dim d As Integer, a As String
d = InputBox("Введите число от 1 до 40", "Пример 2", 1)
   If d < 11 Then
      a = "Число " & d & " входит в первую десятку"
   ElseIf d > 10 And d < 21 Then
      a = "Число " & d & " входит во вторую десятку"
   ElseIf d > 20 And d < 31 Then
      a = "Число " & d & " входит в третью десятку"
   Else
      a = "Число " & d & " входит в четвертую десятку"
   End If
MsgBox a
End Sub

#VBA #ExcelVBA #IfThen #IfThenElse

Оператор Select Case

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

Синтаксис

Select Case выражение
    Case условие 1
        ' Код для условия 1
    Case условие 2
        ' Код для условия 2
    Case условие  3, условие  4
        ' Код для нескольких условий (3 и 4)
    Case Else
        ' Код для всех других случаев
End Select

✳️ выражение: любое числовое или строковое выражение, переменная.
✳️ Case: проверяемые значения или диапазоны значений, условия с ключевым словом Is.
✳️ Case Else: выполняется, если ни одно из условий не подошло.

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

Пример 1
Использование оператора Select Case с операторами сравнения в условиях:

Sub primer1()
Dim a As Integer, b As String
a = InputBox("Введите число от 1 до 5", "Пример 1", 1)
    Select Case a
        Case Is = 1
            b = "один"
        Case Is = 2
            b = "два"
        Case Is = 3
            b = "три"
        Case Is = 4
            b = "четыре"
        Case Is = 5
            b = "пять"
        Case Else
            b = "Число не входит в диапазон от 1 до 5"
    End Select
MsgBox b
End Sub

Пример 2
Использование оператора Select Case с заданным диапазоном в условиях:

Sub primer2()
Dim a As Integer, b As String
a = InputBox("Введите число от 1 до 30", "Пример 2", 1)
    Select Case a
        Case 1 To 10
            b = "Число " & a & " входит в первую десятку"
        Case 11 To 20
            b = "Число " & a & " входит во вторую десятку"
        Case 21 To 30
            b = "Число " & a & " входит в третью десятку"
        Case Else
            b = "число " & a & " не входит в первые три десятки"
    End Select
MsgBox b
End Sub

Для решения подобной задачи в VBA Excel можно использовать многострочную конструкцию оператора If…Then…Else, но решение с Select Case выглядит изящней.

Пример 3
Проверка одного значения:

Sub CheckDay()
    Dim day As String
    day = "Среда"
    
    Select Case day
        Case "Понедельник"
            MsgBox "Начало рабочей недели!"
        Case "Среда"
            MsgBox "Середина недели."
        Case "Пятница"
            MsgBox "Скоро выходные!"
        Case Else
            MsgBox "Обычный день."
    End Select
End Sub

#VBA #ExcelVBA #SelectCase #Case #CaseElse

Функция IIf

Функция IIf проверяет заданное условие и возвращает значение в зависимости от результата проверки.

Синтаксис

IIf([условие], [если True], [если False])

✳️ условие — числовое или строковое выражение, возвращающее логическое значение True или False;
✳️ если True — значение, которое возвращает функция IIf, если условие возвратило значение True;
✳️ если False — значение, которое возвращает функция IIf, если условие возвратило значение False.
Компоненты если True и если False могут быть выражениями, значения которых будут вычислены и возвращены.

Пример 1

Sub Primer1()
Dim d As Integer, a As String
Instruk:
On Error Resume Next
d = InputBox("Введите число от 1 до 20 и нажмите OK", "Пример 3", 1)
If d > 20 Then GoTo Instruk
    a = IIf(d < 10, d & " - число однозначное", d & " - число двузначное")
MsgBox a
End Sub

Пример 2
Стоит отметить, что не зависимо от того, выполняется условие или нет, функция IIf вычислит оба выражения в параметрах если True и если False:

Sub Primer2()
On Error GoTo Instruk
Dim x, y
x = 10
y = 5
    MsgBox IIf(x = 10, x + 5, y + 10)
    MsgBox IIf(x = 10, x + 5, y / 0)
Exit Sub
Instruk:
MsgBox "Произошла ошибка: " & Err.Description
End Sub

Так как IIf всегда вычисляет оба результата (и если True и если False), даже если условие не выполнено, это может привести к ошибкам, если выражения содержат операции, которые не должны быть выполнены в одном из случаев.

Если вам нужно избегать этой проблемы, лучше использовать конструкцию If…Then…Else, которая будет вычислять только ту часть, которая соответствует условию.

#VBA #ExcelVBA #IIf #ФункцияIIf

Цикл For… Next

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

Синтаксис

For counter = start To end [ Step step ]
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ counter ]

#VBA #ExcelVBA #ЦиклForNext #ForNext #Цикл

Если атрибут Step отсутствует, цикл For… Next выполняется с шагом по умолчанию, равному 1.
Атрибуты start, end и step могут быть представлены числом, переменной или числовым выражением.

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

Заполняем десять первых ячеек первого столбца активного листа Excel цифрами от 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 и записываем результаты в четвертый столбец активного листа Excel:

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

Результат работы циклов представлен на изображении ниже.

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

Цикл For Each… Next

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

Синтаксис

For Each element In group
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ element ]

#VBA #ExcelVBA #ForEach #ForEachNext #ЦиклForEach #Цикл

Если цикл For Each… Next используется для прохождения элементов коллекции (объект Collection) или массива, тогда переменная element должна быть объявлена с типом данных Variant, иначе цикл работать не будет.

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

Цикл для диапазона ячеек
На активном листе рабочей книги Excel выделите диапазон ячеек и запустите на выполнение следующую процедуру:

Sub test1()
Dim element As Range, a As String
  a = "Данные, полученные с помощью цикла For Each... Next:"
    For Each element In Selection
      a = a & vbNewLine & "Ячейка " & element.Address & _
      " содержит значение: " & CStr(element.Value)
    Next
  MsgBox a
End Sub

Информационное окно MsgBox выведет адреса выделенных ячеек и их содержимое, если оно есть. Если будет выбрано много ячеек, то полностью информация по всем ячейкам выведена не будет, так как максимальная длина параметра Prompt функции MsgBox составляет примерно 1024 знака.

Цикл для коллекции листов
Скопируйте следующую процедуру VBA в стандартный модуль книги Excel:

Sub test2()
Dim element As Worksheet, a As String
  a = "Список листов, содержащихся в этой книге:"
    For Each element In Worksheets
      a = a & vbNewLine & element.Index _
      & ") " & element.Name
    Next
  MsgBox a
End Sub

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

Цикл для массива
Присвоим массиву список наименований животных и в цикле For Each… Next запишем их в переменную a. Информационное окно MsgBox выведет список наименований животных из переменной a.

Sub test3()
Dim element As Variant, a As String, group As Variant
group = Array("бегемот", "слон", "кенгуру", "тигр", "мышь")
'или можно присвоить массиву значения диапазона ячеек
'рабочего листа, например, выбранного: group = Selection
a = "Массив содержит следующие значения:" & vbNewLine
  For Each element In group
    a = a & vbNewLine & element
  Next
MsgBox a
End Sub

Повторим ту же процедуру VBA, но всем элементам массива в цикле For Each… Next присвоим значение «Попугай». Информационное окно MsgBox выведет список наименований животных, состоящий только из попугаев, что доказывает возможность редактирования значений элементов массива в цикле For Each… Next.

Sub test4()
Dim element As Variant, a As String, group As Variant
group = Array("бегемот", "слон", "кенгуру", "тигр", "мышь")
'или можно присвоить массиву значения диапазона ячеек
'рабочего листа, например, выделенного: group = Selection
a = "Массив содержит следующие значения:" & vbNewLine
  For Each element In group
    element = "Попугай"
    a = a & vbNewLine & element
  Next
MsgBox a
End Sub

Цикл для коллекции подкаталогов и выход из цикла
В этом примере мы будем добавлять в переменную a названия подкаталогов на диске C вашего компьютера. Когда цикл дойдет до папки Program Files, он добавит в переменную a ее название и сообщение: «Хватит, дальше читать не буду! С уважением, Ваш цикл For Each… Next.».

Sub test5()
Dim FSO As Object, myFolders As Object, myFolder As Object, a As String
'Создаем новый FileSystemObject и присваиваем его переменной "FSO"
Set FSO = CreateObject("Scripting.FileSystemObject")
'Извлекаем список подкаталогов на диске "C" и присваиваем
'его переменной "myFolders"
Set myFolders = FSO.GetFolder("C:\")
a = "Папки на диске C:" & vbNewLine
'Проходим циклом по списку подкаталогов и добавляем в переменную "a"
'их имена, дойдя до папки "Program Files", выходим из цикла
  For Each myFolder In myFolders.SubFolders
    a = a & vbNewLine & myFolder.Name
    If myFolder.Name = "Program Files" Then
      a = a & vbNewLine & vbNewLine & "Хватит, дальше читать не буду!" _
      & vbNewLine & vbNewLine & "С уважением," & vbNewLine & _
      "Ваш цикл For Each... Next."
  Exit For
    End If
  Next
Set FSO = Nothing
MsgBox a
End Sub

Информационное окно MsgBox выведет список наименований подкаталогов на диске C вашего компьютера до папки Program Files включительно и сообщение цикла о прекращении своей работы.

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

Цикл Do While… Loop

Цикл Do While… Loop в VBA Excel предназначен для повторения блока операторов до тех пор, пока выполняется заданное условие (возвращается значение True). Этот цикл позволяет проверять условие как до, так и после выполнения операторов. Предусмотрен принудительный выход из цикла с помощью оператора Exit Do

Синтаксис
Синтаксис цикла Do While… Loop существует в двух вариантах, определяющих, когда проверяется условие.

Условие проверяется до выполнения операторов:

Do While condition
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop

Условие проверяется после выполнения операторов:

Do
    [ statements ]
    [ Exit Do ]
    [ statements ]
Loop While condition

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

#VBA #ExcelVBA #DoWhile #DoWhileLoop #ЦиклDoWhile #Цикл

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