VBA arrays with side-by-side values

Question

Is there a way to define and write an array in VBA such that value pairs are shown two to a line?

fndList = Array _
( _
    "PS4",  "PlayStation 4" _
    "WIN",  "Microsoft Windows", _
    "SNES", "Super Nintendo Entertainment System" _
)

Something like the above? Using two separate arrays is fine too, thanks.

Why not test it? If you put a comma immediately after `"PlayStation 4"` then what you have above is perfectly valid VBA. Your actual data seems like it would most naturally fit in a `Dictionary`. — John Coleman, May 29 '17 at 13:03
Looks like you are missing a comma. Are you trying to create a `fndList(1 to 3, 1 to 2)` array? — , May 29 '17 at 13:03

John Coleman · Accepted Answer · 2017-05-29T14:44:57.850

What you have is (after the missing comma is inserted after "PlayStation 4") perfectly valid VBA. However, it creates a 1-dimensional array. If you want similar notation to create a 2-dimensional array, you can create a custom function:

Function Matrix(m As Long, n As Long, ParamArray items()) As Variant
    'Creates an mxn matrix where the items() are filled in by row
    Dim A As Variant
    Dim i As Long, j As Long
    ReDim A(1 To m, 1 To n)
        For i = 1 To m
            For j = 1 To n
                A(i, j) = items(n * (i - 1) + j - 1)
            Next j
        Next i
    Matrix = A
End Function

Used like:

Sub test()
    Dim fndList As Variant
    fndList = Matrix _
    (3, 2, _
        "PS4", "PlayStation 4", _
        "WIN", "Microsoft Windows", _
        "SNES", "Super Nintendo Entertainment System" _
    )
    Range("A1:B3").Value = fndList
End Sub

The array which is created is 1-based rather than 0-based since in Excel VBA 1-based is the default for interacting with ranges. It would obviously be easy enough to tweak the function so that the array returned is 0-based.

Looking a bit more at your actual problem, if your goal is to lookup values like "Microsoft Windows" from keys like "WIN" then you can use a Dictionary:

Sub test2()
    Dim fndList As Object
    Set fndList = CreateObject("Scripting.Dictionary")
    fndList.Add "PS4", "PlayStation 4"
    fndList.Add "WIN", "Microsoft Windows"
    fndList.Add "SNES", "Super Nintendo Entertainment System"
    Debug.Print fndList("WIN") 'prints "Microsoft Windows"
End Sub

You could modify the Matrix() function so that it returns such a dictionary. For example, something like this:

Function Dict(ParamArray pairs()) As Object
    'returns a dictionairy where successive pairs of
    'items in the pairs array are treated as key-value
    'pairs. It is assumed than an even number of items
    'are passed
    Dim D As Object
    Dim i As Long, n As Long

    Set D = CreateObject("Scripting.Dictionary")
    n = (UBound(pairs) - 1) / 2
    For i = 0 To n
        D.Add pairs(2 * i), pairs(2 * i + 1)
    Next i
    Set Dict = D
End Function

Which can be used like:

Sub test3()
    Dim fndList As Object
    Set fndList = Dict _
    ( _
        "PS4", "PlayStation 4", _
        "WIN", "Microsoft Windows", _
        "SNES", "Super Nintendo Entertainment System" _
    )
    Debug.Print fndList("WIN") 'prints "Microsoft Windows"
End Sub

@posfan12 You can use a `for-each` loop as explained here: https://stackoverflow.com/q/1296225/4996248 .Note that the loop index should be a variant. This is a nice tutorial on dictionaries: https://excelmacromastery.com/vba-dictionary/ . If you want to go this route, hopefully the function I added in the last edit will be helpful. — John Coleman, May 29 '17 at 14:49

VBA arrays with side-by-side values

1 Answers1