Презаписване на VBA код и повторно компресиране на MS-OVBA в Delphi

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

Защо потокът на VBA модула не е текстов файл

VBA модулът в XLS работна книга или самостоятелен файл на VBA проект не е изходен текст, който чака да бъде прочетен от поток — той е малък двоичен контейнер. Първо идва кешът за компилирана производителност, тоест байтовете, които Office използва, за да пропусне повторното компилиране на модула при зареждане, когато кешът все още съответства на версията на хоста, а след него следва същинският изходен текст, обработен чрез патентована схема за компресиране, която MS-OVBA определя специално за съхранение на VBA. Тази схема не е zip, не е deflate и не е нещо, което Windows API за компресиране създава естествено, поради което повечето библиотеки на трети страни за Excel могат да прочетат изходния код на модул — декомпресирането е по-лесната половина от проблема — но спират, преди да го запишат обратно, тъй като именно при повторното компресиране една незабележимо грешна битова стойност може да създаде файл, който Excel отказва да отвори. Публични описания на четенето съществуват, но реализациите за запис, които действително изпълняват повторно компресиране, вместо само да разопаковат съществуващ модул за преглед, са достатъчно редки, за да остане това един от най-слабо документираните ъгли на файловите формати на Excel

Какво всъщност променя свойството SourceCode на HotXLS

HotXLS представя всеки VBA модул като обект TXLSVBAModule с обикновено свойство SourceCode: WideString, а задаването на нова стойност е точно толкова просто, колкото изглежда: модулът се маркира като променен в паметта и нищо не докосва основния OLE поток, докато проектът не бъде записан. Самият проект идва от IXLSWorkbook.VBAProject в класическия XLS механизъм или от TXLSXWorkbook.ParsedVBAProject в OOXML механизма за работни книги с макроси, като и двата връщат TXLSVBAProject, чиито модули стоят зад индексатор Item[] с начало от 1 и свойство 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, когато целевият формат запазва оригиналния формат на файла, и OOXML TXLSXWorkbook.SaveAs за пакет 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 Component, заедно с останалия API за класически XLS и OOXML работни книги