How do I call an xll addin function from vba?

Question

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?

Answer 1

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.

Answer 2

I know this is a way late answer, but I discovered this alternate method and think it's worth sharing. You can declare the 3rd party functions in the same manner as a Win32 call. This has the added benefit of showing up in the Intellisense completion when you are coding.

Private Declare Function XLLFunction Lib "C:\PathTo3rdPartyDLL\3rdParty.xll" (ByVal A as Integer, ByVal B as String, C as Double) As Double

Sub Function myVBAFunction(A as Integer, B as String, C as Double) as Double
    myVBAFunction = XLLFunction(A, B, C)
End Sub