如何处理从 VBA 中的 C# 方法返回的字符串数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9981404/
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
How do I handle a string array returned from a C# Method in VBA
提问by JMK
I have written an assembly in C# which returns a string array, the C# code is below:
我用 C# 编写了一个程序集，它返回一个字符串数组，C# 代码如下：
[ComVisible(true)]
public class PostcodeFinder
{
public string[] SearchPostcodes(string postCode)
{
var searchService = new QuickAddress("http://x.x.x.x:xxxx/")
{Engine = QuickAddress.EngineTypes.Singleline, Flatten = true};
var mPicklist = searchService.Search("GBR", postCode, PromptSet.Types.OneLine);
var x = mPicklist.Picklist.Items.Count();
var resultsToReturn = new string[x];
for (var i = 0; i < x; i++)
{
resultsToReturn[i] = mPicklist.Picklist.Items[i].PartialAddress;
}
return resultsToReturn;
}
}
I have then built this assembly, being sure to tick the Register for COM interopbox in properties. Then in Microsoft Access, I have added the .tlbfile to the references and created a form with a couple of controls (one of which is a Listboxcontrol named lstResults). Under the main button control, I have the following VBA code:
然后我构建了这个程序集，确保勾选Register for COM interop属性中的框。然后在 Microsoft Access 中，我将.tlb文件添加到引用中并创建了一个带有几个控件的表单（其中一个是Listbox名为的控件lstResults）。在主按钮控件下，我有以下 VBA 代码：
Private Sub btnSearch_Click()
Dim postcodeToSearch As String
postcodeToSearch = Me.txtPostcode
Dim c As New PostcodeFinder
Dim results
results = c.SearchPostcodes(postcodeToSearch)
End Sub
Edit: This runs without error, however when I query the Immediate window with ?resultsafter putting some dummy code below to allow me to place a breakpoint, I get the following error:
编辑：这运行没有错误，但是当我在?results下面放置一些虚拟代码以允许我放置断点后查询立即窗口时，我收到以下错误：
Run-time error '13' - Type mismatch
运行时错误“13” - 类型不匹配
Effectively I want to rewrite the following C# code in VBA:
实际上，我想在 VBA 中重写以下 C# 代码：
var results = c.SearchPostcodes(postcodeToSearch);
foreach(var x in results)
{
lstResults.Items.Add(x);
}
Thanks in advance
提前致谢
采纳答案by JMK
Updated Answer: Instead of returning string[], try returning objectinstead:
更新的答案：不要返回string[]，而是尝试返回object：
[ComVisible(true)]
public class PostcodeFinder
{
public object SearchPostcodes(string postCode)
{
//Unchanged code
return (object)resultsToReturn;
}
}
You will still get the type mismatch error in the immediate window when performing ?results(you need to specify an index such as ?results(0)), however you are able to iterate through the array as:
执行时，您仍然会在立即窗口中收到类型不匹配错误?results（您需要指定一个索引，例如?results(0)），但是您可以按以下方式遍历数组：
results = c.SearchPostcodes(postcodeToSearch)
Dim result As Variant
For Each result In results
MsgBox result
Next result
Original Answer: You need to instantiate the PostcodeFinder class. In your btnSearch_Click subroutine, try:
原始答案：您需要实例化 PostcodeFinder 类。在您的 btnSearch_Click 子程序中，尝试：
Dim c As New PostcodeFinder
Dim results
results = c.SearchPostcodes(postcodeToSearch)
Alternatively, you could separate the declaration from the instantiation as:
或者，您可以将声明与实例化分开为：
Dim c As PostcodeFinder
Dim results
Set c = New PostcodeFinder
results = c.SearchPostcodes(postcodeToSearch)
回答by mischab1
In VBA, the element in a For Each loop must be a Variant when looping thru an array.
在 VBA 中，当循环遍历数组时，For Each 循环中的元素必须是 Variant。
Dim v As Variant
For Each v In Array("value 1", "value 2", "value 3")
' do something with v
Next v
Typing ?resultsin the Immediate Window is giving an error because VBA can't auto-convert an array into a string. You have to either tell it which part of the array you want, ?results(2), or use Join(sourcearray, delimiter)to tell it how to convert the array, ?Join(results, ",").
?results在立即窗口中键入会出错，因为 VBA 无法将数组自动转换为字符串。你要么告诉它你想要数组的哪一部分?results(2)，要么Join(sourcearray, delimiter)用来告诉它如何转换数组，?Join(results, ",")。
