×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

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!

*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

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

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. 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