Referring to Variable in Code

[raskew]

Hi Folks -

Have gone seriously brain-dead on this. Situation: In a public sub, have dim'd DOW1, DOW2 etc. thru DOW7 as integers.

Were this a recordset and we had fields DOW1, DOW2, etc.. we could do something like:

n = 6 'as an example
strHold = "DOW" & format
rs(strHold) = rs(strHold) + 1

...this would increment rs!DOW6 by 1

Where I'm at a loss is how to perform the similar action but referring not to fields in a table but rather to a specific variable (DOW6). Realize that I could do this with case statements, or the Switch() or Choose() functions, but the code would be really ugly. Know I'm overlooking the obvious here.

If you can help put me out of misery, I'd really be grateful.

Thanks, Bob

 

[Sergeant]

I would use an array variable.
You could do:
Dim DOW(6)
Then you can load it up...
For i = 0 to 6
DOW(i) = ~whatever
next i

Now you can refer to DOW to get the particular value you need.

 

[Cosmos75]

ByRef / Using a Collection

I was going to suggest an array but Sergeant beat me to it.

But since I've already thought about it, I may as well share the other two ways I thought of.

The first version is creating a formula that passes all the variables you want to change for a function by reference. Probably isn't ideal if you have a lot of variables.

Public Sub TestAddByRef()
    Dim lng1 As Long
    Dim lng2 As Long
    Dim lng3 As Long

    lng1 = 10
    lng2 = 100
    lng3 = 1000

    Debug.Print "***** TestAddByRef() START *****"
    Debug.Print "lng1 = " & lng1
    Debug.Print "lng2 = " & lng2
    Debug.Print "lng3 = " & lng3

    Dim i As Long
    For i = 1 To 5
        AddByRef lng1, lng2, lng3
        Debug.Print "*****     i = " & i & "    *****"
        Debug.Print "lng1 = " & lng1
        Debug.Print "lng2 = " & lng2
        Debug.Print "lng3 = " & lng3
    Next i

    Debug.Print "***** TestAddByRef()  END  *****"

End Sub

Private Sub AddByRef(ByRef lng1 As Long, _
                     ByRef lng2 As Long, _
                     ByRef lng3 As Long)
    'ByRef is the default but I put it in there anyway
    lng1 = lng1 + 1
    lng2 = lng2 + 1
    lng3 = lng3 + 1
End Sub

Must you change the actual value of the variables? You could also use a collection to do something like this.

Public Sub TestAddCol()
    Dim lng1 As Integer
    Dim lng2 As Integer
    Dim lng3 As Integer
    Dim colVar As New Collection
    Dim i As Long, j As Variant

    lng1 = 10
    lng2 = 100
    lng3 = 1000

    colVar.Add lng1, "lng1"
    colVar.Add lng2, "lng2"
    colVar.Add lng3, "lng3"

    Debug.Print "***** TestAddCol() START *****"
    
    
    Debug.Print "colVar(lng1) = " & colVar("lng1")
    Debug.Print "colVar(lng2) = " & colVar("lng2")
    Debug.Print "colVar(lng3) = " & colVar("lng3")

    Dim lngTemp As Long
    For i = 1 To 5
    Debug.Print "*********** i = " & i & " **************"
        For j = 1 To 3
            lngTemp = colVar("lng" & j)
            colVar.Remove "lng" & j
            colVar.Add lngTemp + 1, "lng" & j
            Debug.Print "colVar(lng" & j & ") = " & colVar("lng" & j)
        Next j
    Next i
    Debug.Print "*********** i END **************"

    Debug.Print "*********** Variable BEFORE **************"
    'Original variables were not changed
    Debug.Print "lng1 = " & lng1
    Debug.Print "lng2 = " & lng2
    Debug.Print "lng3 = " & lng3
        
    'Set to Collection values
    lng1 = colVar("lng1")
    lng2 = colVar("lng2")
    lng3 = colVar("lng3")

    Debug.Print "*********** Variable AFTER **************"
    'Original variables were now changed
    Debug.Print "lng1 = " & lng1
    Debug.Print "lng2 = " & lng2
    Debug.Print "lng3 = " & lng3
    
    Debug.Print "***** TestAddCol()  END  *****"

End Sub

EDIT: You can find a sample file in the article here.

 

[raskew]

Sergeant and Cosmos75 -

Thanks to both of you for your quick responses. What I hadn't mentioned was that I was attempting to prepare a whiz-bang response to an interesting thread and unexpectedly ran up on a serious senior-moment where I absolutely was lost for a solution.

See here: http://www.access-programmers.co.uk/forums/showthread.php?p=502053#post502053

How 'bout an array? Well, duh-huh, who would've thought? Sergeant, went with this 'cause it worked with a minimum of coding. Cosmos75, I'm currently studying your solutions and they bring up some interesting concepts that I think will be useful in a variety of situations. Definitely a keeper!

Thanks again - Bob