How do I handle a string array returned from a C# Method in VBA

Question

I have written an assembly in C# which returns a string array, the C# code is below:

[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 interop box in properties. Then in Microsoft Access, I have added the .tlb file to the references and created a form with a couple of controls (one of which is a Listbox control named lstResults). Under the main button control, I have the following VBA code:

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 ?results after putting some dummy code below to allow me to place a breakpoint, I get the following error:

Run-time error '13' - Type mismatch

Effectively I want to rewrite the following C# code in VBA:

var results = c.SearchPostcodes(postcodeToSearch);

foreach(var x in results)
{
    lstResults.Items.Add(x);
}

Thanks in advance

score 3 · Accepted Answer · 2012-04-03 17:45:50Z

3

Updated Answer: Instead of returning string[], try returning object instead:

[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 = 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:

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)

edited Apr 3, 2012 at 17:45

answered Apr 2, 2012 at 18:18

user425495

Sign up to request clarification or add additional context in comments.

3 Comments

JMK Over a year ago

Ah thankyou. Now this runs without error, however I still don't understand how to deal with the resulting string array. Also, if I put some dummy code below to allow me to place a breakpoint, then use the Immediate window to type ?results I get Run-time error '13', Type mismatch.

user425495 Over a year ago

The problem is that you are returning a string array from the SearchPostcodes method, when you need to return a COM object. Here is another SO thread detailing this issue with a solution: stackoverflow.com/questions/948712/….

user425495 Over a year ago

Updated my answer with a solution to your other question. While I'm unsure if this is the best solution, it provides the expected functionality.

mischab1 · Accepted Answer · 2012-04-02 22:47:57Z

2

In VBA, the element in a For Each loop must be a Variant when looping thru an array.

Dim v As Variant
For Each v In Array("value 1", "value 2", "value 3")
    ' do something with v
Next v

Typing ?results in 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, ",").

answered Apr 2, 2012 at 22:47

mischab1

1,60113 silver badges17 bronze badges

Collectives™ on Stack Overflow

How do I handle a string array returned from a C# Method in VBA

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related