Difference between Calling a Sub and Application.Run

13,781

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
Share:
13,781
Badja
Author by

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, 2022

Comments

  • Badja
    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
    Badja over 4 years
    Yeah I was dumming down the names, but running them through an array would cut down on the guff in the code too
  • FunThomas
    FunThomas over 4 years
    I 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
    Hasib_Ibradzic over 4 years
    I 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
    jamheadart over 4 years
    I posted an answer without reading this so deleted and am just backing it up. Application.Run(x), where x can be a vbaproject.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 has Application.Run(string)... whereas Call Sub1 is just plain hard-coded
  • Badja
    Badja over 4 years
    Great answer! A good use of Application.Run(). What if your general approach was that all Subs are public? Based on the only users touching Macros being the creators
  • Dude_Scott
    Dude_Scott over 4 years
    Call 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
    AJD over 4 years
    Call is deprecated. Should not be using that word. just use priv().
  • Dude_Scott
    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
    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.