Background

Kind of a follow up - slightly related to my other SO question.

I thought that if somehow I find a way in VBA to pass a single column to .NET and convert it to a native .NET type then I could finally overcome the mystery of explicitly looping on objects via COM (which is super slow as we all know). I'd then be able to use all the cool stuff and not worry about constantly looping through Excel.Range object.

Please note: this is all about a single column not an object[,].

VBA side of things

It turns out in VBA you can get a 1D array like this:

.NET Compatibility

Sample .tlb/.dll library in C# - click here to get an idea

It comes down to one method really:

public object Deduplicate1DArray(object arr)
{
    Array column = (Array)arr; // converts assumed/expected 1d array to System.Array
    List<string> list = column.OfType<string>().ToList(); // generic list to use the cool stuff 
    column = list.Distinct<string>().ToArray(); // convert back to match the type
    return column;
}

So from all the research I have done I thought that passing the 1d from VBA to .NET as object type is the way to go. If you know/experienced a better way (like passing the Excel.Range please share).

A successful conversion to a native .NET System.Array is possible which is just sweet because an Array can easily be converted to a generic List<string> which gives me access to all the cool stuff.

So at this point I am not relying any longer on VBA to manipulate that Range and I can do amazing things that .NET allows me to on a generic List<T> type - I mean, how cool's that?

Testing...

Attached references in the VBE to my .tlb and now I am able to use my library like

Dim c as new COMClass

Currently, I can test it like this:

Sub Main()

    Cells.ClearContents

    [A1] = "foo"
    [A2] = "boo"
    [A3] = "doo"
    [A4] = "goo"
    [A5] = "foo"

    Dim arr1D As Variant
    arr1D = Application.Transpose(Range("A1:A5"))

    Dim c As New COMClass

    Dim noDuplicates As Variant
    noDuplicates = c.Deduplicate1DArray(arr1D)

    Range("B1").Resize(UBound(noDuplicates) + 1, 1) = Application.Transpose(noDuplicates)

End Sub

or

noDuplicates = Application.Transpose(c.Deduplicate1DArray(arr1D))
Range("B1").Resize(UBound(noDuplicates), 1) = noDuplicates

and that works as expected (either way)

What bothers me...

1. A user of the library has to know and remember that the COMClass.Deduplicate1DArray needs to be passed a 1d array... therefore, in VBA they have to call the Application.Transpose() on the Range object to pass it properly. Things can get very .Net'ish if the user decides to do things like:

   noDuplicates = Application.Transpose(c.Deduplicate1DArray(Application.Transpose(Range("A1:A5"))))
   

   or even worse

   Range("B1").Resize(SOME_BIG_NUMBER, 1) = Application.Transpose(c.Deduplicate1DArray(Application.Transpose(Range("A1:A5"))))
   

   Anything I could do in either VBA or C# to improve that? Creating another wrapper or maybe just accepting the Excel.Range object and try to break it down to a 1d array in .NET?

2. This:

   Range("B1").Resize(10, 1) = c.Deduplicate1DArray(Range("A1:A5").Value)
   

   would have been kind of good or at least look sensible in terms of syntax but the problem is I shouldn't be .Resizing my B1 - (destination) because I don't know how big the returned array after deduplication is going to be.

Any ideas of improving this are very welcome and if I have missed something please let me know I'll update with as much detail as I can.