Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Creating Table Data in VBA

  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
    21,731
    Post Thanks / Like
    Mentioned
    354 Post(s)
    Tagged
    36 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 2003 & 2013 on Win 7

  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
    21,731
    Post Thanks / Like
    Mentioned
    354 Post(s)
    Tagged
    36 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 2003 & 2013 on Win 7

  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
    21,731
    Post Thanks / Like
    Mentioned
    354 Post(s)
    Tagged
    36 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 2003 & 2013 on Win 7

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
  •