Creating Table Data in VBA

[BrianExcel]

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?

 

[Fluff]

Maybe something like

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

 

[BrianExcel]

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

 

[Fluff]

You're welcome & thanks for the feedback.

 

[BrianExcel]

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:

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)

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?

 

[Fluff]

In that case you're probably better off using a dictionary

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