Calling an XLA add-in method (function/sub) when t

问题:

I need to call a method that is provided by a third-party XLA add-in via my VBA code.

There are already answers for doing this with a loaded XLL add-in, but the intention is to call the method directly.

回答1:

Based on e.James' answer for calling an XLL add-in, I figured out the right format to use if the Add-in isn't already loaded. You can use Application.Run for calling methods inside an .xla Add-in the following way:

If we assume that the path to our .xla file is C:\Program Files\example.xla and the method we want to call is ExampleMethod expecting one integer parameter, then the call would be as follows:

' Method call without return value
Private Sub XLAExample()
    Application.Run "'C:\Program Files\example.xla'!ExampleMethod", 10
End Sub

' Method call with return value (check if you can declare correct type for return value!)
Private Function XLAExample() As Variant
    XLAExample = Application.Run("'C:\Program Files\example.xla'!ExampleMethod", 10)
End Sub

So you will need to construct a string of the form

"'<ADDIN_PATH>'!<METHOD_NAME>"

for the call and then just append all needed parameters like

Application.Run <PATH_AND_METHOD>, <PARAMETER_1>, <PARAMETER_2>, ...

Unfortunately, I found that CuberChase's answer that declares the method directly like an API call doesn't work with my specific .xla file, maybe it would have needed to be loaded to work.