Passing a 1D array (single column of a Range object) to .NET to manipulate it without loops and returning it back to VBA

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 list = column.OfType().ToList(); // generic list to use the cool stuff 
    column = list.Distinct().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 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 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

I don't know much about VBA or COM, so I'm going to focus on the single C# function:

If you know that all elements in the input sequence are of the same type, you should use Cast, not OfType. OfType filters out all elements of the wrong type, which could easily hide bugs.

You don't need to call ToList to start using LINQ methods, they work on anything that's IEnumerable, which includes the results of Cast and OfType.

When calling a method like Distinct, you don't need to specify the type parameter (string in your case), the compiler is able to infer it itself.

StackExchange Code Review Q#66645, answer score: 7