Loop Through Variables

  • This has sort of been asked before


    http://www.ozgrid.com/forum/showthread.php?t=38479


    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


    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!