Procedure Too Large Error

[James Bowyer]

I have a (very) complicated bit of calculation script which has just come up with the error "Procedure Too Large"

Clearly, I need to split it into smaller subroutines.

However, having never done this, I need to ask a few questions.

1. are any variants and recordsets that I have called in the initial subroutine still accessable as normal?
2. are subroutines that run within subroutines counted as part of the intial sub, and therefore would't help my intial problem anyway?
3. Do they automatically end up back where they started once the sub is finished (ie, you call a sub it executes, then the code will return to the original sub it was called from?)

Thanks

[patjones]

Hi James,

Splitting a subroutine or function into smaller parts (technically called "functional decomposition") is good programming practice as long as it's not taken to an extreme. I suppose the compiler was written with this in mind. Extremely large procedures are unwieldy: they are harder to debug, and more difficult for programmers unfamiliar with your code...to become familiar with it. For your specific questions:

1. Variables defined at the subroutine level are accessible only within that subroutine. There are two ways around this that I can think of. One is to declare the variables at the top of the module so that all routines within the module have access to them. Another way is to define the called routines such that they can accept arguments, and so you can pass variables in at the point where you call the routine.
   
   
2. No.
   
   
3. Yes. Execution picks up with the line following the subroutine or function call. One interesting point about this has to do with errors. I have had instances where if an error occurs in a called subroutine or function, VBA doesn't put up a message until it has backed out of the called code and encounters the error handling code in the calling procedure. I don't have a concrete example in mind right now, but will try and create one when I have more time.

I hope this helps a little!

Pat

[James Bowyer]

Hi Pat,
Yes that does help! I was hoping that I would only need to define the variables in the top subroutine, but never mind, not too much of a hardship! I assume that same would be for recordsets that I have called?

James

[NeoPa]

Yes James. It would.

To clarify Pat's third point a little further :

1. If code execution hits an error within a procedure that has an error handler, that handler will handle the error.
2. If code execution hits an error within a procedure that has no error handler, but any of the calling procedures at a higher level does have an error handler, that handler (from the higher level calling procedure) will handle the error.
3. If code execution hits an error within a procedure that has no error handler, and no higher level calling procedures have error handlers either, the application will handle the error and the code will stop.

[Rabbit]

How large is it exactly? I've had some pretty large procedures without running into that problem.

What is the purpose of the procedure? Perhaps we can offer suggestions on optimization.

[Mihail]

Do not forget about FUNCTIONS.
The functions design is the same of a subroutine (with arguments as usual in math). And a function return a value based on arguments you pass. It is the most elegant way to split a procedure.