-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
procedure too large
- Thread starter Lino
- Start date
Hi,
The procedure, when compiled cannot exceed 64kilobytes. Follow the advice of this MS Support article and try to break your procedure down into smaller parts.
The procedure, when compiled cannot exceed 64kilobytes. Follow the advice of this MS Support article and try to break your procedure down into smaller parts.
Upvote
0
Lino,
There’s some interesting reading here regarding the 64kb limit:
http://makeashorterlink.com/?X3E512446
In particular, this comment from Rob Bovey (Microsoft MVP) -
“64KB is not some kind of absolute limit. In fact I don't even know where that particular size originated. Lacking any guidance from MS, and faced with an obvious problem, it may simply have been empirically determined by the Excel development community that size-related problems stopped once modules were smaller than this.”
I found that a module with +2,300 lines of code exceeded the 64kb limit. This is contrary to various comments on the Net that problems occur with more than 4,000 lines of code. It seems that the number of lines and the amount of code on each line are contributing factors.
To determine the size of a module, right click the module, and select Export File (this does not delete the code) and look at the bas file with Windows Explorer.
Regards,
Mike
There’s some interesting reading here regarding the 64kb limit:
http://makeashorterlink.com/?X3E512446
In particular, this comment from Rob Bovey (Microsoft MVP) -
“64KB is not some kind of absolute limit. In fact I don't even know where that particular size originated. Lacking any guidance from MS, and faced with an obvious problem, it may simply have been empirically determined by the Excel development community that size-related problems stopped once modules were smaller than this.”
I found that a module with +2,300 lines of code exceeded the 64kb limit. This is contrary to various comments on the Net that problems occur with more than 4,000 lines of code. It seems that the number of lines and the amount of code on each line are contributing factors.
To determine the size of a module, right click the module, and select Export File (this does not delete the code) and look at the bas file with Windows Explorer.
Regards,
Mike
Upvote
0
I'm going to step out on a limb here and question what Mr Bovey has said 
What they're referring to in that discussion is the size of the text file created when one exports the module from within the VBA editor. The limitation quoted in the (admittedly not very useful) article I referred to says 'When compiled, the code for a procedure can't exceed 64K'. The size of the compiled p-code would certainly be different to that of the simple text file that we see, although I'd expect there to be a correlation between the two. Determining the size of the compiled code is probably possible, but something I wouldn't like to try! Some interesting reading here.
What they're referring to in that discussion is the size of the text file created when one exports the module from within the VBA editor. The limitation quoted in the (admittedly not very useful) article I referred to says 'When compiled, the code for a procedure can't exceed 64K'. The size of the compiled p-code would certainly be different to that of the simple text file that we see, although I'd expect there to be a correlation between the two. Determining the size of the compiled code is probably possible, but something I wouldn't like to try! Some interesting reading here.
Upvote
0
I had the same error. But a large part of my code is just information about scrolling. It looks like this:
ActiveWindow.ScrollRow = 594
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 557
ActiveWindow.ScrollRow = 520
ActiveWindow.ScrollRow = 483
ActiveWindow.ScrollRow = 428
ActiveWindow.ScrollRow = 391
ActiveWindow.ScrollRow = 373
Will it change how the macro functions if I just delete that stuff? I don't care about what I see going on as long as the macro actually works. And it looks like I need to halve the size of my marco to get it to function. The scrolling stuff makes up much more then half the VB code.
ActiveWindow.ScrollRow = 594
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 557
ActiveWindow.ScrollRow = 520
ActiveWindow.ScrollRow = 483
ActiveWindow.ScrollRow = 428
ActiveWindow.ScrollRow = 391
ActiveWindow.ScrollRow = 373
Will it change how the macro functions if I just delete that stuff? I don't care about what I see going on as long as the macro actually works. And it looks like I need to halve the size of my marco to get it to function. The scrolling stuff makes up much more then half the VB code.
Upvote
0
DougRobertson
Active Member
- Joined
- Sep 22, 2009
- Messages
- 334
- Office Version
- 365
- Platform
- Windows
I hate to be late to the party, but for anyone else looking for accurate information from Mr. Excel, I have discovered the following:
1) It is not the Number Of Lines in your Procedure that puts you over the limit.
2) It is not the Number Of Lines in the Module or the Size Of The Module that puts you over the limit.
3) It is not the Number Of Characters in either the Procedure or the Module that puts you over the limit.
4) It IS the Number Of Active Characters between the "Sub" and the "End Sub" Lines at the Top and Bottom of your Procedure that is measured. Any Green characters (which follow a Single Quotation Mark) do NOT count against you.
Trial and Error often produces results!!
1) It is not the Number Of Lines in your Procedure that puts you over the limit.
2) It is not the Number Of Lines in the Module or the Size Of The Module that puts you over the limit.
3) It is not the Number Of Characters in either the Procedure or the Module that puts you over the limit.
4) It IS the Number Of Active Characters between the "Sub" and the "End Sub" Lines at the Top and Bottom of your Procedure that is measured. Any Green characters (which follow a Single Quotation Mark) do NOT count against you.
Trial and Error often produces results!!
Upvote
0
I also hate to be late to the party, but...
I had the same error, but (4) is also not the correct answer. I had a large Sub at "Sheet level", but when I moved that Sub to a Module the error was gone.
So there must be something more to it, as I didn't change the number of "active characters" in the sub (I just moved the sub from sheet-level to a module)
I had the same error, but (4) is also not the correct answer. I had a large Sub at "Sheet level", but when I moved that Sub to a Module the error was gone.
So there must be something more to it, as I didn't change the number of "active characters" in the sub (I just moved the sub from sheet-level to a module)
Upvote
0
