How to create an Array from Range?

Question

I'm using a function I wrote called 'Contains' to determine if entries in a column of data match a dictionary of 'acceptable' values that are predetermined.

Sub Validate_Data()

    Dim My_Dictionary As Variant
    My_Dictionary = Array("Entry1", "Entry2", "Entry3")

    Dim Destination As Range
    Set Destination = Range("C2:C10")

    For Each cell In Destination
        cell.FormulaR1C1 = Contains(My_Dictionary, cell)
    Next cell
End Sub

This code works perfectly fine when I specify the elements of my array, as above. However, when I try to create an array from a range of data, as per this post, my function Contains no longer works

Sub Validate_Data()

    Dim My_Dictionary As Variant
    My_Dictionary = Range("A1:A3").Value

    Dim Destination As Range
    Set Destination = Range("C2:C10")

    For Each cell In Destination
        cell.FormulaR1C1 = Contains(My_Dictionary, cell)
    Next cell
End Sub

Perhaps it is being saved as a different data type in one version as opposed to the other? I can't figure why the first code works and the second does not, and I would rather be able to read the array from a sheet than specify each element in my code (it is much longer than three elements).

Yes - see [here](https://stackoverflow.com/questions/7649046/one-dimensional-array-from-excel-range) - but I'd suggest modifying `Contains`. Also, perhaps consider naming `My_Dictionary` something else. `Dictionary` at a first glance would make one think you're use a `Scripting.Dictionary`. — BigBen, Jun 21 '19 at 13:31
Without wanting to distract, _why_ are you doing this? At first glance I can think of better ways to do this (Lookup, Match, validation)? — , Jun 21 '19 at 13:55

Robert Todar · Answer 1 · 2019-06-26T22:30:24.217

As the comments above, an array created from a range is two dimensional. So instead of accessing the elements like My_Dictionary(x) it would be like My_Dictionary(x, x).

To get a single dimensional array use Application.Transpose on your column.

My_Dictionary = Application.Transpose(Range("A1:A3")).

Side notes

@bigben is correct, I was immediately confused because I thought you were working with Scripting.Dictionary object.

I would change that name to something like acceptableValues or acceptableEntries to make it more clear of what it contains.

Whatever name you do use though I would remove the underscore from the sub name and variable. This has a special meaning in VBA and can lead to errors for you down the road.

How to create an Array from Range?

1 Answers1

Side notes