Регистратор макросов Excel обладает большими возможностями, но имеет свои ограничения. Как описано в другой статье, средство записи макросов часто записывает ненужный код и не может записывать такие вещи, как логика или взаимодействие с другими программами. Его также может быть сложно использовать для более длинных макросов - вы можете заранее подготовить раскадровку своих действий, чтобы избежать дорогостоящих ошибок.
Эта статья призвана помочь вам начать кодирование макросов на VBA с нуля. Вы узнаете, где хранятся макросы, напишите базовый макрос и изучите основы программирования на VBA с использованием переменных, логики и циклов.
Начиная
VBA и редактор Visual Basic
VBA или Visual Basic для приложений - это язык, на котором написаны макросы. Все макросы хранятся в виде кода VBA, независимо от того, написаны ли они вручную или созданы с помощью средства записи макросов.
Вы можете получить доступ ко всему коду VBA в книге с помощью редактора Visual Basic. Это специальный текстовый редактор и отладчик, встроенный во все офисные приложения, включая Excel. Обычно вы открываете этот редактор с ALT + F11 сочетание клавиш в Excel, но вы также можете получить к нему доступ из Excel Разработчик вкладка, если она у вас включена.
Обозреватель проекта
В Обозреватель проекта - это окно внутри редактора VB, в котором показаны все элементы, в которых может быть код VBA. Если вы не видите это окно, нажмите F5 чтобы он появился или выберите Обозреватель проекта от Вид меню.
Двойной щелчок по элементу в Project Explorer покажет код этого элемента. В Project Explorer могут отображаться несколько типов элементов:
- Рабочие тетради
- Рабочие листы
- Пользовательские формы
- Модули классов
- Модули (в этих элементах хранятся макросы)
Хотя все эти типы элементов могут включать код VBA, лучше всего кодировать макросы в модулях.
Создание вашего первого макроса
Использование списка макросов
Список макросов показывает вам все макросы в вашей книге. Из этого списка вы можете редактировать существующий макрос или создать новый.
Чтобы создать новый макрос с помощью списка макросов:
- Выберите вкладку Разработчик и нажмите Макросы (или нажмите ALT + F8)
- Введите новое имя для вашего макроса, затем нажмите «Создать».
После нажатия кнопки «Создать» появится редактор VB, показывающий только что созданный макрос. При необходимости Excel создаст новый модуль для макроса.
Вручную в редакторе VB
Вы можете добавить новый макрос вручную без списка макросов. Это лучший вариант, если вы хотите указать модуль, в котором сохраняется макрос.
Чтобы добавить макрос вручную:
- Откройте редактор VB (ALT + F11)
- Или:
- Добавьте новый модуль, нажав Вставить> Модуль в меню (модуль откроется автоматически)
-
- ИЛИ дважды щелкните существующий модуль в Project Explorer, чтобы открыть его.
- В модуле введите код нового макроса
Sub MyMacro () End Sub
Эти две строки обозначают начало и конец макроса с именем «MyMacro» (обратите внимание на обязательные скобки). Это будет отображаться в диалоговом окне «Просмотр макросов» в Excel и может быть назначено кнопке (даже если она еще ничего не делает).
Добавьте код в макрос
Теперь давайте добавим код между строками «Sub» и «End Sub», чтобы этот макрос действительно что-то делал:
Sub MyMacro () Range («A1»). Value = «Hello World!» Конец подписки
Базовые структуры кода
Объект Range
Excel VBA использует объект Range для представления ячеек на листе. В приведенном выше примере объект Range создается с кодом Диапазон («A1») чтобы получить доступ к значению ячейки A1.
Объекты диапазона в основном используются для установки значений ячеек:
Диапазон («A1»). Значение = 1.
Диапазон («A1»). Значение = «Первая ячейка»
Обратите внимание, что при определении значений ячеек как чисел вы просто вводите число, но при вводе текста вы должны заключить текст в кавычки.
Диапазоны также можно использовать для доступа ко многим свойствам ячеек, таким как их шрифт, границы, формулы и многое другое.
Например, вы можете установить для ячейки полужирный шрифт следующим образом:
Диапазон («A1»). Полужирный шрифт = True
Вы также можете установить формулу ячейки:
Диапазон («A1»). Формула = «= Sum (A2: A10)»
В Excel вы можете выделить блок ячеек с помощью курсора (скажем, от A1 до D10) и выделить их все полужирным шрифтом. Объекты диапазона могут получить доступ к таким блокам ячеек:
Диапазон («A1: D10»). Полужирный шрифт = True
Вы также можете сослаться сразу на несколько ячеек / блоков:
Диапазон («A1: D10, A12: D12, G1»). Жирный шрифт = True
Формат для этого такой же, как формат, который вы использовали бы при выборе ячеек для формулы СУММ () в Excel. Каждый блок разделяется запятой, а блоки обозначаются левой верхней и правой ячейками, разделенными двоеточием.
Наконец, объекты Range имеют встроенные методы для выполнения общих операций на листе. Например, вы можете захотеть скопировать некоторые данные из одного места в другое. Вот пример:
Диапазон («A1: D10»). Копировать диапазон («F1»). PasteSpecial xlPasteValues Range («F1»). PasteSpecial xlPasteFormats
Это копирует ячейки A1: D10 в буфер обмена, а затем выполняет PasteSpecial (), начиная с ячейки C1 - точно так же, как вы это делали вручную в Excel. Обратите внимание, что в этом примере показано, как использовать PasteSpecial () для вставки только значений и форматов - есть параметры для всех параметров, которые вы увидите в диалоговом окне «Специальная вставка».
Вот пример вставки «Все» на другой лист:
Диапазон («A1: D10»). Копировать листы («Лист2»). Диапазон («A1»). PasteSpecial xlPasteAll
Если утверждения
С Если заявление, вы можете запустить часть кода только «если» определенное утверждение верно.
Например, вы можете выделить ячейку жирным шрифтом и покрасить ее в красный цвет, но только «если» значение в ячейке меньше 100.
Если Range («A4»). Value <100 Then Range («A4»). Font.Bold = True Range («A4»). Interior.Color = vbRed End If
Правильная структура оператора If следующая (квадратные скобки указывают необязательные компоненты):
Если то
[ElseIf Then]
[Еще]
Конец, если
Вы можете включить столько ElseIf блоки, как вы хотите, чтобы проверить несколько условий. Вы также можете добавить Еще блок, который запускается только в том случае, если не выполняется ни одно из других условий в операторе If.
Вот еще один пример, основанный на предыдущем, где ячейка форматируется несколькими способами в зависимости от значения:
Если Range ("A4"). Value <100 Then Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed ElseIf Range ("A4"). Value <200 Then Range ( "A4"). Font.Bold = False Range ("A4"). Interior.Color = vbYellow Другой диапазон ("A4"). Font.Bold = False Range ("A4"). Interior.Color = vbGreen End If
В приведенном выше примере ячейка не выделена жирным шрифтом в блоках ElseIf, где значение не меньше 100. Вы можете гнездо Операторы if, чтобы избежать дублирования кода, например:
Если Range ("A4"). Value <100, Then Range ("A4"). Font.Bold = True Range ("A4"). Interior.Color = vbRed Else Range ("A4"). Font.Bold = False ' удаление жирного шрифта только один раз If Range ("A4"). Value <200 Then Range ("A4"). Interior.Color = vbYellow Else Range ("A4"). Interior.Color = vbGreen End If End If
Переменные
А Переменная это часть памяти, используемая для хранения временной информации во время выполнения макроса. Они часто используются в циклах в качестве итераторов или для хранения результата операции, которую вы хотите использовать несколько раз в макросе.
Вот пример переменной и то, как вы можете ее использовать:
Sub ExtractSerialNumber () Dim strSerial As String 'это объявление переменной' As String 'означает, что эта переменная предназначена для хранения текста' настройка фиктивного серийного номера: Range ("A4"). Value = «serial # 804567-88 ”'Выделите серийный номер из ячейки A4 и назначьте его переменной strSerial = Mid (Range (« A4 »). Value, 9)» теперь используйте переменную дважды, вместо того, чтобы дважды анализировать серийный номер Range (« B4 ”). Значение = strSerial MsgBox strSerial End Sub
В этом базовом примере переменная «strSerial» используется для извлечения серийного номера из ячейки A4 с помощью функции Mid (), а затем используется в двух других местах.
Стандартный способ объявлять переменная выглядит следующим образом:
Тусклый какое бы то ни было имя [В качестве тип]
- какое бы то ни было имя это имя, которое вы решили дать своей переменной
- тип это тип данных переменной
«[Как тип] »Можно опустить - в этом случае переменная объявляется как тип Variant, который может содержать любые данные. Хотя типы Variant совершенно допустимы, их следует избегать, поскольку они могут привести к неожиданным результатам, если вы не будете осторожны.
Есть правила для имен переменных. Они должны начинаться с буквы или символа подчеркивания, не могут содержать пробелов, точек, запятых, кавычек или символов «! @ & $ # ».
Вот несколько примеров объявления переменных:
Dim strFilename As String 'хороший стиль имени - описательный и использует префикс Dim i As Long' плохой стиль - приемлем только для некоторых итераторов Dim SalePrice As Double 'хорошо стиль имени - описательный, но не использует префикс Dim iCounter' допустимое имя - не слишком информативный, использует префикс, без типа данных
Во всех этих примерах используются несколько разные схемы именования, но все они действительны. Неплохая идея добавить к имени переменной префикс с краткой формой ее типа данных (как в некоторых из этих примеров), поскольку это делает ваш код более читаемым с первого взгляда.
VBA включает в себя множество базовых типы данных. К наиболее популярным относятся:
- Нить (используется для хранения текстовых данных)
- Длинный (используется для хранения целых чисел, т.е. без десятичных знаков)
- Двойной (используется для хранения чисел с плавающей запятой, то есть десятичных знаков)
Полный список внутренних типов данных VBA можно найти здесь: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Переменные объекта диапазона
Также можно создавать переменные, которые ссылаются на объекты диапазона. Это полезно, если вы хотите сослаться на определенный диапазон в вашем коде в нескольких местах - таким образом, если вам нужно изменить диапазон, вам нужно будет изменить его только в одном месте.
Когда вы создаете объектную переменную Range, вы должны «установить» ее в экземпляр Range. Например:
Dim rMyRange As Range Set rMyRange = Диапазон («A1: A10; D1: J10»)
Отсутствие оператора Set при присвоении переменной Range приведет к ошибке.
Петли
Циклы - это блоки, которые повторяют код внутри них определенное количество раз. Они полезны для уменьшения объема кода, который вам нужно написать, и позволяют написать один фрагмент кода, который выполняет одни и те же действия со многими различными связанными элементами.
For-Next
А For-Next block - это цикл, который повторяется определенное количество раз. Он использует переменную как итератор чтобы подсчитать, сколько раз он был выполнен, и эту переменную итератора можно использовать внутри цикла. Это делает циклы For-Next очень полезными для перебора ячеек или массивов.
Вот пример, который перебирает ячейки в строках с 1 по 100, столбец 1, и устанавливает их значения равными значению переменной итератора:
Dim i до тех пор, пока i = от 1 до 100 ячеек (i, 1). Value = i Next i
Строка «For i = 1 To 100» означает, что цикл начинается с 1 и заканчивается после 100. Вы можете установить любые начальные и конечные числа по своему усмотрению; вы также можете использовать переменные для этих чисел.
По умолчанию количество циклов For-Next равно 1. Если вы хотите использовать другое число, вы можете написать цикл с явным Шаг пункт:
Для i = от 5 до 100 Шаг 5
Этот цикл начинается с 5, затем добавляется 5 к «i» каждый раз, когда цикл повторяется (таким образом, «i» будет 10 при втором повторении, 15 при третьем и т. Д.).
С использованием Шаг, вы также можете сделать обратный отсчет цикла:
Для i = от 100 до 1, шаг -1
Вы также можете гнездо Циклы For-Next. Для каждого блока требуется своя собственная переменная, но вы можете использовать эти переменные где угодно. Вот пример того, как это полезно в Excel VBA:
Dim i до тех пор, j до тех пор, пока i = от 1 до 100 Для j = от 1 до 100 ячеек (i, j). Value = i * j Next j Next i
Это позволяет вам перемещаться по строкам и столбцам.
ПРЕДУПРЕЖДЕНИЕ: хотя это разрешено, НИКОГДА НЕ МОДИФИЦИРУЙТЕ переменную итератора внутри блока For-Next, поскольку он использует этот итератор для отслеживания цикла. Изменение итератора может вызвать бесконечный цикл и зависание макроса. Например:
Для i = 1 До 100 i = 1 Далее i
В этом цикле «I» никогда не выйдет за пределы 2, пока не будет сброшено на 1, и цикл будет повторяться бесконечно.
Для каждого
Для каждого блоки очень похожи на блоки For-Next, за исключением того, что они не используют счетчик, чтобы указать, сколько раз они повторяются. Вместо этого блок For-Each принимает «коллекцию» объектов (например, диапазон ячеек) и запускается столько раз, сколько объектов в этой коллекции.
Вот пример:
Dim r как диапазон для каждого r в диапазоне ("A15: J54") Если r.Value> 0, то r.Font.Bold = True End If Next r
Обратите внимание на использование переменной объекта Range «r». Это переменная итератора, используемая в цикле For-Each - каждый раз при прохождении цикла «r» получает ссылку на следующую ячейку в диапазоне.
Преимущество использования циклов For-Each в Excel VBA заключается в том, что вы можете перебирать все ячейки в диапазоне без циклов вложенности. Это может быть удобно, если вам нужно перебрать все ячейки в сложном диапазоне, например Диапазон («A1: D12, J13, M1: Y12»).
Одним из недостатков циклов For-Each является то, что вы не можете контролировать порядок обработки ячеек. Несмотря на то что на практике Excel будет перебирать ячейки по порядку, в теории он мог обрабатывать клетки в совершенно случайном порядке. Если вам нужно обрабатывать ячейки в определенном порядке, вы должны вместо этого использовать циклы For-Next.
Do-Loop
В то время как блоки For-Next используют счетчики, чтобы знать, когда остановиться, Do-Loop блоки выполняются до тех пор, пока не будет выполнено условие. Для этого вы используете До предложение в начале или в конце блока, которое проверяет условие и заставляет цикл останавливаться, когда это условие выполняется.
Пример:
Dim str As String str = "Buffalo" До тех пор, пока str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & "" & "Buffalo" Диапазон цикла ("A1"). Value = str
В этом цикле «Buffalo» соединяется с «str» каждый раз, пока он не будет соответствовать ожидаемому предложению. В этом случае тест выполняется в начале цикла - если 'str' уже было ожидаемым предложением (а это не потому, что мы не запустили его таким образом, а если) цикл даже не запустится .
Вы можете запустить цикл хотя бы один раз, переместив предложение до конца, например:
Do str = str & "" & "Buffalo" Цикл до str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo"
Вы можете использовать любую версию, которая имеет смысл в вашем макросе.
ПРЕДУПРЕЖДЕНИЕ: вы можете вызвать бесконечный цикл с блоком Do-Loop, если условие «Пока» не выполняется. Всегда пишите свой код так, чтобы при использовании этого типа цикла обязательно выполнялось условие «Пока».
Что дальше?
Когда вы усвоите основы, почему бы не попробовать изучить более сложные техники? Наше руководство на https://easyexcel.net/excel/learn-vba-tutorial/ будет опираться на все, что вы узнали здесь, и расширит ваши навыки с помощью событий, пользовательских форм, оптимизации кода и многого другого!
