Excel VBA User Defined Data Type

I got exposed to this concept when I learned C. C is not an object-oriented or even object based language – however there is a way to aggregate a grouping of variables into a data structure called a struct.  In VBA it’s called a User Defined Type – and is the help in MSDN and/or Excel Help sparse or what…

So, I’m going to be quoting from my version of Bruce McKenney’s Hardcore Visual Basic second edition.

User Defined Types – let’s from now on call them UDT’s – are the pre-cursors to classes.  Think of it this way – those with experience with .NET, Java or C++ already have a bit of a leg up here.  Types are templates that describe the kind of data that a variable of that type can contain plus contain the rules as to how the type should behave.  Intrinsic types are the usual variables you define and use – they are the Integer, String etc.  Then come UDT’s which are aggregates of intrinsic types; then come classes which is another post.

UDT’s are defined this way:

Public or Private Type <Name>
 <UDT Variable Name> as
<Keep going if you need to>~
End Type

A more readable example would be:

Private Type Report
  Name As String
  SaveLocation As String
  TemplatePath As String
End Type

The UDT is used by using the VBA declaration statement:

Private or Public udtReport As Report

Now udtReport can have it’s aggregates initialized as such:

udtReport.SaveLocation = <some path I want to assign it>

And can be read or used as:

strSomeThrowawayStringVar = udtReport.SaveLocation

Why use this – and why at the end of the post?

Well – I have found it easier to aggregate variables into some kind of logical structure and then use the structure.  Instead of having a bunch of variables I found it easier to keep tabs on some kind of idea – so I create a structure called a Report, SummaryPointer, DetailPage etc. and then add individual variables as needed.

Something also to keep in mind – as in the C language a UDT is far less overhead – and ‘lighter’ than building a class in Java or C++ but with all the convenience of dot notation.  Those who have Java experience will probably recognize this as a typedef UDT.

VBA classes in a future post!

%d bloggers like this: