Procedure Too Large

Question

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

Shul

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

Brewster

This error is straight out of the 1980's... VBA has reached a new level of archaic... face palm – Feisty

Answer 2

Your compiled procedure cannot exceed 64kb. You should break it up into different sub routines.

http://msdn.microsoft.com/en-us/library/Aa264541

Best

Or otherwise refactor the method. – Scuttle

Answer 3

The idea of GiantProcedure didn't work for me, using Microsoft Powerpoint 2013. Then I added a "call" before each "proc". Like this:

Sub GiantProcedure()

  Call Proc1()
  Call Proc2()
  Call Proc3()

End Sub

Now, it works.

Blowing

Answer 4

You might get this error message if the macro has been created using the 64-bit version of Office. See the following article for further details and a workaround:

"Compile Error: Procedure too large" error message when you try to run a VBA macro in a 32-bit version of an Office 2010 program

Wintergreen

Answer 5

Not sure why the top voted post is recommended as the giant procedure code didn't work for me either. What did end up working was this:

Sub MacroName

Call ProcedureName1
Call ProcedureName2
etc...

End Sub

Sub ProcedureName1
'insert your vba code here

End Sub

The Call statement pulled each of the subs as long as I called the correct named sub.

Sevigny

Answer 6

Agreed, procedures should be split into smaller subs instead of one gigantic piece of code.

I have seen the Procedure too large errors on 32 bit installations of Excel, when they run / compile perfectly fine on the 64 bit installation the code was written on - so if it is a case of Procedure too large error [suddenly] occurring and it used to work before, check if the Excel installation you are trying to run the code on is 32 bit, and try on a 64 bit installation instead.

Leanaleanard

Answer 7

Use a 32-bit version of an Office 2010 program Or Divide the module into parts

Kippar

Answer 8

My employees get the same message on their weaker computers, but I don't on mine. So it's either the fact that the memory on your computer is less than 16GB or otherwise it's the fact that you have an intel processor that's too weak. An i7 intel processor that's after the 8th generation should definitely get the job done. Just get a better computer processor and better memory on your computer and it will work.

Hulk