title: Calling an xll UDF from VBA
body: <p><strong>I would like to call one of my User Defined Function from VBA.</strong></p>
  
  <p>My User Defined Function is declared in C++:</p>
  
  <pre><code>XLOPER12*WINAPI HelloWorld()noexcept{
      THROWS();
      static XLOPER12 res=[](){
          static std::array&lt;wchar_t,13&gt;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 &amp;res;}
  </code></pre>
  
  <p>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 <code>LPXLOPER12</code>.</p>
  
  <p>I can successfully register my function with <a href="https://msdn.microsoft.com/en-us/library/office/bb687900.aspx" rel="nofollow noreferrer"><code>xlfRegister</code></a> specifying a <code>pxTypeText</code> of <code>"U$"</code>. I can then call my UDF from Excel:</p>
  
  <pre><code>=HelloWorld()
  </code></pre>
  
  <p>And it works!</p>
  
  <hr>
  
  <p>If I try to call my function from VBA as suggested <a href="https://stackoverflow.com/a/375753/1824274">here</a>:</p>
  
  <pre><code>Sub macro_test()
      Dim hw As Variant
      hw = Application.Run("D:\Path\MyAddIn.xll!HelloWorld")
  End Sub
  </code></pre>
  
  <p>I get an error message from Application.run: </p>
  
  <blockquote>
    <p>Run-time error '1004': Application-defined or object-defined error</p>
  </blockquote>
  
  <hr>
  
  <p><strong>If I try to call my function from VBA as suggested <a href="https://stackoverflow.com/a/17468786/1824274">here</a>:</strong></p>
  
  <pre><code>Private Declare PtrSafe Function HelloWorld Lib "C:\Path\MyAddIn.xll" () As Variant
  
  Sub macro_test()
      Dim hw As Variant
      hw = HelloWorld()
  End Sub
  </code></pre>
  
  <p><strong>I get an empty result</strong> instead of <code>"Hello World"</code>.</p>
  
  <hr>
  
  <p><strong>What am I doing wrong ?</strong></p>
  
  <p>Miscellaneous pieces of information:</p>
  
  <ul>
  <li>Using Excel 2013</li>
  <li>Using VS 2017 15.5</li>
  <li>Using the first method (<code>Application.Run</code>), VBA does not call my function (I cannot step into my function with the debugger).</li>
  <li>Using the second method, VBA calls my function (I can step into my function with the debugger).</li>
  <li>Using the second method, when I add <code>xlbitDLLFree</code> to my <code>xltype</code>, function <code>xlAutoFree12</code> is not called, which makes me think that somehow the return value is not understood properly by VBA.</li>
  </ul>