RECORDS

Sometimes you must deal with tables of information where each row represents a different sample and each column gives the value of a particular attribute of the sample. In general, each attribute may have different data type.

A classic example would be the elements in a periodic table arranged alphabetically according to the name of each element. The atomic properties for each element are listed in a row. The first column gives the name of the element, the second column gives its atomic symbol, the third column gives its atomic number, the fourth column gives its atomic weight, etc.

One way to handle such data would be to use an array to store each column of information:

Dim ElementName(118) As String
Dim Symbol(118) As String * 3
Dim AtomicNumber(118) As Integer
Dim AtomicWeight(118) As Double
This would work but would be rather clumsy.

Note that one cannot use a two-dimensional array since each column of information has different data types.

VBA allows you to store all the information in a single variable of a user defined type called record using the Type statement. A Type statement has the form:
Type userType
    variableName1 As type1
    variableName2 As type2
    ...
End Type
and it is typically placed in the Public area before any Sub or Function definitions so that every module can use this type.

After establishing the type, you can then dimension an array of such type to define a record as 
Dim recordName(n) As userType
Individual samples of the record are identified by appending a period and the name of the variable as in 
recordName(i).variableName2
The following example shows how information from the above table of atomic properties could be stored in a record:
Option Explicit
Option Base 1
Type AtomicData
    elementName As String
    symbolName As String * 3
    atomicNumber As Integer
    atomicWeight As Double
End Type

Sub atomicInformation()
    Dim info(118) As AtomicData
    Dim msg As String

    info(1).elementName = "Actinium"
    info(1).symbolName = "Ac"
    info(1).atomicNumber = 89
    info(1).atomicWeight = 227.0278
    ...
    msg = "Atomic numer =" & info(1).atomicNumber
    MsgBox Msg, , info(1).elementName
End Sub
When this Sub is run, a message "Atomic number = 89" will be displayed in a message box with the title "Actinium".

The syntax for passing a record to a procedure is similar to that for an array, with one notable modification: the type of record must be specified in the argument list of the procedure. For example, suppose we want to pass info() to another procedure named Display, which will display some of the data in the record info(). Then the Call statement will be (the same way as for an array): 
Call Display(info())
However, the argument of the Sub statement must include the Type specification to inform the procedure that info() is a record: 
Sub Display(info() As AtomicData)