vba - Procedure Too Large

Question

I received this error message -Procedure too large - in VBA. What is the reason and way out for this error?

Answer 1

You probably have one or more gigantic procedures/functions and I think VBA has a limit of 64k or something per procedure.

You fix it by splitting that procedure up into multiple procedures that can then be called by the one procedure.

So instead of having:

 Sub GiantProcedure()
      ... ' lots and lots of code
 End Sub

You'd have something like:

 Sub GiantProcedure()
      ... ' a little bit of common code
      Proc1()
      Proc2()
      Proc3()

 End Sub

 Sub Proc1()
      ... ' quite a bit of code
 End Sub

 Sub Proc2()
      ... ' quite a bit of code
 End Sub

 Sub Proc3()
      ... ' quite a bit of code
 End Sub