×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Does VBA have a 64K limit on Modules?
2

Does VBA have a 64K limit on Modules?

Does VBA have a 64K limit on Modules?

(OP)
These fora contain occasional references to a size limit of 64K for a VBA module in Excel (the most recent being thread766-279070: Microsoft Office Excel has stopped working..).  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.

Quote:


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.

 

RE: Does VBA have a 64K limit on Modules?

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/
 

RE: Does VBA have a 64K limit on Modules?

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)'  ?

RE: Does VBA have a 64K limit on Modules?

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.
 

RE: Does VBA have a 64K limit on Modules?

(OP)
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.

RE: Does VBA have a 64K limit on Modules?

Quote:

"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/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

White Paper – Data Security and Know-How Protection
Our data is constantly exposed to the danger of being intercepted or stolen as it wends its way over global data networks. Data security measures and measures for protecting intellectual property should not, however, first be implemented when data is exchanged – companies must lay the foundation for these measures within their own organization. Download Now
White Paper – Collaboration in the PLM Context
The influence exerted by the Internet of Things (IoT) means that there is a steadily growing need for collaboration in industry. Partners from new industries and areas of application need to be integrated in cross-company business processes to ensure that the lifecycle of smart, connected products can be managed from end to end. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close