18 трюков для Excel VBA 2018 год

Итог: Изучите 18 советов для экономии времени при написании макросов VBA для Excel или других приложений Office.

Уровень мастерства: Средний

18 Советов для макросов VBA

В начале прошлого года я написал популярный пост о 17 советах для Excel на 2017 год. Я решил продолжить традицию в этом году и написать пост о советах, которые сэкономят ваше время при написании макросов VBA.

Здесь часть из моих любимых советов, а также некоторые любимые из блога на YouTube и моего курса VBA Pro.

Очевидно, есть множество советов для VBA. Поэтому, пожалуйста, оставьте комментарий внизу поста с вашими любимыми советами. Таким образом, мы все можем учиться друг у друга! 🙂

Скачать PDF

Вы можете скачать печатную версию статьи в формате PDF.

Содержание

1. Alt + F11, чтобы открыть редактор VB
2. Храните свои макросы в личной книге макросов
3. Ctrl + Пробел для автозаполнения
4. Intellisense для листов
5. Использовать комментарии либерально
6. F8, чтобы пройти через каждую строку кода
7. Назначить макросы фигурам
8. Автоматизируйте повторяющиеся задачи с помощью цикла For Next
9. Используйте Option Explicit
10. Таблицы Excel (ListObjects)
11. Получить код с помощью Macro Recorder
12. Непосредственное окно
13. Назначить сочетание клавиш для макроса
14. Убедитесь, что выбран диапазон
15. Ctrl + Y, чтобы удалить строку кода
16. Ctrl + I для быстрой информации
17. Ctrl + J Открывает раскрывающийся список Intellisense
18. Функции рабочего листа

# 1 — Alt + F11, чтобы открыть редактор VB

VB Editor — это приложение, которое мы используем для написания макросов и создания пользовательских форм. Его можно открыть, нажав кнопку Visual Basic на вкладке «Разработчик» в Excel.

Сочетание клавиш для открытия редактора VB в любой версии Excel для Windows — Alt + F11.

Версия для Mac — Opt + F11 или Fn + Opt + F11.

Если вы не видите вкладку «Разработчик» на ленте, щелкните изображение, чтобы узнать, как его включить:

Клавиши Fn (функция) на ноутбуках

Если вы используете клавиатуру ноутбука, вам нужно нажать и удерживать клавишу Fn, прежде чем нажимать F11. Функциональные клавиши на ноутбуках, как правило, являются многофункциональными клавишами и требуют нажатия клавиши Fn для активации функциональных клавиш (F1 – F12).

Некоторые ноутбуки имеют функцию Fn Lock, которая делает функциональные клавиши первичными, что означает, что вам не придется нажимать клавишу Fn при нажатии F1-F12.

Ознакомьтесь с моей статьей «Лучшие сочетания клавиш для Excel», чтобы узнать больше.

# 2 — Храните свои макросы в личной книге макросов

Personal Macro Workbook (PMW) — это отличное место для хранения часто используемых вами макросов. Эта книга открывается в фоновом режиме каждый раз, когда мы открываем Excel, что делает наши макросы легко доступными.

Мы также можем создавать собственные панели инструментов с кнопками для запуска наших макросов. Мне нравится думать о PMW как о нашем инструментальном поясе Excel, который может сэкономить нам время на выполнение задач, которые мы выполняем каждый день.

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

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

# 3 — Ctrl + Пробел для автозаполнения

Это одно из часто используемых мною сочетаний клавиш в VBA. Когда мы набираем код, Ctrl + Space открывает раскрывающееся меню Intellisense, которое содержит список подходящих объектов, свойств, методов, констант и переменных.

Чтобы использовать сочетание клавиш Ctrl + Space в VB Editor:

1. Начните вводить строку кода, подобную ActiveCell.
2. После ввода первых нескольких букв нажмите Ctrl + Пробел
3. Вы увидите список всех слов VBA, которые начинаются с Act.
4. Нажмите стрелки вверх / вниз, чтобы выбрать слово
5. Затем нажмите Tab или Enter, чтобы завершить слово.

У этого трюка есть два основных преимущества:

1. Это экономит время, так как не нужно набирать длинные слова и имена переменных.
2. Это предотвращает опечатки, потому что VBA завершает слова для вас.

Эти преимущества могут значительно сэкономить время при отладке кода.

# 4 — Intellisense для листов

Обычно мы также видим раскрывающееся меню Intellisense после ввода точки (.) В редакторе VB.

Однако это работает не всегда. Одним из распространенных случаев является свойство Worksheets.

Если мы введем Worksheets («Лист1»). Мы НЕ видим меню Intellisense. Вы можете расстроиться и подумать, что Intellisense не работает.

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

Есть два способа обойти это и увидеть Intellisense для рабочих листов:

1. Используйте кодовое имя рабочего листа, на который мы хотим сослаться. Это мой любимый метод для ссылок на рабочие листы, потому что код не сломается, если пользователь изменит имя листа.

2. Сначала установите для рабочей таблицы переменную объекта Worksheets. Затем, когда мы введем имя переменной, за которым следует точка (ws.), Появится меню Intellisense.

Сначала установите для рабочей таблицы переменную объекта Worksheets. Затем, когда мы введем имя переменной, за которым следует точка (ws.), Появится меню Intellisense.

# 5 — Используйте комментарии чаще

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

Чтобы создать комментарий в VBA, вы вводите апостроф в начале строки. Как только вы переместите текстовый курсор за линию, текст станет зеленым.

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

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

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

Если вы собираетесь поделиться своим проектом VBA или в конечном итоге передать его кому-то другому для технического обслуживания, тогда им будет намного легче выучить ваш код, если вы добавите много комментариев. Я называю это «вежливым планированием вашего наследия». 🙂

№ 6 — F8, чтобы пройти через каждую строку кода

Сочетание клавиш для перехода по каждой строке кода — F8. Эквивалентом Mac для Step Into/Through является Cmd + Shift + I.

Это позволяет нам тестировать и отлаживать каждую строку кода в наших макросах. Мы также можем открыть Excel рядом с редактором VB (или на отдельном мониторе), чтобы увидеть действия, выполняемые в Excel при выполнении каждой строки.

Часто это может помочь вам быстро найти ошибку с помощью листа или диапазона.

Чтобы использовать ярлык Step Into/Through:

1. Нажмите внутри макроса, который вы хотите запустить. Вы можете нажать на любую строку кода. Макрос всегда будет начинаться сверху.
2. Нажмите F8.
3. Имя макроса будет выделено желтым цветом.
4. Нажмите F8 еще раз, чтобы запустить эту строку и выделить следующую строку.
5. Продолжайте нажимать F8, чтобы запустить каждую строку.

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

# 7 — Фигуры, запускающие макросы

Листовые элементы управления для кнопок, запускающих макросы, выглядят немного устаревшими. К счастью, мы также можем использовать любую фигуру в Excel для запуска макроса. Формы можно раскрасить и отформатировать, чтобы они больше походили на современные кнопки, которые можно найти в Интернете и в мобильных приложениях.

Чтобы назначить макрос формой:

1. Вставьте фигуру на лист и отформатируйте ее по своему вкусу. Обычно это будет прямоугольная или круглая форма, содержащая текст.
2. Щелкните правой кнопкой мыши форму и выберите «Назначить макрос …».
3. Выберите макрос из списка и нажмите ОК. Макрос обычно будет тем, который хранится в той же книге, что и фигура.
4. Нажмите на форму, выбрав ячейку на листе.
5. При наведении курсора курсор изменится на указатель руки. Нажатие на форму запустит макрос.

Я рекомендую, чтобы окно «Да / Нет» появилось до того, как макрос действительно запустится. Это предотвращает любые случайные нажатия кнопок. Посмотрите мое видео о добавлении окна сообщения Да / Нет в ваши макросы. Это часть моей серии видеороликов о Персональной рабочей тетради.

# 8 — Автоматизируйте повторяющиеся задачи с помощью цикла For Next

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

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

Существует несколько различных типов циклов, но цикл For Next Loop является наиболее распространенным. Ознакомьтесь с моей углубленной статьей «For Next Loop» в VBA для более подробной информации об этой обязательной технике кодирования.

# 9 — Используйте Option Explicit

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

Итак, что это и почему?

Опция Explicit требует от нас объявить все переменные. Когда мы видим строки кода с инструкциями Dim в верхней части макроса, это объявляет переменную.

Мы в основном говорим VBA создать переменную в памяти для последующего использования во время выполнения кода. Затем мы можем установить значения или ссылки на объекты для этих переменных в макросе под оператором Dim.

Основное преимущество Option Explicit заключается в том, что он предотвращает опечатки и экономит время. Редактор VB выдаст сообщение «Ошибка компиляции: переменная не определена» при попытке запустить код, если переменная в коде не объявлена. Также будет выделена переменная, чтобы вы могли объявить ее или исправить опечатку.

Если у вас нет Option Explicit и неправильно написана переменная, код все равно будет работать и может привести к ошибкам в результатах. Если ваш макрос длинный, то может понадобиться много времени, чтобы найти эти опечатки. Доверьтесь мне. Там было сделано это!

Опция Explicit предотвращает эти ошибки и помогает вам оставаться в здравом уме. 🙂

Чтобы включить Option Explicit, просто введите слова Option Explicit в верхней части модуля кода. Вы также можете сделать так, чтобы VB Editor автоматически добавлял слова в новые модули кода, для этого перейдите в Инструменты> Параметры> установите флажок «Требовать декларацию переменной». Опция Explicit теперь будет отображаться в верхней части каждого нового кода модуля, который вы создаете.

# 10 — Таблицы Excel (ListObjects)

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

Таблицы Excel также значительно упрощают написание кода VBA для динамических диапазонов данных. Это список или набор данных, в котором количество строк или столбцов постоянно меняется по мере получения новых / обновленных данных.

Например, следующая строка кода ссылается на ячейки в диапазоне A2: A15.

Range("A2:A10").Font.Bold = True

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

Однако, если мы храним данные в таблице Excel и ссылаемся на столбец таблицы, нам не нужно об этом беспокоиться.

Следующая строка кода ссылается на тот же столбец.

Range("Table1[Date]").Font.Bold = True

Преимущество в том, что код будет автоматически включать новые строки, добавленные в таблицу. Не требуется ручное обновление или обслуживание кода.

Мы также можем ссылаться на таблицы Excel с помощью объекта, свойств и методов ListObjects в VBA.

ActiveSheet.ListObjects("Table57").ListColumns("Date").DataBodyRange.Font.Bold = True

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

# 11 — Получите код с помощью Macro Recorder

Macro Recorder — удивительная особенность Excel и VBA. Он создает код VBA при выполнении действий в Excel.

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

Это отличный инструмент для использования, когда мы начинаем с макросов. И это также отличный инструмент для изучения и получения фрагментов кода. Объектная модель Excel огромна, и для меня невозможно запомнить все ссылки на свойства, методы и объекты. Таким образом, средство записи макросов — отличный способ получить некоторый код для сводной таблицы, объекта списка, среза, фигуры или любого другого объекта, с которым вы не знакомы.

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

# 12 — The Immediate Window

Immediate Window в редакторе VB позволяет нам запускать одну строку кода. Мы можем запустить действие (метод) над объектом или вернуть результат кода обратно в Immediate Window. Может быть, мы хотим определить, сколько рабочих листов в рабочей книге.

Введите ?Worksheets.Count и нажмите Enter. Результат будет отображен в строке ниже.

Immediate Window также является местом, в которое выводится метод Debug.Print.

Сочетание клавиш для открытия окна Immediate в редакторе VB — Ctrl + G

За подробностями обращайтесь к моей статье о 5-ти вариантах использования Immediate Window в VBA.

# 13 — Назначьте сочетание клавиш для макроса

Мы также можем назначить сочетания клавиш для запуска часто используемых макросов. Я рекомендую хранить эти макросы в вашей личной книге макросов.

Чтобы назначить сочетание клавиш:

1. Нажмите кнопку «Макросы» на вкладке «Разработчик» или «Просмотр» на ленте.
2. Выберите файл, содержащий макрос, в раскрывающемся списке «Макросы в».
3. Выберите макрос из списка.
4. Нажмите кнопку «Опции…».
5. Введите букву в поле «Сочетание клавиш», которому вы хотите назначить макрос. Все ярлыки начнутся с Ctrl. Вы можете удерживать клавишу Shift при наборе буквы, чтобы создать комбинацию клавиш Ctrl + Shift. Обычно это рекомендуется, потому что большинство комбинаций клавиш Ctrl + уже имеют выделенные сочетания клавиш в Excel.
6. Нажмите ОК и закройте окно макроса.
7. Теперь вы можете нажать комбинацию клавиш для запуска назначенного макроса.

# 14 — Проверьте, выбран ли диапазон

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

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

Вот код, который проверит, выбран ли диапазон.

' Проверьте, что диапазон выбран
If TypeName(Selection) <> "Range" Then
  MsgBox "Please select a range first.", vbOKOnly, "Select Range"
  Exit Sub
End If

Функция TypeName возвращает тип данных или имя объекта для данной переменной или объекта. В этом случае он оценивает Selection и возвращает тип выбранного объекта. Если это не (<>) диапазон, тогда оператор If верен.

Обычно вы хотите поместить это в начало макроса. Если диапазон НЕ выбран, появится окно сообщения (всплывающее), в котором пользователю будет предложено выбрать диапазон. Строка Exit Sub завершит макрос.

# 15 — Ctrl + Y, чтобы удалить строку кода

В редакторе VB Ctrl + Y удаляет строку кода, в которой находится текстовый курсор.

Это создает большую путаницу, поскольку Ctrl + Y обычно используется для команды «Повторить» практически во всех других приложениях, включая Excel!

Если вы посмотрите на меню «Правка» в редакторе VB, то увидите, что для «Вернуть» нет выделенного ярлыка. Мы можем использовать Alt, E, R в качестве альтернативного ярлыка для Redo.

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

# 16 — Ctrl + i для быстрой информации

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

Чтобы использовать сочетание клавиш Ctrl + i:

1. Поместите текстовый курсор в слово, для которого вы хотите отобразить всплывающую подсказку.
2. Нажмите Ctrl + i.
3. Появится всплывающая подсказка.
4. Нажмите Escape, чтобы закрыть его или переместить курсор.

Если в строке выделена переменная, и вы хотите видеть информацию о параметре вместо значения / типа данных переменной, нажмите Ctrl + Shift + i, чтобы просмотреть информацию о параметре.

# 17 — Ctrl + J открывает раскрывающийся список IntelliSense

Сочетание клавиш Ctrl + J откроет раскрывающееся меню Intellisense, в котором отображается список объектов, свойств, методов, переменных и т.д.

Обычно я использую его, когда набираю частичную строку кода и заканчиваю ее точкой, например Range («A1»).

Затем я хочу вернуться к этой строке и увидеть выпадающий Intellisense. Нажатие Ctrl + J открывает меню. В противном случае вы должны удалить период и ввести его снова.

Мы также можем использовать Ctrl + J, чтобы выбрать другую переменную из списка. Если вы где-то использовали неправильное имя переменной или вам нужно его изменить, нажмите Ctrl + J, чтобы увидеть список имен переменных. Если вы добавите префикс к своим именам переменных (он же венгерская нотация), то другие переменные должны быть довольно близки в этом списке.

№ 18 — Функции листа

Знаете ли вы, что мы можем использовать функции рабочего листа в VBA? Это функции, которые мы используем в формуле в Excel, такие как: vlookup, match, countif, max, min и т.д.

Тип WorksheetFunction. в макросе, чтобы увидеть список всех функций Excel, которые доступны в VBA.

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

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

Функция рабочего листа, которую я чаще всего использую в VBA — это Match. Мы можем использовать Match для поиска значения и возврата номера строки или столбца ячейки, содержащей соответствующее значение. Иногда это может быть проще, чем с помощью метода Range.Find. Ознакомьтесь с моей статьей и видео о том, как найти последнюю использованную ячейку на листе, чтобы узнать больше о методе Range.Find.

Скачать PDF

Вы можете скачать печатную версию статьи в формате PDF.

Какой ваш любимый совет для VBA?

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

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