Run or Call

[jungix]

I was wondering the difference between Run and Call to launch a macro within VBA.

Also, are the 2 instructions "Call main arg1, arg2" and
"Call main(arg1, arg2)" exactly the same?

[XLGibbs]

Run method can be used to call prodedures in both excel 4.0 and new versions.

The Call method requires that if you use
[VBA]
Call main (Arg1,arg2)
[/VBA]

and the Main sub procedure has arguments, you must enclose in parenthesis.

However, the same result can be achieved without "Call" and without the parenthetical enclosuer

[VBA]
Main arg1,arg2
[/VBA]

The VBA help file for Run method and Call statement should be of additional value to you.

[Ken Puls]

The run method can also be used to run macros in other workbooks, while I don't believe that the Call method could.

[matthewspatrick]

Another big plus for Run: you can use string variables, constants, and formulas to build the file/name of the sub you want to run.

[Cyberdude]

Interesting point, matthewspatrick. That fact had eluded me, and indeed, it can be very useful to be able to manufacture the name you want to call .. er, run.

[Ken Puls]

Another big plus for Run: you can use string variables, constants, and formulas to build the file/name of the sub you want to run.

Thanks for jogging my memory!

Yes, this is huge. I have a file which maintains a list of all my month end inventories, their file paths and the macros that I use to roll them over each month end. Application.Run is essential to make it work.

My rule of thumb is pretty much to use Call within a project (unless I need to build the macro name on the fly for some reason), and use the Run method going outside my project.

[Bob Phillips]

A big minus for run is that there is no connect back to the calling routine. SO if you have an error handler at the top of the stack, and it fails on a run procedure,...oops!. Never use it in production work myself.

[Jacob Hilderbrand]

I think I have only used Run rarely when I need to run a macro in another workbook, otherwise I use Call.

[Aaron Blood]

The main difference I see is Call can pass variables and should be your default standard, while Run is OK to use occasionally if you have some external macro or, more likely, a Com file you'd like to run.

Call, as it applies to Functions and Subs (whether explicity stated or implied by exclusion), allows the passing of variables byVal and byRef which I just don't think can be duplicated in the RUN command. (Admittedly I haven't tested this, but feels about right, if someone can make it work, feel free to post back. I'd still default to using Call.)

Generally, I think of Run in terms of running external com/exe files of any type. I believe Call should always be your preference and if forced or required Run can be called upon in special situations.

I usually go ahead and type out "Call", even though it's one of those commands that can be implied by exclusion (does anyone use Let anymore?). I just like to make all my Calls to other subs/functions stand out; except of course in situations where you're forced to exclude it (assigning a Function to a variable).

The comment about with & without parens is nothing really specific to these commands, that's more related to VB programming structure in general I think. Really the parens are more an indication of whether or not the invoked sub/function is going to be assigned or set to a variable within the calling code. If it stands alone, no parens, if it's assigned, parens.

Also, I'm not exactly sure of the library structure, but I have to think that a command like Run which is capable of running external com files probably has to shell out of VB or rely on some 'extra stuff' to work (hows that for a technical explanation).

The issue of Run allowing the string buildup would probably not be enough of a reason for me to use that vs. Call for referencing internal subs. You can get similar functionality with Call using it within IF/THEN or SELECT/CASE structures.

[Bob Phillips]

The main difference I see is Call can pass variables and should be your default standard, while Run is OK to use occasionally if you have some external macro or, more likely, a Com file you'd like to run.

So can Run.

Call, as it applies to Functions and Subs (whether explicity stated or implied by exclusion), allows the passing of variables byVal and byRef which I just don't think can be duplicated in the RUN command. (Admittedly I haven't tested this, but feels about right, if someone can make it work, feel free to post back. I'd still default to using Call.)

The best you can do is return the function result, you cannot pass ByRef.

The comment about with & without parens is nothing really specific to these commands, that's more related to VB programming structure in general I think. Really the parens are more an indication of whether or not the invoked sub/function is going to be assigned or set to a variable within the calling code. If it stands alone, no parens, if it's assigned, parens.

Not correct

[vba]


Sub TestCalledMacro()
Call CalledMacro("hello")
End Sub

Sub CalledMacro(msg)
MsgBox msg
End Sub
[/vba]

The issue of Run allowing the string buildup would probably not be enough of a reason for me to use that vs. Call for referencing internal subs. You can get similar functionality with Call using it within IF/THEN or SELECT/CASE structures.

Nor me, the loss of control is a no-no to me.

Also you cannot specify named arguments in run, it has to be by position, which is an annoyance, but worse, objects are converted to values.

[Aaron Blood]

I could've articulated myself a little better, but you follow what I was saying about the parens right?

In the example you provide, the Call would make it not a "standalone" situation. Granted, it's not being assigned to a variable as I had mentioned.

[vba]
'kinda like this...
Sub TestCalledMacro()
Call CalledMacro("hello")
End Sub

'vs this...
Sub TestCalledMacro()
CalledMacro ("hello")
End Sub

'or this...
Sub TestCalledMacro()
CalledMacro "hello"
End Sub

'or this...
Sub TestCalledMacro()
CalledMacro msg:="hello"
End Sub

'I guess my preference would look like this...
Sub TestCalledMacro()
Call CalledMacro(msg:="hello")
End Sub


[/vba]

But maybe that is all Call related stuff huh?

[matthewspatrick]

Yes, Run has its drawbacks, but for me it has one very convenient use.

I created and manage an add-in that actually comes in dozens of different pieces. The XLA that stays open is a traffic cop only--it builds the menu the user sees, and then based on menu selections it opens the file that actually has the code in it, and executes a Run command to launch the appropriate sub.

So, the code that ultimately needs to run is not in the "main" XLA, and thus Call (implicit or explicit) will produce compile-time errors (I always use Option Explicit).

Also, the same XLA hidden worksheet that holds the menu stuff also indicates what file holds the code I want to run, and what sub to launch--this again means Run over Call, because I have to build the procedure call using variable expressions. I find maintaining it that way easier than trying to maintain it in code.

So, for that specific purpose, Run makes sense. But for nearly all other purposes, Call is better suited.

[Bob Phillips]

I could've articulated myself a little better, but you follow what I was saying about the parens right?

I do. What you wrote was a tad at odds with what I thought you meant, so I thought best to be explicit.

In the example you provide, the Call would make it not a "standalone" situation. Granted, it's not being assigned to a variable as I had mentioned.
But maybe that is all Call related stuff huh?

That depends upon what you mean by stand-alone, but I do get what you mean, and I think it is call related. In many ways I would argue that it is better to use the Call statement to enforce a standard call syntax (unfortuantely VBA will strip the parentheses in say

[vba]

Call MacroWithNoArguments())
[/vba]

The XLA that stays open is a traffic cop only

Love that term

--this again means Run over Call, because I have to build the procedure call using variable expressions. I find maintaining it that way easier than trying to maintain it in code.

Wouldn't Select/Case as Aaron suggested work just as well?

[Aaron Blood]

(I always use Option Explicit)

Well... so do I. ...and so should everyone else.

Hope I didn't confuse anyone with the terms Implicit/Explicit while referring to the Call command as somehow being associated with using (or not using) Option Explicit.

I could almost substitute that parenthetical with "(I like jellybeans)" and it has about the same relevance in my mind. hehehe...

[matthewspatrick]

this again means Run over Call, because I have to build the procedure call using variable expressions. I find maintaining it that way easier than trying to maintain it in code

Wouldn't Select/Case as Aaron suggested work just as well?

In my particular case, no, I do not think so. One reason is esthetic--I simply prefer updating a worksheet to updating code. For another reason, if I used Call and then named the procedures that get called, I will get an error because there is a sub referenced in the project that does not exist in any open project--the file having that sub only gets opened on demand.

[Aaron Blood]

Hey it has it's uses, no doubt.

Even if MatPat could have made Call work... Run may have just been a better fit.

[TonyJollans]

There is a difference which I don't think anyone else has mentioned yet (although I could have missed it as I scanned the thread).

Using Call, your code is run synchronously - that is, the call statement triggers the called routine which runs and then control returns to the statement following the call when it has finished.

Using Application.Run, your code is run asynchronously - in other words, the statement following the Run statement executes immediately and VBA executes the 'Runned' procedure as and when it can, or feels like it, so if later code depends on it you need to code extra logic to ensure that it runs in time.

[matthewspatrick]

Hey it has it's uses, no doubt.

Even if MatPat could have made Call work... Run may have just been a better fit.

Sometimes you just need the right tool for the job.

[Bob Phillips]

There is a difference which I don't think anyone else has mentioned yet (although I could have missed it as I scanned the thread).

Using Call, your code is run synchronously - that is, the call statement triggers the called routine which runs and then control returns to the statement following the call when it has finished.

Using Application.Run, your code is run asynchronously - in other words, the statement following the Run statement executes immediately and VBA executes the 'Runned' procedure as and when it can, or feels like it, so if later code depends on it you need to code extra logic to ensure that it runs in time.

I think that was mentioned Tony, and I also made the point that any errors in the Run macro cannot ripple back up to the top of the stack, which can be really problemmatical.

[johnske]

I think it's more a matter of scope than anything. Generally you can either use Call or Run for public procedures ("Macros"), but when you make it a procedure by using the prefix Private (as in Private Sub MySub()) you can generally only Call that procedure when it's in the same code module as the calling procedure, if it's in another code module you then have to use Run...