VBA - What makes a procedure "too long" ?

[Dimitris254]

What makes a procedure too long? Is it the number of local variables or the sheer size of it (ie. number of code lines)?

 

[Scott Huish]

Sheer size.

See https://msdn.microsoft.com/en-us/library/office/gg251481.aspx

 

[Dimitris254]

and is there a rule of thumb for this limit?

for example, 700 lines of code?

 

[rollis13]

As Microsoft states, the limit is 64k; so if 700x50 is the same as 350x100 then ...

 

[Dimitris254]

a ha, so it's about characters per line, right?

 

[MARK858]

If you want to get more confused by it, see the old thread in the link below (please note it is so old that the links no longer work).

But if you are anywhere near the limit then IMHO you should have been splitting the modules anyway.

http://www.mrexcel.com/forum/excel-questions/70914-procedure-too-large.html

 

[Dimitris254]

thx

i've already started splitting the sub into more subs, but it's again in the same module...

since i'm new to vba, is it possible that two module communicate? so i place one sub in one module, and the rest ones in the other?

 

[MARK858]

Sorry it should have said

then IMHO you should have been splitting the sub anyway

not modules

since i'm new to vba, is it possible that two module communicate? so i place one sub in one module, and the rest ones in the other?

but yes you can i.e. Module2.zzzz where zzzz is the name of a sub in Module2.

 

[Dimitris254]

Ok, I checked and the module is ~7k lines (one Sub and many Private Subs called by the main).

So, should I start splitting the big subs (one is 2k) or put some of them to another module?

Most variables are declared above the main sub (so every sub sees them), but is the scope ok for subs put in another module?

So confused atm :D

 

[MARK858]

You don't need to put them in a different module. I have never seen Microsoft state a limit in regards to the number of lines in a module for VBA only the 64k limit on a compiled procedure (I have seen a limit stated for Visual Studio and that was 65,534 lines).

We can't give you a fixed number of lines for when you need to split a procedure as one doesn't exist and is dependent on the number of characters to start with (and I haven't seen anyone explain how the VBA compiles, maybe someone else has).

Can I just ask is the sub with over 2000 lines recorded code?

See the link below for scope of variables.

Understanding Scope