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
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
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 EnumValue1 EnumValue2 EnumValue3 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 procedures / functions||
|Need to be defined using the Set statement||
|Can be declared in any module||
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.
Are 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.