about XLL memory management

[yushang]

Hi guys,

I have problem to deal with XLL memory management. Firstly, have a look at the following code please. (plainxll.cpp).

This XLL registered 2 UDFs: test and testd. Both return an LPXLOPER12 with xlbitDLLFree set. But test is registered with signature "Q" and testd is registered with "Q$", the $ means testd is thread safe. And there is a function xlAutoFree12 which is used to free XLOPER12 returned by test and testd when Excel has finished with it.

The strange thing is that calling test will crash Excel but testd won't. How to explain this? Many thanks!

BTW you can compile the XLL with the command line embedded in the source code

BTW2 I'm using the most recent Excel 365.

#include "windows.h"
#include "xlcall.h"
#include <cwchar>
/*
cl /nologo /std:c++latest /Zi /Zc:strictStrings- /ID:\excel2013sdk\include plainxll.cpp D:\excel2013sdk\src\xlcall.cpp /link /debug /dll /out:plainxll.xll /libpath:D:\excel2013sdk\lib\x64 xlcall32.lib user32.lib
*/
extern "C" {
  
__declspec(dllexport)
LPXLOPER12 test() {
  auto ret = new XLOPER12;
  ret->xltype = xltypeInt | xlbitDLLFree;
  ret->val.w = 10;
  return ret;
}

__declspec(dllexport)
LPXLOPER12 testd() {
  auto ret = new XLOPER12;
  ret->xltype = xltypeInt | xlbitDLLFree;
  ret->val.w = 10;
  return ret;
}

__declspec(dllexport)
void WINAPI xlAutoFree12(LPXLOPER12 pxFree) {
  Excel12(xlFree,nullptr,1,pxFree);
  delete pxFree;
}

__declspec(dllexport)
int WINAPI xlAutoOpen() {
  XLOPER12 xExp;
  XLOPER12 xSig;
  XLOPER12 xFun;
  XLOPER12 xArg;
  XLOPER12 xMcr;
  XLOPER12 xCat;
  XLOPER12 xCut;
  XLOPER12 xTpc;
  XLOPER12 xHlp;
  XLOPER12 xBlk;
  
  xExp.xltype = 
  xSig.xltype = 
  xFun.xltype = 
  xArg.xltype = 
  xCat.xltype = 
  xCut.xltype = 
  xTpc.xltype = 
  xHlp.xltype = 
  xBlk.xltype = xltypeStr;
  xMcr.xltype = xltypeInt;
    
  xExp.val.str = L"\x0004test";
  xSig.val.str = L"\x0001Q";
  xFun.val.str = L"\x0004test";
  xArg.val.str = L"\x0000";
  xMcr.val.w = 1;
  xCat.val.str = L"\x0004test";
  xCut.val.str = L"\x0000";
  xTpc.val.str = L"\x0000";
  xHlp.val.str = L"\x0000";
  xBlk.val.str = L"\x0001 ";  

  XLOPER12 xDll;
  XLOPER12 xRet;
  Excel12(xlGetName,&xDll,0);
  Excel12(xlfRegister,&xRet,11,&xDll,&xExp,&xSig,&xFun,&xArg,&xMcr,&xCat,&xCut,&xTpc,&xHlp,&xBlk);
  
  xExp.val.str = L"\x0005testd";
  xSig.val.str = L"\x0002Q$";
  xFun.val.str = L"\x0005testd";
  Excel12(xlfRegister,&xRet,11,&xDll,&xExp,&xSig,&xFun,&xArg,&xMcr,&xCat,&xCut,&xTpc,&xHlp,&xBlk);
  Excel12(xlFree,nullptr,1,&xDll);
  return 1;  
}
}

[#2]

@yushang 

The reason why calling test crashes Excel while calling testd doesn't is related to the difference in their function signatures and how Excel handles them.

When an XLL function is registered with a "Q" signature, Excel expects it to return a pointer to a static XLOPER12 object, which is valid until the next call to the same function. However, your test function allocates a new XLOPER12 object on the heap each time it's called and returns a pointer to that object. This violates Excel's assumption that the returned pointer will point to a static object, which can cause Excel to crash.

On the other hand, when a function is registered with a "Q$" signature, Excel knows that the function is thread-safe and can be called concurrently from multiple threads. Excel will call the function multiple times, and each time it will allocate and return a new XLOPER12 object on the heap. This is exactly what your testd function does, and therefore it doesn't crash Excel.

To fix the crash when calling test, you need to modify it to return a static XLOPER12 object instead of allocating a new object on the heap each time it's called. One way to do this is to declare a static XLOPER12 object inside the test function and return a pointer to that object. Here's an example of how you could modify test:

__declspec(dllexport)
LPXLOPER12 test() {
  static XLOPER12 ret = { xltypeInt | xlbitDLLFree, { 10 } };
  return &ret;
}

Note that testd is already thread-safe and doesn't need any changes.

[#3]

@Rr_ Thanks for reply. I know the static approach and I believe there is a typo in your example, xlbitDLLFree is wrong, which will direct Excel to free (by call back into xlAutoFree12) a static allocated memory. The right one should be.

__declspec(dllexport)
LPXLOPER12 test() {
  static XLOPER12 ret = { xltypeInt, { 10 } };
  return &ret;
}

 What I'm curious is how the xlFree (in xlAutoFree12) know whether an LPXLOPER12 is returned by a thread-safe UDF or by a non thread-safe UDF.

Just few minutes ago, I implemented my free logic as follow

void myxlFree(LPXLOPER12 px) {
  if (px->xltype == xltypeStr) {
    if (px->val.str) {
      delete px->val.str;
      px->val.str = nullptr;
    }
  } else if (px->xltype == xltypeRef ) {
    if(px->val.mref.lpmref) {
      delete px->val.mref.lpmref;
      px->val.mref.lpmref = nullptr;
    }
  } else if (px->xltype == xltypeMulti) {
    if (px->val.array.lparray) {
      auto count = px->val.array.rows*px->val.array.columns;
      for(unsigned i=0; i < count; i++) {
        myxlFree(&px->val.array.lparray[i]);
      }
      delete px->val.array.lparray;
      px->val.array.lparray = nullptr;
    }
  }
}

and replace following line

__declspec(dllexport)
void WINAPI xlAutoFree12(LPXLOPER12 pxFree) {
  Excel12(xlFree,nullptr,1,pxFree);
  delete pxFree;
}

 with

__declspec(dllexport)
void WINAPI xlAutoFree12(LPXLOPER12 pxFree) {
  myxlFree(pxFree);
  delete pxFree;
}

test just works so far. But I'm not sure if there are potential issues.

[#4]

@yushang 

You are correct about the typo in my previous response. Thank you for pointing that out.

Regarding your question about how xlFree in xlAutoFree12 knows whether an LPXLOPER12 is returned by a thread-safe UDF or a non thread-safe UDF, the answer is that it doesn't.

When xlFree is called, it simply deallocates the memory pointed to by the LPXLOPER12. It is up to the UDF to make sure that it only calls xlFree on memory that was allocated using xl* functions like xlCoerce, xlret, xlAsyncReturn, and xlSet. If the UDF allocated memory using any other means, it must handle the deallocation itself.

In your implementation of myxlFree, you are correctly freeing the memory based on the xltype of the LPXLOPER12. This is a good practice to ensure that the memory is freed correctly for each type of data. Your implementation should work fine as long as you only call it on memory that was allocated using xl* functions.

[#5]

@Rr_ Thanks for reply. I would like to summarize the usage of xlbitDLLFree as follow

1. xlbitDLLFree is used only in scenario when UDF return a dynamic allocated XLOPER12

2. For the returned XLOPER12, if xlbitDLLFree is set, Excel will pass it back to xlAutoFree12. if not set there will be a memory leak.

there leaves a question, what will happen if XLL did not export xlAutoFree12?

In parallel, can I summarize the usage of xlbitXLFree as follow?

1. xlbitXLFree is used only in scenario when UDF return a dynamic allocated XLOPER12

2. For the returned XLOPER12, if xlbitXLFree is set, Excel will free it including the XLOPER12 itself (I'm especially caring about this, from the official example, it seems there is little chance I'm right), if not set, there will be a memory leak.

(all the dynamic allocated XLOPER12 mentioned above does not contain static allocated memory, for example, val.str is not static allocated).

[#6]

@yushang 

For writing high performance custom functions I'd recommend using a C++ wrapper to avoid memory management headaches. Options include,

XLL+: https://www.planatechsolutions.com/xllplus/

XLW: https://www.youtube.com/watch?v=k0V6nvWjMnI

Both provide a C++ interface for creating xlls (templates, smart pointers, etc.) the latter being open source. 

[#7]

https://excel-dna.net/

how about excel-dna?
also an option for xll development.

[#8]

@yushang I'm overthinking. And I would like to summarize the memory bits as follow, memory bits is only used when UDF returning the address of XLOPER12

memory bits vs XLOPER12	static allocated	dynamic allocated
xlbitDLLFree	Fail. Excel will pass back the static allocated XLOPER12 to xlAutoFree12	Ok. Excel will pass back the XLOPER12 to xlAutoFree12
xlbitXLFree	Ok. Excel will free dynamic allocated memories in the XLOPER12 (whether they are allocated by Excel or UDF, it makes no difference). XLOPER12 itself will be freed when XLL is unloaded.	Leak. Excel will work as descripted in the left column. But it will not pass back the XLOPER12.