Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    21
    Thanks
    16
    Thanked 1 Time in 1 Post

    Variables with variable names

    Howdy,

    Using VBA, I am wondering if anybody knows how to cycle through variables with their names something like varname1, varname2, varname3, etc. I know about array variables but I am asking something different.

    Example:
    variable name JohnsCar (Johns + x where x = car)
    Variable name JohnsBike (Johns + x where x = Bike)

    Also, is it possible to use arrays within arrays?

    transportation = Array(cars(), bikes(), motorcycles())

    I have nothing to post as a sample. I am just curious if this can be done.

    TIA,
    Nicole

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Nicole,

    As to the arrays within arrays you can accomplish this via multi dimensional arrays but they can get confusing. A better way, IMHO is through the definition of a structure and then create an array of that structure. Here's an example you can work from.
    Code:
    Option Explicit
    
       Type MyStructure
           LName As String
           FName As String
           Cars(5) As String
       End Type
    
    Sub Test()
    
       Dim iCntr        As Integer
       Dim iCarCntr     As Integer
       Dim Database(10) As MyStructure
       
       With Database(0)
           .LName = "Mouse"
           .FName = "Mickey"
           .Cars(0) = "Chevy"
           .Cars(1) = "Ford"
           .Cars(2) = "Dodge"
       End With
       
       With Database(1)
           .LName = "Mouse"
           .FName = "Minnie"
           .Cars(0) = "BMW"
           .Cars(1) = "Roles Royce"
           .Cars(2) = "Mini Cooper"
           .Cars(3) = "Ferrari"
       End With
       
       For iCntr = 0 To UBound(Database) - 1
          If Database(iCntr).LName = "" Then Exit For
          With Database(iCntr)
              Debug.Print "Owner: " & .LName & ", " & .FName
              For iCarCntr = 0 To UBound(.Cars) - 1
                 If .Cars(iCarCntr) = "" Then Exit For
                 Debug.Print "Car No:" & Format(iCarCntr, " 0 ") & .Cars(iCarCntr)
              Next iCarCntr
          End With
       Next iCntr
      
    End Sub  'Test
    Sample Output:
    Code:
    Owner: Mouse, Mickey
    Car No: 0 Chevy
    Car No: 1 Ford
    Car No: 2 Dodge
    Owner: Mouse, Minnie
    Car No: 0 BMW
    Car No: 1 Roles Royce
    Car No: 2 Mini Cooper
    Car No: 3 Ferrari
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Nicole545 (2015-06-22)

  4. #3
    New Lounger
    Join Date
    Nov 2014
    Posts
    21
    Thanks
    16
    Thanked 1 Time in 1 Post
    Retired Geek,

    Thanks for the input using types. They would offer a better way, even better, a class. But still curious if using a multidimensional array where each element was an array itself is possible.

    Any ideas on having the name of a variable include a variable in its name using the example in my post?

    TIA,
    Nicole

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can have arrays of arrays, yes.

    If you need to access a variable by name, you can use a class or you can use something like a Collection or Dictionary which allows you to use a string as the Key for accessing its items.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #5
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The Eval function does that. E.g. Eval ("Johns" & "Car") returns whatever is the value of the variable named JohnsCar.

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Only in Access - it's not a native VBA function.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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