How to return a variable through an argument of a

Question

I have a C++ function that I use in Excel 2013 through a DLL built with VC2013:

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

In Excel VBA I include this function like this:

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

This works well so far, however, now, I'd like to be able to return a second piece of information, say an error code, through a second argument. Ideally this error code would have the ability to be output to a cell in Excel, or at least to the console through debug.print . I am stuck at making the whole thing work and have had Excel crash several times. This is my fruitless attempt:

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

When I call the function from the worksheet and indicate a cell as the 2nd argument Excel crashes at me. What is the correct, elegant way to do this?

Answer 1

You just cant give exel cell as long number to c\c++, since it doesn't automatically cast

You can do this:

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;
}

in Excel declare the new function too:

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)