使用c++创建的DLL从Excel和VBA调用c++函数

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.

我创建了一个DLL，其中包含一个名为“koduj”的函数。通过在Excel工作表单元中使用它来调用这个函数，将返回期望的结果。从VBA中调用“koduj”返回错误的答案。

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.

koduj需要两个参数:string nr_id和integer x1。它计算nr_id的字母在ASCII表示法中的和并添加x1。计算出的总数大于返回的总数。

I was following instructions found here.

我是按照这里找到的指示做的。

Here's my .cpp sourcefile:

这是我的. cpp源文件:

#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:

这是我的VBA函数声明:

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)

在工作表单元格中，我将获得所需的结果(292)

Debugging this VBA code:

调试这VBA代码:

Sub test()

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

End Sub

reveals wrong result (a = 24930)

显示错误结果(a = 24930)

I believe my C++ code is fine, as it works properly when called from Excel's worksheet.

我相信我的c++代码很好，因为当从Excel的工作表中调用时，它可以正常工作。

4 个解决方案

#1

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.

原因是即使VBA字符串是内部UTF-16, VB总是在与外部世界对话之前将它们转换成ASCII(声明的函数、文件输入/输出)。所以当你将一个参数声明为字符串时，VBA会自动将字符串转换为ASCII格式。c++端的匹配参数类型应该是LPSTR或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.

如果您想在c++方面使用BSTR，您还需要为该函数创建一个IDL文件，将其编译为TLB并从VBA引用TLB，只有这样VBA才会尊重并使用BSTR。

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

另一个问题是c++的int转换成VBA的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.

当从Excel表调用时，它能够工作的原因是，显然Excel忽略了字符串转换的VBA规则。我认为这是一个bug。

#2

Try declare a as long: Dim a As Long

试着申报一样长:申报一样长

#3

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).

我从错误的大小猜测是数值参数出错了——我将尝试在测试VBA例程(可能是整数)中更显式地声明参数类型，并在c++端接受它作为特定类型(在这种情况下，是带符号的短)。

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

微软在http://msdn.microsoft.com/en-us/library/office/bb687915(v=office.15)上有一篇很棒的微软文章。

#4

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.

你的DLL函数:int _stdcall koduj (BSTR nr_id, int & x1)声明x1作为一个32位整数的引用。

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.

您的VBA声明:声明函数koduj Lib“ ”(ByVal x作为字符串，ByRef y作为整数)声明y作为16位整数的指针。

I'd suggest try changing VBA declaration as follows:

我建议尝试改变VBA声明如下:

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

声明函数koduj _lib“ ”(ByVal x作为字符串，ByVal y作为Long)

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

并且，切换你的DLL函数签名以通过值传递x1:

int _stdcall koduj (BSTR nr_id, int x1)

int _stdcall koduj (BSTR nr_id, int x1)

#1