VBA Get name of Dim, at index, in Array

Question

Sub foobar()

Dim wine As Variant
wine = Array("Red", "White", "Rose", "Sparkling")

Dim spirits As Variant
spirits = Array("Vodka", "Whiskey", "Rum", "Gin")

Dim beer As Variant
beer = Array("Ale", "Lager", "Pilsner", "Stout")

Dim inventory As Variant
inventory = Array(wine, spirits, beer)

Range("A1") = inventory(1)

End Sub

My current idea is to put the name of the array in the array,

wine = Array("wine", "Red", "White", "Rose", "Sparkling")

spirits = Array("spirits", "Vodka", "Whiskey", "Rum", "Gin")

beer = Array("beer", "Ale", "Lager", "Pilsner", "Stout")

inventory = Array(wine, spirits, beer)

Therefore inventory(x)(0) will always return the name of the array that way.

I want "A1" to show the name of the Dimension, i.e. spirits, but spirits itself is an array, so in "A1" I get the equivalent of inventory(1)(0).

Is there a better way to get the wine, spirits, or beer Dimensions returned as their name, than to include the name in the array (as above)?

They are variable and not objects. You will need to store another array with the desired names as strings. — Scott Craner
– Scott Craner, Commented Mar 18, 2016 at 18:22

Tim Williams · Accepted Answer · 2016-03-18 20:18:58Z

3

If you want to reference things by name then a Dictionary is one solution.

Sub foobar()

    Dim wine As Variant
    wine = Array("Red", "White", "Rose", "Sparkling")

    Dim spirits As Variant
    spirits = Array("Vodka", "Whiskey", "Rum", "Gin")

    Dim beer As Variant
    beer = Array("Ale", "Lager", "Pilsner", "Stout")

    Dim inventory As Object, k
    Set inventory = CreateObject("Scripting.Dictionary")
    With inventory
        .Add "Wine", wine
        .Add "Spirits", spirits
        .Add "Beer", beer
    End With

    'access a specific item
    Debug.Print Join(inventory("Beer"), ",")

    'loop over items
    For each k in inventory.keys
        Debug.Print k, Join(inventory(k), ",")
    Next k

End Sub

edited Mar 18, 2016 at 20:18

answered Mar 18, 2016 at 18:32

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Get name of Dim, at index, in Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related