Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using string to reference a variable (Excel Office 2003)

    Hi Guys

    I am sure this has been asked before but a search didn't turn up anything.

    Is there a way to use a string derived by concatenation & to refer to a variable which has already been declared with the resulting name.

    eg
    Dim Value1 as integer
    Dim N as variant
    N=1
    'then I want to set Value1 to 100
    'eg
    "Value" & N = 100

    this obviously doesn't work but how do I do it
    I tried Evaluate but that doesn't seem to work

    Any Ideas

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using string to reference a variable (Excel Office 2003)

    Evaluate applies to strings that contain an Excel cell reference or range name, not VBA variables.

    Instead of using Value1, Value2, etc., define an array:

    Dim arrValues(1 To 10) As Long
    Dim N As Long

    For N = 1 To 10
    arrValues(N) = ...
    Next N

    You can get the value of an array element:

    N = 7
    MsgBox arrValues(N)

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using string to reference a variable (Excel Office 2003)

    Thanks for the reply Hans

    Yes I have done that in the past where it was applicable.

    My problem at the moment is not as simple as my example suggests as there is dynamic string information it there as well.

    I have also often wanted to use something like this with other dynamic problems.

    It woul be great if it could be done ! ?

    Thanks again

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using string to reference a variable (Excel Office 2003)

    Access VBA has an Eval function, but this is not available in Excel.

    Can't you store the dynamic info in cells in a worksheet (which might be hidden)? The Evaluate function can work with cell references as strings that can be constructed dynamically: Evaluate("Sheet1!D" & N).Value

    If you have userforms, you can refer to controls dynamically: UserForm1.Controls("TextBox" & N).Text

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using string to reference a variable (Excel Office 2003)

    I am not sure, but maybe the CallByName function can do it (I doubt it though)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Using string to reference a variable (Excel Office 2003)

    You can do this (sort of ) with CallByName, if you want to go to the trouble. CallByName function only works with object references. So you'd need to create a class module ("Class1" in example) for this purpose. Simple example:

    Option Explicit

    Private Long1 As Long
    Private Long2 As Long
    Private Long3 As Long

    Public Property Get Proc1() As Long
    Proc1 = Long1
    End Property

    Public Property Let Proc1(ByVal lngNewVal As Long)
    Long1 = lngNewVal
    End Property

    Public Property Get Proc2() As Long
    Proc2 = Long2
    End Property

    Public Property Let Proc2(ByVal lngNewVal As Long)
    Long2 = lngNewVal
    End Property

    Public Property Get Proc3() As Long
    Proc3 = Long3
    End Property

    Public Property Let Proc3(ByVal lngNewVal As Long)
    Long3 = lngNewVal
    End Property

    Test sub (standard code module):

    Public Sub TestCallByName()

    Dim obj As Class1
    Dim ProcName As String
    Dim lngVal
    Dim n As Long

    ' Instantiate object:
    Set obj = New Class1

    ProcName = "Proc"
    lngVal = 10

    For n = 1 To 3
    CallByName obj, ProcName & n, VbLet, lngVal ^ n
    Debug.Print ProcName & n & ": " & CallByName(obj, ProcName & n, VbGet)
    Next n

    Set obj = Nothing

    End Sub

    Sample results printed out:

    Proc1: 10
    Proc2: 100
    Proc3: 1000

    I tested this with an Excel 2000 workbook. Note that you can't directly reference variable names as strings, but can do so indirectly by using Property procedures (can also use functions in similar fashion). You can call subs/functions using the VbMethod constant for the third argument (CallType), in addition to getting/setting property values as shown in example. The optional fourth argument (Args()) is a Variant array that allows you to pass any required arguments to the procedure being called. Anyone interested, refer to VBA Help for CallByName function for more info. (I think there are probably simpler ways to accomplish whatever this is being used for).

    HTH

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using string to reference a variable (Excel Office 2003)

    Thanks Jan Karel

    CallByName did exactly what I wanted.

    Thanks also to Mark, As I was working with objects I did not need a Class module.

    Thanks for the Help Guys

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using string to reference a variable (Excel Office 2003)

    Hi Mark,

    Nice example!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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