469,159 Members | 1,454 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,159 developers. It's quick & easy.

Procedure Too Large Error

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?)

Sep 8 '11 #1
5 3814
931 Expert 512MB
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!

Sep 8 '11 #2
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?

Sep 8 '11 #3
32,167 Expert Mod 16PB
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.
Sep 8 '11 #4
12,516 Expert Mod 8TB
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.
Sep 8 '11 #5
759 512MB
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.
Sep 9 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Peter Frost | last post: by
1 post views Thread by Randy Harris | last post: by
6 posts views Thread by Paul | last post: by
reply views Thread by %NAME% | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.