VBA Error msg: "Procedure Too Large"

Question

Hello All,

The sub I am working on returned the following error msg at compile time:

"When compiled, the code for a procedure can't exceed 64K. This error has the following cause and solution: Code for this procedure exceeds 64K when compiled.  Break this, and any other large procedures, into two or more smaller procedures. "

• I assume there is no work-around but I was wondering if anyone can tell me how I can find out how much bigger than 64k my code is.  Knowing this will help me determine how many pieces i need to cut it up to and how big each should be. Any ideas?? Regards Peter *********** 68Xbit processor windows xp office 2003 excel 2003

Answer 1

You could export the code module and see what size it is.  This is probably not an exact value for the compiled size but should be fairly close.

Go into the VB Editor and select the code module in the VBAProject window and then use File --> Export to export that module to a .bas file.  Then use the windows file browser to find it and see what its size is.  You can then delete the .bas file to keep things cleaned up.

If you have a single process (one Sub or Function) that fills a complete code module, then it is probably going to be almost unmanageable if you ever need to perform maintenance on it.

Look for repeated actions that you might be able to move into another code module and call from the original one via RUN "ProcedureInOtherModule" or CALL.  If there are several independent Subs or Functions in the module, you might consider moving them into one or more other code modules.

hi JLLatham,

Thankyou for your suggestions about how to find the storage size of the file and also to reduce its size by splitting it up.  I will do as you suggest regarding the storage size measurement.

Concerning the possibilities of splitting it up, I have already examined this and alas I am not hopeful.

You see, the problem comes from the fact that I have had to create a sub that programs the vbe by creating  hat has turned out to be one huge sub.  The sub created in the vbe iterates through columns of data and creates a routine for each column involving a separately numbered array.  There is one col for each week and therefore one routine for each week of the year.  The trouble is there is alot of processing for each array.  I couldnt make a variable out of the array names.  So i had to create code that creates a duplicate routine for each numbered array.  Each routine is identical except for number at the end of the array name ie array1 array2 array3...etc.

Each final array is kept in memory and used by later procedures.  My solution would be to create a sub for each array in the program I made to program the vbe.  However, this current project is my first foray into vbe programming (helped admirably by chip pearsons webpage and his help in this forum answering a question as well as by Hans V and others for which i am very thankful).  My trouble is I dont know enough about vbe programming to easily split the code up using a sub for each iteration.

I will work keep trying to work it out though.  I have got this far down the path of a programming solution using the vbe extensibliity model and I cannot back off the project now.

Thanks again for your help.

Regards

Peter

Answer 2

Can't you use a central routine that takes an array as argument, and call that from other procedures?

Answer 3

I think the only way to find out is by experiment. I'd start by splitting the procedures into two parts.

Thanks for your help Hans. I really appreciate it.

Pls see my reply to JLLatham.  (It seems I will have to work out how to split the sub up by using the vbe programming that created it in the first place.  Its a layer of complexity I am not looking forward to.)

I will be back with more questions no doubt!

Regards

peter

Answer 4

You could export the code module and see what size it is.  This is probably not an exact value for the compiled size but should be fairly close.

Go into the VB Editor and select the code module in the VBAProject window and then use File --> Export to export that module to a .bas file.  Then use the windows file browser to find it and see what its size is.  You can then delete the .bas file to keep things cleaned up.

If you have a single process (one Sub or Function) that fills a complete code module, then it is probably going to be almost unmanageable if you ever need to perform maintenance on it.

Look for repeated actions that you might be able to move into another code module and call from the original one via RUN "ProcedureInOtherModule" or CALL.  If there are several independent Subs or Functions in the module, you might consider moving them into one or more other code modules.

Answer 5

I think the only way to find out is by experiment. I'd start by splitting the procedures into two parts.