VBA Procedure too large, only the first procedure works

Question

Well after writing a big big code and running it I got the message

Procedure too large.

After some researches I found that i can write the code this way :

Sub mycode()
Call proc1
Call proc2
Call proc3
End sub

Sub proc1()
'the code
End sub

Sub proc2()
'the code
End sub

Sub proc3()
'the code
End sub

And that's exactly what I've done but unfortunately only PROC1 code that is being executed.

How can I do to run the whole code : PROC1 then PROC2 then PROC3 ????

If you are calling proc 1/2/3 from mycode it will run. are you sure there isn't something else wrong in your code? — DragonSamu, Aug 03 '16 at 10:09
Do you have lots of lines like ` ActiveWindow.ScrollRow = 9838`? I'd advise looking at the accepted answer on this thread: http://stackoverflow.com/questions/11450232/getting-error-procedure-too-large-in-vba-macros-excel — Darren Bartrup-Cook, Aug 03 '16 at 10:13

SierraOscar · Accepted Answer · 2016-08-03T10:33:20.293

3

You must have the End keyword somewhere in proc1:

'// some code here
'// some more code
End

Replace this with:

'// some code here
'// some more code
Exit Sub

which will return control to the calling procedure.

The End word on it's own will stop execution without returning control to the calling procedure and shouldn't really be used unless there is good reason.

As a side note, there is no need to use the Call keyword in VBA it's just there for legacy purposes.

edited Aug 03 '16 at 10:33

answered Aug 03 '16 at 10:15

SierraOscar

17,507
6
40
68

Hey Macro Man, thank you for your quick answer, well when i replaced END by Exit Sub I got a message box "with an error" – MissMacro Aug 03 '16 at 11:16
@MacroMan His question shows he uses `End Sub`, it simply seems like he is replacing `End Sub` with `Exit Sub`...thus the `Sub` is never closed. – DragonSamu Aug 03 '16 at 11:29
1

@DragonSamu that's why I'm asking for the error message. If it's a compile error then OP misunderstood my answer. – SierraOscar Aug 03 '16 at 11:31
@IkrameBahhar Don't replace `End Sub` with `Exit Sub` - I meant somewhere in your code _before_ `End Sub` there must be the word `End` written on it's own somewhere. _That_ is what needs to be replaced – SierraOscar Aug 03 '16 at 12:31
@MacroMan I don't have any `End` in the proc1 that's why i'm struggling with this :( – MissMacro Aug 03 '16 at 12:36
@IkrameBahhar how come you've accepted it as the answer then? – SierraOscar Aug 03 '16 at 12:46
I really don't understand, that's getting me crazy :/ – MissMacro Aug 03 '16 at 12:47
@IkrameBahhar can you come into this room? http://chat.stackoverflow.com/rooms/111528/vba-lounge – SierraOscar Aug 03 '16 at 12:47
@MacroMan They told me : You must have 20 reputation on Stack Overflow to talk here. See the faq. – MissMacro Aug 03 '16 at 13:19
@IkrameBahhar I've updated your access it should let you now – SierraOscar Aug 03 '16 at 13:25
@MacroMan I'm sorry, due to the jet lag I slept.. :/ – MissMacro Aug 04 '16 at 14:19

VBA Procedure too large, only the first procedure works

1 Answers1