Loop numbered variables in Excel VBA - [duplicate]

Question

How can I loop through a set of variables which are numbered consecutively (Bay1, Bay2, Bay3, etc.)?

I have 10 variables and I need to examine each variable with an If-Then statement and depending on the value of the variable assign three unique item numbers to each variable/iteration.

Possibly I would need to create an array or similar structure to store the item numbers for each iteration. I am unsure and I don't have any experience working with arrays.

Answer 1

You better use a collection Object or the dictionary. Keep in mind that for using the dictionary, you have to reference the library first.

You could also use the createObject() method to create a dictionary Object, but using the reference will support you with intelisense functionality and a kind of type safety.

The last example is how to loop trough an array.

1. Dictionary Example
2. Collection Example
3. Array Example
4. Class Example

Code

Option Explicit


' -------------------------------------------------
'   Example using a Collection object.
Public Sub ShowExample_Collection()

    ' Get some Variables with initialized values
    Dim var1 As Integer: var1 = 11
    Dim var2 As Integer: var2 = 22
    Dim var3 As Integer: var3 = 33
    Dim var4 As Integer: var4 = 44
    Dim var5 As Integer: var5 = 55

    ' Store all of them inside of a collection
    Dim theCollection As New Collection
    Call theCollection.Add(var1)
    Call theCollection.Add(var2)
    Call theCollection.Add(var3)
    Call theCollection.Add(var4)
    Call theCollection.Add(var5)

    ' Now you can for each loop
    Dim intCheckVar As Integer
    Dim cntItem As Integer
    For cntItem = 1 To theCollection.Count
        intCheckVar = theCollection.Item(cntItem)
        ' Do something
        If intCheckVar < 30 Then
            Debug.Print "Variable number " & cntItem & " is lower than 30"
        Else
            Debug.Print "Variable number " & cntItem & " is higher than 30"
        End If

    Next

End Sub



' -------------------------------------------------
'   Example using a dictionary object.
'   Before using, you have to reference "Microsoft Scripting Runtime"
Public Sub showExample_Dictionary()

    ' Before you can use the Dictionary Object, you have to 
    ' reference the "Microsoft Scripting Runtime" library
    Dim theDic As New Dictionary

    ' Get your Variables
    Dim var1 As Integer: var1 = 11
    Dim var2 As Integer: var2 = 22
    Dim var3 As Integer: var3 = 33
    Dim var4 As Integer: var4 = 44
    Dim var5 As Integer: var5 = 55

    ' Using a Dictionary is like using a collection.
    ' But you are able to set each item an unique Name
    Call theDic.Add("Variable_1", var1)
    Call theDic.Add("Variable_2", var2)
    Call theDic.Add("Variable_3", var3)
    Call theDic.Add("Variable_4", var4)
    Call theDic.Add("Variable_5", var5)

    ' You still can use a for each loop. But this time you can get the unique name
    ' of each item
    Dim intCheckVar As Integer
    Dim cntItem As Integer
    ' Dictionary is starting at index 0 !!
    For cntItem = 0 To theDic.Count - 1

        ' You can get the Value of the item using the items property of the dictionary object
        ' You can get the unique name of the item using the keys property of the dictionary object
        If theDic.Items(cntItem) < 30 Then
            Debug.Print "Variable with the name " & theDic.Keys(cntItem) & " is lower then 30"
        Else
            Debug.Print "Variable with the name " & theDic.Keys(cntItem) & " is higher then 30"
        End If

    Next

End Sub


' -------------------------------------------------
'   Example using a array.
Public Sub showExample_Array()

    ' Create your array
    Dim aVars(4) As Integer
    aVars(0) = 11
    aVars(1) = 22
    aVars(2) = 33
    aVars(3) = 44
    aVars(4) = 55

    ' You can loop through an array, but your pointing variable has to be of the datatpy variant
    Dim theVar As Variant
    Dim cntItem As Integer: cntItem = -1
    For Each theVar In aVars
        cntItem = cntItem + 1
        If theVar < 30 Then
            Debug.Print "Variable Number " & cntItem & " is lower than 30 because its value is " & theVar
        Else
            Debug.Print "Variable Number " & cntItem & " is higher than 30 because its value is " & theVar
        End If

    Next

End Sub

You can also store your three values in an own class, and storing its Object inside of your iterating dictionary. The following code shows the class definition and the example code.

class cls_Data
   Public Data1 As Integer
   Public Data2 As Integer
   Public Data3 As Integer
end class


Public Sub showExample_Class()

    ' Before you can use the Dictionary Object, you have to reference the
    ' "Microsoft Scripting Runtime" Lib
    Dim theDic As New Dictionary

    ' Get your Variables
    Dim var1 As New cls_Data
    var1.Data1 = 11
    var1.Data2 = 12
    var1.Data3 = 13

    Dim var2 As New cls_Data
    var2.Data1 = 21
    var2.Data2 = 22
    var2.Data3 = 23

    Dim var3 As New cls_Data
    var3.Data1 = 31
    var3.Data2 = 32
    var3.Data3 = 33


    ' Using a Dictionary is like using a collection.
    ' But you are able to set each item an unique Name
    Call theDic.Add("Variable_1", var1)
    Call theDic.Add("Variable_2", var2)
    Call theDic.Add("Variable_3", var3)

    ' You still can use a for each loop. But this time
    ' you can get the unique name of each item
    Dim intCheckVar As Integer
    Dim cntItem As Integer
    ' Dictionary is starting at index 0!!
    For cntItem = 0 To theDic.Count - 1
        ' Get the reference of your stored values to have easier access
        Dim checkVar As cls_Data
        Set checkVar = theDic.Items(cntItem)
        ' Now you can check the data values of your referenced object and change it
        If checkVar.Data1 < 30 Then
            checkVar.Data1 = 100
            checkVar.Data2 = 200
            checkVar.Data3 = 300
        Else
            checkVar.Data1 = -100
            checkVar.Data2 = -200
            checkVar.Data3 = -300
        End If

    Next

End Sub