VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

One of the key features of VBA is its ability to handle different types of data, such as numbers, strings, and Boolean values. However, there are times when the standard data types provided by VBA are not enough to meet the specific needs of the programmer. In such cases, the user-defined data type comes into play. In this blog post, we will explore the syntax, storage, and range of the user-defined data type in VBA and provide the top 5 examples of using it.

VBA DataType ‘User-defined’ – A Powerful Tool for Custom Data Types

Syntax of User-defined Data Type

The user-defined data type is declared using the keyword “Type” followed by the name of the data type. It is followed by a list of variables and their respective data types, enclosed within parentheses. Each variable is separated by a comma. The syntax for declaring a user-defined data type is as follows:

Type DataTypeName
    Variable1 As DataType1
    Variable2 As DataType2
    VariableN As DataTypeN
End Type

It is important to note that the data types used in user-defined data types must be one of the standard VBA data types or other user-defined data types.

Storage and Range of User-defined Data Type

User-defined data types are stored in memory just like other data types. However, they provide more flexibility in terms of range and size. Unlike the standard data types, the user-defined data types allow the programmer to specify the range and size of the data. This makes them particularly useful when dealing with large or complex sets of data. Moreover, user-defined data types can also be used to store multiple values in a single variable, making data management more efficient.

Example VBA Codes using User-defined Data Type

Record Keeping

Let’s say you have a student database in Microsoft Excel, and you want to store the data for each student in a structured manner. You can use a user-defined data type to create a structure for each student’s data, including their name, age, grade, and other relevant information. This will help in easier data management, retrieval, and analysis.

Type Student
    Name As String
    Age As Integer
    Grade As String
    Address As String
End Type

Sub RecordKeeping()
    Dim student1 As Student
    student1.Name = "John Smith"
    student1.Age = 15
    student1.Grade = "10th"
    student1.Address = "123 Main Street"
End Sub

Inventory Management

If you are using VBA to develop an inventory management system, you can use a user-defined data type to store information about each product, including its name, price, quantity, and description. This will help in keeping track of the available products and their details without having to create multiple variables for each attribute.

Type Product
    Name As String
    Price As Double
    Quantity As Integer
    Description As String
End Type

Sub InventoryManagement()
    Dim product1 As Product
    product1.Name = "Laptop"
    product1.Price = 999.99
    product1.Quantity = 10
    product1.Description = "Intel Core i7, 8GB RAM, 256GB SSD"
End Sub

Employee Management

Similar to inventory management, a user-defined data type can also be used to store information about employees in an organization. This can include their name, position, department, and salary. This will make it easier to track and manage employee records without creating multiple variables for each attribute.

Type Employee
    Name As String
    Position As String
    Department As String
    Salary As Long
End Type

Sub EmployeeManagement()
    Dim employee1 As Employee
    employee1.Name = "Jane Smith"
    employee1.Position = "Manager"
    employee1.Department = "Marketing"
    employee1.Salary = 50000
End Sub

Banking System

For a banking system developed in Excel using VBA, user-defined data types can be used to store customer information, such as account number, balance, transaction history, and contact details. This will make it easier to manage and access customer information during transactions or inquiries.

Type Customer
    AccountNumber As Long
    Balance As Double
    TransactionHistory() As String
    ContactDetails As String
End Type

Sub BankingSystem()
    Dim customer1 As Customer
    customer1.AccountNumber = 123456789
    customer1.Balance = 10000
    customer1.TransactionHistory = {"Deposit - $500", "Withdrawal - $200"}
    customer1.ContactDetails = "123-456-7890"
End Sub

Game Development

User-defined data types can also be useful in game development using VBA. For example, for a 2D game, a user-defined data type can be used to store the properties of an object, including its position, size, color, and speed. This will make it easier to manipulate and control the objects within the game.

Type Object
    X As Integer
    Y As Integer
    Height As Integer
    Width As Integer
    Color As String
    Speed As Double
End Type

Sub GameDevelopment()
    Dim player As Object
    player.X = 100
    player.Y = 200
    player.Height = 50
    player.Width = 20
    player.Color = "Red"
    player.Speed = 5
End Sub


In conclusion, the user-defined data type in VBA is a powerful tool that allows programmers to customize and manage their data more efficiently. By specifying the range, size, and structure of the data, they provide greater flexibility in handling complex sets of data. In this blog post, we have explored the syntax, storage, and range of the user-defined data type in VBA, and provided practical examples of using it. By incorporating user-defined data types into your VBA projects, you can take your programming skills to the next level.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA Data TypesLast Updated: September 23, 2023

Leave A Comment