Создание собственного (или не совсем) макроса в Excel


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

Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз.

Допустим, нам нужно из 200 файлов Excel, расположенных в одной папке, взять значение одной и той же ячейки и сохранить их в отдельной книге Excel для дальнейшего анализа примерно в таком виде:


Файл Значение ячейки
1 1.xlsx 111
2 111.xlsx 222
3 1111.xlsx 2

Для решения такой специфической задачи вы нашли в интернете текст процедуры, что-то вида:


Эта процедура перебирает все файлы Excel, находящиеся по адресу, указанному в строке

wkbook_path = "D:\proba\xls\"

, где: D:\proba\xls\ - папка, где находятся файлы.

Затем вытаскивает из каждого файла значения ячейки таблицы, определенной в строке

«nwsheet.Cells(i, 3).Value = wk.ActiveSheet.Range("A1").Value»

- в данном случае это ячейка A1 и размещает эти значения в таблице в отдельной книге Excel.


Чтобы воспользоваться процедурой, нужно:


  1. Создать новую книгу Excel
  2. В новой книге нажать комбинацию клавиш Alt + F11, чтобы вызвать Редактор Visual Basic for Applications (или сокращенно VBA), в среде которого будет выполнятся наша процедура:


  3. Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В книге Excel можно создать любое количество программных модулей и разместить там макросы. Один модуль может содержать любое количество макросов. Чтобы создать модуль выберите в меню Insert - Module.


  4. … и вставьте в окно модуля текст процедуры:



    Заметьте, что редактор VBA автоматически выделил цветом начало и конец процедуры, цикл Do While и другие служебные слова.

  5. Теперь нужно сохранить макрос. Нажмите кнопку с изображением дискеты или меню File – Save


  6. Обратите внимание, что тип файла который нужно выбрать для сохранения – книга Excel с поддержкой макросов (*.xlsm)



  7. Чтобы выполнить макрос нажмите комбинацию клавиш Alt + F8 или через меню Вид – Макросы. В появившемся окне нажмите кнопку Выполнить:


Результатом работы макроса будет новая таблица Excel с собранными данными:



Для быстрого вызова макроса можно назначить ему сочетание клавиш:



… по нажатию которого макрос запускается на выполнение.

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

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа:



Затем в появившемся окне выберите категорию Макросы и с помощью кнопки Добавить перенесите макрос в правую половину окна:



В результате на панели быстрого доступа появится кнопка для запуска макроса:



Помимо кнопки на панели быстрого доступа можно создать кнопку для запуска макроса непосредственно на листе рабочей книги Excel

Для этого нужно, чтобы на ленте Excel была включена вкладка Разработчик:

Меню Файл – Параметры – Настройка ленты



откройте выпадающий список Вставить на вкладке Разработчик и выберите элемент Кнопка. Не отпуская левую клавишу мыши...



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



Все! Ваш первый макрос готов к работе.



Далее:
Как написать и использовать собственную надстройку в Excel