The Application.Run method is a versatile mechanism to call a subroutine particularly in another workbook or add-in. Its one documented limitation, if we want to call it that, is that all arguments are passed by value (ByVal). So, how does one pass an argument by reference (ByRef)?
We start by examining the syntax from the Excel help file.
expression.Run(Macro, Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)
expression A variable that represents an Application object.
The help documentation continues
The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.
Fortunately, the latter is not true. Arguments are indeed passed by value (ByVal) but objects are not converted to values by applying the Value property. Instead they are simply passed by value.
That means we have to understand how an object is passed ‘by value.’ For a scalar (a simple number, string, or Boolean) the system makes a copy of the current value and passes this copy to the called procedure. Upon return from the called procedure the system discards the copy. So, even if the called procedure changes the value of a ByVal argument, there is no way for that change to propagate back to the caller. However, in the case of an object the system makes a copy, not of the contents of the object, but of the pointer to the object! So, any change the called procedure makes to the object will stick! We leverage this implementation detail to change the value of an argument passed using the Application.Run method.
In this section, we will create an object that can subsequently be used to pass and update a Boolean value. Start by creating a class module, named, clsBoolean with just one property in it.
Option Explicit
Dim bCancel As Boolean
Property Get Cancel() As Boolean
Cancel = bCancel
End Property
Property Let Cancel(ByVal uCancel As Boolean)
bCancel = uCancel
End Property
Now, in a standard module, add the code:
Option Explicit
Sub CalledSub(ByVal oCancel As clsBoolean)
oCancel.Cancel = Not oCancel.Cancel
End Sub
Sub Caller()
Dim oCancel As clsBoolean: Set oCancel = New clsBoolean
oCancel.Cancel = False
CalledSub oCancel
Debug.Print oCancel.Cancel
Application.Run "CalledSub", oCancel
Debug.Print oCancel.Cancel
End Sub
The Caller subroutine creates an object of type clsBoolean and calls the CalledSub twice, once directly and the second time through the Application.Run method, printing the value of the Cancel property after each call. The result in the Immediate Window is:
True
False
By passing the Boolean as part of the object, the called procedure is able to return a value to the caller. Obviously, the called procedure does not have to be in the same workbook. It could just as easily be in another workbook and the result will be the same. Of course, calling a procedure in another workbook requires some more setup. That is documented in an MSDN article I authored some time ago:
How to use a class (object) from outside of the VBA
project in which it is declared
http://support.microsoft.com/kb/555159
It is possible to simplify the above code by making Cancel the default property of the clsBoolean class. The technique for doing so is summarized below. It can also be found on the Internet through a Google search (one of the Google references should be fellow Microsoft MVP Chip Pearson’s http://www.cpearson.com/excel/DefaultMember.aspx).
Remove the clsBoolean module from the project (in the VBE select File | Remove clsBoolean… and when asked “Do you want to export clsBoolean before removing it?” click Yes. In the resulting dialog box, save the module in some location. The default filename will be clsBoolean.cls. Next, open this file in a document editor such as Notepad. Just after the Property Get line, add:
Attribute Value.VB_UserMemId = 0
The code should look like
Property Get Cancel() As Boolean
Attribute Value.VB_UserMemId = 0
Cancel = bCancel
End Property
Save the file and back in the VBE import the file into the project. VBA honors but does not show the newly added attribute. So, the clsBoolean class module will seemingly look exactly as before. But the way we use it will be dramatically simplified. Except for the declaration as clsBoolean and the Set statement, all other references could just as well have been to a Boolean variable rather than a clsBoolean object!
Option Explicit
Sub CalledSub(ByVal Cancel As clsBoolean)
Cancel = Not Cancel
End Sub
Sub Caller()
Dim Cancel As clsBoolean: Set Cancel = New clsBoolean
Cancel = False
CalledSub Cancel
Debug.Print Cancel
Application.Run "CalledSub", Cancel
Debug.Print Cancel
End Sub
The result will be the same as before.
I’ve used these techniques for some years now to return values from procedures called via Application.Run. The reason for documenting it is that it will is a key building block in a planned post “Simplified Handling of Chart Events.”