VBA tweedimensionale variabelen

In dit artikel worden drie verschillende typen tweedimensionale variabele met elkaar vergeleken, namelijk de Collection, de Dictionary en de Array.

Voorbeeld 1 Voorbeeld 2
Key Item Key Item
1 X 4 X
2 Y 9 Y
3 Z 8 Z
Dim c As Collection

Sub vb1Collection()

    Set c = New Collection
   
    c.Add "X"
    c.Add "Y"
    c.Add "Z"
     
    MsgBox getItem(2)
    MsgBox getIndex("Z")

End Sub


Dim c As Collection

Sub vb2Collection()

    Set c = New Collection
   
    c.Add item:=4, key:="X"
    c.Add item:=9, key:="Y"
    c.Add item:=8, key:="Z"
     
    MsgBox getItem("Y")
    MsgBox getIndex(9)  '= index 2

End Sub

Function getIndex(item)

    Dim i As Integer
    Dim v As Variant
    
    For Each v In c
        i = i + 1
        If item = v Then
            getIndex = i
        End If
    Next v

End Function
Function getItem(index)

    getItem = c(index)

End Function

Het voordeel van een Collection is dat deze uitermate geschikt is als ééndimensionale variabele maar minder als tweedimensionale. Het is namelijk niet mogelijk om de Key te bevragen. De collection werkt zowel binnen Office voor Windows als voor de MAC en start vanaf 1.

Dim d As Scripting.Dictionary

Sub vb1Dictionary()
    
    Set d = New Scripting.Dictionary
     
    d.Add key:=1, item:="X"
    d.Add key:=2, item:="Y"
    d.Add key:=3, item:="Z"
             
    MsgBox getItem(2)
    MsgBox getKey("Z")

End Sub
Dim d As Scripting.Dictionary

Sub vb2Dictionary()
    
    Set d = New Scripting.Dictionary
     
    d.Add key:=4, item:="X"
    d.Add key:=9, item:="Y"
    d.Add key:=8, item:="Z"
             
    MsgBox getItem(4)
    MsgBox getKey("Z")

End Sub
Function getKey(item)

    Dim v As Variant
    Dim i As Integer
    
    For i = 0 To d.Count - 1
    
        If d.Items(i) = item Then
            getKey = d.Keys(i)
            Exit Function
        End If
    
    Next i

End Function
Function getItem(key)

    getItem = d(key)

End Function

Bij een Dictionary is het wel mogelijk om de Key te bevragen. Echter kent het gebruik van een Dictionary ook een nadeel; het vereist namelijk een aparte verwijzing naar de bibliotheek “Microsoft Scripting Runtime”. Op zich is dit niet zo erg behalve bij Office voor MAC. Hier is deze bibliotheek gewoonweg niet aanwezig. Een alternatieve oplossing is hier te lezen. De Dictionary start bij 1.

Dim a(2, 1)

Sub vb1Array()

    a(0, 0) = 1
    a(0, 1) = "X"
    a(1, 0) = 2
    a(1, 1) = "Y"
    a(2, 0) = 3
    a(2, 1) = "Z"

    MsgBox getItem(3)
    MsgBox getKey("Y")

End Sub
Dim a(2, 1)

Sub vb2Array()

    a(0, 0) = 4
    a(0, 1) = "X"
    a(1, 0) = 9
    a(1, 1) = "Y"
    a(2, 0) = 8
    a(2, 1) = "Z"

    MsgBox getItem(4)
    MsgBox getKey("Z")

End Sub
Function getItem(key)

    Dim i As Integer

    For i = LBound(a) To UBound(a)
    
        If a(i, 0) = key Then
            getItem = a(i, 1)
            Exit Function
        End If
    
    Next i

End Function
Function getKey(item)

    Dim i As Integer

    For i = LBound(a) To UBound(a)
    
        If a(i, 1) = item Then
            getKey = a(i, 0)
            Exit Function
        End If
    
    Next i

End Function

De Array is de meeste flexibele variabele wanneer het gaat om meerdere dimensies. De array kan – net zoals bij de collection – zowel binnen Office voor Windows als voor de MAC gebruikt worden. Het enige nadeel is dat de Array @runtime minder dynamisch is. Zo kan m.b.v. de instructie Redim Preserve alleen de UBound (2e) gewijzigd worden en niet de LBound (1e). Daarnaast is het gebruik van een Array vrij complex. Voorts kan de array via ‘Option Base 1’ beginnen met 1 i.p.v. 0.


Option Explicit
Option Base 1 'start vanaf 1 i.p.v. 0

Sub Arr()
 
    Dim a() As Variant
     
    Dim r As Integer
    Dim k As Integer
     
'   Inlezen
     
    For r = 1 To 5
     
        For k = 1 To 2
         
            ReDim Preserve a(2, r) 'LBound kan niet gewijzigd worden
             
            a(k, r) = k & "+" & r
             
            Debug.Print a(k, r)
         
        Next k
         
        Debug.Print "---"
     
    Next r
     
    Debug.Print "***"
     
'   Uitlezen
     
    For r = LBound(a, 2) To UBound(a, 2)        ' (1, 2) > 2 = r
 
        For k = LBound(a, 1) To UBound(a, 1)    ' (1, 2) > 1 = k
 
            Debug.Print a(k, r)
 
        Next k
         
        Debug.Print "---"
 
    Next r
      
End Sub

Het bovenstaande voorbeeld demonstreert hoe @runtime de Array aangepast kan worden met behoud van de waarden m.b.v. Redim Preserve. Het nadeel is echter wel dat alleen de Ubound (2e) aangepast kan worden. Hierdoor is het niet meer mogelijk om de gebruikelijke rij en kolom volgorde te houden. Vandaar dat deze ook zijn omgedraaid.