Программирование макросов в Excel: полное пошаговое руководство

Informatec Digital » Программирование » Программирование макросов в Excel: полное пошаговое руководство

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

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

Что такое макрос в Excel?

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

Для начала работы вам не нужно уметь программировать: программа записи преобразует ваши действия в код VBA. Тем не менее, некоторые знания VBA позволят вам создавать пользовательские функции, формы и даже мини-приложения в Excel.

Зачем использовать макросы? Реальные преимущества

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

К его преимуществам относятся экономия времени, стабильное выполнение (без пропущенных шагов) и возможность создания ярлыков и кнопок для ускорения процессов . Если вы используете Excel лишь изредка, это может быть нецелесообразно; но если вы используете его ежедневно, вы заметите разницу с первого дня.

Включить вкладку «Разработчик» (Windows и Mac)

Вкладка «Разработчик» по умолчанию скрыта. В Windows щелкните правой кнопкой мыши ленту и выберите «Настроить ленту». Установите флажок «Разработчик» и нажмите «ОК». После этого вы увидите вкладку «Разработчик» рядом с вкладкой «Вид».

На Mac перейдите в Excel > Настройки > Панели инструментов и лента. В разделе «Основные вкладки» выберите «Разработчик» и сохраните. Это предоставит вам доступ к средству записи, VBA и элементам управления . Если вы новичок в Excel, ознакомьтесь с базовым руководством по Excel , чтобы понять, как работает лента и её параметры.

Как создавать макросы с помощью Recorder

Программа для записи идеально подходит для начала работы. В меню «Разработчик» > «Запись макроса» (или Alt+T+M+R в Windows) присвойте макросу описательное имя. Помните: имя должно начинаться с буквы, без пробелов и символов.

Выберите место сохранения: в большинстве случаев подойдет вариант «Эта книга». Если вы хотите, чтобы она всегда была доступна, выберите «Личная книга макросов», и Excel создаст/сохранит ее в файле Personal.xlsb , который открывается в скрытом виде при загрузке Excel.

При желании можно добавить сочетание клавиш. Во избежание конфликтов (например, потери Ctrl+Z) рекомендуется использовать сочетание клавиш Ctrl+Shift. Также можно написать короткое и понятное описание (очень полезно, если у вас много макросов).

Нажмите кнопку ОК и выполните действия, которые необходимо записать. Например: выберите ячейку A1 и примените формат «Проценты». Избегайте ненужных щелчков или изменений выделения, не являющихся частью предполагаемого процесса, поскольку программа записи записывает практически все.

По завершении записи остановите её. Чтобы запустить макрос, перейдите в меню «Разработчик» > «Макросы» (или нажмите Alt+F8), выберите нужный макрос и нажмите «Запустить». Вы увидите, как Excel повторит шаги в том же порядке.

Запускайте, редактируйте и управляйте макросами

В меню «Разработчик» > «Макросы» (Alt+F8) вы можете запускать, редактировать или удалять макросы. Если вы выберете «Редактировать», откроется редактор Visual Basic с записанным кодом, который вы можете очистить и оптимизировать (он часто содержит избыточные шаги).

Назначить макрос кнопке или фигуре очень удобно: вставьте фигуру, щелкните правой кнопкой мыши > Назначить макрос, выберите макрос, и готово. При щелчке по фигуре он запустится мгновенно.

Чтобы кнопка всегда была под рукой на панели быстрого доступа, откройте меню панели инструментов (стрелка вниз) > Дополнительные команды > в разделе «Доступные команды» выберите «Макросы», добавьте свой и при желании измените значок. Теперь у вас будет постоянно отображающаяся кнопка, даже когда вы не находитесь в режиме разработчика.

Если вам нужно повторно использовать макрос в другой рабочей книге, вы можете скопировать модуль из редактора Visual Basic. Откройте VBE (Alt+F11), перетащите модуль в другую открытую рабочую книгу или воспользуйтесь функцией экспорта/импорта файла .bas.

Программирование макросов в Excel с помощью редактора Visual Basic (VBA)

Чтобы выйти за рамки средства записи, откройте редактор с помощью Alt+F11. Вставьте модуль (Вставка > Модуль) и создайте свою первую подпрограмму. Базовый шаблон: Sub Name() … End Sub.

Например, чтобы отобразить сообщение и записать значение в ячейку A1, можно начать с чего-нибудь простого. Таким образом, вы поймете структуру и как вызвать макрос из Excel (Alt+F8) или назначить его кнопке, используя всего несколько строк кода :

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Ещё один простой пример применения стиля в процентах к текущему выделению. Этот макрос повторяет то, что вы делали с помощью диктофона , но вводится напрямую:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

Идеальное сочетание: запись для ускорения процесса, затем открытие редактора VBE для удаления ненужных шагов, добавления переменных, условий или циклов. Таким образом, вы превращаете "буквальную" запись в надежный и многократно используемый макрос.

Абсолютные и относительные ссылки

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

При использовании параметра "Использовать относительные ссылки" макрос работает относительно активной ячейки в момент начала выполнения. Например, если вы начали запись в ячейке B3 и ввели "Имя", "Фамилия" и т. д., двигаясь вправо, то при запуске из ячейки C4 будет введена та же информация относительно ячеек C4, D4, E4 и т. д.

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

Распространенные ошибки при записи процессов

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

Решение состоит в изменении подхода: используйте динамические диапазоны (например, определение последней строки), циклы или метод CurrentRegion, либо настройте макрос в VBA после его записи так, чтобы он масштабировался до любого размера.

Хорошие практики при записи

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

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

Запускайте макросы разными способами

Помимо диалогового окна «Макросы» (Alt+F8), вы можете запускать макросы с помощью фигуры, кнопки на панели быстрого доступа или путем открытия рабочей книги. Вы даже можете настроить Excel на запуск макроса из других приложений Office, что позволит автоматизировать задачи, выполняемые в разных приложениях (например, обновление таблицы и отправка электронного письма через Outlook).

Тщательно продумайте сочетания клавиш: если вы назначите Ctrl+Z макросу, вы потеряете возможность отмены действий, пока рабочая книга открыта. Поэтому рекомендуется использовать комбинации Ctrl+Shift во избежание конфликтов.

Сохранение рабочих книг с помощью макросов

Если файл содержит код VBA, сохраните его как файл .xlsm, чтобы код сохранился и мог быть запущен. Сохранение в файл .xlsx приведет к уничтожению проекта VBA . Если вам нужны глобальные макросы, используйте личную книгу макросов (Personal.xlsb).

Безопасность и включение макросов

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

Назначьте макросы кнопкам, фигурам и элементам управления

Чтобы пользователи могли автоматически запускать макросы, назначьте макрос кнопке, фигуре или элементу управления. Щелкните правой кнопкой мыши по объекту > Назначить макрос > выберите. Вы также можете разместить значок на ленте или панели быстрого доступа и связать его с макросом.

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

Полезные примеры макросов (VBA)

Эти примеры иллюстрируют распространенные задачи. Вы можете вставить их в модуль (Alt+F11 > Вставка > Модуль) и адаптировать. Не забудьте подписать рабочие книги или изменить параметры безопасности, если это требуется вашей организацией, чтобы вы могли запускать их без предупреждений.

Скопируйте выделенное содержимое в другое место на том же листе: Выделение.Копировать Место назначения

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Распечатать активный лист: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Сохраните файл как рабочую книгу с поддержкой макросов (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Примените форматирование к диапазону (настройте диапазон в соответствии с вашими потребностями): С помощью диапазона(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Найти значение в заданном диапазоне и отобразить первое совпадение: Range(…).Find

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Практический пример: форматирование в процентах

Запись: Начните запись, дайте ей имя, выберите место сохранения и, при необходимости, назначьте сочетание клавиш и описание. Выделите ячейки и примените стиль "Процент" на вкладке "Главная". Остановите запись, а затем возобновите её с помощью Alt+F8. Если вы хотите воспроизвести запись с минимальным набором кода, используйте Selection.Style = "Percent" в VBA.

Совет: Во время записи избегайте ненужного переключения ячеек. Эти перемещения туда-обратно записываются и могут замедлить работу макроса и испортить его.

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

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

Например, один макрос для подготовки данных, другой для форматирования, третий для создания распечатки, а четвертый — для экспорта в PDF или отправки по электронной почте . Вы можете вызывать их по одному или все сразу.

Отредактируйте и очистите записанный код.

Программа записи фиксирует «все»: выделения, щелчки мыши и переключения вкладок. При открытии редактора VBE она удаляет ненужные выделения (Select/Selection), заменяя их прямыми ссылками (например, Range("A1").Value = ... ), и группирует форматирование в блоки With/End With.

Кроме того, он добавляет переменные и базовую обработку ошибок. Несколько операторов if и простые проверки предотвращают ошибки, когда диапазон пуст или выборка не соответствует ожиданиям.

Часто задаваемые вопросы

Как создать пользовательскую форму в Excel? Откройте редактор Visual Basic (Alt+F11), перейдите в меню Вставка > Пользовательская форма и добавьте элементы управления (текстовые поля, кнопки и т. д.). Вы можете отобразить её с помощью макроса.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

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

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

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

Как удалить макрос? Перейдите в меню «Разработчик» > «Макросы», выберите макрос и нажмите «Удалить». Если вы хотите удалить весь код из модуля, откройте редактор VBE (Alt+F11), найдите модуль в проекте, щелкните правой кнопкой мыши и выберите «Удалить модуль» (при желании вы можете предварительно экспортировать его для сохранения).

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