MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro error message 'procedure is too large'


Posted by Disa on January 21, 2002 1:50 AM

How can I get over this without splitting my macro into 2 separate macros?
Are there any other ways I can break my macro down or make it less than the 64KB limit?


Posted by Catherine Munro on January 21, 2002 12:36 PM

I had to do this not too long ago -- it's not hard!

You can break it up into two or more logical chunks. Each section that achieves a different goal within your main macro can become its own mini-macro. Then you just call each mini-macro in turn from the main.

You start with:

Sub TooLargeMainMacro()
code for task 1 (getting data, etc)
code for task 2 (performing calculations, etc)
code for task 3 (sorting and formatting, etc)
End Sub

You end up with:

Sub Task1_Macro()
code for task 1 (getting data, etc)
End Sub

Sub Task2_Macro()
code for task 2 (performing calculations, etc)
End Sub

Sub Task3_Macro()
code for task 3 (sorting and formatting, etc)
End Sub

And finally, to tie it all together,

Sub MainMacro()
Task1_Macro
Task3_Macro
Task3_Macro
End Sub

You don't have to change the rest of your code at all, aside from making sure that each mini-macro is self-contained (has all the instructions it needs to complete its task), and has its variables declared properly. If you need to use variables in more than one mini-macro, declare them at the module level (at the beginning of the module, before the first Sub) instead of within the procedure.

Does that help?
Catherine


Posted by Disa on January 22, 2002 1:44 AM

That all works well - thank you Catherine.