c++ - 如何通过 DLL 函数的参数将变量返回给 Excel？

Question

我有一个 C++ 函数，我通过用 VC2013 构建的 DLL 在 Excel 2013 中使用它:

double my_function(double input) {
//do something
return input*input;
}

在 Excel VBA 中，我像这样包含这个函数:

Declare Function my_function Lib "DLL_with_my_function.dll" (ByVal input As Double) As Double

到目前为止，这工作得很好，但是，现在，我希望能够通过第二个参数返回第二条信息，比如错误代码。理想情况下，此错误代码能够输出到 Excel 中的单元格，或者至少通过 debug.print 输出到控制台。我坚持让整个事情正常进行，并且 Excel 崩溃了好几次。这是我徒劳的尝试:

double my_function(double input, long *error_code) {
*error_code = 5;
return input*input;
}

#in Excel:    
Declare Function my_function Lib "DLL_with_my_function.dll" (ByVal input As Double, ByRef error_code as long) As Double

当我从工作表调用该函数并将单元格指定为第二个参数时，Excel 崩溃了。执行此操作的正确、优雅的方法是什么？

Answer 1

你不能将 exel 单元格作为 long number 给 c\c++，因为它不会自动转换

你可以这样做:

double my_function(double input, long *error_code) {
  *error_code = 5;
  return input*input;
}
//unless you don't want to build the long from bytes, you can use function to do so. 
long get_error_code(long* error_code ){
  return *error_code;
}

在 Excel 中也声明新函数:

Declare Function my_function Lib "DLL_with_my_function.dll" (ByVal input As Double, ByVal error_code as long) As Double
Declare Function get_error_code Lib "DLL_with_my_function.dll" (ByVal error_code as long) As Long

#now in the function you should allocate memory to the error code:
Dim hMem As Long, pMem As Long
#hMem is handle to memory not a pointer
hMem = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, 10) 
#pMem is your pointer     
pMem = GlobalLock(hMem)
#now you can call to my_function with the pointer:
retval = my_function(input, pMem)

#in VB there is auto cast so this will work: 
YourCell = get_error_code(pMem)
# Unlock memory make the pointer useless
x = GlobalUnlock(hMem)
# Free the memory
x = GlobalFree(hMem)