locked
Dynamically Select a Variable Based on Another Variables Contents RRS feed

  • Question

  • I am new to VBA and hoping someone can point me in the right direction ..

    I am trying to use a do loop to make a variable assignment. The variables in this case are all the same but end with a number. Example: test1 - test10. I need to assign a value to each of these ten and want to use the do loop index to select the correct variable. So instead of:

    test1 = somevalue

    test2 = somevalue

    test3 = somevalue       Etc.

    I want to:

    Do I = 1 to 10

    "test" + I = newvalue           ' Assign newvalue to the tag test1

    End Do

    The body of the loop is where I'm lost. I want to somehow use "test" + I to reference test1, test2, etc. and not have to make an assignment for each one. I do not know how to concatenate the two pieces together to be able to reference the desired variable. My example is a very simple one. My actual application is hundreds of lines of code and I need to simplify the program.

    I hope I have sufficiently described my question and thanks in advance for any help!

    Wednesday, December 7, 2011 7:17 PM

Answers

  • Hi VTKeith,

    Add a reference to 'Microsoft Script Control' and then the following will work or at least it does in Excel, not sure about your environment.

     

    ' these need to be public so that execute statement can access them

    Public Var1 As Double

    Public Var2 As Double

    Public Var3 As Double

     

    Private Sub Workbook_Open()

        Dim scriptCtrl As ScriptControl

        Set scriptCtrl = New ScriptControl

        scriptCtrl.Language = "VBScript"

     

        ' add the object containing the public data fields that will be updated

        ' and give it a name so it can be accessed

        scriptCtrl.AddObject "wrkbook", ThisWorkbook, True

     

        ' run the script accessing the public properties

        For i = 1 To 3

            scriptCtrl.ExecuteStatement "wrkbook.Var" & i & " = 5.557"

        Next

     

        MsgBox Var1

        MsgBox Var2

        MsgBox Var3

    End Sub


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    • Proposed as answer by danishani Thursday, January 26, 2012 5:06 AM
    • Marked as answer by danishani Friday, January 27, 2012 7:40 PM
    Monday, January 9, 2012 3:58 PM

All replies

  • On Wed, 7 Dec 2011 19:17:03 +0000, VTKeith wrote:
     
    >
    >
    >I am new to VBA and hoping someone can point me in the right direction ..
    >
    >I am trying to use a do loop to make a variable assignment. The variables in this case are all the same but end with a number. Example: test1 - test10. I need to assign a value to each of these ten and want to use the do loop index to select the correct variable. So instead of:
    >
    >test1 = somevalue
    >
    >test2 = somevalue
    >
    >test3 = somevalue       Etc.
    >
    >I want to:
    >
    >Do I = 1 to 10
    >
    >"test" + I = newvalue           ' Assign newvalue to the tag test1
    >
    >End Do
    >
    >The body of the loop is where I'm lost. I want to somehow use "test" + I to reference test1, test2, etc. and not have to make an assignment for each one. I do not know how to concatenate the two pieces together to be able to reference the desired variable. My example is a very simple one. My actual application is hundreds of lines of code and I need to simplify the program.
    >
    >I hope I have sufficiently described my question and thanks in advance for any help!
     
    You can use an array and then iterate through the elements of an array.  eg:
     
    dim i as long
    dim test(1 to 10) as variant (or string or whatever)
    for i = 1 to 10
        test(i) = ???
    next i
     
    But, in order to make the assignments, you will need to have the various newvalue's accessible someplace.  How are you proposing to do that?
     

    Ron
    Wednesday, December 7, 2011 7:45 PM
  • Thanks for your reply Ron! I should have elaborated in my original posting. I cannot use an array. My variables are test1, test2, etc.. That cannot change. The environment the VBA code is running in does not recognize arrays. I certainly know VBA does but the VBA program I am writing has to access variables that are not arrays. Unfortunately I have over a thousand of these assignments to make.

    I do not want to do this:

    test1 = newvalue1

    test2= newvalue2

    .... test1000 = newvalue1000

    I was hoping to use a do loop of I=1 to 1000 and then somehow append/concatenate the index value to "test" to reference each of the one thousand variables. This would save me thousands of lines of code. My example is simplified for sake of discussion.

    Maybe another way to describe my question is:

    Dim test1 as integer, Dim num as integer

    num = 1

    Somehow concatenate "test" and the value stored in num to reference test1

    If num is 2 then I need to reference test2 and so on.

    If I have the thousand variables the contents of num "points" to which variable to write to.

    Again, thanks for your help! Hopefully, I have provided more information which will make my question clearer!

    Friday, December 9, 2011 3:39 AM
  • On Fri, 9 Dec 2011 03:39:17 +0000, VTKeith wrote:
     
    >
    >
    >Thanks for your reply Ron! I should have elaborated in my original posting. I cannot use an array. My variables are test1, test2, etc.. That cannot change. The environment the VBA code is running in does not recognize arrays. I certainly know VBA does but the VBA program I am writing has to access variables that are not arrays. Unfortunately I have over a thousand of these assignments to make.
    >
    >I do not want to do this:
    >
    >test1 = newvalue1
    >
    >test2= newvalue2
    >
    >.... test1000 = newvalue1000
    >
    >I was hoping to use a do loop of I=1 to 1000 and then somehow append/concatenate the index value to "test" to reference each of the one thousand variables. This would save me thousands of lines of code. My example is simplified for sake of discussion.
    >
    >Maybe another way to describe my question is:
    >
    >Dim test1 as integer, Dim num as integer
    >
    >num = 1
    >
    >Somehow concatenate "test" and the value stored in num to reference test1
    >
    >If num is 2 then I need to reference test2 and so on.
    >
    >If I have the thousand variables the contents of num "points" to which variable to write to.
    >
    >Again, thanks for your help! Hopefully, I have provided more information which will make my question clearer!
     
    What you need is some VBA equivalent of Excel's Indirect command and, to the best of my knowledge, that does not exist.
     
    Even if the calling program does not recognize arrays, could you pass the values in the array to it one at a time, as a simple variable?  So if your calling program wanted test 1, the VBA it called would put test(1) into a variable and pass that?
     
    If not, maybe you could use VBA code to generate the array-less variables.  I don't know much about doing that, but http://www.cpearson.com/Excel/vbe.aspx may have some hints.
     
     

    Ron
    Friday, December 9, 2011 4:07 AM
  • Hi,

    Thinking maybe the Eval method might work for this. This will run a string as a bit of code. If you create a string of the assignment in the loop then run it through Eval it might work.

     http://bytes.com/topic/visual-basic/answers/767159-using-eval-function-treat-string-vba-statement


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    • Edited by Derek Smyth Friday, December 9, 2011 8:46 AM
    Friday, December 9, 2011 8:45 AM
  • Thanks Ron and Derek for your help! I will check out the information you both provided.
    Saturday, December 10, 2011 2:17 AM
  • I just wanted to update my posting. I am still looking for a solution if anyone can help.
    Thursday, December 29, 2011 2:49 PM
  • Hi VTKeith,

     


    Var1 = 0.0
    Var2 = 0.0
    Var3 = 0.0

    Default = 5.557

    For i = 1 to 3
        Execute("Var" & i & " = Default")
    Next

    Msgbox Var1
    Msgbox Var2
    Msgbox Var3


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    • Edited by Derek Smyth Wednesday, January 4, 2012 11:17 AM
    Wednesday, January 4, 2012 11:16 AM
  • Thanks for the follow up Derek!

        I've had no luck finding a solution but it looks like you may have pointed me in the right direction. if Var1, Var2, Var3 all equal 5.557 then this is what I am looking for. But, I am not familiar with the "Execute" function and have not found any description of it. Can you provide some details? I've searched but can't find anything. In the meantime I will try your suggested code in my application.

    Again, thanks Derek. I am in dire need for a solution.

    VTKeith

    Monday, January 9, 2012 2:35 AM
  • Hey VTKeith,

    The code posted before is vbscript, a variation of VBA. If you copy and paste the code into an empty text file and then save it with a .vbs extension then you can double click it to run. All the Var variables are set to the default value. 

    There isn't a great deal of information but here is what I found.

    http://www.aspdev.org/asp/asp-eval-execute/

    http://www.devguru.com/technologies/vbscript/quickref/regexp_execute.html

    http://itknowledgeexchange.techtarget.com/vbscript-systems-administrator/vbscript-statements-explanation-of-the-execute-statement/

    To be honest it's rubbish arrays aren't supported in the environment your using. It's resulted in nasty code. Hopefully the Execute command is supported.  


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    Monday, January 9, 2012 9:40 AM
  • Hi Derek,

       Just wanted to post a quick update. Thanks for the links! I looked them over. As you said, there's not a lot of info there but they were still helpful.

       I tried the "Execute" function and was greeted by an "undefined function or procedure" error message. I am assuming there needs to be a linkage of some sort to make this available. I also tried the "Eval" and got the same results.

    VTKeith

    Monday, January 9, 2012 3:21 PM
  • Hi VTKeith,

    Add a reference to 'Microsoft Script Control' and then the following will work or at least it does in Excel, not sure about your environment.

     

    ' these need to be public so that execute statement can access them

    Public Var1 As Double

    Public Var2 As Double

    Public Var3 As Double

     

    Private Sub Workbook_Open()

        Dim scriptCtrl As ScriptControl

        Set scriptCtrl = New ScriptControl

        scriptCtrl.Language = "VBScript"

     

        ' add the object containing the public data fields that will be updated

        ' and give it a name so it can be accessed

        scriptCtrl.AddObject "wrkbook", ThisWorkbook, True

     

        ' run the script accessing the public properties

        For i = 1 To 3

            scriptCtrl.ExecuteStatement "wrkbook.Var" & i & " = 5.557"

        Next

     

        MsgBox Var1

        MsgBox Var2

        MsgBox Var3

    End Sub


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks
    • Proposed as answer by danishani Thursday, January 26, 2012 5:06 AM
    • Marked as answer by danishani Friday, January 27, 2012 7:40 PM
    Monday, January 9, 2012 3:58 PM
  • Hi VTKeith,

     

    How about the problem on your side? Do you still need any assistant about the problem?

     

    Is the answer given by Derek helpful.

     

    If you still show any concern on the problem, just feel free to let us know.
     
    Best Regards,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, January 20, 2012 11:05 PM
  • Unfortunately Derek's answer does not work in my environment. I am developing HMI (human machine interface) code for use in the automation and controls world. I have found a different method to solve my problem but was never able to resolve or find a solution for my original question. I have had to move on to meet my customer's schedule. Thanks to everyone for their help!

    VTKeith

    Tuesday, February 7, 2012 12:47 AM
  • That is unfortunate. Oh well. Problem was really the lack of array support. 

    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks

    Friday, February 10, 2012 8:42 AM