×
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!

*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

White Paper - Reshoring Prototyping and Production
In this whitepaper, we'll provide insight into why and when it makes sense for U.S. manufacturers to reshore prototyping and production, and how companies can leverage the benefits of working with local design, prototype, and manufacturing partners during the pandemic and beyond. Download Now
Engineering Report - Top 10 Defect Types in Production
This 22-page report from Instrumental identifies the most common production defect types discovered in 2020, showcases trends from 2019 to 2020, and provides insights on how to prevent potential downtime in 2021. Unlike other methods, Instrumental drives correlations between a variety of data sources to help engineers find and fix root causes. Download Now
White Paper - Addressing Tooling and Casting Requirements at the Design Stage
Several of the tooling and casting requirements of a part can be addressed at the design stage. If these requirements are not addressed at the design stage, lot of time is spent in design iteration when the design reaches the die caster. These design issues lead to increase in time and cost of production leading to delay in time to market and reduced profits for the organization. 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