When Running Long Macros Is It Better To?...

[indi visual]

When running long macros is it more efficient-faster to break them up into individual subs, or to combine them all with one set of references (Dims, Ranges, etc..) ?

 

[Luke M]

Anything that gets repeated/called out several times should probably be a seperate macro. It's also handy simply for debugging purposes to have breaks whenever a major change has happened (say, open workbook B, sort, copy, paste, do some stuff in workbook A. Would be nice to have the stuff in workbook A be a seperate macro in case I want to skip the first part).

 

[indi visual]

Gotcha, definitely with major event changes breaking is best.


But by repeated does that include "MyRange" as well?


I set it at the top once, and I reference it dozen times.


Is this okay?

 

[Luke M]

Yes, you'd want your module definitions called out before all the subs, like:


Dim MyRange as Range

Dim MyString as String


Sub FirstMacro()

'blahblahblah

End Sub


Sub SecondMacro()

'more stuff

End Sub

 

[indi visual]

Oh wow I thought you had to set the Dim inside each sep Sub.


Good stuff thanks Luke.