VBA Type

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Type is a statement in VBA used to define variables similar to the DIM function. We may use it at the user-defined level where we have one or more values in a variable. There are two nomenclature for type statements, which are public and private. However, these are optional to use, but the variable name and the element name are required.

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 defined type name.

We can avoid using Class modules in VBAClass Modules In VBAUsers have the ability to construct their own VBA Objects in VBA Class Modules. The objects created in this module can be used in any VBA project.read more by declaring the Type statement. It does not need any string modules because we can embed it into existing modules, saving us space.

In one of the earlier articles, we discussed the “VBA ENUMVBA ENUMENUM or enumerations are pre-defined enumerations by users in every programming language. In VBA Vbnewline is an enumeration and we can make our own enumerations using ENUM statement.read more” to group all the variables under a single group name.

VBA Type

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Type (wallstreetmojo.com)

For example, if you have a group called “Mobiles,” we have group members like Redmi, Oppo, Vivo, Samsung, LG, etc. So, the Enum statement we can group with their respective values.

Enum Mobiles

Redmi = 12000

Oppo = 18000

Vivo = 18000

Samsung = 25000

LG = 15000

End Enum

Like this, we have created enumerations in that article. The problem with the Enum statement as it can hold only a LONG data type. We can use the VBA TYPE Statement to group variables with different data types. This article will show you how to construct a Type statement in VBA. Read on.

Syntax

Before you declare variables by using a 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 statements can be declared within and at the top of the module, like our Global Variables in VBA.

VBA Type can hold object variables. It can hold arrays. However, it cannot contain procedures or functions.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Type Statement Example in VBA

You can download this VBA Type Statement Template here –  VBA Type Statement Template

Let us start the process of declaring variables with the Type statement. Then, we will see the same example of declaring mobile brands as we used in VBA Enum.

Step 1: At the top of the module, start the word “Type” and give a name to the Type of group.

Code:

Type MobileBrands

End Type
VBA Type Example 1

Step 2: What are the things we usually see in mobile brands? We see names first so declare the variable as Name as String.

Code:

Type MobileBrands

   Name As String

End Type
VBA Type Example 1-1

Step 3: After the name, we check the LaunchDate. Declare the variable as LaunchDate as Date.

Code:

Type MobileBrands

   Name As String
   LaunchDate As Date

End Type
VBA Type Example 1-2

Step 4: Next, we check storage capacity to declare the variable as storage as Integer.

Code:

Type MobileBrands

  Name As String
  LaunchDate As Date
  Storage As Integer

End Type
VBA Type Example 1-3

Step 5: The next thing is we check RAM capacity.

Code:

Type MobileBrands

  Name As String
  LaunchDate As Date
  Storage As 
  RAM As Integer

End Type
VBA Type Example 1-4

Step 6: At last, we check about the price.

Code:

Type MobileBrands

  Name As String
  LaunchDate As Date
  Storage As Integer
  RAM As Integer
  Price As Long

End Type
VBAType Example 1-5

Now, in the sub procedure, we can access all these variable data types by declaring the variable as Type, Name, i.e., MobileBrands.

Step 7: Create a subprocedure.

Code:

Sub Type_Example1()

End Sub
VBAType Example 1-6

Step 8: Now, declare the variable “Mobile” as MobileBrands.

Code:

Sub Type_Example1()

  Dim Mobile As Mob

End Sub
VBAType Example 1-7

Step 9: Now, with the variable name “Mobile,” we can access all the variables of “MobileBrands.”

Code:

VBA Type Example 1-8

Step 10: Now, store each value like the below.

Code:

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
VBAType Example 1-9

Finally, show the result in a VBA message boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more like the below one.

Code:

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
VBAType Example 1-10

Run the code using the F5 key or manually and see the result in a message box.

VBA Type Example 1-10

Like this, we can use the VBA Type statement to define new data types in the sub procedure.

VBA Types vs. VBA Class

Often, VBA Type compares to VBA Class modules. However, there are certain differences between them. Below are the common differences.

Recommended Articles

This article is a guide to VBA Type. Here, we learn how to construct a Type statement in VBA to define variables, practical examples, and a downloadable template. Below you can find some useful Excel VBA articles:-