Does VBA have a 64K limit on Modules? 2

[Denial]

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.

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.

Does anyone have any definitive knowledge about this supposed 64K limit? Useful facts would include:
» 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.

 

[IDS]

Denial - I doubt if anyone here has any more authoritative information than Chip Pearson.

All I can say is that I have had problems (of the random crash variety) when using large modules, and splitting them into smaller sizes appears to have helped. Due to the nature of these things I can't guarantee that this isn't "confirmation bias" at work, but I don't think so.

If I think a module may be getting too big I just split it; nothing scientific I'm afraid.

I have just checked the ALGLIB library, and the biggest module in that is 183 kB when saved as a text file. That seems to run without any problem, but it contains a large proportion of comments in the text, so the opcode size is presumably much smaller.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

http://msdn.microsoft.com/en-us/library/Aa264541

=====================================
(2B)+(2B)' ?

 

[electricpete]

http://books.google.com/books?id=Vn...&resnum=4&ved=0CDAQ6AEwAw#v=onepage&q&f=false

=====================================
(2B)+(2B)' ?

 

[electricpete]

It would be an interesting question to ask at our sister site tek-tips:

http://www.tek-tips.com/threadminder.cfm?pid=707

=====================================
(2B)+(2B)' ?

 

[electricpete]

Interesting that what the ms link says is "procedure" (function or sub), not "module".

But no clue how to check the size of either.

=====================================
(2B)+(2B)' ?

 

[DavidWilkinson]

Denial,

I recently came across the following URL which refers to Visual Basic Code limitations.

http://msdn.microsoft.com/en-us/library/aa240819(VS.60).aspx

From what I have read (As an example):

http://visualbasic.ittoolbox.com/documents/the-difference-between-vba-and-visual-basic-11956

http://www.avdf.com/nov96/ts_vba.html

http://answers.yahoo.com/question/index?qid=20070118130527AAHaqSR

it is my understanding that VBA is based on VB, so this is the most "official" definition I have seen.

 

[Denial]

My thanks to all who responded. I am still a bit confused about it all. However it LOOKS as if my main worry, that excessively large modules would lead to misbehaviour, does not apply: if I exceed the limit (whatever it actually is, and however it can be measured) my code simply will not run.

"Module too big" in writing VBA code seems to be a bit like "teething" in child rearing. It gets blamed for all sorts of problems, when in fact all learned studies conclude that there is no correlation whatsoever.

 

[IDS]

"Module too big" in writing VBA code seems to be a bit like "teething" in child rearing. It gets blamed for all sorts of problems, when in fact all learned studies conclude that there is no correlation whatsoever.

I'm not really convinced by the "learned studies" in either case, but surely it's a good idea to keep modules to a moderate size anyway.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/