Время прочтения: 3 мин.
Практически каждый день нам приходится сталкиваться с выгрузками и таблицами, которые необходимо преобразовать, добавлять в них новые значения и тп. Довольно часто мы повторяем одни и те же действия, требующие рутинной, ручной настойки и необходимого уровня обслуживания по мере изменения вводных данных. В такой момент нам могут помочь макросы.
Макросы – команда (макрокоманда) с чёткой запрограммированостью шагов, зафиксированных пользователем. При помощи зашитого в Microsoft языка сценариев Visual Basic for Application (VBA) производится запись макросов. Существуют следующие способы таких записей, использующие:
- VBA: лично пишем макросы путем программирования. Для этого задействуем редактор VBA находящийся в версиях Microsoft Excel.
- Macro Recorder: в этом процессе Excel сам сохраняет любое действие пользователя, которое станет элементом макроса, далее инструмент записи макросов перезапишет эти действия в шаги в VBA.
Хочу поделиться опытом использования в работе некоторых таких макросов.
1. VBA
- замена применённой формулы на её результат. Для этого используются специальные вставки (выбрать диапазон – копировать — спец.вставка — значение). Упрощаем эти действия макросом следующего содержания:
Sub FormulaText ()
‘код макроса: каждая ячейка выделенной
области меняет формулы на их значения
For Each cell In Selection
cell.Formula = cell.Value
Next cell
‘информация о завершении работы
MsgBox "Готово !"
End Sub
- исключить ручной способ поиска и маркирования дубликатов. Приведенный ниже макрос ищет дубликаты и подсвечивает их:
Sub Dublikat ()
' задаем переменные диапазона
Dim MyRange As Range
Dim MyCell As Range
' задаем выделение диапазона
Set MyRange = Selection
' задаем цикл через диапазон
For Each MyCell In MyRange
'задаем функцию СЧЕТЕСЛИ, определяя значений в диапазоне
If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then
' используем цвет по rgb
MyCell.Interior.ColorIndex = 27
End If
'возвращаем следующую ячейку
Next MyCell
End Sub
2. Macro Recorder
При работе с текстовыми файлами возникает необходимость их экспорта в Excel (например, получение информации из банковских АС по созданным ранее шаблонам, в которых меняются периоды выгрузки, объекты, а структура остается неизменной). В этом случае используется мастер импорта текста (выбираем формат файла, символы-разделители, форматы данных столбцов).
Для исключения дублирования вызова мастера импорта текста, с помощью macro recorder создаем макрос, позволяющий экономить наше время на импорт файлов:
— запускаем макрорекордер (разработчик-запуск макроса), задаем имя;
— открываем текстовый файл, с помощью мастера импорта выполняем все необходимые нам настройки;
— останавливаем запись макрорекордера.
Для удобства быстрого обращения к макросам используем:
- добавление кнопки запуска макроса на панель быстрого доступа (Файл-параметры-панель быстрого доступа-выбрать команды из: макросы – добавить — изменить);
- сочетание клавиш (Разработчик-макросы-параметры макроса).
Таким образом макросы могут существенно увеличить эффективность работы с таблицами — просто выбрав одну команду, нажав клавишу либо щелкнув на панели инструментов.