views:

1610

answers:

1

I have a 3rd party XLL addin I'd like to wrap in my own custom vba function. How would I call the 3rd party function from my code?

Thanks

+4  A: 

Edit: There are at least two ways to do this:


Option 1: Application.Run(...)

This looks like the best way to go about it, since your arguments are automatically converted to an appropriate type before being sent to the XLL function.

Public Function myVBAFunction(A as Integer, B as String, C as Double)
    myVBAFunction = Application.Run("XLLFunction", A, B, C)
End Sub

See this page for more details.


Option 2: Application.ExecuteExcel4Macro(...)

With this method, you will have to convert any arguments into string format before passing them to the XLL function.

Public Function myVBAFunction(A as Integer, B as String, C as Double)
    dim macroCall as String
    macroCall = "XLLFunction(" & A
    macroCall = macroCall & "," & Chr(34) & B & Chr(34)
    macroCall = macroCall & "," & C
    macroCall = macroCall & ")"
    myVBAFunction = Application.ExecuteExcel4Macro(macroCall)
End Sub

See this page for more details.

e.James
Option 1 works great. Thanks for the detailed response.
TheDeeno
My pleasure. I'm glad it was useful
e.James
Now, if someone can figure out why the entire answer shows up as <code> formatted, I'd love to know!
e.James
Looks great to me. Did you try a hard browser refresh?
TheDeeno
e.James