VBA Excel Macro worked for 2-years now dosen't??
in [Programming]
|
Prev: Auto complete a row
Next: Userforms & worksheets
From: Chip Pearson on 14 Mar 2010 15:22 >How do you find the current size of a module? You really can't. 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 wouldn't give it much credibility. As far as I know, the 64K limit isn't publicly documented. It was revealed to an MVP by a Softie and has propagated via usenet. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sat, 13 Mar 2010 14:14:29 -0700, "Robert Crandal" <nobody(a)gmail.com> wrote: >How do you find the current size of a module? > >Robert > >"Bob Phillips" <bob.phillips(a)somewhere.com> wrote in message >news:uTDsiatwKHA.3536(a)TK2MSFTNGP06.phx.gbl... >> >> There is a limit of 64K in a module, so try splitting the procedures >> across multiple modules. >> >> -- >>
From: Canlink on 18 Mar 2010 10:55
On Mar 14, 3:22 pm, Chip Pearson <c...(a)cpearson.com> wrote: > >How do you find the current size of a module? > > You really can't. The 64K limit is on the size of "compiled" code,notthe 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 > wouldn't give it much credibility. As far as I know, the 64K limit > isn't publicly documented. It was revealed to an MVP by a Softie and > has propagated via usenet. > > Cordially, > Chip Pearson > Microsoft Most Valuable Professional, > Excel, 1998 - 2010 > Pearson Software Consulting, LLCwww.cpearson.com > > On Sat, 13 Mar 2010 14:14:29 -0700, "Robert Crandal" > > > > <nob...(a)gmail.com> wrote: > >How do you find the current size of a module? > > >Robert > > >"Bob Phillips" <bob.phill...(a)somewhere.com> wrote in message > >news:uTDsiatwKHA.3536(a)TK2MSFTNGP06.phx.gbl... > > >> There is a limit of 64K in a module, so try splitting the procedures > >> across multiple modules. > > >> -- I would like to thank all that posted their comments. Rob Bovey's CodeCleaner add-in did the work!! THANKS TO ALL amd to Rob Bovey's code. |