Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Value of Dynamically Created Variable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Value of Dynamically Created Variable
Hi Connie,
Am Sun, 1 Dec 2013 08:14:45 -0800 (PST) schrieb Connie: Dim CHT_1, CHT_2, CHT_3, CHT_4 as Integer in the above case only CHT_4 is declared as integer try: Dim CHT_1 As Integer, CHT_2 As Integer Dim CHT_3 As Integer, CHT_4 As Integer Dim i 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_" & i MsgBox VariableName Next i Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Value of Dynamically Created Variable
"Connie" wrote:
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 "Claus Busch" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Value of Dynamically Created Variable
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 --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Value of Dynamically Created Variable
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.
On Sunday, December 1, 2013 3:03:54 PM UTC-5, GS wrote: 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 --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Value of Dynamically Created Variable
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use Value of Dynamically Created Variable
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Radio Buttons Created Dynamically | Excel Programming | |||
delete dynamically created combo box | Excel Programming | |||
How do I call a dynamically created String in a VBA Sub | Excel Programming | |||
dynamically created listbox | Excel Programming | |||
Scaling a chart that has been dynamically created in vb | Charts and Charting in Excel |