Custom Variable Types in VBA

Large macro generally suffer from at least one problem: they have lots of variables, and it is hard to understand how they relate together. A starting solution is to give to variables more evocative names. This is however a partially good solution: as the program grows more complex, the variable names will become longer, or less clear. You got the idea? VBA custom variable types can help you!

Understanding VBA custom variable types

VBA allows you to create your own types of variables! More precisely, it allows you to create specific data structures. Still not clear? Imagine you are doing a program that handles different financial loans. Each loan has specific characteristics like its maturity, its face value, its currency, its coupon interest rate and amortization method. If you are comparing 2 loans, you would need to have variables like Maturity_ofLoan1, Maturity_ofLoan2, FaceValue_ofLoan1, FaceValue_ofLoan2 and so on.

Custom types would allow you to have a Loan1 and a Loan2 variable that contain all these values. It is much clearer! You can then simply set and access the values like this: Loan1.Maturity, Loan2.FaceValue…

Not only is it clearer, it is also programming good practice. It links some of your variables together and makes it easier for anyone to understand the purpose of a variable, and so the way the program is working. This kind of trick (when it makes sense to use it) really makes the difference between a good programmer and a nasty one. Additionally it is a first glance at the world of custom objects that can be hard to understand for a programming beginner. And by the way I talk about Classes there, which are the continuation of this subject.

An example of VBA custom types

Imagine a program that computes the amount of food needed by a person, depending on his body shape and the width of his wallet.

Consider the following variables set:

Dim LineIncrement1 as integer, ColumnIncrement1 as integer

Dim LineIncrement2 as integer, ColumnIncrement2 as integer

Dim PersonName as string

Dim PersonHeight as Double, PersonWeight as Double

Dim PersonWealth as currency

Private Type Person

     Name as string

     Height as Double

     Weight as Double

     Wealth as currency

End Type

Note that custom types need to be declared on the TOP of the module, outside of a sub procedure. The outcome is that it is not limited to a Sub like any normal variable would. You can set it as Private (usable only in this module) or as Public (usable in the whole project).

But Types can go further! What if your program needs to manage not only one person, but needs to manage a static team of three people, composed of Robert, Henry and John? You can add the following type:

Type Team

Boss as Person

Senior as Person

Intern as Person

End Type

Wow! Nice isn’t it? Actually doing this kind of complex data structure looks good but you also have to think about the flexibility of the program. The previous example lives on the assumption that the team is only composed of three people and that it will remain that way.

How to use custom data types

Let us sum up the keep points to use custom data types in VBA:

  • You need to declare the custom data type on the top of the module outside a Sub procedure 

Private Type Person

    Name As String

    Height As Double

    Weight As Double

    Wealth As Currency

End Type

  • You can use these custom types anywhere in the module (if set to Private) or anywhere in the project (if set to Public)
  • Now you have created a new data type, just like there are Int, Double and String. But the new type is useless if you don’t create variables that use it! Define a variable that use that data type, and give it some values.

Sub test()

Dim Barack As Person

Barack.Name = "Obama"

Barack.Height = 1.80

Barack.Weight = 80

Barack.Wealth = 1500000

End Sub

  • Call the data using the right structure 

Sub test2()

    MsgBox Barack.Name

End Sub


Note : You can define as many variables as you want that use your custom type!

Sub test_Michelle()

Dim Michelle As Person

   With Michelle

        .Name = "Obama"

        .Height = 1.7

    End With

End Sub

Advantages of using VBA custom data types

Here are some great advantages of using VBA’s custom data types:

  • There are really easy to use, especially after you have read that great piece of blog
  • They allow you to simplify and make more transparent the data structure of your application. What does that mean? For example if you need multiple times the variable type “person”, it is far better to use it than to define multiple unrelated variables that are hard to connect together, and that really create a mess.
  • Data types allow VBA to suggest you to fill the variable attributes when you type:

Drawbacks of VBA custom data types

Additionally you have some limitations when using custom data types,

  • They are not extremely flexible, especially in case you need to store a lot of variables,
  • More specifically, to continue on the previous critic, you can’t add a custom data type into a collection, even if that would look great :(

VBA custom data types look similar in some ways to the use of custom VBA Classes, that we will see in the next post of this blog. Classes also allow to store data in a structured way like [Object Name].[Attribute], but they also allow many, many more things. To take the previous example of the Person data type, what if you could check that the height is between 1 and 2.5 automatically when the value of the Height attribute is changed? It is only a tiny example but even that looks great!

So see you soon!

Matthieu Liatard
Remember this!
Remember this
Take a quizz on this subject

Hello :)
At Question-Player, we want to share more knowledge and help you never forget anything ever. And for free.

To show us your support, you can create an account:

Login / Create an account
Rate this article :

Up next

Related posts