Have a look at the previous post on this topic here. The previous post included the following:
"OnlyHelloWorld.xlsm is 11KB and CleanedCompiledExample.xlsm is 89KB. What’s going on there? They are visually and structurally identical!!!
Why? Simply put, the Excel VBA Compiler is a very nifty piece of kit, but it is not optimised for removal of variables. So when a variable is created a token is used in the compiled code to represent that variable. But when the variable is deleted from the code, the token remains.
What’s the impact of this?
Well, for most spreadsheets, most of the time, absolutely nothing. But when you are writing VBA heavy, advanced spreadsheets used in financial modelling, real time data analysis, portfolio modelling and so on, this causes problems. Excessive memory usage in spreadsheets slows down processing. Not usually a massive slowdown but one that can be observed in advanced VBA systems
How do you resolve this problem of spreadsheet bloat?
There are a number of techniques. One technique I created and recommend is the use of a spreadsheet industrialisation technique. Simply put, the use of outside code to clean up and remove all of these lost variables and reclaim the memory. This spreadsheet industrialisation is not needed for simple Excel spreadsheets but does come into play in the VBA heavy environments of most Investment Bank trading rooms.
For a bloated single spreadsheet, the way to resolve this issue manually is to create an identical spreadsheet with all of the same user interface elements. Then re-create the VBA environment by creating standard and class modules with the same names as the bloated spreadsheets modules. Now cut and paste the code from the bloated spreadsheet modules over to the new spreadsheet modules. Compile and save. You now have a bloated spreadsheet and a visually and structurally identical spreadsheet. Look at the file sizes and you will see the difference in file sizes."
A bit of experimentation and I've found a new way to remove the spreadsheet bloat:
And this new way to do this is joyfully simple. Take your spreadsheet to an Apple Mac, open it in Excel 2011 on the Mac, save it as “Apple_CleanedCompiledExample.xlsm”and close. Now reopen in Windows
You will see that “Apple_CleanedCompiledExample.xlsm” is 11KB. With a finer eye we see that the file is 11,133 bytes, whereas “OnlyHelloWorld.xlsm” is 11,007 bytes.
So, the switch to and from Apple Mac has removed the bloat.
This leaves a few further questions:
- Will this bloat removal work by round-tripping from a 64bit instance of Excel on Windows to a 32bit instance and back?
- Will bloat removal work by switching Excel file formats while retaining the VBA code?
I will experiment on a 32bit instance of Excel, I suspect that the result will be positive, since the VBA interpreter is very different in 32bit versus 64bit versions.
Comments
Post a Comment