Using a loop to set multiple variables quickly

[SerenityNetworks]

I have have sets of variables where I would like to set to a single initial value to all the variables. Rather than defining each variable on its own line, I was hoping to be able to use a loop to quickly perform the task.

The example below shows 5 variables. I have one task where I need to set each of 33 variables to a value of zero. Rather than write 33 lines setting each variable one line at a time, a loop would be handy. Is this possible? If so, how?

Thanks in advance,
Andrew

Sub TestLoopingVariables()
'Goal: reset all 5 variables to zero
    Dim i As Integer
    Dim gvar1 As Double
    Dim gvar2 As Double
    Dim gvar3 As Double
    Dim gvar4 As Double
    Dim gvar5 As Double
    gvar1 = 1
    gvar2 = 2
    gvar3 = 3
    gvar4 = 4
    gvar5 = 5
    MsgBox ("gvar1 = " & gvar1 & vbCr & "gvar2 = " & gvar2 & vbCr & "gvar3 = " & gvar3 & vbCr & "gvar4 = " & gvar4 & vbCr & "gvar5 = " & gvar5)
        For i = 1 To 5
          "gvar" & i = 0 'This does not work.  Is there something that would?  I've tried different syntax, but have found none that work.
        Next i
    MsgBox ("gvar1 = " & gvar1 & vbCr & "gvar2 = " & gvar2 & vbCr & "gvar3 = " & gvar3 & vbCr & "gvar4 = " & gvar4 & vbCr & "gvar5 = " & gvar5)
' All 5 variables should now be zero
End Sub

 

[mmhill]

Use an array.

Sub TestLoopingVariables()
'Goal: reset all 5 variables to zero
Dim i As Integer
Dim gvar(1 to 33) As Double

gvar1 = 1
gvar2 = 2
gvar3 = 3
gvar4 = 4
gvar5 = 5
MsgBox ("gvar(1) = " & gvar(1) & vbCr & "gvar(2) = " & gvar(2) & vbCr & "gvar(3) = " & gvar(3) & vbCr & "gvar(4) = " & gvar(4) & vbCr & "gvar(5) = " & gvar(5))

For i = 1 To 5
gvar(i) = 0
Next i

MsgBox ("gvar1 = " & gvar1 & vbCr & "gvar2 = " & gvar2 & vbCr & "gvar3 = " & gvar3 & vbCr & "gvar4 = " & gvar4 & vbCr & "gvar5 = " & gvar5)
' All 5 variables should now be zero
End Sub

 

[mmhill]

I don't know how to edit or delete an answer and accidently submitted the one above before it was finished.

Use an array.

Sub TestLoopingVariables()
'Goal: reset all 5 variables to zero
Dim i As Integer
Dim gvar(1 to 33) As Double

for i = 1 to 5
gvar(i) = 1
next i

MsgBox ("gvar(1) = " & gvar(1) & vbCr & "gvar(2) = " & gvar(2) & vbCr & "gvar(3) = " & gvar(3) & vbCr & "gvar(4) = " & gvar(4) & vbCr & "gvar(5) = " & gvar(5))

For i = 1 To 5
gvar(i) = 0
Next i

MsgBox ("gvar(1) = " & gvar(1) & vbCr & "gvar(2) = " & gvar(2) & vbCr & "gvar(3) = " & gvar(3) & vbCr & "gvar(4) = " & gvar(4) & vbCr & "gvar(5) = " & gvar(5))

End Sub

 

[SerenityNetworks]

Whoohoo! Thank you. So simple, but I just couldn't figure it out.

Thanks again!

 

[SerenityNetworks]

Whoops! I guess what you posted doesn't work for me. It seems it makes a difference where the variable is declared (and I didn't mention it earlier).

In my case , gvar1 through gvar5 are public variable set right below Option Explicit ("Public gvar1 As Double", etc.). In different subs, the variables are assigned various values. Then at one point I want to set them all back to zero in an entirely different sub. Using "gvar(i) = 0" in a loop gives me a compile error.

Thoughts?

 

[mmhill]

It doesn't make much difference where they are declared. Even though you named them gvar1, gvar2, and gvar3, VBA sees them no different than if you'd named them HotDog, Purple and HopScotch. They're all completely different variables. I'm not sure how a loop can be created for this. I tried a few tricks and none worked.

It may make sense to change all the gvar# variables in your applicaiton into a single array variable. If that's the case you'd declare the array in a similar way to what you have now. Instead of
Public gvar1 As Double, gvar2 As Double, ... gvar33 as Double
you do
Public gvar(1 to 5) As Double 'or 1 to 33 or whatever number of items you need

That 1 short line takes care of all of it ... whether there's 5 or 50,000.

Then in the code, wherever you refer to gvar1 you would change that to gvar(1) ... this means you are referencing the first element in the gvar array. All your gvar2 would need to be changed to gvar(2), etc. You could use a global Find/Replace to do this. Once they are in an array, you can loop to your hearts content.

Otherwise, it might be easier to just do gvar1 = 0, gvar2 = 0, etc.

 

[SerenityNetworks]

Thanks @mmhill that's works perfectly. The changes weren't too tough and it cleans up a lot of lines of code.