Calling C++ function from Excel and VBA using DLL created in C++
The reason is that even though VBA strings are internally UTF-16, VB always converts them to ASCII before talking to the outside world (Declare
d functions, file input/output). So when you Declare
a parameter As String
, VBA automatically converts the string and passes it out as ASCII. The matching parameter type on the C++ side should be LPSTR
or LPCSTR
.
If you want to use BSTR
on the C++ side, you need to also create an IDL file for that function, compile it into a TLB and reference the TLB from VBA, only then VBA will respect and use BSTR
.
Another problem is that C++'s int
translates to VBA's Long
.
The reason why it works when called from Excel sheet is that apparently Excel ignores the VBA rules for string conversion. I believe this to be a bug.
browning0
Updated on July 26, 2022Comments
-
browning0 almost 2 years
I created a DLL containing a function named "koduj". Calling this function by using it inside an Excel worksheet cell returns the desired result. Calling "koduj" from VBA returns wrong answer.
koduj needs two arguments:
string nr_id
andinteger x1
. It calculates sum ofnr_id
's letters in ASCII representation and addsx1
. Calculated sum is than returned.I was following instructions found here.
Here's my .cpp sourcefile:
#include<Windows.h> #include<string> using namespace std; //Convert BSTR to wstring for convenience wstring BSTR_to_wstring (BSTR text){ return wstring(text, SysStringLen(text)); } //Calculate sum of letters in ASCII representation int ASCII_sum (wstring ws){ int sum = 0; for (unsigned int i = 0; i < ws.length(); i++) sum += ws[i]; return sum; } //"koduj" function int _stdcall koduj (BSTR nr_id, int & x1){ wstring ws_nr_id = BSTR_to_wstring(nr_id); return ASCII_sum(ws_nr_id) + x1; }
Here's my VBA function declaration:
Declare Function koduj _ Lib "<dll_directory_and_full_name>" (ByVal x As String, ByRef y As Integer) As Integer
By writing:
=koduj("aaa";1)
Inside a worksheet cell I get desired result (292)
Debugging this VBA code:
Sub test() Dim a As Integer a = koduj("aaa", 1) End Sub
reveals wrong result (a = 24930)
I believe my C++ code is fine, as it works properly when called from Excel's worksheet.