Is there a way to reference a variable in VBA via a concatenation of the name?

[Andrew Chanter]

The snippet of code below shows the long way to set a variable (lngRow) with
the value of a corresponding constant, based on a counter value. Is there a
way I could set lngRow without resorting to the Case statement by
introducing the value of J into the variable name like this: "Sess" & J &
"Row". Is there a way this can be achieved?

Select Case J
Case 1
lngRow = Sess1Row
Case 2
lngRow = Sess2Row
Case 3
lngRow = Sess3Row
Case 4
lngRow = Sess4Row
End Select

[Larry Linson]

Check the EVAL function -- it should provide what you need.

Larry Linson
Microsoft Access MVP

"Andrew Chanter" <he****@radsolutions.com.au> wrote in message
news:8i********************@news-server.bigpond.net.au...

The snippet of code below shows the long way to set a variable (lngRow) with the value of a corresponding constant, based on a counter value. Is there a way I could set lngRow without resorting to the Case statement by
introducing the value of J into the variable name like this: "Sess" & J &
"Row". Is there a way this can be achieved?

Select Case J
Case 1
lngRow = Sess1Row
Case 2
lngRow = Sess2Row
Case 3
lngRow = Sess3Row
Case 4
lngRow = Sess4Row
End Select

[Neil Ginsberg]

You could use an array of values:

Dim SessRow(1 to 4) as Long

SessRow(1)=9494
SessRow(2)=394894
SessRow(3)=3483
SessRow(4)=309390

lngRow= SessRow(J)

Or you could use the SessRow(J) value directly in code without assigning it
to lngRow.

Neil

"Andrew Chanter" <he****@radsolutions.com.au> wrote in message
news:8i********************@news-server.bigpond.net.au...

The snippet of code below shows the long way to set a variable (lngRow)
with
the value of a corresponding constant, based on a counter value. Is there
a
way I could set lngRow without resorting to the Case statement by
introducing the value of J into the variable name like this: "Sess" & J &
"Row". Is there a way this can be achieved?

Select Case J
Case 1
lngRow = Sess1Row
Case 2
lngRow = Sess2Row
Case 3
lngRow = Sess3Row
Case 4
lngRow = Sess4Row
End Select