Denial
Structural
- Jun 3, 2003
- 924
These fora contain occasional references to a size limit of 64K for a VBA module in Excel (the most recent being thread766-279070). Over the years I have also come across similar mentions elsewhere in CyberLand.
To try to sort this out once and for all, I have just searched Google Groups. Among the more believable posts I came across was the following one, on eggheadcafe.com, by the widely-respected Chip Pearson on 14th March 2010.
» Whether it does in fact exist, or is merely some sort of e-urban myth;
» Exactly what the limit applies to;
» Which versions of Excel it applies to;
» How one determines how close one is to the limit;
» What happens when the limit is exceeded.
To try to sort this out once and for all, I have just searched Google Groups. Among the more believable posts I came across was the following one, on eggheadcafe.com, by the widely-respected Chip Pearson on 14th March 2010.
Does anyone have any definitive knowledge about this supposed 64K limit? Useful facts would include:The 64K limit is on the size of "compiled" code, not the size to the text source code. (VBA code is never stored as text within the workbook. It is stored in an intermediate byte-code language called OpCodes -- similar in theory to Java -- and at runtime or when you choose Compile from the Debug menu, VBA converts the OpCodes, which are version/platform neutral, to ExCodes, which are version specific and feeds those ExCodes to the VBA interpreter runtime which executes machine code on behalf of VBA based on the ExCodes. What you see on the editor screen as text code is the OpCodes translated to text for display.)
Exporting the module to a text file and looking at the size of that file might give you a crude approximation of the compiled size, but I would not give it much credibility. As far as I know, the 64K limit is not publicly documented. It was revealed to an MVP by a Softie and has propagated via usenet.
» Whether it does in fact exist, or is merely some sort of e-urban myth;
» Exactly what the limit applies to;
» Which versions of Excel it applies to;
» How one determines how close one is to the limit;
» What happens when the limit is exceeded.