Создание собственных макросов в Microsoft Excel

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

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

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

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

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

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

    Sub CelValue()
    Application.ScreenUpdating = False
    wkbook_path = "D:\proba\xls\"
    i = 1
    file_name = Dir(wkbook_path & "*.xls*")
    Set wkbook = Workbooks.Add
    Set nwsheet = wkbook.Sheets.Add
    nwsheet.Activate
    nwsheet.Cells(i, 1).Value = "№"
    nwsheet.Cells(i, 2).Value = "Файл"
    nwsheet.Cells(i, 3).Value = "Значение ячейки"
    Do While file_name <> ""
    i = i + 1
    Application.StatusBar = "Файл " & file_name
    Workbooks.Open (file_name)
    Set wk = ActiveWorkbook
    nwsheet.Cells(i, 1).Value = i - 1
    nwsheet.Cells(i, 2).Value = file_name
    nwsheet.Cells(i, 3).Value = wk.ActiveSheet.Range("A1").Value
    wk.Close SaveChanges:=False
    file_name = Dir
    Loop
    Application.StatusBar = False
    Application.ScreenUpdating = True
    End Sub

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

wkbook_path = "D:\proba\xls\"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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