How to know how close I am to fixing "Compile error: Procedure too large".

[JustJim]

Well howdy, strangers! Long time no see.

I have an Access application that's throwing the "Procedure too large" error. The Sub really* does need to be as complex as it is, but I have been trying to save some bits by minimising remarks and variable name lengths etc, while still trying to maintain some readability.

What I really want to know is how can I tell if I'm getting close to solving the problem? If I paste everything between Sub and End Sub into Notepad as a text file, the size of that file is down to 53.0 KB (reporting as 56.0 KB "on disc"). This seems like it should compile to under the 64 KB limit that Access imposes.

Any ideas as to how I can get Access/VBA to tell me if I'm getting close?

Thanks in advance, and I promise I'll try to return before five years is up this time!

*No, really, it does.

[twinnyfo]

JustJim,

All I can recommend is that you try to export some of the code to other subs. I understand large subs, too, as I have some that turned out much larger than I expected. You also may not have code that is readily divisible like that, but sometimes you have to make it fit. I don't think changing the variable lengths will affect anything, as Access allocates resources based on the data type, and not so much the name.

Hope this helps.

[NeoPa]

Hi Jim.

I'm afraid I'm with Twinnyfo on this one. Comments are not likely to be a factor. Nor are the sizes of variables. This is a compiler problem.

I hear you state categorically that the code in this one individual procedure must all stay together, but frankly I'm utterly at a loss even to imagine how that can possibly take you to 53KB. I have a long and clumsy procedure in my project that I haven't split up as well as I should have and that is barely over 3KB. Maybe some clues as to why so much needs to be together in the same procedure might point is in a direction that we can find some helpful tips for you.

Obviously, the standard way of reducing procedure size is to do as Twinnyfo has already suggested and to encapsulate parts of the logic in procedures that are called once or many times from within your original procedure, but I expect you've already considered that approach and done whatever you can on that score.

[JustJim]

Thanks folks,

It seems that I shall have to put some real effort into splitting up this code. It probably can be done, but it's going to be a pain in the sitter-upon.

What I was really after though, was a way to peek under the bonnet/hood of Access and see exactly how large the code in question was once compiled so that I could get an idea of how far I had left to go.

I tried the built-in "Database Documenter" on the form that contains the button that calls the Sub in question, but it either doesn't document that item or I couldn't find it in the myriad pages that even one form generates!

In the meantime, I'll get on with dividing up the code into separate bits.

[NeoPa]

In case it helps, the built-in documenter has various options that you can include - or not - in the results. Also, if you save it as an RTF file you can open it in Word and search for whatever it is you most want to see.

I'm not sure it has anything of that ilk included though, I'm afraid.