Results 1 to 6 of 6

Thread: Creating Table Data in VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Posts
    879
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating Table Data in VBA

    I am building a workbook as an add-in that will provide specific site information such as address, phone, etc.

    I'd prefer to not store that data in a sheet, and instead would like to store it in VBA, since it doesn't change.

    What's the best way to do that? In an ideal world, I'd love to write a statement of some kind that has all the data in a single line, and then reference it's location in that line through code.

    So, if "Chicago" was location 1, and "Dallas" was location 2, an example of the data would be:

    1234 Main Street, Chicago, Illinois, 60050, 815-555-5555
    5678 Main Street, Dallas, TX, 75231, 214-555-5555

    Then in the code, if I wanted to reference the State for the "Chicago" entry, I would call line 1, variable 3. If I wanted to reference the phone number for the "Dallas" entry, I would call Line 2, variable 4, and so on.

    Any thoughts on the best way to do that? or any suggestions?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Creating Table Data in VBA

    Maybe something like
    Code:
    Sub BrianExcel()
       Dim Ary As Variant
       Ary = Array(Array("1234 Main Street", "Chicago", "Illinois", "60050", "815-555-5555"), _
                  Array("5678 Main Street", "Dallas", "TX", "75231", "214-555-5555"))
       MsgBox "Chicago state is " & Ary(0)(2)
       MsgBox "Dallas Phone is " & Ary(1)(4)
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    879
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Table Data in VBA

    This is great Fluff - thanks so much. I'll try that.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Creating Table Data in VBA

    You're welcome & thanks for the feedback.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    879
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Table Data in VBA

    Follow Up Question:

    I have the Arrays (about 50 of them, all named individually and "dimmed" as variants), in a module. All are currently under one Sub called "Sub LocationArrays".

    What I am trying to make happen, is when I click a Radio Button on Userform called "Dallas", or "Chicago", or "Detroit", I am then passing that value as a variable to the Sub in the Module.

    At the end of the module, I am then trying to populate textboxes with specific location information based on the value passed, using the defined array.

    Here is the code I am employing, in order:

    Code:
    Sub optLocation1_Click()Dim cntl As Control
    Dim sLocationName As String
    
    
    For Each cntl In UserForm1.frmLocations.Controls
        If TypeName(cntl) = "OptionButton" Then
            If cntl.Value = True Then
                sLocationName = Replace(cntl.Caption, " ", "")
            End If
            Exit For
        End If
    Next cntl
    
    
    Call LocationArrays(sLocationName)
    
    
    End Sub
    (The next code is the start of the sub inside a module)

    Code:
    Sub LocationArrays(LocationName As String)Dim Detroit as Variant
    
    Detroit = Array(Array("1234 Main Street", "Detroit", "MI", "48326", "(248) 555-5555", "CST", "001"))
    
    With UserForm1
        .txtAddress1 = LocationName(0)(0)
        .txtPhone1 = LocationName(0)(0)
        .txtCity1 = LocationName(0)(1)
        .txtState1 = LocationName(0)(2)
        .txtZip1 = LocationName(0)(3)
        .txtPhone1 = LocationName(0)(4)
        .txtTimeZone1 = LocationName(0)(5)
        .txtLocalTime1 = Time '+ 1 / 24
        .txtSiteID1 = LocationName(0)(6)
    End With
    
    
    End Sub

    When I run the code, I have confirmed the variable (in this case, "Detroit"), is being passed correctly. But when the code gets to the populating of the userform, I get an error message "Compile Error: Expected Array".

    Any idea what I can do to get the proper values pass from the written array back into the userform textboxes?

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,983
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Creating Table Data in VBA

    In that case you're probably better off using a dictionary
    Code:
    Sub LocationArrays(LocationName As String)
       Dim dic As Object
       
       Set dic = CreateObject("scripting.dictionary")
       dic.Add "Detroit", Array("1234 Main Street", "Detroit", "MI", "48326", "(248) 555-5555", "CST", "001")
       dic.Add "Dallas", Array("5678 Main Street", "Dallas", "TX", "75231", "214-555-5555")
    
    With UserForm1
        .txtAddress1 = dic(LocationName)(0)
        .txtPhone1 = dic(LocationName)(4)
    '    .txtCity1 = LocationName(0)(1)
    '    .txtState1 = LocationName(0)(2)
    '    .txtZip1 = LocationName(0)(3)
    '    .txtPhone1 = LocationName(0)(4)
    '    .txtTimeZone1 = LocationName(0)(5)
    '    .txtLocalTime1 = Time '+ 1 / 24
    '    .txtSiteID1 = LocationName(0)(6)
    End With
    
    
    End Sub
    But everytime you click the option button you're reloading the dictionary.
    You'd be better of loading the dictionary in the Userform Initialise event & keeping all the code in the userform module
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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