PDA

View Full Version : Understanding About Memory Used By VBA Macros



Cyberdude
07-27-2006, 02:26 PM
I have been spending some time moving common VBA macros from their current workbooks to the Personal.xls workbook, in the interests of memory reduction and load time reduction when a workbook is opened. But I don?t really understand the extent to which this is a good practice.

Question 1: VBA macros are compiled before they are used. That means that there are always two copies of the macro ? one editable version and one compiled version. When a workbook is opened, are both versions loaded?
Or are both (or either) kept in a file somewhere handy for retrieval if and when necessary? Said differently, do the macros contribute to the resources (like RAM) used when a workbook is opened, even if the macros aren?t used?

Question 2: I have the understanding that a compiled macro does not contain any comments that are present in the editable version. Is this true? Are compiled macros compressed in any way?

Comment: I read somewhere, and I have verified, that you can reduced the amount disk storage occupied by a workbook by exporting all its macros, deleting the existing macros from the workbook, then importing the macros that were previously exported. This is a fact. The reduction in disk storage can be substantial in some cases.

matthewspatrick
07-28-2006, 12:56 PM
Comment: I read somewhere, and I have verified, that you can reduced the amount disk storage occupied by a workbook by exporting all its macros, deleting the existing macros from the workbook, then importing the macros that were previously exported. This is a fact. The reduction in disk storage can be substantial in some cases.

This is correct. Basically, when you work on code, the workbook starts accumulating junk that never does get completely cleaned up. Over time, it can add up. The same thing can supposedly occur by repeatedly adding and dropping Names and PivotTables, too.