WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Type

VBA Type

Type is a statement in VBA which is used to define variables similar to the DIM function, it used at the user-defined level where we have one or more values in a variable, there are two nomenclature for type statement which is public or 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.

By declaring the 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 one of the earlier articles, we have discussed the “VBA ENUM” to group together all the variables under the single group name.

VBA Type

For example, if you have a group name called “Mobiles,” we have group members like “Redmi, Oppo, Vivo, Samsung, LG and etc..” So Enum statement we can group together 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. In order to group together variables 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…

Syntax

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 types of statements can be declared within the module as well as at the top of the module, like our Global Variables in VBA.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

Type Statement Example in VBA

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

Ok, let start the process of declaring variables with the 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 the Type of group.

Code:

Type MobileBrands

End Type

VBA Type Example 1

Step 2: In Mobile Brands, what are the things we usually see. We see Name 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 Launch date. 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: The next thing is 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 by declaring the variable as Type, Name, i.e., MobileBrands, we can access all these variable data types.

Step 7: Create a subprocedure.

Code:

Sub Type_Example1()

End Sub

VBAType Example 1-6

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

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 box 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

Now 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 subprocedure.

VBA Types vs. VBA Class

VBA Type is 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 contains 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.

Recommended Articles

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 –

  • VBA Data Type
  • Excel VBA UCase
  • Next Loop using VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Type Statement Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More