Макросы VBA для инженерных задач

Макросы VBA автоматизируют в Excel рутинные инженерные операции: пересчёт таблиц, выборку строк по условию, форматирование ведомостей, сборку отчётов из выгрузок. VBA (Visual Basic for Applications) — встроенный язык программирования Excel; проще всего понять, как работают макросы VBA для инженерных задач, на связке из четырёх вещей: записи макроса, редактора кода, циклов и обработки табличных данных. Ниже разобран весь путь — от записи первого макроса до цикла, который проходит тысячи строк спецификации за доли секунды.

Что такое макрос и когда он нужен

Макрос — это подпрограмма на VBA, выполняющая последовательность действий над книгой Excel. Любой макрос — это процедура между ключевыми словами Sub и End Sub. Автоматизация оправдана, когда операция повторяется: пересчитать нагрузки по сотне позиций, перекрасить строки по допуску, собрать сводку из десятка листов, привести выгрузку из 1С к единому формату.

Правило простое: если операцию приходится повторять руками более нескольких раз, её стоит превратить в макрос.

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

Запись макроса: быстрый старт без кода

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

1. Включите вкладку «Разработчик». Файл → Параметры → Настроить ленту → отметьте «Разработчик» (Developer).
2. Запустите запись. Вкладка «Разработчик» → «Записать макрос». Задайте имя (без пробелов и спецсимволов, не с цифры).
3. Выполните действия. Проделайте нужную операцию в таблице — рекордер запишет каждый шаг.
4. Остановите запись. «Разработчик» → «Остановить запись».
5. Сохраните книгу. В формате «Книга Excel с поддержкой макросов» (.xlsm) — иначе код не сохранится.

У записи есть предел: рекордер фиксирует конкретные действия с конкретными ячейками, но не умеет ветвлений и повторов по переменному числу строк. Всё, что требует «пройти по всем строкам таблицы» или «сделать, только если условие», добавляют вручную в редакторе.

Кнопка «Относительные ссылки» на вкладке «Разработчик» переключает запись в режим смещений относительно активной ячейки — полезно, когда макрос должен работать не от фиксированного адреса, а от текущего положения курсора.

Редактор VBA: где живёт код

Редактор VBA (Visual Basic Editor, VBE) — отдельное окно, где хранится и правится код. Открывается сочетанием Alt+F11 или кнопкой «Visual Basic» на вкладке «Разработчик». Записанный макрорекордером код попадает в модуль, который виден в редакторе.

Новый модуль добавляют через меню Insert → Module. Запуск макроса — клавиша F5, пошаговое выполнение для отладки — F8 (построчно, с остановками). Ручная запись даёт то, чего рекордер не умеет: циклы, условия, работу с переменным числом строк.

Sub Привет()
    ' простейший макрос: сообщение на экране
    MsgBox "Макрос запущен"
End Sub

Обращение к ячейкам: Range и Cells

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

Определение последней строки — ключевой приём для таблиц переменной длины: Cells(Rows.Count, 1).End(xlUp).Row имитирует переход Ctrl+↑ со дна столбца и возвращает номер последней непустой строки. Так макрос сам подстраивается под число позиций в спецификации.

Циклы: ядро автоматизации

Циклы заставляют макрос повторять действия — именно они превращают запись одной операции в обработку тысяч строк. В VBA три основных вида цикла.

For…Next — когда известно число повторений

Счётный цикл: перебирает строки от первой до последней. Основной инструмент для таблиц.

Sub СуммаПоСтрокам()
    Dim i As Long, last As Long
    last = Cells(Rows.Count, 1).End(xlUp).Row   ' последняя строка
    For i = 2 To last                        ' со 2-й (после шапки)
        Cells(i, 4).Value = Cells(i, 2).Value + Cells(i, 3).Value
    Next i
End Sub

Ключевое слово Step задаёт шаг счётчика: Step 2 — через строку, Step -1 — в обратную сторону. Обратный ход обязателен при удалении строк, иначе после удаления цикл пропускает соседнюю строку.

For Each…Next — перебор коллекции

Проходит по всем элементам диапазона, листам книги или элементам массива, не оперируя номерами.

Sub ПометитьПревышение()
    Dim c As Range
    For Each c In Range("D2:D200")
        If c.Value > 150 Then c.Interior.Color = vbRed
    Next c
End Sub

Do While / Do Until — пока выполняется условие

Повтор, пока условие истинно (или пока не станет истинным). Полезен, когда число шагов заранее неизвестно — например, идти по столбцу до первой пустой ячейки.

В любом цикле Exit For (или Exit Do) досрочно прерывает перебор, как только цель найдена. Без выхода макрос впустую пройдёт остаток большой таблицы.

Обработка таблиц: типовые инженерные задачи

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

Sub ВыбратьПозиции()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim last As Long, i As Long, out As Long
    last = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    out = 2                                ' строка вывода в столбце F
    For i = 2 To last
        If ws.Cells(i, 4).Value > 100000 Then
            ws.Cells(out, 6).Value = ws.Cells(i, 1).Value
            ws.Cells(out, 7).Value = ws.Cells(i, 4).Value
            out = out + 1
        End If
    Next i
End Sub

По этому же шаблону строятся другие задачи: пересчёт параметров по формуле для каждой позиции, форматирование по допуску, подсчёт итогов, сборка данных с нескольких листов через перебор For Each по коллекции Worksheets.

Скорость и надёжность макроса

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

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

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

Частые вопросы