VBA šaltinio perrašymas ir MS-OVBA suspaudimas naudojant Delphi

Sunku įsivaizduoti tūkstančio makrokomandas turinčių ataskaitų šablonų redagavimą atidarant kiekvieną failą VBA rengyklėje rankiniu būdu. HotXLS, savasis Delphi ir C++Builder Excel komponentas, išsprendžia šį atvejį pateikdamas VBA modulio šaltinį kaip redaguojamą SourceCode savybę ir suspausdamas kiekvieną pakeitimą MS-OVBA suspaudimo algoritmu, kurį Microsoft apibrėžia VBA saugyklai, o rezultatą įrašydamas atgal į klasikinę XLS VBA saugyklą, atskirą VBA projekto failą arba makrokomandas palaikančią XLSM darbaknygę. Šiame kelyje niekur nenaudojamas Excel egzempliorius, VBA rengyklė ar makrokomandų įrašytuvas

Kodėl VBA modulio srautas nėra tekstinis failas

VBA modulis XLS darbaknygėje arba atskirame VBA projekto faile nėra šaltinio tekstas, laukiantis sraute, kol bus perskaitytas — tai nedidelis dvejetainis konteineris. Pirmiausia pateikiama sukompiliuota našumo podėlio informacija, kurią Office naudoja, kad įkeliant modulį nereikėtų jo kompiliuoti iš naujo, jei podėlis vis dar atitinka pagrindinio kompiuterio versiją, o po jos eina tikrasis šaltinio tekstas, apdorotas patentuota suspaudimo schema, kurią MS-OVBA apibrėžia būtent VBA saugyklai. Ši schema nėra zip, nėra deflate ir nėra tai, ką Windows suspaudimo API savaime sugeneruoja, todėl dauguma trečiųjų šalių Excel bibliotekų gali perskaityti modulio šaltinį — išskleidimas yra lengvesnė problemos pusė — tačiau sustoja ties jo įrašymu atgal, nes būtent suspaudimo metu subtiliai neteisingas bitas sukuria failą, kurio Excel atsisako atidaryti. Viešai aprašyta skaitymo pusė, tačiau įrašymo realizacijų, kurios iš tikrųjų vykdo pakartotinį suspaudimą, o ne tik išpakuoja esamą modulį patikrai, yra pakankamai mažai, todėl ši sritis tebėra viena menkiausiai dokumentuotų Excel failų formatų vietų

Ką iš tikrųjų pakeičia HotXLS SourceCode savybė

HotXLS kiekvieną VBA modulį pateikia kaip TXLSVBAModule objektą su paprasta SourceCode: WideString savybe, todėl naujos reikšmės priskyrimas yra toks paprastas, kaip atrodo: modulis atmintyje pažymimas kaip pakeistas, o pagrindinis OLE srautas neliečiamas, kol projektas neišsaugomas. Pats projektas gaunamas iš IXLSWorkbook.VBAProject klasikiniame XLS variklyje arba iš TXLSXWorkbook.ParsedVBAProject OOXML makrokomandas palaikančiame variklyje, abu grąžina TXLSVBAProject, kurio moduliai pasiekiami per 1 pagrindu skaičiuojamą Item[] indeksatorių ir Count savybę, todėl paketinis kiekvieno darbaknygės modulio redagavimas tėra ciklas per sveikųjų skaičių intervalą

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;

Šis ciklas taip pat yra audito eigos forma. Prieš paliečiant tūkstantį šablonų, dauguma komandų pirmiausia nori sužinoti, kiek jų iš tikrųjų turi makrokomandas ir į ką tos makrokomandos nurodo — būtent tokiam scenarijui skirtas darbaknygės audito ir konvertavimo darbastalis — tas pats Project.Count, kuris čia valdo perrašymo ciklą, ten tampa kiekvieno failo makrokomandų skaičiumi

MS-OVBA suspaudimo konteinerio vidus

MS-OVBA suspaudimo formatas supakuoja šaltinio baitus į tai, ką specifikacija vadina CompressedContainer: vieną parašo baitą, kuris turi būti lygus 0x01, po jo einančią CompressedChunk blokų seką, kur kiekvienas blokas apima iki 4096 išskleistų duomenų baitų. 16 bitų bloko antraštėje yra trys laukai — 3 bitų parašas, kuris turi būti lygus 3, 12 bitų dydžio laukas ir CompressedChunkFlag bitas, nurodantis, ar bloko duomenys yra tiesioginiai baitai, ar žetonais suspausta seka. Kai vėliavėlė nustatyta, duomenys yra aštuonių žetonų grupių seka su vėliavėlės baitu prieš kiekvieną grupę, o kiekvienas žetonas yra arba vienas tiesioginis baitas, arba CopyToken: poslinkio ir ilgio atgalinė nuoroda į tame pačiame bloke anksčiau išskleistus baitus, kai bitų plotis tarp poslinkio ir ilgio keičiasi priklausomai nuo to, kiek toli bloke šiuo metu yra išskleidiklis. Ši MS-OVBA dalis (§2.4.1, Compression and Decompression) dažniausiai priverčia rankinės realizacijos autorių sugaišti visą dieną dėl vieneto neatitikimo skaičiuojant šį bitų plotį

Kodėl HotXLS rašo neapdorotus blokus, o ne atkartoja žetonus

HotXLS įrašymo kelias visiškai apeina šio algoritmo žetonų paieškos dalį. Kai jis iš naujo suspaudžia pakeistą modulį, kiekvienas blokas išvedamas išvalius CompressedChunkFlag, o tai reiškia, kad bloke laikomi tiesioginiai baitai, o ne atgalinių nuorodų žetonai — tai leidžiama pagal MS-OVBA, nes suspaustą konteinerį gali sudaryti vien tik nesuspausti blokai, ir taip pašalinama būtent ta algoritmo dalis, kurią sunkiausia teisingai įgyvendinti rankiniu būdu: rasti tinkamas atgalines nuorodas ir supakuoti poslinkio bei ilgio porą į bitų plotį, priklausantį nuo dabartinės padėties bloke. Pasekmė matoma failo dydyje, o ne teisingume — perrašyto modulio srautas yra beveik tokio dydžio kaip jo šaltinio tekstas ir dviejų baitų antraštė kiekvienam 4096 baitų blokui, o ne mažesnis, kaip būtų naudojant visiškai žetonais suspaustą bloką. Kiekvienas skaitytuvas, įgyvendinantis specifikacijos išskleidimo pusę, įskaitant Excel, vis tiek teisingai atidaro rezultatą, nes neapdorotas blokas yra toks pat tinkamas CompressedChunk kaip ir žetonais suspaustas blokas

Ko HotXLS nepakeičia perrašydamas modulį

Pakartotinis suspaudimas pakeičia tik dalį modulio srauto. Kiekviename modulio sraute pirmiausia saugoma našumo podėlio informacija, o po jos — suspaustas šaltinis, ir projekto dir srautas MODULEOFFSET įraše tiksliai nurodo, kur kiekvienam moduliui yra ši riba; HotXLS perskaito tą poslinkį, išsaugo visus iki jo esančius baitus visiškai taip, kaip juos rado, ir nuo poslinkio atkuria tik suspaustą konteinerį

Pats šaltinio tekstas apvaliai konvertuojamas per VBA projekto kodų puslapį, o ne per UTF-8 — tą pačią senąją kodų lentelę, su kuria Office iš pradžių įrašė projektą. Jei SourceCode pakeitime atsiranda simbolių, kurių ši kodų lentelė nepalaiko, HotXLS iš naujo koduodamas eilutę į baitus juos tyliai pakeičia artimiausiais pakaitiniais simboliais, o ne atmeta, todėl neįprastas regioninis simbolis komentare ar eilutės literale yra vieta, kurioje praradimą pastebėsite greičiausiai. Išorinės nuorodos ir bibliotekų susiejimai tame pačiame projekte išsaugojami susijusiu, bet atskiru keliu, aprašytu gretimame straipsnyje apie VBA išorinių nuorodų išsaugojimą, kurį verta perskaityti prieš perrašymo eigai paliečiant projektą, susietą su kitomis darbaknygėmis ar tipų bibliotekomis

Kaip perrašytas makrokomandas grąžinti į darbaknygę

Pakartotinio suspaudimo žingsnio nereikia iškviesti aiškiai — jis paleidžiamas automatiškai tą akimirką, kai išsaugoma darbaknygė arba atskiras VBA projektas. TXLSVBAProject.ApplyChanges pereina per kiekvieną modulį, iš naujo suspaudžia tuos, kurių SourceCode pasikeitė nuo paskutinio išsaugojimo, ir perrašo tik to modulio srautą; klasikinio TXLSWorkbook.SaveAs atveju, kai išsaugojimo paskirties vieta išlaiko pradinį failo formatą, ir OOXML TXLSXWorkbook.SaveAs atveju, kai tai makrokomandas palaikantis XLSM paketas, abu metodai jį iškviečia viduje prieš įrašant ką nors į diską, o SaveVBAProjectToFile iškviečia tą patį metodą, kai paskirties vieta yra atskiras VBA projekto failas, o ne visa darbaknygė

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;

Visos trys paskirties vietos viduje naudoja tą pačią SourceCode ir ApplyChanges logiką; vienintelis tikras skirtumas yra tas, kuris išsaugojimo iškvietimas galiausiai paleidžia pakartotinį suspaudimą

Kur tai vis dar gali sutrikti

Prieš vykdant perrašymo eigą gamybiniuose failuose verta numatyti du pakankamai dažnus sutrikimo scenarijus. Skaitmeniniu parašu pasirašytas VBA projektas tampa negaliojančiai pasirašytas iškart, kai pakeičiamas jo šaltinis, nes parašas apima projekto turinį; HotXLS negali už jus iš naujo pasirašyti projekto, o Excel kitą kartą atidarydamas failą parašą pašalina arba pažymi, todėl pasirašytam makrokomandų projektui reikės vėlesnio pasirašymo žingsnio, jei jūsų darbo eiga iš tikrųjų tikrina tą parašą. Antrasis sutrikimo scenarijus aktualus visiems, kurie norėtų iš naujo įgyvendinti šį suspaudimo formatą nuo nulio, užuot naudoję biblioteką, kuri jį jau tvarko: vienas neteisingas bitas bloko antraštėje, parašo nibble, dydžio lauke arba suspaudimo vėliavėlėje sukuria failą, kurio Excel atsisako atidaryti, dažniausiai parodydamas bendrą sugadinimo įspėjimą, nepasakantį, kuris baitas buvo neteisingas — būtent tokios klasės klaidos išvengti skirtas anksčiau aprašytas neapdorotų blokų įrašymo būdas

Norint tai naudoti, formato atvirkštinės inžinerijos nereikia. Delphi ir C++Builder kūrėjai gauna SourceCode skaitymo ir rašymo prieigą, su MS-OVBA suderinamą pakartotinį suspaudimą ir visas tris čia aprašytas įrašymo atgal paskirties vietas kaip standartinio HotXLS komponento dalį kartu su likusia klasikinio XLS ir OOXML darbaknygės API