Переписывание исходного кода VBA и повторное сжатие MS-OVBA в Delphi

Переименование жёстко закодированной ссылки на лист в тысяче шаблонов отчётов с макросами исключает открытие каждого файла в редакторе VBA вручную. HotXLS, нативный компонент Excel для Delphi и C++Builder, решает эту задачу, предоставляя исходный код модуля VBA как редактируемое свойство SourceCode и повторно сжимая каждое изменение алгоритмом сжатия MS-OVBA, который Microsoft определяет специально для хранилища VBA, записывая результат обратно в хранилище VBA классического XLS, отдельный файл проекта VBA или книгу XLSM с макросами. Ни один экземпляр Excel, редактор VBA или макрорекордер нигде на этом пути не задействован

Почему поток модуля VBA — не текстовый файл

Модуль VBA внутри книги XLS или отдельного файла проекта VBA — это не исходный текст, лежащий в потоке в ожидании чтения, — это небольшой бинарный контейнер. Сначала идёт скомпилированный кэш производительности — байты, которые Office использует, чтобы пропустить перекомпиляцию модуля при загрузке, пока кэш всё ещё соответствует версии хоста, — а затем следует сам исходный текст, пропущенный через проприетарную схему сжатия, которую MS-OVBA определяет специально для хранилища VBA. Эта схема — не zip, не deflate и не что-либо, что нативно производят API сжатия Windows, и именно поэтому большинство сторонних библиотек Excel умеют читать исходный код модуля — распаковка это более лёгкая половина задачи, — но останавливаются, не дойдя до записи его обратно, поскольку повторное сжатие — это как раз то место, где едва заметно неверный бит производит файл, который Excel отказывается открывать. Публичные описания стороны чтения существуют; реализации стороны записи, которые действительно выполняют повторное сжатие, а не просто распаковывают существующий модуль для осмотра, достаточно редки, так что это остаётся одним из наименее задокументированных уголков форматов файлов Excel

Что на самом деле меняет свойство SourceCode в HotXLS?

HotXLS представляет каждый модуль VBA как объект TXLSVBAModule с обычным свойством SourceCode: WideString, и присвоение ему нового значения работает именно так просто, как выглядит: модуль помечается как изменённый в памяти, и ничто не затрагивает лежащий в основе поток OLE, пока проект не будет сохранён. Сам проект приходит из IXLSWorkbook.VBAProject в классическом движке XLS или TXLSXWorkbook.ParsedVBAProject в движке OOXML с макросами, оба возвращают TXLSVBAProject, чьи модули находятся за индексатором Item[] с отсчётом от единицы и свойством Count, так что пакетное редактирование каждого модуля в книге — это просто цикл по диапазону целых чисел

var
  Wb: TXLSWorkbook;
  Project: TXLSVBAProject;
  I: Integer;
  Updated: WideString;
begin
  Wb := TXLSWorkbook.Create;
  try
    Wb.Open('MonthlyReport.xls');
    if Wb.HasVBAProject then
    begin
      Project := Wb.VBAProject;
      for I := 1 to Project.Count do
      begin
        Updated := StringReplace(Project[I].SourceCode,
          'ReportSheet2025', 'ReportSheet2026', [rfReplaceAll]);
        if Updated <> Project[I].SourceCode then
          Project[I].SourceCode := Updated;   // marks the module dirty
      end;
      Wb.SaveAs('MonthlyReport.xls');          // recompresses on write
    end;
  finally
    Wb.Free;
  end;
end;

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

Внутри контейнера сжатия MS-OVBA

Формат сжатия MS-OVBA упаковывает байты исходного кода в то, что спецификация называет CompressedContainer: единственный байт сигнатуры, обязательно равный 0x01, за которым следует последовательность блоков CompressedChunk, каждый охватывает до 4096 байтов распакованных данных. 16-битный заголовок фрагмента несёт три поля — 3-битную сигнатуру, обязательно равную 3, 12-битное поле размера и бит CompressedChunkFlag, помечающий, является ли полезная нагрузка фрагмента буквальными байтами или последовательностью, сжатой токенами. Когда флаг установлен, полезная нагрузка — это последовательность групп по восемь токенов, предварённых байтом флагов, и каждый токен — либо один буквальный байт, либо CopyToken: обратная ссылка смещение/длина на байты, уже распакованные ранее в этом же фрагменте, причём разбиение битовой ширины между смещением и длиной меняется в зависимости от того, насколько далеко внутрь фрагмента продвинулся декомпрессор в данный момент. Именно эта часть MS-OVBA (§2.4.1, Compression and Decompression) чаще всего заставляет самописную реализацию потерять день на ошибке на единицу в этом расчёте битовой ширины

Почему HotXLS пишет сырые фрагменты вместо сопоставления токенов

Путь записи в HotXLS полностью обходит стороной половину алгоритма, отвечающую за сопоставление токенов. Когда он повторно сжимает отредактированный модуль, каждый фрагмент выходит с очищенным CompressedChunkFlag, то есть фрагмент содержит буквальные байты, а не токены обратных ссылок, — это законно по MS-OVBA, поскольку сжатому контейнеру разрешено целиком состоять из несжатых фрагментов, и это убирает именно ту часть алгоритма, которую труднее всего сделать правильно вручную: поиск действительных обратных ссылок и упаковку пары смещение/длина в битовую ширину, зависящую от текущей позиции внутри фрагмента. Компромисс проявляется в размере файла, а не в корректности — переписанный поток модуля оказывается близок по размеру к своему исходному тексту плюс двухбайтовый заголовок на каждый 4096-байтовый блок, а не меньше, каким был бы полностью сжатый токенами фрагмент. Любая программа чтения, реализующая сторону распаковки спецификации, включая сам Excel, всё равно открывает результат корректно, потому что сырой фрагмент так же валиден как CompressedChunk, как и сжатый токенами

Что HotXLS оставляет нетронутым при переписывании модуля

Повторное сжатие заменяет только часть потока модуля. Каждый поток модуля сначала хранит свой кэш производительности, а затем сжатый исходный код, и поток проекта dir точно фиксирует, где проходит это разделение для каждого модуля, в записи MODULEOFFSET; HotXLS читает это смещение, сохраняет каждый байт до него в точности таким, каким его нашёл, и перестраивает только сжатый контейнер, начиная с этого смещения

Сам исходный текст проходит цикл туда-обратно через собственную кодовую страницу проекта VBA, а не через UTF-8 — ту же устаревшую кодовую страницу, с которой Office изначально записал проект. Правка SourceCode, вводящая символы вне репертуара этой кодовой страницы, при перекодировании HotXLS строки обратно в байты молча заменяется наиболее подходящими символами замены, а не отклоняется, так что необычный региональный символ, попавший в комментарий или строковый литерал, — самое вероятное место, где можно заметить эту потерю. Внешние ссылки и привязки к библиотекам внутри того же проекта следуют по родственному, но отдельному пути сохранения, описанному в сопутствующей статье о сохранении внешних ссылок VBA, и её стоит прочитать прежде, чем проход переписывания затронет проект, ссылающийся на другие книги или библиотеки типов

Как вернуть переписанные макросы обратно в книгу?

Ничто не вызывает шаг повторного сжатия явно — он выполняется автоматически в момент сохранения книги или отдельного проекта VBA. TXLSVBAProject.ApplyChanges обходит каждый модуль, повторно сжимает те, чей SourceCode изменился с последнего сохранения, и переписывает поток только этого модуля; классический TXLSWorkbook.SaveAs, когда цель сохранения сохраняет исходный формат файла, и TXLSXWorkbook.SaveAs в OOXML для пакета XLSM с макросами — оба вызывают его внутри себя перед тем, как что-либо записывается на диск, а SaveVBAProjectToFile вызывает тот же метод, когда целью является отдельный файл проекта VBA, а не полная книга

var
  Wb: TXLSWorkbook;
begin
  Wb := TXLSWorkbook.Create;
  try
    if Wb.LoadVBAProjectFromFile('LegacyMacros.ole') = 1 then
    begin
      Wb.VBAProject[1].SourceCode :=
        StringReplace(Wb.VBAProject[1].SourceCode, 'OldServer', 'NewServer', [rfReplaceAll]);
      Wb.SaveVBAProjectToFile('LegacyMacros_Patched.ole');  // ApplyChanges runs internally
    end;
  finally
    Wb.Free;
  end;
end;

var
  Xlsx: TXLSXWorkbook;
  Project: TXLSVBAProject;
begin
  Xlsx := TXLSXWorkbook.Create;
  try
    Xlsx.Open('Dashboard.xlsm');
    Project := Xlsx.ParsedVBAProject;
    if Assigned(Project) then
    begin
      Project[1].SourceCode := StringReplace(Project[1].SourceCode,
        'ConnStringV1', 'ConnStringV2', [rfReplaceAll]);
      Xlsx.SaveAs('Dashboard.xlsm');   // SyncParsedVBAProject recompresses before the part is written
    end;
  finally
    Xlsx.Free;
  end;
end;

Все три назначения разделяют один и тот же механизм SourceCode и ApplyChanges внутри себя; единственное реальное различие между ними — какой именно вызов сохранения в итоге запускает повторное сжатие

Где это всё ещё ломается

Два режима отказа достаточно распространены, чтобы спланировать их заранее прежде, чем проход переписывания запустится на продакшн-файлах. Цифровым образом подписанный проект VBA перестаёт быть корректно подписанным в момент изменения его исходного кода, поскольку подпись покрывает содержимое проекта; у HotXLS нет способа заново подписать проект от вашего имени, и Excel отбрасывает или помечает подпись при следующем открытии файла, так что подписанному проекту с макросами нужен шаг повторной подписи ниже по конвейеру, если эта подпись — то, что ваш рабочий процесс действительно проверяет. Второй режим отказа принадлежит любому, кто соблазнится переписать этот формат сжатия с нуля вместо использования библиотеки, которая уже с ним справляется: единственный неверный бит в заголовке фрагмента — в полубайте сигнатуры, поле размера или флаге сжатия — производит файл, который Excel отказывается открывать, обычно за общим предупреждением о повреждении, не дающим ни намёка, какой байт был неверен, — именно тот класс ошибок, ради избежания которого существует описанная выше стратегия записи сырых фрагментов

Ничто из этого не требует обратной инженерии формата для использования. Разработчики на Delphi и C++Builder получают доступ на чтение и запись к SourceCode, повторное сжатие, соответствующее MS-OVBA, и все три места записи обратно, описанные здесь, как часть стандартного компонента HotXLS, наряду с остальным его API книг классического XLS и OOXML