Работа с макросами Excel VBA
Макросы в Excel хранятся в виде кода VBA, и иногда вам нужно отредактировать этот код напрямую. В этом руководстве будет рассказано, как просматривать и редактировать макросы, описаны некоторые методы отладки макросов и приведены некоторые общие примеры редактирования.
Просмотр макросов
Список макросов можно отобразить в диалоговом окне «Макросы». Чтобы просмотреть это диалоговое окно, выберите вкладку «Разработчик» на ленте и нажмите кнопку «Макросы».
Если открыто несколько книг, в списке будут показаны макросы из всех книг. Макросы в активной книге будут отображаться только по имени, в то время как макросы в других книгах будут иметь префикс в виде имени книги и восклицательного знака (например, «Book2! OtherMacro»).
Откройте макрос для редактирования
Вы можете использовать диалоговое окно "Макрос", чтобы открыть код макроса, выбрав имя макроса и нажав кнопку "Изменить". Это откроет макрос в редакторе VB.
Кроме того, вы можете открыть редактор VB напрямую, нажав кнопку Visual Basic на вкладке «Разработчик» или нажав сочетание клавиш ALT + F11.
Используя этот метод, вам нужно будет перейти к желаемому макросу (также называемому «процедурой»). Мы рассмотрим макет редактора VBA:
Обзор редактора VB
Редактор VB имеет несколько окон; в этом руководстве мы рассмотрим окно проекта, окно свойств и окно кода.
Окно проекта
В окне «Проект» каждый файл Excel отображается как отдельный проект, при этом все объекты в этом проекте сгруппированы по типу. Записанные макросы появятся в категории «Модули», как правило в объекте «Модуль1». (Если в вашем проекте несколько модулей, и вы не знаете, где хранится ваш макрос, просто откройте его в вышеупомянутом диалоговом окне «Макросы».)
Окно свойств
В окне «Свойства» отображаются свойства и связанные с ними значения объекта - например, щелчок по объекту рабочего листа в окне «Проект» отображает список свойств рабочего листа. Имена свойств находятся слева, а значения свойств - справа.
Выбор модуля в окне проекта покажет, что у него есть только одно свойство, «(Имя)». Вы можете изменить имя модуля, дважды щелкнув значение свойства, введя новое имя и нажав Enter. При изменении имени модуля он будет переименован в окне проекта, что полезно, если у вас много модулей.
Окна кода
Окна кода - это специальные текстовые редакторы, в которых вы можете редактировать код VBA вашего макроса. Если вы хотите увидеть код макроса, расположенный в Module1, вы должны дважды щелкнуть «Module1» в окне проекта.
Запуск макросов в редакторе VB
Макросы можно запускать прямо из редактора VB, что полезно для тестирования и отладки.
Запуск макроса
- В окне проекта дважды щелкните модуль, содержащий макрос, который вы хотите протестировать (чтобы открыть его окно кода).
- В окне кода поместите курсор в любое место кода макроса между «Sub» и «End Sub».
- Щелкните значок Запустить на панели инструментов или нажмите сочетание клавиш F5
«Пошаговый» макрос
Вместо того, чтобы запускать макрос сразу, вы можете запускать макрос по одной строке за раз, используя сочетание клавиш для «пошагового выполнения» кода. Макрос будет приостанавливаться на каждой строке, позволяя вам убедиться, что каждая строка кода выполняет то, что вы ожидаете от Excel. Вы также можете остановить продолжение макроса в любое время, используя этот метод.
Чтобы выполнить макрос:
- В окне проекта дважды щелкните модуль, содержащий макрос, который вы хотите протестировать (чтобы открыть его окно кода).
- В окне кода поместите курсор в любом месте кода макроса.
- Нажмите сочетание клавиш F8, чтобы начать пошаговый процесс.
- Нажмите F8 несколько раз, чтобы ускорить выполнение кода, на что указывает желтая подсветка в окне кода.
- Чтобы остановить продолжение макроса, нажмите кнопку Сброс настроек кнопка
Зачем редактировать макросы VBA?
Запись макросов, хотя и эффективна, также очень ограничена. В некоторых случаях он создает медленные макросы, записывает действия, которые вы не собирались повторять, или записывает то, что, как вам казалось, вы не делали. Умение редактировать макросы поможет им работать быстрее, эффективнее и предсказуемее.
Помимо устранения этих проблем, вы также значительно повысите производительность, если воспользуетесь всей мощью макросов. Макросы не должны быть просто записью задач - макросы могут включать логику, поэтому они выполняют задачи только при определенных условиях. Всего за пару минут вы можете закодировать циклы, которые повторяют задачу сотни или тысячи раз за один раз!
Ниже вы найдете несколько полезных советов, которые помогут оптимизировать код макроса, а также инструменты, которые помогут сделать макросы более эффективными и умными.
Общие примеры редактирования макросов
Макросы ускорения
Если у вас есть макрос, выполнение которого занимает много времени, может быть несколько причин, по которым он работает медленно.
Во-первых: при запуске макроса Excel покажет все, как это происходит в реальном времени, хотя может Смотреть быстро к тебе, на самом делепоказывая работа является значительным ударом по производительности. Один из способов значительно ускорить работу Excel - это сообщить ему прекратить обновлять экран:
'Отключить обновление экрана приложения.ScreenUpdating = False' Включить обновление экрана приложения.ScreenUpdating = True
Строка Application.ScreenUpdating = False означает, что вы не увидите, что макрос работает, но он будет работать намного быстрее. Обратите внимание, что вы всегда должны устанавливать для ScreenUpdating значение True в конце макроса, иначе Excel может работать не так, как вы ожидаете позже!
Другой способ ускорить макросы:отключить автоподсчет в макросе. Если вы работали со сложными таблицами, вы знаете, что небольшие изменения могут вызвать тысячи вычислений, выполнение которых требует времени, поэтому многие люди отключают автоматический расчет в параметрах Excel. Вы также можете переключить это с помощью кода VBA, чтобы ваш макрос по-прежнему быстро работал на других компьютерах. Это помогает в случаях, когда вы копируете много ячеек формулы или вызываете выполнение большого количества вычислений при вставке данных в диапазон:
'Отключить приложение автоматического расчета.Calculation = xlCalculationManual' Включить приложение автоматического расчета.Calculation = xlCalculationAutomatic
Добавить циклы и логику (если операторы)
Средство записи макросов сохраняет все ваши действия в виде кода на языке VBA. VBA - это больше, чем просто способ записи действий в Excel - это язык программирования, что означает, что он может содержать код, позволяющий принимать решения о том, какие действия выполнять, или повторять действия до тех пор, пока не будет выполнено условие.
Зацикливание
Допустим, вы хотели создать макрос, который подготовил отчет, и как часть этого макроса вам нужно было добавить девятнадцать листов в книгу, всего двадцать. Вы можете записывать, как вы нажимаете кнопку (+) снова и снова, или можете написать цикл, который повторяет действие за вас, например:
Sub ReportPrep () Dim i As Long For i = от 1 до 19 листов Добавить следующий i End Sub
В этом примере мы используем Для цикла, который представляет собой своего рода цикл, который выполняет итерацию по диапазону элементов. Здесь наш диапазон - это числа от 1 до 19, с использованием переменной с именем «i», чтобы цикл мог отслеживать. Внутри нашего цикла только одно действие повторяется между для а такжеследующий строк (добавление листа), но вы можете добавить столько кода внутри цикла, сколько захотите, например, отформатировать лист или скопировать и вставить данные на каждый лист - все, что вы хотите повторить.
Если утверждения
An Если заявление используется, чтобы решить, запускается какой-то код или нет, используя логический тест для принятия решения. Вот простой пример:
Sub ClearIfSmall () Если Selection.Value <100 Then Selection.Clear End If End Sub
Этот простой пример показывает, как работает оператор If - вы проверяете какое-либо условие, которое истинно или ложно (значение выбранной ячейки меньше 100?), и если тест возвращает True, код внутри выполняется.
Недостатком этого кода является то, что он проверяет только одну ячейку за раз (и не будет работать, если вы выберете несколько ячеек). Это было бы более полезно, если бы вы могли … пройтись по каждой выбранной ячейке и протестировать каждую …
Sub ClearIfSmall () Dim c As Range для каждого c In Selection.Cells, если c.Value <100 Then c.Clear End If Next c End Sub
В этом примере есть немного другой цикл For - он не перебирает диапазон чисел, а вместо этого перебирает все ячейки в выделенном фрагменте, используя для отслеживания переменную с именем «c». Внутри цикла значение «c» используется для определения, следует ли очистить ячейку или нет.
Операторы Loops и If можно комбинировать как угодно - вы можете помещать циклы внутри циклов или один If внутри другого, или использовать If, чтобы решить, должен ли цикл запускаться вообще.
<<>>
Удалить эффекты прокрутки
Распространенной причиной редактирования кода макроса является удаление прокрутки экрана. При записи макроса вам может потребоваться прокрутка для доступа к другим областям листа, но для доступа к данным макросы не нужно прокручивать.
Прокрутка может загромождать ваш код сотнями или даже тысячами строк ненужного кода. Вот пример кода, который записывается, когда вы нажимаете и перетаскиваете полосу прокрутки:
В таком коде нет необходимости, и его можно удалить, не затрагивая другие функции. Даже если бы вы хотели сохранить прокрутку, этот код все равно можно было бы сжать в цикл.
Удалить избыточный код
Записанные макросы, как правило, добавляют много избыточного кода, который не обязательно отражает то, что вы хотите, чтобы макрос выполнял. Возьмем, к примеру, следующий записанный код, в котором записано изменение имени шрифта в ячейке:
Несмотря на то, что было изменено только имя шрифта, было записано одиннадцать (11) изменений шрифта, таких как размер шрифта, текстовые эффекты и т. Д. Если целью макроса было только изменить имя шрифта (оставив все остальные свойства в покое), это записанный макрос не работал!
Этот макрос можно изменить так, чтобы он изменял только имя шрифта:
Теперь этот макрос не только будет работать так, как задумано, но и его будет намного легче читать.
Удалить перемещение курсора
Еще одна вещь, которая записывается в макросах, - это выбор рабочих листов и ячеек. Это проблема, потому что пользователь может легко потерять из виду, над чем он только что работал, если курсор переместится в другую позицию после запуска макроса.
Как и при прокрутке, ты может потребоваться переместить курсор и выбрать разные ячейки для выполнения задачи, но макросам не обязательно использовать курсор для доступа к данным. Рассмотрим следующий код, который копирует диапазон, а затем вставляет его в три других листа:
С этим кодом есть несколько проблем:
- Пользователь потеряет свое прежнее место в книге
- Макрос не указывает, какой лист мы копируем.из - это могло быть проблемой, если макрос был запущен не на том листе
Кроме того, код труден для чтения и расточителен. Эти вопросы решаются достаточно легко:
В этом коде ясно видно, что мы копируем из Sheet1, и ни активный лист, ни выбранный диапазон не нужно изменять, чтобы вставить данные. (Одним из существенных изменений является использование «PasteSpecial» вместо «Paste» - объекты Range, такие как «Range (« C4 ″) », имеют доступ только к команде PasteSpecial.)
Всякий раз, когда код наполняется ссылками на «.Select» и «Selection», это признак того, что есть место для оптимизации этого кода и повышения его эффективности.
