Use Value of Dynamically Created Variable

[Connie]

I know this is easy, but I can't figure out the syntax to use the actual value of a variable created dynamically in a For loop. See below. Any help would be appreciated. Thanks!

Dim CHT_1, CHT_2, CHT_3, CHT_4 as Integer
Dim VariableName as String

CHT_1 = 5
CHT_2 = 6
CHT_3 = 7
CHT_4 = 8

For i = 1 to 4
VariableName = "CHT_" + CStr(i)
Can't figure out syntax to use actual value of variable name
Next i

 

[joeu2004]

CHT_1 = 5
CHT_2 = 6
CHT_3 = 7
CHT_4 = 8
For i = 1 to 4
VariableName = "CHT_" + CStr(i)
Can't figure out syntax to use actual value of variable name
Next i

For i = 1 To 4
VariableName = "CHT_" & i
MsgBox VariableName
Next i

That simply displays the variable names CHT_1, CHT_2, etc. I'm sure Connie
wants the respective values 5, 6, etc. And she might also want to assign
values to indirect variable names.

Effectively, I think Connie wants the VBA equivalent of Excel INDIRECT.

Doing a Google search for "vba indirect variable reference" without quotes,
it appears that VBA does not have any such feature. Some of the
work-arounds might or might not be useful, to wit:


1. Use an array CHT instead of individual variables. That is:

Dim CHT(1 to 4) As Long
For i = 1 to 4
MsgBox CHT(i)
Next


2. Use a class to declare each variable and individuals Get and Let methods,
and use the VBA CallByName function to specify the appropriate method
mnemonically.


A non-work-around: the AddressOf operator. That seems to work only for
procedure names, just like CallByName.

 

[GS]

I fail to see anything 'dynamic' here since 'everything' is hard-coded!

Perhaps if your values were stored in a worksheet range (which could be
dynamic if desired) so they can be entered on-the-fly (ergo 'dynamic'!)
then your code won't need as much maintenance going forward...

Example 1: store values in a single row

Name: "Sheet1!MyValues"
RefersTo: =OFFSET($A$1,0,0,1,COUNTA($1:$1))
Content: [A1] 5, [B1] 6, [C1] 7, [D1] 8


Code example:

Dim vValues, n&
vValues = Range("MyValues")

For n = LBound(vValues, 2) To UBound(vValues, 2)
Debug.Print vValues(1, n)
Next 'n


Example 2: store values in a single column

Name: "Sheet1!MyValues"
RefersTo: =OFFSET($A$1,0,0,COUNTA($A:$A),1)
Content: [A1] 5, [A2] 6, [A3] 7, [A4] 8


Code example:

Dim vValues, n&
vValues = Range("MyValues")

For n = LBound(vValues) To UBound(vValues)
Debug.Print vValues(n, 1)
Next 'n


Example 3: store values in multiple rows/columns
Location: $A$1:$C$4
Name: "Sheet1!MyValues"
RefersTo: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Dim vValues, n&, j&
vValues = Range("MyValues")

Code example:

(column values row by row)
For n = LBound(vValues) To UBound(vValues)
For j = LBound(vValues, 2) To UBound(vValues, 2)
Debug.Print vValues(n, j)
Next 'j
Next 'n

(row values column by column)
For n = LBound(vValues, 2) To UBound(vValues, 2)
For j = LBound(vValues) To UBound(vValues)
Debug.Print vValues(n, j)
Next 'j
Next 'n

I recommend using ranges outside the data area (above or to the left).
Optionally, you can store the data on a separate sheet (which could
also be hidden, if desired) but must ref that sheet in code...

vValues = Sheets("Sheet2").Range("MyValues")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Connie]

Thank you so much Garry. The code snippet I provided was an example, as the actual code is too much to post here, I believe. I really like your idea, though, and am going to incorporate it into the code. What I meant by dynamic is that the variable name is dynamic. I still would like to know, ifI create the name of a variable dynamically as a string, how do I then retrieve the actual value of the variable? Ages ago, we used to use somethinglike Temp = &VariableName to store the value of the variable "VariableName", but I don't believe that was Visual Basic.

 

[GS]

Thank you so much Garry. The code snippet I provided was an example,

as the actual code is too much to post here, I believe. I really
like your idea, though, and am going to incorporate it into the code.
What I meant by dynamic is that the variable name is dynamic. I
still would like to know, if I create the name of a variable
dynamically as a string, how do I then retrieve the actual value of
the variable? Ages ago, we used to use something like Temp =
&VariableName to store the value of the variable "VariableName", but
I don't believe that was Visual Basic.

You're welcome! Thanks for the feedback...

<FWIW>
Variables are declared components of code in VB[A], as in most
languages. You can ref them 'as declared' and assign values to them
that can be retrieved later as your intent indicates, but not the same
way as CallByName works (as joeu explains) because variables do not
have string names. To access a variable in this way it has to be
declared as a property of a class (as joeu suggests) because the
CallByName function only works for procedures that are defined as
public methods (of a class), and properties of a class that are defined
with public scope.

You can define your variables as UDTs also, but you must ref them
individually same as you must with your code sample. IMO, the most
efficient way is to use an array to access the values in a loop as I
exampled. If the values are fixed (ie: never change) then you can hard
code them and use something like this...

Dim vValue, n&
Const MyValues$ = "5,6,7,8"

For Each vValue In Split(MyValues, ",")
Debug.Print vValue 'string
Debug.Print CLng(vValue) 'long integer
Debug.Print CInt(vValue) 'integer
Next 'vValue

-OR-

vValue = Split((MyValues, ",")
For n = LBound(vValue) To UBound(vValue)
Debug.Print vValue(n) 'string
Debug.Print CLng(vValue(n)) 'long integer
Debug.Print CInt(vValue(n)) 'integer
Next '

...where vValue is treated as a Variant because no 'Type' was declared.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Connie]

On Sunday, December 1, 2013 11:19:37 PM UTC-5, GS wrote:
What an awesome response and explanation. I get it now. Thank you!

Thank you so much Garry. The code snippet I provided was an example,

as the actual code is too much to post here, I believe. I really

like your idea, though, and am going to incorporate it into the code.

What I meant by dynamic is that the variable name is dynamic. I

still would like to know, if I create the name of a variable

dynamically as a string, how do I then retrieve the actual value of

the variable? Ages ago, we used to use something like Temp =

&VariableName to store the value of the variable "VariableName", but

I don't believe that was Visual Basic.

You're welcome! Thanks for the feedback...



<FWIW>

Variables are declared components of code in VB[A], as in most

languages. You can ref them 'as declared' and assign values to them

that can be retrieved later as your intent indicates, but not the same

way as CallByName works (as joeu explains) because variables do not

have string names. To access a variable in this way it has to be

declared as a property of a class (as joeu suggests) because the

CallByName function only works for procedures that are defined as

public methods (of a class), and properties of a class that are defined

with public scope.



You can define your variables as UDTs also, but you must ref them

individually same as you must with your code sample. IMO, the most

efficient way is to use an array to access the values in a loop as I

exampled. If the values are fixed (ie: never change) then you can hard

code them and use something like this...



Dim vValue, n&

Const MyValues$ = "5,6,7,8"



For Each vValue In Split(MyValues, ",")

Debug.Print vValue 'string

Debug.Print CLng(vValue) 'long integer

Debug.Print CInt(vValue) 'integer

Next 'vValue



-OR-



vValue = Split((MyValues, ",")

For n = LBound(vValue) To UBound(vValue)

Debug.Print vValue(n) 'string

Debug.Print CLng(vValue(n)) 'long integer

Debug.Print CInt(vValue(n)) 'integer

Next '



..where vValue is treated as a Variant because no 'Type' was declared.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion







---

This email is free from viruses and malware because avast! Antivirus protection is active.

http://www.avast.com