User Defined Types

User Defined Types (UDTs) are a convenient way to store related data in one variable. They are essentially data types that you (as the programmer) setup. You could, of course, store unrelated data in UDTs, but that’s not generally how they’re used.

I find them most useful when I have to pass information between procedures. Rather than have, say, four parameters in a procedure, I prefer to use a UDT with four elements. That way, I can pass one variable. For me, it makes the code more readable and self-documenting. And you get the benefit of Intellisense when using UDTs, which is nice.

Here’s a simple, contrived example of how to use a UDT. The Type statement is used to define the UDT and must be outside of any procedures.

Option Explicit

Private Type Applicant
    FirstName As String
    LastName As String
    ResumeRecd As Boolean
    InterviewDate As Date
End Type
Sub DefineUDT()

    Dim uApp As Applicant
    uApp.FirstName = “Dick”
    uApp.LastName = “Kusleika”
    uApp.ResumeRecd = True
    uApp.InterviewDate = #7/15/2004#
    UseUDT uApp
End Sub

Sub UseUDT(uInput As Applicant)

    If uInput.InterviewDate = Date Then
        Debug.Print uInput.FirstName, uInput.LastName
        Debug.Print uInput.ResumeRecd
    End If
End Sub

Posted in Uncategorized

2 thoughts on “User Defined Types

  1. When would you use a struct (Type) and when to use a class? I almost always use classes because they are more fun . There have been a few posts in the ngs recently which have put me off Types even more e.g. can’t add them to a Collection, can’t be used for return types for functions in class modules, etc.



  2. I use UDTs when I can simply replace them with variables, like the example. Anything more complicated than that, and I use a class. For instance, if I have a variable that I need to split out, e.g. a playing card – I may want the suit or the number separately, so I’ll use a class with Let/Get Value (10S) and Get only suit (S) and number (10) properties. So it doesn’t take much complexity for me to go to a class over a UDT. The other situation is if I have to manipulate a lot of properties/variables at once. I use a class so I can have a method that does all that work.

    In practice, I usually start with a bunch of variables. The I find myself passing those variables between subs and think “How about a UDT”. Once I have the UDT I start seeing the benefits of having a class. So it’s kind of an evolutionary process for me. I really should consider classes first, but I don’t.

    And I agree that they’re more fun.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.