How to Export Sheet Buttons and Details with VBA

Question

I've been messing around with importing/exporting VBA modules and classes in an Excel app that I work on. Seems really useful to use with version control. I like it. I can easily implement code changes in modules, export my changes, commit and push my changes, and "reload" (delete all modules and re-import) my changes in another instance of the Excel app.

However, I can't find a way to use this with sheets. If I add a visual change to a sheet (say, I add a button or change the way the sheet looks), is there a way to export the sheet as code, just like modules, so that I can then import the sheet exactly as it looked from my changes?

I'm aware of some pseudo-solutions to this already, but are either not going to fit into what I need in terms of version control, or will be too much of a lift. For one, I can write a VBA script to copy my Excel app to a .xlsx file, which will house any of my sheet changes, and then I can import them into another instance of the app if I wanted to. The problem with that is that I won't be able to include them in my version control. I also know that I can write scripts in the event Workbook_Open to essentially make my user interface on my sheets programmatically each time the Excel app is opened. This will solve my problem of committing my code, but is too much of a lift.

Any suggestions or solutions to this problem?

EDIT: To be clear, I'm aware that I can export my sheets as modules as well. However, there doesn't seem anything that indicates what the sheet looked like on export. Consequently, when I import the "sheet", it is imported as a class module.

Answer 1

I replied to a similar question yesterday Version control system for Excel-VBA code

Source controlling sheets is tough. Imagine you serialise data in cells A1,A2,A3 so you might have

A1:"Foo"
A2:"Bar"
A3:"Baz"
...
A5577: "Kevin"

i.e. plenty of rows. then you insert a row at line at the top, so 3 for example, then all of the rows below would then be out of sync and would throw 5000! mismatchs in a version comparison; this was quite irritating (when I worked on a system that did this).

Perhaps you might like to have a good think about this problem and post a possible solution. Having a quick think about it myself, I wonder if some logic analogous to a linked list would help. So, one serialises a cell and then also serializes a link to the next cell and one traverse a whole list of cells. Then a single row insert would only require one change, a link insertion.

Answer 2

A "sheet" is a Document-type VBComponent, which is a class module, subclassing a specific object type in the host application's object model (here a Worksheet class). So the "code" part is exported as a class module; the "document" part is, well, your host document - that content is much more than just cell values and formulas (fonts, cell formats, conditional formats, cell comments, shapes, etc.), and in Office 2007+ those are serialized in an XML format, wrapped-up in a .zip (which is then renamed with a .xlsx extension): for proper source control of the document you need the diffs on the actual XML.

Moreover, you can't import a document-type VBComponent back into a VBProject, because it's the host application that owns document-type components. For example to add a worksheet to ThisWorkbook, you need to use the Excel object model and call ThisWorkbook.Worksheets.Add; ditto for removing them.

That's why there is no source control solution for VBA that deals with every single use case: for it to be full-featured, it would have to be able to modify the compressed XML of the document's very structure, while that document is opened, by loading the XML from disk.

I have this OSS project, Rubberduck, which features integrated Git source control:

It's host-agnostic, so there's nothing host-specific about what it does - but even if some claim "it's simple", the reality is that, well, it isn't: one person will be happy with being able to restore the code-behind; another will want the cell values and formulas; another will need to be able to restore shapes and their formats and assigned macros, and another person will complain because they lose their conditional formats when restoring their project from source control: at the end of the day the only way to have a document-type VBComponent under source control, is to extract its XML and keep that under source control... but then, you can't quite restore the document.

The only reasonable solution, is to decouple the VBA code implmentation from the document as much as possible - write VBA code that works regardless of what worksheets are in the workbook the code is hosted in; parameterize everything, and have a module that generates the worksheet layouts and formats and whatnot - in other words, don't "design" the worksheets (in Excel), "code" them (in the VBE).

As for the data... well, tough luck: source control is meant to be for code, not for data.