VBA compressed source injection?

[sancarn]

Hi Decalage

For a long while I've been looking to make a VBA IDE outside of the VBE (because the VBE is rubbish) and I personally feel VBA macros should be writable outside Microsoft Office, even if they do have to be run within Microsoft Office.

Related to our conversation here, how easy would it be to convert something like olevba into a writer for VBA macros?

**Pseudocode: **

x = VBAProject.new("some/file/location.xlsm")   //or VBAProject.open()
x.addFile("my/file.bas", VB_MODULE)
x.addFile("my/file.cls", VB_CLASS)
x.addFile("my/file.frm", VB_FORM)
x.rewrite("sheet1", "my/sheet.cls", VB_SHEET)
x.rewrite("ThisWorkbook", "my/wb.cls", VB_WORKBOOK)

In theory DidierStevens described how to do this

You can create a Module Stream without PerformanceCache but with CompressedSourceCode ... achieved by setting MODULEOFFSET to 0.

However I'm not exactly sure how to do this within your code? (In fairness my unfamiliarity with python likely doesn't help here...)

[decalage2]

It is technically doable of course, but there are two issues to solve:

1. Writing OLE files: if you want to edit an existing file and replace VBA code, olefile can overwrite a stream as long as we don't change its size (https://olefile.readthedocs.io/en/latest/Howto.html#overwriting-a-stream). However, if the new stream is larger or if you want to create a file from scratch, then olefile is lacking generic write support for now: improve olefile to write OLE files olefile#6
2. Compressing VBA code: olevba contains code to decompress VBA to obtain the source code. Here you would need the opposite, to compress VBA source code into the compressed form. It's not very difficult, but AFAIK nobody has developed it in Python yet. Algorithms: https://docs.microsoft.com/en-us/openspecs/office_file_formats/ms-ovba/4742b896-b32b-4eb0-8372-fbf01e3c65fd

Now if you're not constrained to use Python, people have aleady done it (overwriting VBA) in C#, for example in EvilClippy: https://github.com/outflanknl/EvilClippy

And there's also http://rubberduckvba.com/ to improve the VBE but using a different approach, by extending it instead of trying to replace it.

[sancarn]

@decalage2 - Thanks for the links to EvilClippy - Sounds like this will at least be the easiest route for my use case for now :)

As for RubberDuck, yes, I follow its development. I believe they are currently looking into making a language server from the rubber duck codebase. If they do manage that, this will be the another step closer for VBA IDE in VSCode 👍 .

Feel free to close this issue if you won't be improving oletools in this regard.

[Beakerboy]

I believe this suggestion has been tossed around here several times. Personally, I would love to be able to manage VBA code with git, and configure a CI tool to automatically package it up into an excel addin when tagged for release. I have a python project with the goal of creating the addin given a .bin (https://github.com/Beakerboy/Excel-Addin-Generator). If there were a tool to generate the bin file, I think we could see a lot more VBA code on GitHub.

[sancarn]

@Beakerboy and any other visitors. FYI, I did start this project over at VBAPackager. Currently re-working and modularising EvilClippy's code... Not totally sure when it'll be finished but just a heads up :)

[Beakerboy]

@sancarn Could you open up the “issues” section on that project? I’d love to test things out and be able to offer feedback.

[sancarn]

@Beakerboy This is where DMs would be nice lol... Managing expectations, currently the the module simply loads the VBAProject into memory and not much else, will be focussing on VBAModule class for v0.1 with some cobbled together code probably, which may take time :)

[JJK96]

A note, before writing VBA code is actually useful, PCode should also be written: https://github.com/bontchev/pcodedmp

[sancarn]

A note, before writing VBA code is actually useful, PCode should also be written: https://github.com/bontchev/pcodedmp

I don't think this is strictly required because if the pcode is missing the application will auto-generate it from the source code as long as the pcode isn't present in the file.

[JJK96]

That is true, but then the p-code should still be removed or overwritten.

[sancarn]

That is true, but then the p-code should still be removed or overwritten.

Yes, either that or you just always start with a template which has no P-Code 😛 Depends on your use case of course.
I honestly got lost with the VBAPackager project. I spent alot of time just staring at the files and realising it's going to be a tonne of work... Then moved onto other things. 😂

EvilClippy isn't even close to being there, although it is part of the way there.

[decalage2]

This project implements the VBA compression algorithm in python: https://github.com/coldfusion39/excel-press
(link provided by @JJK96 in decalage2/olefile#6)

[Beakerboy]

Does that mean the last piece of the process is combining the compressed project files (*.cls, *.bas) into one file (vbaProject.bin)?