Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing Variables in Code (XL97; SR-2)

    Does anyone know if its possible to pass a text variable within the code where the outcome will result in a number?

    I hope the code below provides a better picture what I'm after.


    Sub age()
    aAGE = Range("Actual") Where actual = 21
    lAGE = Range("LastYr") 'Where lastyr = 20
    Var1 = "a" ' Var1 could be either a or l

    VariableAge = "" & Var1 & "AGE" 'VariableAge should be 21

    End Sub



    Thanks,
    John

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Passing Variables in Code (XL97; SR-2)

    I don't know if there is a function to "construct" a variable name, but there other ways to handle this, including arrays and collections, and if you reference the scripting library, the Dictionary object. Arrays can be accessed using a numeric index (e.g., 0 for first item, 1 for second item); collections and dictionaries can be accessing using textual keys (e.g., Actual for first item, LastYr for second item). Unless there is a reason that this will not work for you, I recommend one of these approaches.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Variables in Code (XL97; SR-2)

    You can't create variables in code, which is what you appear to be trying to do. In fact, all you need to do is something like this:

    Sub age(Var1 As Variant)
    aAGE = Range("Actual") Where actual = 21
    lAGE = Range("LastYr") 'Where lastyr = 20

    If Var1= "a" Then
    VariableAge = aAge
    Else
    VariableAge = IAge
    End If

    End Sub

    It is possible to create a variable that gets assigned the value of another variable (i.e., if Var1="a" then NewVar = aAge: VariableAge = NewVar) but in this case, that appears to be overkill.
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Variables in Code (XL97; SR-2)

    > pass a text variable within the code

    I wrote some code that embedded sub-strings within strings, nested as deeply as you like, that allwoed me to pass a table of symbols (identifiers and values) as a single stringf to a routine that could easily parse it to obtain the values.

    The string device included simple arithmetic operators. (+-*/)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •