Procedure Too Large

vba error-handling ms-office
Sourav picture Sourav · Sep 20, 2010 · Viewed 72.1k times · Source

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

Answer

Hans Olsson picture Hans Olsson · Sep 20, 2010

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

Related questions

Error Handler - Exit Sub vs. End Sub

Why would I want to get out of an Error Handler (after handling) with an Exit Sub instead of just letting it go to the End Sub? I'm sure it's simple. I just don't understand. Thanks for any help. Example: …

Read More
What is causing "Automation error Unspecified error" on Worksheet_Activate?

I have a worksheet named "Dates" (object name is A_Dates) that needs to be calculated when it is activated (It may be worth noting that this is in my Personal macro workbook). I regularly have workbooks open that have …

Read More
Handle Error 9 when there is an Empty Array

I am writing a script that will loop through an Excel spreadsheet and find if there are duplicates of selected cells. If there are duplicates then the function will return an array of which rows are duplicates and create a …

Read More