arrays - VBA获取数组中Dim的名称，在索引处

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

我现在的想法是把数组的名字放到数组里面，

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)

因此inventory(x)(0) 将始终以这种方式返回数组的名称。

我希望“A1”显示维度的名称，即精神，但精神本身是一个数组，所以在“A1”中我得到了库存(1)(0)的等价物。

有没有比将名称包含在数组中(如上)更好的方法来获取作为名称返回的葡萄酒、烈酒或啤酒维度？

Answer 1

如果您想按名称引用事物，那么字典是一种解决方案。

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