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
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:
Boss as Person
Senior as Person
Intern as Person
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
- 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.
Dim Barack As Person
Barack.Name = "Obama"
Barack.Height = 1.80
Barack.Weight = 80
Barack.Wealth = 1500000
- Call the data using the right structure
Note : You can define as many variables as you want that use your custom type!
Dim Michelle As Person
.Name = "Obama"
.Height = 1.7
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!