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?

e.James · Accepted Answer · 2009-09-24T03:05:43.420

27

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.

edited Sep 24 '09 at 03:05

answered Dec 17 '08 at 19:36

e.James

116,942
41
177
214

My pleasure. I'm glad it was useful – e.James Dec 17 '08 at 19:49
Now, if someone can figure out why the entire answer shows up as formatted, I'd love to know! – e.James Dec 17 '08 at 19:50
Looks great to me. Did you try a hard browser refresh? – Dane O'Connor Dec 17 '08 at 20:43
Yes, I did. Maybe it is just an IE thing. When I look at it, everything from the end of the first Sub to the end of the second Sub has the same font. Only the stuff before & after either sub is in the normal format. Oh well! – e.James Dec 17 '08 at 21:55
I have a question: Does Option 1 also work if arguments A, B, C are complex objects like Range, Worksheet, etc.? – Steve06 Oct 27 '11 at 00:56
@Steve06: Yes, I believe they do. `Application.run("Function", ...)` takes all of the arguments after "Function" as `Variant' types, so you can pass in anything you like. – e.James Oct 28 '11 at 13:57
_Option 1_ also works to call methods in (even unloaded) **XLA add-ins** [if you use the correct method string](https://stackoverflow.com/a/49402129/2822719). Thanks! – Marcus Mangelsdorf Mar 21 '18 at 09:14

score 16 · Answer 2 · answered Jul 04 '13 at 11:10

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

How do I call an xll addin function from vba?

2 Answers2

Linked