Data Transfer Objects in VBA

You can't create a collection of custom types in VBA. What can we do instead?

Data Transfer Objects in VBA

Data Transfer Objects (DTO) are simple objects for transferring sets of data between remote endpoints.  The objects are "simple" in the sense that they do not contain any business logic.  Rather, they exist only to move related data between two points.

User-defined types in VBA

You may be wondering why you should even care about such a concept in VBA. User-defined types (also known as custom types) are a simpler way to transfer groups of related data between processes than building an entire dedicated class module.  In fact, if you can accomplish what you want to with a custom type, then that is what you should do.

Type typCheckInfo
    BranchID As Long
    IssueDate As Date
    TypeCode As String
End Type
Example of a custom type in VBA

Won't work with collections

However, custom types have several shortcomings.  For one thing, you cannot create a collection of custom types.  

Sub TestCheckInfoCollection()
    Dim Info As typCheckInfo
    Info.BranchID = 1
    Dim Coll As New Collection
    Coll.Add Info
End Sub
This code won't compile

If we try to compile the TestCheckInfoCollection routine, we get the following error:

Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions
Compile error you get when trying to add a custom type to a collection

Basically, what the above error message is telling us is that we would need to define our custom type inside of a class module if we want to add it to a collection.  The same compile error appears if trying to add a custom type to a dictionary, too.

Managing groups of custom types with arrays

One alternative is to manage multiple instances of custom types using arrays.  I've done this on many occasions, but it tends to make for code that's harder to read than using collections or dictionaries.  I usually only go this route if I'm trying to keep a group of related functions self-contained within a single standard code module.  The only compelling reason to keep code in a single module is if I want to make it part of my code libary.

Custom VBA types: equivalent to C# structs

Another restriction with custom types is that you cannot add any additional code to them.  In this way, they are analogous to the struct data types in C#.  The lack of code makes these objects lighter weight than full blown class modules, but it does limit their flexibility.

Original purpose of the DTO design pattern

Martin Fowler defined this pattern in his Patterns of Enterprise Application Architecture as a way to improve network communications between remote endpoints.  

Each transfer of data between endpoints requires a certain fixed amount of overhead.  Thus, moving data back and forth using many tiny packets of data is slower than combining those bits of data into fewer packets.

The DTO's role was to provide a lightweight mechanism for packing that data together on the sending side and unpacking the data on the receiving side.  

Serializing and deserializing

The fancy names for the processes of packing and unpacking the individual pieces of data are serializing and deserializing.  These same concepts are also seen when saving and loading binary files.  

When you save a binary file, you are serializing the data needed to recreate the file later.  The current state of a program is captured and converted to a long string of ones and zeroes in a losslessly reversible way.  When you load that file later, you deserialize the same string of ones and zeroes to restore the program state you saved earlier.

Fowler addresses criticism of his pattern as an anti-pattern

Several years after publishing the pattern, Fowler responded to some harsh criticism the pattern had received.  The link to the criticism is now dead, but one can read between the lines of Fowler's response to infer its thesis.

The critic seemed fed up with a proliferation of DTOs in situations where a more lightweight approach would suffice.  The criticism sounds like it was another version of the Complicator's Gloves story.

"The Complicator's Gloves" (I disregard the advice more than I should...)

Fowler took umbrage with criticism of the pattern per se.  He had done nothing more than define a hammer and how (and when) it should be used.  It wasn't his fault that a bunch of programmers started using the hammer to hit screws and bolts in addition to nails.


While I don't use DTOs in VBA for Martin Fowler's originally intended purpose, I still find them indispensable in certain situations.

Adding simple DTOs to collections and dictionaries

As I demonstrated above, we cannot add instances of custom VBA types to collections or dictionaries.  So, if we want to do that, we need to create a simple DTO in lieu of the custom data type.

'--== Class module named:  udtCheckInfo  ==--
Option Compare Database
Option Explicit

Public TypeCode As String
Public BranchID As Long
Public IssueDate As Date

With this class module in place, we can now add it to a collection without generating a compile error:

Sub TestCheckInfoCollection()
    Dim Info As New udtCheckInfo
    Info.BranchID = 1
    Dim Coll As New Collection
    Coll.Add Info
End Sub
This code compiles

Simple vs. complex DTOs

I distinguish between simple and complex DTOs like so:

  • Simple DTO: public properties only
  • Complex DTO: may contain property get/let/set, functions, and methods

As part of my naming convention, I use different prefixes to distinguish between the two types:

  • udt: User-defined type; I use this prefix for Simple DTOs
  • dto: Data transfer object; I use this prefix for Complex DTOs

Use the simplest approach possible

When deciding what type of object to use, start with the simplest and work your way up as you run into limitations.  From simplest to most complex:

  • Custom Data Type
  • Simple DTO
  • Complex DTO (some methods and functions, but no business logic)
  • Full blown class module (including business logic)

More about complex DTOs

In tomorrow's article, I will talk more about complex DTOs: when to use them; why to use them; how to use them; and the design procedure I use to automatically generate and update them without having to type any boilerplate code.  Stay tuned!

Image by ArtTower from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0