VBA Type in Excel — Group Related Fields Into One Variable (User-Defined Types vs a Class)

TL;DR — A Type (a user-defined type, or UDT) lets you name a shape once and carry all its fields in a single variable. Instead of juggling three arrays that must stay index-aligned, you get one array of records. A Type is a value type: assigning b = a copies every field, so the two variables are independent — the opposite of a class instance, which is shared. Declare it at the top of a standard module, and reach for a class module only when the record needs to do something.

' At the top of a standard module, before any Sub or Function:
Type Employee
    Name   As String
    Age    As Long
    Salary As Currency
End Type

Sub Demo()
    Dim e As Employee          ' one variable holds all three fields
    e.Name = "Sarah"
    e.Age = 34
    e.Salary = 65000

    Dim copy As Employee
    copy = e                   ' VALUE COPY — every field duplicated
    copy.Salary = 99000        ' changing the copy does NOT touch e
    MsgBox e.Salary            ' still 65000
End Sub

Most VBA code that "tracks several things about one item" ends up as parallel arrays — names(), ages(), salaries() — that all have to stay in the same order. Sort one and forget the others, and row 5's name now belongs to row 12's salary, with no error to warn you. A Type fixes that at the root: the fields can no longer drift apart, because they live in one variable.

What you'll learn

• The mental model — a row as a single variable
• Where a Type must be declared (and why inside a Sub fails)
• The rule that surprises people — a Type copies, it doesn't share
• The real payoff — one array of records instead of parallel arrays
• The hard limit — a Type can't hold behavior or go in a Collection
• Type vs class module — how to choose

The mental model: a row as a single variable

Think of a Type as one row of a table, promoted to a variable. A worksheet row for an employee has a name, an age, and a salary sitting side by side; a Type gives you the same grouping in code. You define the columns once (Type Employee ... End Type), and every Dim e As Employee is a fresh row with those same slots.

That is the whole idea: stop passing loose values that belong together, and pass one labelled thing instead. A function that needs an employee takes a single Employee argument, not three parameters you have to keep in the right order.

Where a Type must be declared

A Type declaration lives at module level — the very top of a standard module, above any procedure. Put it inside a Sub and the project won't compile. That trips up people who treat Type like Dim: Dim is a statement you run inside a procedure, but Type is a declaration that defines a shape for the whole module or project.

Two placement rules worth memorising:

• Public Type (the default at module level) makes the type usable across the whole project. Use Private Type to keep it to one module.
• You cannot declare a Public Type inside a class module — VBA rejects it with "Cannot define a Public user-defined type within a private object module." Keep your shared Types in a normal module (Module1, or a dedicated Types module), not in a class or a sheet's code.

The rule that surprises people: a Type copies, it doesn't share

This is the single most important thing to understand about a Type, because it is the exact point where it differs from an object. A Type is a value type. When you write copy = e, VBA duplicates every field into a brand-new, independent variable:

Dim a As Employee, b As Employee
a.Salary = 50000
b = a                 ' full copy of all fields
b.Salary = 70000      ' edits b only
Debug.Print a.Salary  ' 50000  — a is untouched

Compare that with a class instance, where Set b = a makes a and b two names for the same object, so editing one edits both. If you have ever been burned by an object changing "by itself" after you assigned it somewhere, the Type's copy-on-assign behaviour is the antidote — and often exactly what you want.

The one place the copy doesn't happen is passing to a procedure. A Type argument is ByRef by default, so a Sub can change your original:

Sub GiveRaise(emp As Employee)      ' ByRef by default
    emp.Salary = emp.Salary * 1.1   ' mutates the caller's record
End Sub

Pass it ByVal if you want the callee to work on a private copy — the same ByRef vs ByVal rule that governs ordinary variables.

The real payoff: one array of records

Here is where a Type earns its place. Instead of N parallel arrays that you have to keep aligned by hand, you declare one array of records:

Dim staff(1 To 100) As Employee
staff(1).Name = "Sarah"
staff(1).Salary = 65000
staff(2).Name = "Tom"
staff(2).Salary = 58000

' Sort, filter, or reorder staff() and every field moves together —
' a name can never desync from its salary again.

The failure mode this removes is silent and nasty: with names(), ages(), and salaries() as separate arrays, any operation that reorders one — a sort, an insert, a delete — will desync the rest unless you remember to apply it to all of them identically. With an array of Employee, there is nothing to keep in sync, because the fields never left each other's side.

The hard limit: no behaviour, and no Collection

A Type is deliberately dumb — it holds data and nothing else. That gives it two hard limits that tell you when you have outgrown it:

• It can't have methods. The moment your record needs to do something — validate its own age, format itself as a string, recalculate a total — a Type can't help. Behaviour belongs to a class module.
• It can't go into a Collection or Dictionary. Those stores hold Variant values, and a UDT cannot be coerced into a Variant. Try coll.Add e with a Type and VBA refuses. If you need a growable, keyed store of records, that alone is a reason to switch to a class, whose instances are objects a Collection accepts.

Type vs class module: how to choose

My rule of thumb: if the fields only need to stay together and you want cheap, independent copies, a Type is the right tool — reaching for a class here is over-engineering. Switch to a class the moment the record needs behaviour, or the moment you need to keep many of them in a Collection. Everything in between is a judgement call, and "start with the Type, promote it later" is a fine default.

How ExcelMaster helps

Deciding between parallel arrays, a Type, and a full class — and then remembering that a Type copies while an object shares — is exactly the kind of modelling choice that's easy to get subtly wrong. The bugs it causes (a desynced array, an object that mutated "by itself") compile cleanly and only surface later.

ExcelMaster lets you describe the data instead. Say "I need to track name, age and salary for a list of employees and sort them by salary," and it will model the records for you — an array of a Type, with the sort applied so nothing desyncs — and explain, in the code, when it would have chosen a class instead. You get the right structure without having to hold all the value-versus-reference rules in your head.

Frequently asked questions

What is a Type in VBA?

A Type (user-defined type, or UDT) is a custom data structure that groups several related fields under one name — for example an Employee with Name, Age, and Salary. You declare it once with Type ... End Type at the top of a standard module, then use it like any other type: Dim e As Employee. It lets related values travel together as a single variable instead of as separate, easily-desynced variables.

What is the difference between a Type and a class module in VBA?

A Type holds data only and is a value type — assigning one to another copies every field, so the two are independent. A class module holds data and behaviour (methods) and is a reference type — Set b = a makes both names point at the same instance. Use a Type for a passive record; use a class when the record needs methods or must be stored in a Collection.

Where do I declare a Type in VBA?

At module level, above any procedure, in a standard module. Declaring a Type inside a Sub or Function is a compile error. Use Public Type (the default) to share it across the project, or Private Type to limit it to one module. You cannot declare a Public Type inside a class module.

Can I store a VBA Type in a Collection or Dictionary?

No. A Collection and a Dictionary store Variant values, and a user-defined type cannot be converted to a Variant, so coll.Add someType fails. If you need a growable or keyed store of records, use a class module instead — its instances are objects that a Collection accepts — or keep the records in an array of the Type.

Does assigning one Type variable to another copy or share the data?

It copies. Because a Type is a value type, b = a duplicates every field into an independent variable; changing b afterwards does not affect a. This is the opposite of objects (class instances), where Set b = a shares a single instance. The only exception is passing a Type to a procedure, which is ByRef by default and can therefore mutate the caller's record.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-04.

Related guides: VBA Class Module · VBA Property · VBA Data Types · VBA Array · VBA ByRef vs ByVal