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?
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?
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.
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
The run method can also be used to run macros in other workbooks, while I don't believe that the Call method could.
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
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.
Regards,
Patrick
I wept for myself because I had no PivotTable.
Then I met a man who had no AutoFilter.
Microsoft MVP for Excel, 2007 & 2008
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.
Thanks for jogging my memory!Originally Posted by matthewspatrick
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.
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
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.
I think I have only used Run rarely when I need to run a macro in another workbook, otherwise I use Call.
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.
So can Run.Originally Posted by Aaron Blood
The best you can do is return the function result, you cannot pass ByRef.Originally Posted by Aaron Blood
Not correctOriginally Posted by Aaron Blood
[vba]
Sub TestCalledMacro()
Call CalledMacro("hello")
End Sub
Sub CalledMacro(msg)
MsgBox msg
End Sub
[/vba]
Nor me, the loss of control is a no-no to me.Originally Posted by Aaron Blood
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.
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?
Last edited by Aaron Blood; 07-26-2006 at 06:27 AM.
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.
Regards,
Patrick
I wept for myself because I had no PivotTable.
Then I met a man who had no AutoFilter.
Microsoft MVP for Excel, 2007 & 2008
I do. What you wrote was a tad at odds with what I thought you meant, so I thought best to be explicit.Originally Posted by Aaron Blood
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 sayOriginally Posted by Aaron Blood
[vba]
Call MacroWithNoArguments())
[/vba]
Love that termOriginally Posted by matthewspatrick
Wouldn't Select/Case as Aaron suggested work just as well?Originally Posted by matthewspatrick
Well... so do I. ...and so should everyone else.Originally Posted by matthewspatrick
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...
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.Originally Posted by xld
Regards,
Patrick
I wept for myself because I had no PivotTable.
Then I met a man who had no AutoFilter.
Microsoft MVP for Excel, 2007 & 2008
Hey it has it's uses, no doubt.
Even if MatPat could have made Call work... Run may have just been a better fit.
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.
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
Originally Posted by Aaron Blood
Sometimes you just need the right tool for the job.
Regards,
Patrick
I wept for myself because I had no PivotTable.
Then I met a man who had no AutoFilter.
Microsoft MVP for Excel, 2007 & 2008
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.Originally Posted by TonyJollans
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...
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.