What is Type Statement in Excel VBA?
VBA Type Statement is used to define variables under one single group name with different data types assigned to each variable. This helps us to group together multiple variables under a single object to use them under the define type name.
By declaring Type statement we can avoid using Class modules in VBA. It doesn’t need any string modules because it can be embedded into already existing modules which can save us space.
In our one of the earlier articles, we have discussed the “VBA Enumerations” to group together all the variables under the single group name.
For example, if you have the group name called “Mobiles” we have group member like “Redmi, Oppo, Vivo, Samsung, LG and etc..” So Enum statement we can group together with their respective values.
Redmi = 12000
Oppo = 18000
Vivo = 18000
Samsung = 25000
LG = 15000
Like this, we have created enumerations in that article. The problem with the Enum statement was it can hold only LONG data type. In order to group together variable with different data types, we can use “VBA TYPE Statement”. In this article, we will show you how to construct a Type statement in VBA. Read on…
Before you declare variables by using Type statement take a look at the syntax:
Type Group Name [Variable 1] as Variable Data Type [Variable 2] as Variable Data Type [Variable 3] as Variable Data Type [Variable 4] as Variable Data Type [Variable 5] as Variable Data Type End Type
These type of statement can be declared within the module as well as at the top of the module like our Global Variables.
VBA Type can hold object variables, it can hold arrays. However, it cannot contain procedures, functions.
Type Statement Example in VBA
Ok, let start the process of declaring variables with Type statement. We will see the same example of declaring Mobile brands like how we have used in VBA Enum.
Step 1: At the top of the module start the word “Type” and give a name to Type of group.
Type MobileBrands End Type
Step 2: In Mobile Brands what are the things we usually see. We see Name first so declare the variable as Name as String.
Type MobileBrands Name As String End Type
Step 3: After the name, we check the Launch date. Declare the variable as LaunchDate as Date.
Type MobileBrands Name As String LaunchDate As Date End Type
Step 4: Next thing is we check Storage capacity. To declare the variable as Storage as Integer.
Type MobileBrands Name As String LaunchDate As Date Storage As Integer End Type
Step 5: Next thing is we check RAM capacity.
Type MobileBrands Name As String LaunchDate As Date Storage As RAM As Integer End Type
Step 6: At last we check about the Price.
Type MobileBrands Name As String LaunchDate As Date Storage As Integer RAM As Integer Price As Long End Type
Now in the Sub Procedure by declaring the variable as Type Name i.e. MobileBrands we can access all these variable data types.
Step 7: Create a sub procedure.
Sub Type_Example1() End Sub
Step 8: Now declare the variable “Mobile” as MobileBrnads.
Sub Type_Example1() Dim Mobile As Mob End Sub
Step 9: Now with the variable name “Mobile” we can access all the variables of “MobileBrands”.
Step 10: Now store each value like the below.
Type MobileBrands Name As String LaunchDate As Date Storage As Integer RAM As Integer Price As Long End Type Sub Type_Example1() Dim Mobile As MobileBrands Mobile.Name = "Redmi" Mobile.LaunchDate = "10-Jan-2019" Mobile.Storage = 62 Mobile.RAM = 6 Mobile.Price = 16500 MsgBox Mobile.Name & vbNewLine & Mobile.LaunchDate & vbNewLine & _ Mobile.Storage & vbNewLine & Mobile.RAM & vbNewLine & Mobile.Price End Sub
Finally, show the result in a VBA message box like the below one.
Sub Type_Example1() Dim Mobile As MobileBrands Mobile.Name = "Redmi" Mobile.LaunchDate = "10-Jan-2019" Mobile.Storage = 62 Mobile.RAM = 6 Mobile.Price = 16500 MsgBox Mobile.Name & vbNewLine & Mobile.LaunchDate & vbNewLine & _ Mobile.Storage & vbNewLine & Mobile.RAM & vbNewLine & Mobile.Price End Sub
Now run the code using F5 key or manually and see the result in a message box.
Like this, we can use the “VBA Type” statement to define new data type in the subprocedure.
VBA Types vs VBA Class
VBA Type often compared to VBA Class modules. There are certain differences between them. Below are the common differences.
- Difference 1: VBA Type can contain only Public variables. VBA Class can contain both Public as well as Private variables.
- Difference 2: VBA Type cannot contain Procedures and Function. VBA Class contain both of them along with properties.
- Difference 3: VBA Type can be declared in any of the modules and procedures. VBA Class can only be declared in dedicated class modules.
This has been a guide to VBA Type. Here we learn how to construct a Type statement in VBA to define variables along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –