How to use VBA Structures

This tutorial will demonstrate how to use structures in VBA.

A structure in VBA is essentially a user-defined data type. When we use variables in VBA, we can declare them a string variable, number variables etc. Creating a structure essentially creates a new variable type, the structure itself can contain multiple data types.   Many uses of Excel use class modules instead of structures but that can mean you end up with an unnecessary amount of individual class modules.

Defining a Structure

A structure begins with the word Type and ends with End Type.

Type Customer
 CustName As String
 CustPhone As String
 CustAddress As String
 CustRep As String
End Type

To use this structure within a procedure, we can do as follows:

Sub CustTest ()
 Dim strC As Customer
 strC.CustName = "Fred Jones"
 strC.CustAddress = " 123 Oak Lane, Cleveland"
 strC.CustPhone = "4589341"
 strC.CustRep = "Jim"
End Sub

Where a type is essentially a way of grouping variables together that are all related to each other, such as the customer details above.

A structure needs to be stored in a module and needs to be at the top of the module where module variables would be stored. 

A structure cannot be stored in a class module or form module – if you were to store the type in the class module and then tried to call the type from a module or even from within that class, you would end up with an error occurring.

Using a Structure to get Data from a Range

In our workbook, we have the following data table:

We can now create a structure in a new module to store the Employee Name, Age and Job Description.

Type Employees
 EmpName As String
 EmpAge As Integer
 EmpJob As String
End Type

We can now create a routine to loop through a range in Excel and populate the type with multiple employees.

Sub GetEmployees ()
  Dim Employee() As Employees
  Dim i As Integer
  ReDim Employee(10)
  For i = 0 To 9
    Employee(i).EmpName = Range("B" & i + 2)
    Employee(i).EmpAge = Range("C" & i + 2)
    Employee(i).EmpJob = Range("D" & i + 2)
  Next i
'show in immediate window
  For i = 0 To 9
    Debug.Print (Employee(i).EmpName & " is " & Employee(i).EmpAge & " old and is employed as a " & Employee(i).EmpJob)
  Next i
End Sub

First we declare a variable array as an Employees variable.  We then size the array and loop through a range of cells in an Excel workbook to populate the array.  We then output the results to the debug (immediate) window.

Nested Structures

Consider the following structures:

Now, lets as a third structure below these 2:

Type Invoice
  InvRep As Employees
  InvCust As Customer
End Type

Where the InvRep variable has been defined as being an Employees variable type, while the InvCust variable has been defined as being an Customer variable type.

We can therefore drill down in our procedure to populate some variables.

When we create our procedure, a drop down list will appear showing the variables available in each type.

We can therefore populate our variables as shown in the following procedure below:

Sub GetInfo()
  Dim strInv As Invoice
  strInv.InvCust.CustName = "Bob Smith"
  strInv.InvCust.CustAddress = "123 Oak Lane, Cleveland"
  strInv.InvRep.EmpName = "Fred Lindburgh"
  strInv.InvRep.EmpJob = "Sales Manager"
End Sub