# Loop Through Variables

• This has sort of been asked before

but is there a way to loop through a series of variables like var0...var9? You can obviously loop through and make a set of strings with the right names,

Code
``````Dim I As Integer
Dim Var As String
For I = 1 To 5
Var = "Var" & I
Next I``````

Is there a way to use the contents of a string variable to call a variable with the same name as contents of the string? or somehow concatenate the a string with a number like
array[I]=var.[I]?

• Re: Loop Through Variables

Why not use an array?

Code
``dim var(1 to 5)``

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Loop Through Variables

I am trying to get variables passed to a function from sheets throughout a workbook into an array without having to write out a page of if ismissings as i dont know how many there will be.

Is it possible to pass these as an array?

• Re: Loop Through Variables

Does this help?
[vb]
Sub JR_Test1_LOOP()
' Written by Jack in the UK for http://www.OzGrid.com
' Our Web Site http://www.excel-it.com
' ---------------------------------------------

Dim myStartRange As Excel.Range
Dim I As Integer
Dim Var As String

Set myStartRange = ActiveSheet.Range("a1")
myStartRange.Select

For I = 1 To 5
' jiuk - edit returned value to Your needs
Var = "Var" & " ---- " & I

With ActiveCell
.Value = Var
.Offset(1, 0).Select
End With

Next I

theEnd_JR_Test1_LOOP:
Set myStartRange = Nothing
Exit Sub
End Sub
[/vb]

• Re: Loop Through Variables

--- or -- by array method
[vb]
Sub JR_Test1_LOOP_ARRAY()
' Written by Jack in the UK for http://www.OzGrid.com
' Our Web Site http://www.excel-it.com
' ---------------------------------------------
Dim myStartRange As Excel.Range
Dim i As Integer

Dim var(1 To 5) As Single
Set myStartRange = ActiveSheet.Range("a1")
myStartRange.Select

[a1].Select
' jiuk - use array
For i = 1 To 5
var(i) = i
With ActiveCell
.Value = var(i)
.Offset(1, 0).Select
End With

Next i
theEnd_JR_Test1_LOOP_ARRAY:
Set myStartRange = Nothing
Exit Sub
End Sub
[/vb]
jiuk

• Re: Loop Through Variables

Opps bit better this version

'/// jiuk
[vb]
Sub JR_Test1_LOOP_ARRAY_2()
' Written by Jack in the UK for http://www.OzGrid.com
' Our Web Site http://www.excel-it.com
' ---------------------------------------------
Dim myStartRange As Excel.Range
Dim i As Integer
Dim var(1 To 5) As Single
Set myStartRange = ActiveSheet.Range("a1")
myStartRange.Select
' jiuk - use array
For i = 1 To 5
var(i) = i
With ActiveCell
.Value = var(i)
.Offset(1, 0).Select
End With
Next i
theEnd_JR_Test1_LOOP_ARRAY_2:
Set myStartRange = Nothing
Exit Sub
End Sub
[/vb]

• Re: Loop Through Variables

It would be much easier to use arrays.
Have you looked at passing values with parameter arrays or your whole array could be its own single argument (type variant.) as in

Code
``Call otherRoutine(myArray)``

If you use paramater arrays, you can pass a varying number of arguments without having to check for IsMissing.

This shows a quick way to read an array from a worksheet

Code
``````Dim var As Variant, i As Integer
var = Application.Transpose(Range("a1:a5").Value)

For i = 1 To UBound(var)
MsgBox var(i)
Next i``````
• Re: Loop Through Variables

Is the example JR_Test1_LOOP_ARRAY_2 not covering this based on what the Original Posted has started with?

jiuk

• Re: Loop Through Variables

JR_Test1_LOOP_ARRAY_2 uses a loop to fill the array.

Code
``````For i = 1 to n
myArray(i)=Cells(i,1)
Next i``````

is one of the slowest ways to fill myArray from those cells. The bulk method is much faster. The difference is even greater when writing to the spreadsheet.

• Re: Loop Through Variables

Quote from mikerickson

JR_Test1_LOOP_ARRAY_2 uses a loop to fill the array.

Code
``````For i = 1 to n
myArray(i)=Cells(i,1)
Next i``````

is one of the slowest ways to fill myArray from those cells. The bulk method is much faster. The difference is even greater when writing to the spreadsheet.

Jack never wrote what You have posted, I have never said my code is the best and OzGrid does not do competitions, I have simply used the Original Posters code and adapted it so they can follow it so it is easiest for them. Then they can say OK i need X or y ie faster code. I and no expert on VBA at all but my codes have been tested and work which is all that counts, If You care to post a completed procedure that the Original Poster can copy and just paste in to the VBE and run, then please do so, so far You have posted a 'For' loop section of code not ever member of OzGrid is OK with VBA so we need to help as much as we can and not criticise another's code. However alternatives are of cause very welcome. at first look I was not sure how to use Your code and where.

Critising others in nearly all cases hacks the guy off and ends up messy, I do not allow this at any cost, its about help, or helpper like me could say arg to Excel with it and not help again... that is a big loss as we all work for free and love of are hearts!

jiuk

• Re: Loop Through Variables

Re-reading my post, I see that I crossed the line between building on other's code and criticising it, which should not happpen. I apologize to everyone who was offended or annoyed. I will pay closer attention to the way I phrase things and the impression it may make on others.

Again, I apologize for any bad feelings that I caused.

• Re: Loop Through Variables

Why Transpose???

Code
``````ver = Range("a1:a5").Value
For i = LBound(a,1) To UBound(a,1)
MsgBox ver(i,1)
Next
'even
For Each e In ver
MsgBox e
Next``````
• Re: Loop Through Variables

Quote from mikerickson

Re-reading my post, I see that I crossed the line between building on other's code and criticising it, which should not happpen. I apologize to everyone who was offended or annoyed. I will pay closer attention to the way I phrase things and the impression it may make on others.

Again, I apologize for any bad feelings that I caused.

Cheers buddy for understanding, very kind and yes no hard feelings it is all about helpping, and You have here. Thank You

jiuk

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!