Calling an xll UDF from VBA

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.

Solution

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)