Let's say you are trying to create a school management system. Here, you will have different types of variables like student name, student roll no, class, etc. A school also has teachers, so there will be teacher names, teacher subject, classes, etc. Similarly, there will be many other objects like, librarian, classes, principle etc. Now having different variables for each entity in school will be messy work. How about creating a data type of student, teacher, classes, etc that stores values related to them. For this we can use defined data types of VBA.
In this article, we will learn how you can create your own data type in VBA. They are referred to as UDTs of VBA.
To define a structure or UDTs in VBA we use Type___End Type block. Here's the syntax of a UDT.
Type Tname_Of_Data_Type var1 as datatype 'datatype can be anything, int, array,or even UDT var2 as datatype Var3() as datatype --- VarN() as datatype End Type
So to define a custom data type in VBA we start with Type Keyword. Then we write the name of our custom data type. It is convention to use T before the Name of Data Type so that you can differentiate between vba collections and UDTs.
The DataTypes can be anything. And Integer, String, Variant, another UDT, Arrays, collections, anything.
To use your UDT in the program declare its variable like any other variable.
Sub UseUDT 'Declaring variable of user defined data type Dim myVar1 as Tname_Of_Data_Type Dim myVar2 as Tname_Of_Data_Type End Sub
Simple. Now to use the variables within this UDT we use dot operator. Use the name of the data type followed by a dot and name of variable within.
Sub UseUDT 'Declaring variable of user defined data type Dim myVar1 as Tname_Of_Data_Type Dim myVar2 as Tname_Of_Data_Type myVar1.var1="Abcd" myVar2.Var2="xyvz" End Sub
Enough of the theory, let's jump into an example to see how it works.
So we have a task to create a user defined data type that stores information related to students.
A student has a first name, last name, roll number, date of birth, class, section, subjects.
So let's create it.
'Created a Public Student Data Type Public Type Tstudent fName As String 'For First Name lName As String 'For Last Name rNo As Integer 'For Roll Number clss As string 'For Class section As String 'For Section Name subjects() As String 'For Subjects of student End Type 'Use this Tstudent type in subroutine Sub StudentsInfo() 'Creating and initializing student type variable Dim student1 As Tstudent student1.fName = "Manish" student1.lName = "Singh" student1.rNo = 12334 student1.clss = 10 student1.section = "A" ReDim student1.subjects(2) student1.subjects(0) = "physics" student1.subjects(1) = "Math" 'Printing student details. Debug.Print (student1.fName) Debug.Print (student1.lName) Debug.Print (student1.rNo) Debug.Print (student1.clss) Debug.Print (student1.section) Debug.Print (student1.subjects(0)) Debug.Print (student1.subjects(1)) End Sub
When you run the above sub it will print the result as shown below:
| Manish
Singh 12334 10 A physics Math |
Creating an Array of UDTs and Accessing Elements
Similarly you can create as many as variables of Tstudent type you need. You can even create an array of Tstudent type like any other data type.
Public Type Tstudent
fName As String 'For First Name
lName As String 'For Last Name
rNo As Integer 'For Roll Number
clss As string 'For Class
section As String 'For Section Name
subjects() As String 'For Subjects of student
End Type
'Creating an arrays of Tstudents type
Sub SchoolInfo()
Dim schoolName As String
Dim students() As Tstudent
schoolName = "Senior School"
ReDim students(10)
For i = 0 To 9
students(i).fName = "name" & Str(i + 1)
students(i).rNo = i + 1
Next i
Debug.Print ("Name : Roll No")
For i = 0 To 9
Debug.Print (students(i).fName & " : " & students(i).rNo)
Next i
End Sub
When you run this code, this will be printed in the immediate window.
| Name : Roll No
name 1 : 1 name 2 : 2 name 3 : 3 name 4 : 4 name 5 : 5 name 6 : 6 name 7 : 7 name 8 : 8 name 9 : 9 name 10 : 10 |
In the above code, first defined UDT structure before and the sub (I'll explain later why). The we just created an array using a dim keyword like we do for any variable in VBA.
Then we used Redim to define the size of arrays. Afterwards we use a for loop to initialize the array.
To access the structure's elements we use another for loop. That is it.
Why Did We Declare UDT on Top of the Module?
If we declare a UDT first in a module, outside of any subroutine or function, it is available to all the modules in the workbook. It means if you have a hundred subs and functions in a module all of them can declare Student type variables in their body.
If the UDT is not private, it will be available to all the modules in the workbook. If want a structure (UDT) to be available only to a containing module, declare it private.
Private Type Tstudent fName As String lName As String rNo As Integer clss As Integer section As String subjects() As String End Type
You can't have UDT on a procedural level. It means you can't define a user defined data type inside a subroutine or function.
Let's say you have UDT called a car. Car has its own elements. Similarly you have a UDT called a bike that can have its own properties.
Now let's say you need a data type called vehicle. Vehicle can have a car and bike as its elements. Can we do this? Yes we can do this. See the below code
Private Type Tcar seats As Integer ac As Boolean typ As String color As String manufacturer As String Dop As Date rc_no As String End Type Private Type Tbike seats As Integer typ As String color As String manufacturer As String Dop As Date rc_no As String End Type Private Type Tvehicle number_of_Vehicle As Integer bike As Tbike car As Tcar End Type Sub vehicleVarification() Dim myVehicles As Tvehicle myVehicles.number_of_Vehicle = 2 myVehicles.bike.seats = 1 myVehicles.bike.typ = "Racing" myVehicles.car.seats = "4" myVehicles.car.ac = True Debug.Print myVehicles.number_of_Vehicle Debug.Print myVehicles.bike.typ Debug.Print myVehicles.car.ac End Sub
Here, we have defined three user defined data type. First is Tcar that contains some information related to cars. Second is bike, it also contains some information about bike.
The third UDT is Tvehicle. It contains one variable to store number of vehicles and two variables of Tcar and Tbike type.
Private Type Tvehicle
number_of_Vehicle As Integer
bike As Tbike
car As Tcar
End Type
To access variables of Tcar and Tbike we can use Tvehicle data type. In the sub, we have defined only one variable of Tvehicle type as myVehicles. When we create this variable VBA creates variables of Tcar and Tbike too.
To initialize and access variables of Tcar and Tcar, we can use myVehicle variable. As you can see in the code.
myVehicles.number_of_Vehicle = 2
myVehicles.bike.seats = 1
myVehicles.bike.typ = "Racing"
myVehicles.car.seats = "4"
myVehicles.car.ac = True
When we run the sub, this how result occurs.
This feature really increases the power of programming VBA exponentially. You can structure your data type like real world entities. You can create relationships between data types which can be useful in a big project.
So yeah guys, this is how you can create and use a user defined data type or structure in VBA. I hope I was able to explain it. If you have any questions regarding this article or any other VBA related questions, ask me in the comments section below. I'll be really happy to hear from you.
Related Articles:
VBA variables in Excel| VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.
Excel VBA Variable Scope| In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.
ByRef and ByVal Arguments | When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.
Delete sheets without confirmation prompts using VBA in Microsoft Excel | Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.
Add And Save New Workbook Using VBA In Microsoft Excel 2016| In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc
Display A Message On The Excel VBA Status Bar| The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering.
Turn Off Warning Messages Using VBA In Microsoft Excel 2016| This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
