C ++ function call from Excel and VBA using a DLL created in C ++

Question

I created a DLL containing a function called "koduj". Calling this function using it inside an Excel worksheet cell returns the desired result. Calling "koduj" from VBA returns an incorrect answer.

koduj needs two arguments: string nr_id and integer x1 . It calculates the sum of nr_id letters in the ASCII representation and adds x1 . The calculated amount is returned.

I found the following instructions here .

Here is my .cpp source file:

 #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 is my VBA function declaration:

 Declare Function koduj _ Lib "<dll_directory_and_full_name>" (ByVal x As String, ByRef y As Integer) As Integer 

We write:

 =koduj("aaa";1) 

Inside the sheet cell, I get the desired result (292)

Debugging this VBA code:

 Sub test() Dim a As Integer a = koduj("aaa", 1) End Sub 

shows an incorrect result (a = 24930)

I believe that my C ++ code is great, as it works correctly when called from an Excel worksheet.

Answer 1

The reason is that although VBA strings are internally UTF-16, VB always converts them to ASCII before speaking to the outside world ( Declare d functions, file input / output). Therefore, when you Declare the As String parameter, VBA automatically converts the string and passes it as ASCII. The corresponding parameter type on the C ++ side should be LPSTR or LPCSTR .

If you want to use BSTR on the C ++ side, you also need to create an IDL file for this function, compile it into TLB and specify TLB from VBA, only then VBA will respect and use BSTR ,

Another problem is that C ++ int translates to VBA Long .

The reason it works when called from an Excel worksheet is because Excel seems to ignore VBA rules for converting strings . I think this is a mistake.

Answer 2

Try to declare as long as possible: Dim a As Long

Answer 3

I suppose, due to the magnitude of the error, that this is a numeric parameter that goes wrong - I would try to more explicitly declare the parameter type in your VBA test procedure (possibly Integer) and accept it as this specific type in C ++ ( in this case, a short signature).

There's a great Microsoft article on all of this at http://msdn.microsoft.com/en-us/library/office/bb687915 (v = office.15) .aspx .

Answer 4

Not intended to be a complete answer, but your second type of parameter looks wrong.

Your DLL function: int _stdcall koduj (BSTR nr_id, int & x1) declares x1 as a reference to a (presumably) 32-bit integer.

Your VBA declaration: Declare Function koduj Lib "<dll_directory_and_full_name>" (ByVal x As String, ByRef y As Integer) As Integer declares y as a pointer to a 16-bit integer.

I suggest trying to modify the VBA declaration as follows:

Declare Function koduj _ Lib "<dll_directory_and_full_name>" (ByVal x As String, ByVal y As Long) As Long

And, toggling your signature of the DLL function to pass x1 by value:

int _stdcall koduj (BSTR nr_id, int x1)