Excel VBA - Accessing Arrays that are Populated with Named Ranges

Question

I have been struggling with an array issue, see here: (Excel VBA - Arrays for Data Validation Reference)

I want to try and simplify to see if it would make more sense.

Lets say I have a set of ranges, each with a name; Range1, Range2, etc. I set each named range to an array; MyArray1, MyArray2, etc. I then put these arrays within an array; ContainerArray

I have another array called ReferenceArray, which is based on the named range; RangeRef; which contains values Ref1, Ref2, Ref3

Reference Array populates Data Validation List 1.

Problem Statement:

If the user selects (for example) Ref1 in Data Validation List 1, I want to trigger Data Validation List 2 to fill with the respective array within the ContainerArray (so it would fill with the values from MyArray1)...

Something like:

If Data Validation List 1.Selection = ReferenceArray(i) then
    Data Validation List 2 Options = ContainerArray(i)
Endif

Any help is appreciated. Thank you in advance!

score 0 · Answer 1 · answered Nov 08 '17 at 19:35

0

If you google "Array of Arrays" you'll find lots of information on how to accomplish this, including this SO example: How do I set up a "jagged array" in VBA?

But again, as per my comment at your other thread, why use VBA at all? Without good reason, it's simply not a good approach. You're over-complicating it. Use Dynamic Named Ranges, and look at the example I linked to in your other thread.

answered Nov 08 '17 at 19:35

jeffreyweir

4,668
1
16
27

Hi Jeffreyweir- thank you for your comment. I forgot to mention another reason that I am trying to use VBA, as it wasnt in this peice of the code. I wanted to gain autocomplete functionality within this Excel "form" of sorts, so I found a way to overlay comboboxes on a cell that is selected if it contains data validation. (http://http://www.contextures.com/xlDataVal14.html) – mitchmitch24 Nov 08 '17 at 19:46
Using the methods to create tables and reference the named ranges with =indirect does not work, as comboboxes cannot take the =indirect. I figured out the array solution with VBA that I was looking for (and I'll post it here in a minute) but unfortunately, it too, doesnt work with comboboxes, so I am unable to utilize it. It would be nice if Excel could simply allow autocomplete for the datavalidation lists! – mitchmitch24 Nov 08 '17 at 19:46

score 0 · Accepted Answer · answered Nov 08 '17 at 19:47

I sorted out a solution, albeit, without using named ranges, but simply using arrays created within vba. But as far as the referencing I wanted, it works without issue! :)

Sub Test()

    Dim Array_DV1 As Variant
    Array_DV1 = Array("Array X", "Array Y", "Array Z")

        Range("DV_1").Offset(1, 0).Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Join(Array_DV1, ",")
            .IgnoreBlank = True
            .InCellDropdown = True
        End With

    Dim Array_X As Variant
    Array_X = Array("X1", "X2", "X3")

    Dim Array_Y As Variant
    Array_Y = Array("Y1", "Y2", "Y3", "Y4")

    Dim Array_Z As Variant
    Array_Z = Array("Z1", "Z2", "Z3", "Z4", "Z5")

        Dim Array_XYZ As Variant
        Array_XYZ = Array(Array_X, Array_Y, Array_Z)

        For i = LBound(Array_DV1) To UBound(Array_DV1)

            If Range("DV_1").Offset(1, 0).Value = Array_DV1(i) Then

                Range("DV_2").Offset(1, 0).Select
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=Join(Array_XYZ(i), ",")
                    .IgnoreBlank = True
                    .InCellDropdown = True
                End With

            End If

        Next

End Sub

Side Note: The code will need something to update this code, should the user pick a different choice in the first dv list.

Excel VBA - Accessing Arrays that are Populated with Named Ranges

2 Answers2