VBA Type

The VBA Type – VBA Custom Type alternative to Class

When thinking about custom objects / types in VBA most users turn to the VBA Class and create additional modules that quickly start cluttering their VBA Projects. Often these turn out to be simply structures, for the sole purpose of grouping several variables within a single object, bereft of any procedures or functions. VBA does not allow you to create subfolders in your VBA Project, so next time consider creating a VBA Type instead!

Below an example VBA Type name Person as well as usage:

'Declaration of Person Type
Type Person
  name as String
  surname as String
  age as Long
End Type

'Example usage of created Type
Sub TestType
  Dim p as Person
  p.name = "Tom"
  p.surname = "Hanks"
  p.age = 54

  Debug.Print p.name & " " & p.surname & ", age " & p.age
  'Result: Tom Hanks, age 54"
End Sub

Other VBA custom objects

What is a VBA Type and why use it?

What is a VBA Type? The Type statement defines a new user-defined data type containing one or more variables (basic VBA data types, custom Types or objects including custom VBA Classes). VBA Types fill the gap between native basic VBA data types such as Strings, Integers, Booleans etc. and custom objects – VBA Classes. Type declarations can only consist of variables.

Why would you want to use a VBA Type? Why use a cannon to shoot a fly? i.e why create a new VBA Class when all you need to is to group several variables into a new data type. One other thing is that Types don’t need separate VBA Modules and are embedded into existing modules, saving you space and preventing VBA Module cluttering.

Syntax of the VBA Type

The VBA Typed can be defined within VBA Modules, at the beginning, similarly like global variables. The syntax of a VBA Type looks like:

Type TypeName
  VariableName [as VariableType]
End Type

VBA Types can:

  • Have object such as other VBA Types or VBA Classes as variables
  • Contain arrays – both static (predefined size) and dynamic (dynamically sized/resized)
  • Be declared within VBA Modules. Types can also be declared within Forms and Classes, however, have to be declared Private (scoped only to Form or Class)

VBA Types can’t:

  • Contain procedures or functions
  • Be declared as Public in Classes and Forms
VBA Types are treated like other types (not objects) hence does not require to be defined using the Set statement. A Type object can be immediately used after it has been declared.

Type SomeType
   num as Long
End Type

Sub TestType()
  Dim myType as SomeType
  Set myType = New SomeType 'WRONG!
  myType.num = 10 'OK!
End Sub

VBA Type examples

Now that we now the basic let’s look at some VBA Type examples. The Type example below is an excellent example of how much you can “squeeze” into a custom VBA Types.

Type CustomType
    someString As String
    threeCharacterStrings As String * 3
    someNumber As Long
    someEnum As CustomEnum 
    someObject As Object
    predefinedSizeArray(10) As Long
    dynamicSizedArray() As Long
End Type

Now let’s use this newly defined VBA Type:

Enum CustomEnum
End Enum

Sub TestCustomType()
    Dim myType As CustomType
    myType.someString = "Hello there!"
    myType.threeCharacterStrings = "Hello!" 'Will be trimmed to "Hel"
    myType.someEnum = EnumValue1
    myType.someNumber = 10
    Set myType.someObject = CreateObject("Scripting.Dictionary")
    myType.predefinedSizeArray(0) = 20
    ReDim myType.dynamicSizedArray(20) As Long
    myType.dynamicSizedArray(1) = 100
End Sub

The VBA Type can basically aggregate any VBA Type within – including objects, classes, enums etc.

VBA Type vs Class

Types are typically compared to VBA Classes. Similarly most VBA users start by creating a class instead of contemplating the use of a simple VBA Type. Let’s thus compare the 2.

Property VBA Type VBA Class
Contains variables
  • Types can contain only Public variables
  • Classes can contain both Public and Private variables
Contains procedures / functions
  • Types cannot contain procedures, functions or properties
  • Classes can contain procedures, functions and properties
Need to be defined using the Set statement
  • Types don’t require the Set statement, thus allocate memory when they are declared
  • Classes need to defined to be used, this don’t allocate memory until they are created and can be unallocated by being Set to Nothing
Can be declared in any module
  • True. Can be declared as Public/Private in VBA Modules and Private in Classes and Forms
  • Classes only be declared in dedicated Class modules
See also  Using SQL in VBA on Excel. Run SELECT Queries from VBA

VBA Type vs Module

Consider the reasons for creating new objects. In general I encourage the creation of Types and Classes as this goes in line with Object-Oriented Programming, however sometimes this is an overkill.
vba moduleAre you creating the new Type / Class to have an easily accessible list of variables? Why not use a VBA Module instead. Simply make sure you variables are Public.

VBA Modules are singletons – objects accessible within the whole project and which variables have a single instance. As you probably know Modules like Classes can have procedures and functions.

VBA Types on the other hand are a simple vehicle of grouping variables into a new data type. Hence, don’t use them as singletons.


Hopefully, now you will consider using VBA Types when pondering a reason for creating a new custom VBA structure. I encourage you to use VBA Types and VBA Classes more often as you will notice how your code becomes more readable, comprehensive and less redundant. Do read my post on writing shorter VBA code if you want to read more tips on VBA best practices.

Comments are closed.