procedure too large

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.

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

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.

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.

Yes you can delete that stuff, and probably a lot more.

I'm sure we can give you some pointers on what else can go, so why not start a new thread and post your code there?

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

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)