A ByRef argument with the Application.Run method

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.

Returning a Boolean value

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

Creating a default property

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.

Summary

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.”