Return to VBA Code Examples

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.

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

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. 

VBAStructure Module

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.

VBAStructure Error

Using a Structure to get Data from a Range

In our workbook, we have the following data table:

VBAStructure DataTable

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

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

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.

VBAStructure Example

Nested Structures

Consider the following structures:

VBAStructure Show Structures

Now, lets as a third structure below these 2:

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.

VBAStructure Show variables

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

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!