[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


How to store data of different types in the same array

Published on
5,762 Points
6 Endorsements
Last Modified:
Martin Liss
Over 40 years of programming experience. Expand my "Full Biography" to see links to some articles I've written.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes a way to elegantly solve the problem.

The basics

Just so that we're all on the same page, let's assume you want to do something like the situation described above but you don't have Access available to you to hold the data in a table or you don't want to have to deal with the overhead, or you can't use a sheet in Excel, or you don't know how to use a collection class, and so to start you might create an array to hold a variable number of customer names like this,

Dim MyArray()

Open in new window

but you are a good programmer and/or you have read my Guide to Writing Understandable and Maintainable Code article and so you would do it more like this.

Dim strCustomerArray() As String

Open in new window

You could then create a second array called something like dblSales to hold the sales figures. Then through some process you would fill both arrays and when you wanted to create some sort of report you would loop through both arrays to get the data. That would work, but there's a better way.

A better way

The two arrays described above are of type String and Double respectively, What many people are not aware of, and the basis of this better way, is that the programmer can create his own type. This is done in the Declarations section (and must be done there) at the top of a UserForm, Form, Sheet, or Code Module. For our purposes we can create our type as follows

Private Type CustomerData ' May be Public in a code module
    strCustName As String
    dblSales As Double
End Type

Open in new window

Your type can contain any number of built-in types and once created it can be used exactly like any of the built-in types, and so you can do the following in a procedure.

Dim CustData() As CustomerData

Open in new window

To fill the array you could do something like the following where intEntry is a variable you would increment while filling the array. (This article assumes you know how to Redim the array to initialize it and Redim Preserve it to add more entries.)

CustData(intEntry).strCustName = ...
CustData(intEntry).dblSales = ...

Open in new window

Rather than incrementing intEntry you can do it this way

CustData(UBound(CustData)).strCustName = ...
CustData(UBound(CustData)).dblSales = ...

Open in new window

and not have to worry about incrementing a variable. Of course to get the data you would do something like the following.

blah1 = CustData(intEntry).strCustName
blah2 = CustData(intEntry).dblSales

Open in new window



One array versus 2 arrays or 6 or 10, maybe not a big deal, but to me it is more elegant and somewhat more clear as to what is going on.
If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. Thanks!
Author:Martin Liss

Expert Comment

The suggestion using UBound is incorrect.  With the code like this:
  CustData(UBound(CustData)).strCustName = ...
  CustData(UBound(CustData)).dblSales = ...
the code will create a new entry with the first assignment, causing UBound to increment. So the second line will refer to yet another new entry.  You need to assign both values with one assignment.
LVL 55

Author Comment

by:Martin Liss
I'm sorry but that is not correct. In VB6, once an array is created the only ways to increase the upper bound of the array is through Dim, Redim and Redim Preserve. If you place this bit of code in a new project you'll see that I'm correct.

Option Explicit

Private Type CustomerData
    strCustName As String
    dblSales As Double
End Type
Private Sub Form_Load()
Dim CustData() As CustomerData
Dim lngIndex As Long

ReDim CustData(2)

CustData(0).dblSales = 0
CustData(0).strCustName = "Customer 0"

CustData(1).dblSales = 100
CustData(1).strCustName = "Customer 1"

MsgBox "The upper bound of CustData is " & UBound(CustData)

CustData(UBound(CustData)).dblSales = 200
CustData(UBound(CustData)).strCustName = "Customer 2"

MsgBox "The upper bound of CustData is still " & UBound(CustData)

For lngIndex = 0 To UBound(CustData)
    Debug.Print CustData(lngIndex).strCustName & " has sales of " & CustData(lngIndex).dblSales
End Sub

Open in new window


Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month