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

i created dll containing function named "koduj". calling function using inside excel worksheet cell returns desired result. calling "koduj" vba returns wrong answer.

koduj needs 2 arguments: string nr_id , integer x1. calculates sum of nr_id's letters in ascii representation , adds x1. calculated sum returned.

i following instructions found here.

here's .cpp sourcefile:

#include<windows.h> #include<string> using namespace std;   //convert bstr wstring 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;     (unsigned int = 0; < 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 vba function declaration:

declare function koduj _ lib "<dll_directory_and_full_name>" (byval x string, byref y integer) integer 

by writing:

=koduj("aaa";1) 

inside worksheet cell desired result (292)

debugging vba code:

sub test()  dim integer = koduj("aaa", 1)  end sub 

reveals wrong result (a = 24930)

i believe c++ code fine, works when called excel's worksheet.

the reason though vba strings internally utf-16, vb converts them ascii before talking outside world (declared functions, file input/output). when declare parameter as string, vba automatically converts string , passes out ascii. matching parameter type on c++ side should lpstr or lpcstr.

if want use bstr on c++ side, need create idl file function, compile tlb , reference tlb vba, vba respect , use bstr.

another problem c++'s int translates vba's long.

the reason why works when called excel sheet apparently excel ignores vba rules string conversion. believe bug.