使用在 C++ 中创建的 DLL 从 Excel 和 VBA 调用 C++ 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19214209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling C++ function from Excel and VBA using DLL created in C++
提问by browning0
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”的函数的 DLL。通过在 Excel 工作表单元格内使用它来调用此函数会返回所需的结果。从 VBA 调用“koduj”会返回错误的答案。
koduj needs two arguments: string nr_idand 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 的工作表中调用时可以正常工作。
回答by GSerg
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 Declarea parameter As String, VBA automatically convertsthe string and passes it out as ASCII. The matching parameter type on the C++ side should be LPSTRor LPCSTR.
原因是即使 VBA 字符串在内部是 UTF-16，VB 总是在与外界交谈之前将它们转换为 ASCII（Declared 函数、文件输入/输出）。因此，当您Declare输入参数时As String，VBA 会自动转换字符串并将其作为 ASCII 传递出去。C++ 端匹配的参数类型应该是LPSTRor LPCSTR。
If you want to use BSTRon 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.
如果要BSTR在 C++ 端使用，还需要为该函数创建一个 IDL 文件，将其编译为 TLB 并从 VBA 中引用该 TLB，只有这样 VBA 才会尊重并使用BSTR.
Another problem is that C++'s inttranslates 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 规则。我相信这是一个错误。
回答by anefeletos
Try declare a as long: Dim a As Long
尝试声明一个一样长：Dim a As Long
回答by Chris Rae
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).aspx 上有一篇关于所有这些的很棒的 Microsoft 文章。
回答by Boris Glick
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 x1as a referenceto 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 Integerdeclares y as a pointerto a 16-bitinteger.
您的VBA声明：
Declare Function koduj Lib "<dll_directory_and_full_name>" (ByVal x As String, ByRef y As Integer) As Integery声明为指针，以一个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
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:
并且，将您的 DLL 函数签名切换为按值传递 x1：
int _stdcall koduj (BSTR nr_id, int x1)
int _stdcall koduj (BSTR nr_id, int x1)
