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:

enter image description here

.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?


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()


    [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


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

and that works as expected (either way)

enter image description here

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.


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<T>, 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.


I only know VBA so I'll focus on that bit of things.

You shouldn't be creating an array via worksheet. It's entirely unnecessary. Why not just:

Dim arr1D as Variant
arr1D = Array()
ReDim arr1D(1 to 5)

arr1D(1) = "foo"
arr1D(2) = "boo"
arr1D(3) = "doo"
arr1D(4) = "goo"
arr1D(5) = "foo"

You also don't need to transpose an array when printing to a column. The default first dimension of VBA arrays is already Vertical. Just:

Set printRng = Range(Cells(1, 2), Cells(Ubound(arr1D), 2))
printRng = arr1D

As far as your user having to know to pass only a 1D array, you've literally told them that in the function name. IMO, that's a sufficiently reasonable indication.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy