Excel-vba Application.Run alternative to run macro with named arguments

Question

How can I use Application.Run with named arguments? Based on this MS documentation:

You cannot use named arguments with this method. Arguments must be passed by position.

The solution to force argument passing by position might seem a bit inflexible for my code purpose, which is to create a macro that runs other people's macro by passing some arguments. Example as below:

Sub MyMacro()

    Dim macroName as String
    Dim foo as String
    Dim bar as String

    macroName = "'quux.xlam'!quuz"
    foo = "thud"
    bar = "baz"

    ' arguments pass by position
    ' this method works
    Application.Run macroName, foo, bar

    ' however, I intend to remove position dependency to allow flexibility
    ' hence, I'd like to pass argument by name, as below
    Application.Run macroName, foo:=foo, bar:=bar ' or below
    Application.Run macroName, bar:=bar, foo:=foo ' both raise compile error

End Sub


' Below is a macro from Add-In quux.xlam
Sub quuz(foo as String, bar as String)

    MsgBox foo & bar

End Sub

My question is similar to this but there it does not answer my question. So how can I find the alternative for Application.Run?

---

Scenarios/Assumptions

I have my own macro. At the same time, I'm using other people's macro (from add-in for example). Both of us have no communication, nor have a proper documentation for the addin. The potential scenarios are as below (2):

1. The addin code is fixed

   • I know that I have to pass foo and bar but unknown the order. I cannot open the code bcs the macro is password protected, for example.

2. My code is fixed

   • Somebody else is using my macro. They know that when they use my macro, they have to accept the two arguments foo and bar but unknown position.
   • My macro is password protected.
   • This case is easier since I can at least do my part and create a proper documentation.

My actual situation is more to number 2, so I think my solution now is just pass the arguments by position, and create a proper documentation for it. But I am just testing out the situation number 1, if maybe somebody has encountered it before or have any idea to resolve it?

Answer 1

Solution 1

You could write yourself a wrapper procedure that sorts the variables into the correct order.

Sub test()
    quuzWrapper bar:="bar", foo:="foo"
End Sub

Sub quuzWrapper(foo as String, bar as String)
    Application.Run "quuz", foo, bar 
End Sub



' Below is a macro from Add-In quux.xlam
Sub quuz(foo as String, bar as String)
    MsgBox foo & bar
End Sub

---

Solution 2

1. Make sure your VBA Add-in has a unique VBA name and is not called VBAProject. Eg call it quuxAddIn.

2. Within your project (not the add-in) set a reference (in the VB Editor menu: Extras > References) and select quuxAddIn.

3. Then you can call your sub like:

    quuxAddIn.quuz bar:="bar", foo:="foo"
   

Edit due to comments:
If you set a reference to the project intelli sense (tooltip) should work and after you typed quuxAddIn.quuz it should show the tooltip with the parameters. This way you also can submit the parameters by name. (But actually the add-in must exist to add it as reference so this is only for your scenario 1). In case of your scenario 2 only the solution you described and using Application.Run with a fixed order is available. • Nevertheless good documentation should be mandatory.

Answer 2

This is a decidedly inelegant solution, but if you want to handle incoming arguments in any order, then assuming they are different types, you could make them all variants and then use TypeName inside your method to assign them to strongly typed variables for proper use.

Another possibility would be to accept a PARAM array which would be in pairs, the variable name followed by its value (e.g. "Foo", FooValue, "Bar", BarValue).

Neither of these will win any coding contests, but might help in specific situations.