Tag Archives: type

VBA Type

The VBA Type – custom variables without procedures

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

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!

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!

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

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 module
Public variables are visible in the VBA Module scope
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.

vba data

Saving your VBA data (VBA data dump)

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 3.67 out of 5)

Sometimes VBA algorithms take a long time to execute and you would want to be able to maintain those calculations in memory when you close and reopen you Excel Workbook or Access Database. Why not save your VBA project data and recover it when reopening your VBA project? Actually you can do just that…

Saving VBA data to binary files

save vba dataThe trick to saving your VBA workspace variables is dump them into a binary file. This is not as complex as it seems, and if you are reading this post you probably already have mastered reading/writing data to text files. Binary files as opposed to text files can store any data type(s), while text files can only contain strings (you might try dumping data as strings and then parsing them, but its not exactly a pleasant job).

If you stumbled across my post on Writing files in VBA (the proper way!) you might be aware of how simple saving data to a binary file is (in this case the “testVar” Integer variable):

Dim fileName As String, fileNo As Integer, testVar As Integer
testVar = 4
fileNo = FreeFile
Open fileName For Binary Lock Read Write As #fileNo
Put #fileNo, , testVar
Close #fileNo

Reading the variable back to your VBA project is equally simple:

Dim fileName As String, fileNo As Integer, testVar As Integer
fileNo = FreeFile
Open fileName For Binary Lock Read As #fileNo
Get #fileNo, , testVar 
Close #fileNo

I hope this demonstrates how easily you can dump your VBA project variables to a binary file and then recover them.

The proper way to dump and recover your VBA data

Now the example above will work fairly well if your variables are of constant byte-length. For example an Integer will require 2 bytes, a Double 8 bytes… No problems yet right? Well issues start when you start dumping Objects and/or Strings. Why? Strings for example are simply a sequence of Characters (bytes). Therefore the string “Hello” will require 5 bytes of space, while “Hello World” will use 11 bytes of space. If you think you can manage this by preceding your strings with an Integer header – you are right. But you will have much more issues with dynamic arrays, Classes and other objects which will require additional headers.

How to manage your VBA project variables then? Encapsulate them using the Type statement. When dumping Types to binary files, the object will include a header with all the information needed to recover the whole Type back to your VBA project. Making a second attempt at the code above, your final methods should look like this:

Type TestType
    intVar As Integer
    strVar As String
End Type
Sub SaveVBAVariables()
  Dim fileName As String, fileNo As Integer, testVar As TestType
  '...Some code here...
  testVar.intVar = 4
  testVar.strVar = "Hello!"
  'Now save all testVar variables to a binary file
  fileName = "C:test.bin"
  fileNo = FreeFile
  Open fileName For Binary Lock Read Write As #fileNo
  Put #fileNo, , testVar
  Close #fileNo
End Sub

Sub ReadVBAVariables()
    'Read the testVar variables back to your VBA project
    Dim fileName As String, fileNo As Integer, testVar As TestType
    fileName = "C:test.bin"
    fileNo = FreeFile
    Open fileName For Binary Lock Read As #fileNo
    Get #fileNo, , testVar
    'Recovered the TestType to testVar
    Close #fileNo
End Sub

The SaveVBAVariables procedure demonstrates how you can create and work with your variables, and
save your results to a binary file.
The ReadVBAVariables procedure can be executed even after you close and reopen the workbook – recovering your Test data type along with all enclosed variables.