使用C ++创建的DLL从Excel和VBA调用C ++函数 [英] Calling C++ function from Excel and VBA using DLL created in C++

问题描述

我创建了一个包含名为koduj的函数的DLL。通过在Excel工作表单元格内使用该函数来调用此函数会返回所需的结果。从VBA调用koduj返回错误的答案。

koduj需要两个参数： string nr_id 和整数x1 。它以ASCII表示法计算 nr_id 的字母的总和，并添加 x1 。计算的总和不是返回。

我正在遵循以下说明这里。

这是我的.cpp源文件：

 ＃include< Windows.h> 
＃include< string> 
使用命名空间std; 
 
 
 //为了方便，将BSTR转换为wstring 
 wstring BSTR_to_wstring（BSTR文本）{
 return wstring（text，SysStringLen（text））; 
} 
 
 //以ASCII表示形式计算字母总和
 int ASCII_sum（wstring ws）{
 int sum = 0; 
 for（unsigned int i = 0; i< ws.length（）; i ++）
 sum + = ws [i]; 
返还金额
} 
 
 //koduj函数
 int _stdcall koduj（BSTR nr_id，int& x1）{
 wstring ws_nr_id = BSTR_to_wstring（nr_id）; 
返回ASCII_sum（ws_nr_id）+ x1; 
} 
  

这是我的VBA函数声明：

 声明函数koduj _ 
 Lib< dll_directory_and_full_name> （ByVal x As String，ByRef y As Integer）As Integer 
  

通过写：

  = koduj（aaa; 1）
  

在工作表单元格内我得到所需的结果（292）

调试此VBA代码：

  Sub test（）
 
 Dim a As Integer 
a = koduj（ aaa，1）
 
 End Sub 
  

显示错误的结果（a = 24930）

我相信我的C ++代码是正常的，因为它可以从Excel的工作表中调用。

解决方案

原因是即使VBA字符串是内部的UTF-16，VB也会在与外部世界通话之前将它们转换为ASCII（ Declare d功能，文件输入/输出）。所以当你声明一个参数 As String ，VBA 自动转换字符串并将其作为ASCII传递。 C ++方面的匹配参数类型应为 LPSTR 或 LPCSTR 。

如果要在C ++方面使用 BSTR ，还需要为该函数创建一个IDL文件，将其编译成TLB并引用TLB从VBA开始，只有VBA才会尊重并使用 BSTR 。另一个问题是C ++的 int 转换为VBA的 Long 。

Excel工作表中的工作原理是Excel 忽略VBA规则进行字符串转换。我相信这是一个错误。

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.

解决方案

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.

这篇关于使用C ++创建的DLL从Excel和VBA调用C ++函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！