Calling an xll UDF from VBA

Question

I would like to call one of my User Defined Function from VBA.

My User Defined Function is declared in C++:

XLOPER12*WINAPI HelloWorld()noexcept{
    THROWS();
    static XLOPER12 res=[](){
        static std::array<wchar_t,13>str={
            11,'H','e','l','l','o',' ','W','o','r','l','d','\0'};
        XLOPER12 tmp;
        tmp.xltype=xltypeStr;
        tmp.val.str=str.data();
        return tmp;}();
    return &res;}

This is a simplified version of a real function from the field which can either return a String or a double or even arrays. Of course here I am only returning a String but this limit the return type of my UDF to LPXLOPER12.

I can successfully register my function with xlfRegister specifying a pxTypeText of "U$". I can then call my UDF from Excel:

=HelloWorld()

And it works!

If I try to call my function from VBA as suggested here:

Sub macro_test()
    Dim hw As Variant
    hw = Application.Run("D:\Path\MyAddIn.xll!HelloWorld")
End Sub

I get an error message from Application.run:

Run-time error '1004': Application-defined or object-defined error

If I try to call my function from VBA as suggested here:

Private Declare PtrSafe Function HelloWorld Lib "C:\Path\MyAddIn.xll" () As Variant

Sub macro_test()
    Dim hw As Variant
    hw = HelloWorld()
End Sub

I get an empty result instead of "Hello World".

What am I doing wrong ?

Miscellaneous pieces of information:

Using Excel 2013
Using VS 2017 15.5
Using the first method (Application.Run), VBA does not call my function (I cannot step into my function with the debugger).
Using the second method, VBA calls my function (I can step into my function with the debugger).
Using the second method, when I add xlbitDLLFree to my xltype, function xlAutoFree12 is not called, which makes me think that somehow the return value is not understood properly by VBA.

When using VBA you will have to stick with procedures written in that language. However, I believe you can compile your C++ procedure and create an executable which you can call from VBA's `Shell` function. Look at this thread:- https://stackoverflow.com/questions/20917355/how-do-you-run-a-exe-with-parameters-using-vbas-shell — Variatus, Dec 09 '17 at 13:33
@Variatus I do not want to call an executable, I want to call a function from a library. This is definitely [part](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/declare-statement) of VBA. — Arnaud, Dec 09 '17 at 13:37
Did you try to "Dim hw As String" (and with the function prototype ... As String) ? — Malick, Dec 10 '17 at 13:01
The $ seems incorrect, your function is *not* thread-safe. Otherwise, the Declare approach will never work as Variant structure is not the same as XLOPER12 structure. Application.Run should work. Have you checked the path is correct and doesn't contain special chars: https://stackoverflow.com/questions/2136768/using-application-run-in-excel-vba-when-workbook-name-contains-spaces — Simon Mourier, Dec 12 '17 at 07:23
@Variatus: A C++ (or other compilable language) DLL can be used in process by Excel in 3 ways (i) Ordinary DLL with exported functions `Declare`d in VBA (ii) A COM DLL server used in VBA and also worksheet (iii) An XLL which the OP is using. Thus, your statement "*When using VBA you will have to stick with procedures written in that language*" is patently false and misleading. — S Meaden, Dec 12 '17 at 14:51
@SimonMourier I do not understand the thread safety issue. Is excel modifying my return value ? The path is correct btw. — Arnaud, Dec 12 '17 at 17:16
@S Meaden Calling a procedure written in another language `in` VBA or `from` VBA are two very different concepts. I regret you didn';t get the point I tried to make in my above post which was to consider alternative methods of calling. Since I didn't expect `Shell` to be the answer, I admit however, that my motivation was to be notified of additions to the thread. If the solutions you recommend qualify as answers I suggest you post them as such and I shall award a point, as I did for @Charles William's answer below. — Variatus, Dec 13 '17 at 01:41
$ tells Excel your procedure is thread-safe. In general, using static stuff in a procedure is not thread-safe (in the sample case it's not an issue because you just send a fixed string). This is not related to your specific issue, it's just an advice in case you face strange crash cases later on... Also I was not asking is the path was correct, but does it contain special characters (like space)? — Simon Mourier, Dec 13 '17 at 05:56
@SimonMourier So do we agree that I can in fact use a $ since after inspection my function is thread safe ? Please post an answer about "the Declare approach will never work as Variant structure is not the same as XLOPER12 structure" which I will upvote. The path does not contain any special character. — Arnaud, Dec 13 '17 at 10:44

Charles Williams · Accepted Answer · 2017-12-12T13:02:13.693

If your XLL is loaded and its UDFs are registered so that=HelloWord() in a cell works then you should just be able to call it from VBA like this (unless there is a problem with parameterless string functions)

var=Application.run("HelloWorld")

You can also use Evaluate

var=Application.Evaluate("=HelloWorld()")

I tested my REVERSE.TEXT XLL function like this and it worked correctly.

Sub testing()
Dim var As Variant
var = Application.Run("REVERSE.TEXT", "Charles")
var = Application.Evaluate("=REVERSE.TEXT(""Charles"")")
End Sub

Reverse.Text is registered using UQQ$ (there are 2 parameters , the Text and the Number of characters)

Calling an xll UDF from VBA

1 Answers1