> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Data Transfer Objects in VBA
- URL: https://nolongerset.com/data-transfer-objects-in-vba/
- Published: 2020-12-14T04:25:00.000Z
- Updated: 2026-05-08T13:07:25.000Z
- Description: You can't create a collection of custom types in VBA. What can we do instead?
- Author: Mike Wolfe
- Tags: Intermediate, #Import 2026-05-20 02:59

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](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/ms-vbal/a5fe374b-eddf-4808-9e73-477914940dba) (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.

```vba
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. 

```vba
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](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/12/image-12.png)

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](https://nolongerset.com/code-library/).

#### 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](https://martinfowler.com/eaaCatalog/dataTransferObject.html) 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](https://martinfowler.com/bliki/LocalDTO.html) 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](https://thedailywtf.com/articles/The%5FComplicator%5F0x27%5Fs%5FGloves) story.

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/12/motivator3155329.jpg)

"[The Complicator's Gloves](https://thedailywtf.com/articles/The%5FComplicator%5F0x27%5Fs%5FGloves)" (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.

### DTOs in VBA

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.

```vba
'--== 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:

```vba
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](https://pixabay.com/users/arttower-5337/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=55464) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=55464)*