Creating Table Data in VBA

BrianExcel

Well-known Member
Joined
Apr 21, 2010
Messages
975
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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top