Size Limits for VBA Code for Excel 2K
Chris Jakeman
in extending an Excel 2000 spreadsheet by adding more sheets and code
to one that was working well.
Don't know if Bill solved his problem, but I certainly have one module
that's grown beyond 64K to 71K. I'm splitting it into 2 and will let
you know whether that get's me out of the mire.
Chris
From: Rob Bovey (Rob_...@msm.com)
Subject: Re: Size Limits for VBA Code for Excel 2K
Date: 2001-04-05 23:58:34 PST
Hi Bill,
I don't recall ever seeing it documented, but legend has it (and
my own
experience backs this up) that if the exported text file size of any
of your
code modules exceeds 64KB, you'll eventually start having problems.
It's not
a hard limit, sometimes it works, sometimes it doesn't.
I'd suggest making sure all your code modules are below this size
and
see if that doesn't help. The overall size of your add-in is not
particularly large. I've got several add-ins in production that fall
within
the 2MB - 3MB size range and they work fine. But none of the
individual code
modules within them are larger than 64KB.
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Bill Bell" <bbe...@gte.net> wrote in message
news:3ACD465A...@gte.net...
> I have a workbook that only has VBA code and no worksheet data. The
> code plus forms have grown to a workbook that is now ~950K. I appear to
> be right on the edge of some limit in VBA. The program works, I then
> add in one more component on the form, save the form, and then reopen
> and run it and it fails. When editing, VBA gives no indication it has
> reached any type of limit (other than crashing).
>
> I exported the entire app and reloaded it into Excel and it gave me a
> little more room to add a few more components and little more code. It
> is now doing the same thing again. I do not have any large arrays in
> the VBA code. It has 2 multitab forms with 4-5 pages in each form. I
> am running on a 650Mhz Pentium with 128 meg of memory and nothing else
> running except networking. When the code is running I do not get an
> error even though there are some dynamic arrays that are created. I
> have ~50% of the resources free (as indicated by the system resource
> meter). I am guessing there are some limits on the number of components
> on the forms.
>
> I have looked on MS and have not seen anything that describes any limit
> like this. Any ideas? I have the option to break up the app into
> several smaller apps but I would like to better understand what limit I
> am hitting so I do not do this again. I have already wasted 1 day on
> this problem.
>
>
> Regards, Bill
Ronald Dodge
which also for the most part apply to VBA in Office 2K since the VB in
Office 2K is a subset of VB6. Only thing you would have to do is click on
the link of the sub titles under this title for more specific limitations.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/htm
l/vbconprojectlimitations.asp
--
Ronald R Dodge, Jr
Production Statistician
Master MOUS 2000
"Chris Jakeman" <cjak...@bigfoot.com> wrote in message
news:1dc434e7.02111...@posting.google.com...
Chris Jakeman
"Ronald Dodge" <ronald...@cfgraphics.com> wrote in message news:<uDG0r70iCHA.1676@tkmsftngp08>...
> If you go to the following web site, it list the various limitations of VB6
> which also for the most part apply to VBA in Office 2K since the VB in
> Office 2K is a subset of VB6. Only thing you would have to do is click on
> the link of the sub titles under this title for more specific limitations.
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/htm
> l/vbconprojectlimitations.asp
Got it.
"Each procedure can contain up to 64K of code. If a procedure or
module exceeds this limit, Visual Basic generates a compile-time
error. If you encounter this error, you can avoid it by breaking
extremely large procedures into several smaller procedures, or by
moving module-level declarations into another module."
This is pretty specific and helpful. Sad that Excel 2000 VBA doesn't
actually generate the "compile-time error".
Chris