Passing a List from C# to VBA

Question

I'm writing a DLL in C# that is to be used in VBA. One method I am writing will get a List of a custom type (PdfReaderPage). My question is this: Can I pass a List of a custom type into a VB Collection? I had this in mind to do

public Microsoft.VisualBasic.Collection GetPageByKeyWord(String keyword)
{
    //Do some code to find correct page list
    //Get List<PdfReaderPage> myList
    Microsoft.VisualBasic.Collection VbCollection = myList;
    return VbCollection;
}

Except I'm not sure if I can either A.) Convert a List into a Collection or B.) Am even going at this the best way.

Hypothetically speaking, I don't see this as any different from loading a function from an imported system DLL. If you can import a C level function like FindWindowEx from user32.dll and toss around HWND vars, then loading a collection from an imported custom DLL function should be the same thing. On the other hand, have you considered a custom VBA class? — user4039065
– user4039065, Commented Jan 5, 2016 at 19:44
I understand VB.NET isn't VBA, however VBA does have Collection in the object library — jDave1984
– jDave1984, Commented Jan 5, 2016 at 20:10
I was referring to the use of the VB.NET tag which states DO NOT USE this tag for VB6, VBA, or VBScript questions. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 5, 2016 at 20:11

Cee McSharpface · Accepted Answer · 2016-01-05 20:26:00Z

1

A) Convert a List into a Collection, can be done:

[ClassInterface(ClassInterfaceType.AutoDual)]
public class PdfReaderPage
{        
    public int Foo;
}

[ClassInterface(ClassInterfaceType.AutoDual)]
public class AccessibleFromVBA
{
    public Microsoft.VisualBasic.Collection PdfReaderPages()
    {
        /* 1. make a List of PdfReaderPage elements */
        var csharp_list = new List<PdfReaderPage>()
        { 
            new PdfReaderPage() {Foo=1},
            new PdfReaderPage() {Foo=2},
            new PdfReaderPage() {Foo=3}
        };
        /* 2. convert it into a vb collection */
        var vb_coll = new Microsoft.VisualBasic.Collection();
        csharp_list.ForEach(x => vb_coll.Add(x));
        /* 3. deliver it */
        return vb_coll;
    }
}

There is just one curious thing to observe: In C#, the Collection instance will actually contain one item more than the Count reports, because collections are 0-based in C# and 1-based in VBA, the item at index [0] always contains the static localized string "empty placeholder to adjust for 1-based array."

answered Jan 5, 2016 at 20:26

Cee McSharpface

8,7133 gold badges43 silver badges90 bronze badges

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

1 Comment

jDave1984 Over a year ago

I was able to workaround using recurrence (The class I'm writing already had Lists that were accessible anyway), but I'm definitely keeping this in mind. Thanks!

Collectives™ on Stack Overflow

Passing a List from C# to VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related