Creating Table Data in VBA

BrianExcel

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

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,032
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,032
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.
 

BrianExcel

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,032
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,253
Messages
5,449,262
Members
405,560
Latest member
Jadax

This Week's Hot Topics

Top