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.
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 7 |
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 |
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!
Learn More!
