Макросы VBA в Excel: когда переносить логику в Power Query, Office Scripts или .NET

Почему вопрос встал именно сейчас

Макросы VBA работают в настольном Excel под Windows и macOS так же, как двадцать лет назад, и Microsoft не объявляла о прекращении их поддержки. Однако условия вокруг изменились. Файлы из интернета с макросами по умолчанию блокируются, многие сотрудники открывают книги в браузере или на телефоне, где VBA не выполняется, а безопасники всё чаще требуют подписанных макросов или запрещают их совсем.

Отсюда практический вопрос: какие макросы стоит переносить и куда. Универсального ответа нет, но есть понятные признаки, по которым задача тяготеет к тому или иному инструменту.

Power Query: всё, что загружает и чистит данные

Огромная доля корпоративных макросов делает одно и то же: открывает выгрузки из учётной системы, удаляет лишние строки, переименовывает колонки, склеивает файлы из папки и сводит их на один лист. Это ровно та задача, для которой создан Power Query.

Шаги преобразования в Power Query записываются в виде запроса на языке M и повторяются нажатием «Обновить». Не нужен код с циклами по ячейкам, который ломается от лишней пустой строки. Запрос видит данные как таблицы, а не как диапазоны, и обрабатывает сотни тысяч строк быстрее любого цикла VBA.

• склейка файлов из папки: встроенный коннектор «Из папки» вместо цикла Dir();
• приведение типов, разделение колонок, отмена свёртывания: штатные шаги вместо разбора строк;
• объединение со справочником: Merge Queries вместо VLOOKUP в макросе;
• подключение к базе и веб-API: штатные коннекторы, обновление по кнопке или по расписанию в Power BI.

Office Scripts: автоматизация в Excel для веба

Office Scripts это сценарии на TypeScript, которые работают в Excel для веба и в последних версиях настольного Excel для Microsoft 365. Они хранятся в OneDrive, а не внутри книги, и их можно запускать из Power Automate по расписанию или по событию, например при появлении нового файла в SharePoint.

Хорошие кандидаты на перенос: форматирование отчёта, раскраска строк по условию, заполнение шаблона, рассылка книги по расписанию. API у Office Scripts беднее, чем объектная модель VBA: нет пользовательских форм, событий книги, доступа к файловой системе и другим приложениям Office. Если макрос активно работает с UserForm или с Word, прямой перенос не получится.

// Office Script: подсветить просроченные строки на листе «Счета»
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Счета");
  const table = sheet.getTables()[0];
  const due = table.getColumnByName("Срок оплаты").getRangeBetweenHeaderAndTotal();
  const values = due.getValues();
  const today = new Date();

  values.forEach((row, i) => {
    // даты в Excel хранятся числом дней от 1899-12-30
    const d = new Date(Date.UTC(1899, 11, 30) + (row[0] as number) * 86400000);
    if (d < today) {
      table.getRangeBetweenHeaderAndTotal().getRow(i)
        .getFormat().getFill().setColor("#FDE2E1");
    }
  });
}

.NET: когда макрос перерос Excel

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

Для них разумный путь это вынос логики в .NET. Варианты зависят от того, нужен ли Excel как интерфейс. Если нужен, логику оформляют как надстройку через Excel-DNA (пользовательские функции и ленты на C# или VB.NET) или как веб-надстройку Office на JavaScript. Если Excel нужен только как формат файла, отчёты генерируют на сервере библиотеками ClosedXML или EPPlus, без установленного Office вообще.

Imports ClosedXML.Excel

' Серверная генерация отчёта вместо макроса в шаблоне
Public Sub BuildReport(rows As IEnumerable(Of InvoiceRow), path As String)
    Using wb As New XLWorkbook()
        Dim ws = wb.Worksheets.Add("Счета")
        ws.Cell(1, 1).InsertTable(rows, "Invoices", True)

        ' просроченные строки подсвечиваем условным форматом
        Dim rng = ws.Range("A2:F" & (rows.Count() + 1))
        rng.AddConditionalFormat().
            WhenIsTrue("=$E2<TODAY()").
            Fill.SetBackgroundColor(XLColor.FromHtml("#FDE2E1"))

        ws.Columns().AdjustToContents()
        wb.SaveAs(path)
    End Using
End Sub

Матрица решений

Для каждого макроса из реестра полезно ответить на несколько вопросов. Ответы почти всегда однозначно указывают направление:

• макрос только загружает и преобразует данные: Power Query;
• нужен запуск в браузере, по расписанию или из Power Automate: Office Scripts;
• есть формы ввода, работа с другими программами, тысячи строк кода: .NET с Excel-DNA или отдельное приложение;
• отчёт строится без участия человека на сервере: ClosedXML или EPPlus;
• макрос маленький, стабильный и используется одним отделом в настольном Excel: оставить VBA, подписать сертификатом и сохранить исходник в git.

Как переносить без потерь

Начните с реестра: найдите все книги .xlsm и .xlam на общих дисках, выгрузите модули в текстовые файлы (это умеют утилиты вроде vbaDeveloper или простой скрипт через VBProject.VBComponents) и положите их в репозиторий. Уже это резко снижает риск: код перестаёт жить только внутри бинарного файла.

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

Как это делают сегодня

Вопросы и ответы