VBA, Memory and Speed
Microsoft's VBA (Visual Basic for Applications) is clunky and slow, but occasionally, it is the right tool for the job, particularly for cleaning up data that is stored in an Excel format. One of the problems with VBA, however, is that it tends to suck up memory. Anyone who uses VBA to play with data sets with more than a few thousand observations will run into the problem of seeing their program slow down more and more. In fact, if you run the same program multiple times in a row, you will find it runs slower each time you run it. If your data set is large enough, or your code runs through enough calculations, you will eventually get an out of memory message and the whole thing crashes.
It turns out there is a simple solution to this irritating problem. However, to fix the problem, you have to understand why it exists, which is that Excel has an undo function which also extends into VBA. In order to undo a step, Excel "remembers" the old state, so it can return to it. That takes up memory, particularly if a step involves a large number of calculations. Excel (and VBA) allows for 16 undos, so the undo process, by itself, can quickly take up astounding amounts of memory.
To deal with this, when I build VBA code, I typically create a subroutine called "dehoggalizer." All the dehoggalizer does is pick 16 unused cells in some far flung portion of an Excel sheet and apply "clearcontents" to each one, one at a time. I call the dehoggalizer sub frequently when dealing with data sets that aren't small. Very frequently.
Using the dehoggalizer seems like an absurd fix since going into Excel un-necessarily, let alone doing it 16 times consecutively and doing essentially nothing, is a relatively slow process for VBA. However, I find that the dehoggalizer really speeds up memory-intensive VBA programs tremendously.
One last little counter-intuitive tip for speeding up VBA routines dealing with large-ish data sets: occasionally saving the file in the middle of the program also may help speed the program even when you are already using the dehoggalizer. I am not completely sure why, but it seems that the dehoggalizer and the "save" process clear out different things in memory. However, saving is much slower than the dehoggalizer, so use it far less frequently.
It turns out there is a simple solution to this irritating problem. However, to fix the problem, you have to understand why it exists, which is that Excel has an undo function which also extends into VBA. In order to undo a step, Excel "remembers" the old state, so it can return to it. That takes up memory, particularly if a step involves a large number of calculations. Excel (and VBA) allows for 16 undos, so the undo process, by itself, can quickly take up astounding amounts of memory.
To deal with this, when I build VBA code, I typically create a subroutine called "dehoggalizer." All the dehoggalizer does is pick 16 unused cells in some far flung portion of an Excel sheet and apply "clearcontents" to each one, one at a time. I call the dehoggalizer sub frequently when dealing with data sets that aren't small. Very frequently.
Using the dehoggalizer seems like an absurd fix since going into Excel un-necessarily, let alone doing it 16 times consecutively and doing essentially nothing, is a relatively slow process for VBA. However, I find that the dehoggalizer really speeds up memory-intensive VBA programs tremendously.
One last little counter-intuitive tip for speeding up VBA routines dealing with large-ish data sets: occasionally saving the file in the middle of the program also may help speed the program even when you are already using the dehoggalizer. I am not completely sure why, but it seems that the dehoggalizer and the "save" process clear out different things in memory. However, saving is much slower than the dehoggalizer, so use it far less frequently.
Comments
Post a Comment
Comments subject to moderation.