Creating DLL and using it from Excel-CodePudding

I have the following issue when trying to create a DLL in CPP and using it in Excel. When the argument reaches the CPP function, the value it holds changes (regardless of what it was in Excel). I am guessing somewhere it "drops", but I haven't been able to figure it out.

Here is the code:

Source.cpp


extern "C" double __stdcall my_double(double A)
{
    return 2.0 * A; 
}

Source.def

LIBRARY
EXPORTS
    my_double

VBA Code

Declare PtrSafe Function my_double Lib "C:\MyDir\Example.dll" (ByVal A As Double) As Double

Then when I call my_double from excel, it always returns 0.0.

I attached a debug point to the function, and I can see that the value is indeed 0 when it reaches the cpp function. Any tips on how to debug this issue would be greatly appreciated!

Things I have tried so far without success:

Made sure the bits on excel and the build in CPP match (both x64).
Tried with Release mode and Debug mode.
Tried changing the VBA code with ByVal, ByRef and using neither.
Tried a simpler example using int instead of double.
I checked DUMPBIN in the DLL file I created, and it looks OK.
I was following this tutorial: https://personalpages.manchester.ac.uk/staff/Andrew.Hazel/EXCEL_C .pdf (page 54)

CodePudding user response：

Excel will struggle to use a function declared directly from a dll entry point as it doesn't know the types of the parameters (and other things, eg how to manage the memory of the return value). The OP's method from the tutorial may have worked in the past, but not any more it seems.

Creating a VBA user-defined function (UDF) wrapper gets around this, as Excel knows how to handle and marshal parameters in this case:

Private Declare PtrSafe Function my_double Lib "C:\Users\david\source\repos\CppTests\x64\Release\DllForExcel.dll" (ByVal A As Double) As Double

Public Function MyDouble(d As Double) As Double
    MyDouble = my_double(d)
End Function

Use the MyDouble() function in the spreadsheet.

The other solution is to go down the route of creating a compiled Xll (often using the Excel SDK), which has entry points to register functions and their parameters, as well as managing memory allocations.