Difference between Calling a Sub and Application.Run
Solution 1
I use Application.Run
if I’m trying to run a sub that is private in another module. If I have a some kind of template where I want to hide the macros from the users I will declare it as private so they can’t run/see the macro from there macros dialog box.
In module1 I have
Private Sub priv()
MsgBox “Private”
End Suv
In module2 the below will give you a Sub or Function not defined
error.
Sub callPriv()
Call priv()
End Sub
But in module2 this will run and display the message box
Sub callPriv()
Application.Run “priv”
End Sub
It’s also useful to use Application.Run if you are calling a sub in your sheet or thisWorkbook modules.
Solution 2
You can pass parameters through application.run as well. I use it when I am looping through macros. in your above example instead of having to write this:
Sub button()
Call sub1()
Call sub2()
Call sub3()
Call sub4()
End Sub
you could write this:
for i = 1 to 4
application.run("sub" & i)
next i
if the subs took in a str parameter you could do this:
for i = 1 to 4
application.run("sub" & i, strVariable)
next i
Badja
Technical Operations for a multinational company. One of a few in the company able to work on Stack Overflow as part of the job. A desire to learn VBA, i Series/Personal Communications and the lust for knowledge, brings me here, to a home I can truly feel happy in. Hoping to become a backend iSeries developer
Updated on June 25, 2022Comments
-
Badja over 1 year
In my business, we have a few teams that work on very simple macros. I'm trying to make them all readable to each other and in somewhat of a similar format, so new joiners can start working on the data.
I mention simple macros, because no one will be using Subs with arguments - most are derived from Macro Recorder anyway
Half of the teams use:
Sub button() Call sub1() Call sub2() Call sub3() Call sub4() End Sub
And the other half use
Sub button() Application.Run("sub1") Application.Run("sub2") Application.Run("sub3") Application.Run("sub4") End Sub
I understand that if your sub has no arguments, then Application.Run has a use - but being as there's barely any notation on it - is there a reason people still use
Application.Run("")
?Can every use of it be beaten in speed and process by
Call
? -
Badja over 4 yearsYeah I was dumming down the names, but running them through an array would cut down on the guff in the code too
-
FunThomas over 4 yearsI will never get the idea why subroutines should have names like
sub1
... And you move the check if a Subroutine exists from compile time to runtime. -
Hasib_Ibradzic over 4 yearsI didn't know you could use the call function with strings.... I thought the whole purpose of the
application.run
was to be able to pass the sub as a string. I will have to test out how you do it. -
jamheadart over 4 yearsI posted an answer without reading this so deleted and am just backing it up.
Application.Run(x)
, where x can be avbaproject.method
path, handles calling where the routine to be run is decided later, during the code runtime. E.g. I could have three buttons that pass a different string each, to a method which hasApplication.Run(string)
... whereasCall Sub1
is just plain hard-coded -
Badja over 4 yearsGreat answer! A good use of
Application.Run()
. What if your general approach was that allSub
s are public? Based on the only users touching Macros being the creators -
Dude_Scott over 4 yearsCall would probably be the best since your not making VBA evaluate strings. You can find a decent amount of info on this through google
-
AJD over 4 years
Call
is deprecated. Should not be using that word. just usepriv()
. -
Dude_Scott over 4 years@AJD @Badja yes you can avoid using
Call
but since OP mentioned that he is looking to make his code readable, it does aid in that endeavor. Omission could cause some confusion for those inexperienced in VBA -
AJD over 4 years@Dude_Scott - use of
Call
highlights someone who is either inexperienced in VBA (just copying code without knowing what it does) or is stuck in the past and not kept up. In terms of "making code readable", that is what sensible function/subroutine names are for.Call
does absolutely nothing in those terms - and is deprecated. In other words, in any future release of the VBA engine, support for that term could just disappear.