0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Создание макросов и пользовательских функций на VBA

Создание макросов и пользовательских функций на VBA

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

Кстати, мы ранее писали о том, сколько времени можно сэкономить, приложив усилия и записав макрос.

Exceltip

макросы excel

VBA считается стандартным языком написания сценариев для приложений Microsoft, и в настоящее время он входит в состав всех приложений Office и даже приложений других компаний. Следовательно, овладев VBA для Excel, вы сможете сразу перейти к созданию макросов для других программных продуктов Microsoft. Более того, вы сможете создавать полноценные программные продукты, одновременно использующие функции самых разных приложений.

Как включить макросы в Excel

По умолчанию, вкладка, отвечающая за управление и навигацию макросов в Excel, скрыта. Чтобы активировать данную опцию перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры Excel, перейдите по вкладке Настройка ленты, в правом поле со списком ставим маркер напротив вкладки Разработчик. Данные действия актуальны для версий Excel 2010 и старше.

отображение вкладки разработчик

На ленте появиться новая вкладка Разработчик с элементами управления автоматизации Excel.

вкладка разработчик excel

Написание макросов в Excel

Во вкладке Разработчик в группе Код, нажмите кнопку Запись макроса. Появиться диалоговое окно Запись макроса, которая запрашивает некоторую информацию о будущем записываемом коде. Если вы впервые создаете макрос, можете просто нажать кнопку ОК. С данного момента Excel будет фиксировать каждое действие пользователя в модуле VBA, будь то ввод данных, форматирование или создание диаграмм. Чтобы остановить запись макроса, нажмите кнопку Остановить запись, которая находится в той же группе Код.

Читайте так же:
Заглавные (прописные) и строчные буквы в Excel

отключить макросы в excel

Вы также можете воспользоваться альтернативным вариантом записи макросов, воспользовавшись кнопкой Запись макроса, которая находится в левом нижнем углу рабочей книги Excel (правее статуса Готово).

Теперь вы можете посмотреть список всех созданных макросов, нажав на кнопку Макрос, находящуюся в группе Код. В появившемся диалоговом окне вы можете дать более описательные имена своим кодам или задать сочетания клавиш, которые бы запускали тот или иной макрос. Альтернативным вариантом запуска данного окна является нажатие клавиш Alt + F8.

список макросов

Редактирование макросов

Поздравляю! Вы написали свой первый макрос. Логичным будет проверить теперь, какой же код сгенерировал нам Excel. Сгенерированный код написан на языке VBA (Visual Basic for Applications). Чтобы увидеть его, нужно открыть Редактор VB (VBE), который запускается нажатием клавиш Alt + F11 или кнопкой Visual Basic на вкладке Разработчик.

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

редактор макросов

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

Для просмотра кода, щелкните по ветке Modules в окне проектов и дважды щелкните по появившейся ветке Module1. Редактор откроет окно с кодом, как изображено на картинке.

код в редакторе макросов

Здесь можно редактировать сгенерированный код, который был записан при работе в Excel. К примеру, вам требуется заполнить определенный столбец значениями от 1 до 10. У вас уже есть первые три шага, которые вводят значения 1, 2 и 3 в первые три ячейки столбца А. Нам необходимо дописать оставшиеся семь шагов.

Если вы посмотрите на приведенный выше код, вы увидите, что макрос определенным образом структурирован. Сначала приложение перемещает курсор на ячейку с помощью команды Range("A1").Select, затем редактирует его содержимое с помощью ActiveCell.FormulaR1C1 = "1". Таким образом, для оставшихся шагов мы можем повторить эти действия, меняя адрес ячейки и значение, которое вы хотите записать в эту ячейку. Например, чтобы задать ячейке A4 значение 4, вы должны написать:

Читайте так же:
Как в Excel добавлять названия к диаграммам

И повторить аналогичные шаги для оставшихся значений.

После того, как вы закончите редактирование, сохраните книгу. Запустить макрос вы сможете нажатием кнопки F5, либо, вернувшись в рабочую книгу Excel, перейти по вкладке Разработчик в группу Код -> Макросы и выбрать из списка, интересующий вас макрос.

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

Увеличение скорости выполнения макросов Excel

Пока все хорошо. Давайте разберем пару хитростей, которые помогут ускорить выполнение макроса. Рассмотрим в качестве примера фрагмент кода, приведенный выше. Современные компьютеры отработают рассматриваемый код так быстро, что вы даже не заметите этого. Но что если вам необходимо выполнить операцию 50000 раз. Это займет какое-то время. Если макрос, написанный вами, исчисляется сотнями строк, ускорить выполнение кода можно путем обрезки части процессов, который не используется во время исполнения макроса.

Использование команды Application.ScreenUpdating

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

Команда Application.ScreenUpdating говорит Excel, чтобы он прекратил выводить пересчитанные данные на экран, а выдал готовые значения в конце выполнения кода.

Использование команды Application. Calculation

Вторая хитрость заключается в отключении автоматических вычислений. Давайте я поясню. Каждый раз, когда пользователь или процесс обновляет ячейку, Excel пытается пересчитать все зависимые от нее ячейки. Так скажем, если ячейка, которую пытается обновить макрос, влияет на 10000 остальных ячеек, Excel будет пытаться пересчитать их все до того, как закончится выполнение кода. Соответственно, если существует целый ряд влияющих ячеек, пересчет может значительно замедлить выполнение кода. Чтобы этого не происходило, вы можете установить команду Application. Calculation в начале кода, которая переключит пересчет формул в ручной режим, а затем вернуть автоматическое вычисление в конце макроса.

Читайте так же:
Лучшая читалка fb2 книг для компьютера: ТОП-10 приложений

Будьте внимательны, не забудьте переключить данную опцию снова в автоматический режим в конце макроса. В противном случае, вам необходимо будет это сделать в самом Excel, перейдя по вкладке Формулы в группу Вычисление и выбрать Параметры вычислений –> Автоматический.

Избежание выбора ячеек и диапазонов

В режиме автоматической записи макросов, вы можете заметить, что Excel очень часто использует команду выбора ячеек, например, Range(«A1»).Select. В нашем примере, мы использовали данную команду несколько раз, чтобы выбрать ячейку и изменить ее значение. Вы можете избежать этого просто указав адрес ячейки и задав ей необходимое значение (Макрос записал движение курсора от одной ячейки к другой, следовательно, вставил эти шаги. Однако они не являются необходимыми). Так что, более эффективный код будет выглядеть следующим образом.

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

Примеры макросов Excel

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

При решении ряда задач средствами программирования бывает удобно оперировать данными привязок отчета. Для этого необходимо:

Ниже описано, как это это делается для привязок разных типов.

Тип привязки: Объект

В отчете такие привязки представлены полями.

Использование названия привязки в коде:

Тип привязки: Список и Фильтр

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

Использование названия привязки в коде:

Ближайшие группы:

10:30 — 17:45

сб

Очно
15 950 руб.
Online
13 550 руб.

м. Бауманская
ул. Большая Почтовая, 26В, стр. 1

Норкина Елена Кемеровна

18:30 — 21:45

вт чт

Очно
15 950 руб.
Online
13 550 руб.

м. Бауманская
ул. Большая Почтовая, 26В, стр. 1

Читайте так же:
Как установить uTorrent на компьютер

Казаков Александр Владимирович

18:30 — 21:45

вт чт

Очно
15 950 руб.
Online
13 550 руб.

м. Бауманская
ул. Большая Почтовая, 26В, стр. 1

Казаков Александр Владимирович

Сделайте панель инструментов, которая работает для вас!

Все знают, что Excel — невероятно мощная программа

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

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

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

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

У вас есть совет по созданию макросов Excel? Вы ищете помощь в процессе создания панели инструментов? Присоединяйтесь к разговору в разделе комментариев ниже!

Вы научитесь

  • Использовать автоматическую запись макроса в Excel и исправлять недостатки автоматически генерируемого программного кода
  • Создавать проекты в редакторе VBA, редактировать, отлаживать и выполнять программный код
  • Программировать работу с переменными, условные переходы и циклы, диалоги для ввода значений и вывода сообщений
  • Программировать доступ и управление объектами Excel (такими как рабочая книга, рабочие листы и диапазоны ячеек)
  • Создавать и применять пользовательские функции

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

Читайте так же:
Приложения для создания напоминаний на Андроид

Большинство выпускников наших курсов делают успешную карьеру и пользуются уважением работодателей.

Сделайте панель инструментов, которая работает для вас!

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

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

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

У вас есть совет по созданию макросов Excel? Вы ищете помощь в процессе создания панели инструментов? Присоединяйтесь к разговору в разделе комментариев ниже!

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector