Calling C++ function from Excel and VBA using DLL created in C++

Question

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 and integer x1. It calculates sum of nr_id's letters in ASCII representation and adds x1. 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.

Try dimensioning a as Variant - do you get the same result? — enderland
– enderland, Commented Oct 7, 2013 at 0:47
This is interesting to me. I've replicated the same issue on my machine, also note that koduj("***") of any three lowercase characters returns the same value. Also note that if you do Debug.Print koduj("Ab", 1) - koduj("Aa", 1) you get 256, which strikes me as quite odd. — enderland
– enderland, Commented Oct 7, 2013 at 13:25

GSerg · Accepted Answer · 2017-06-06 18:58:24Z

4

The reason is that even though VBA strings are internally UTF-16, VB always converts them to ASCII before talking to the outside world (Declared 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.

answered Jun 6, 2017 at 18:58

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

anefeletos · Accepted Answer · 2014-03-10 12:08:17Z

-1

Try declare a as long: Dim a As Long

answered Mar 10, 2014 at 12:08

anefeletos

7027 silver badges19 bronze badges

Comments

Chris Rae · Accepted Answer · 2014-04-18 17:17:11Z

-1

I'm guessing from the magnitude of the error that it's the numeric parameter that's going wrong - I would try more explicitly declaring the parameter type in your test VBA routine (probably Integer) and accepting it as that specific type on the C++ side (signed short, in that case).

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

answered Apr 18, 2014 at 17:17

Chris Rae

5,6752 gold badges40 silver badges54 bronze badges

Comments

Boris Glick · Accepted Answer · 2017-02-06 20:22:47Z

-1

Not intended to be a complete answer, but your second parameter's type 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'd suggest try changing VBA declaration as follows:

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

And, switching your DLL function signature to pass x1 by value:

int _stdcall koduj (BSTR nr_id, int x1)

answered Feb 6, 2017 at 20:22

Boris Glick

2122 silver badges4 bronze badges

1 Comment

GSerg Over a year ago

Yes, the Integer/Long situation is as you describe and should be fixed. However it has nothing to do with the string not arriving as bstr.

Collectives™ on Stack Overflow

Calling C++ function from Excel and VBA using DLL created in C++

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related