Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

Deftype statements in VBA – What?

by | Jul 16, 2024 | MS Access, MS Access Features, MS Access VBA Coding | 0 comments

I had never heard of this keyword. I was perusing the Microsoft Learn web site and happened across this in the VBA reference here: Deftype statements (VBA) | Microsoft Learn

Here is the basic definition from that page:

Used at the module level to set the default data type for variables, arguments passed to procedures, and the return type for Function and Property Get procedures whose names start with the specified characters.

Before continuing further, just wanted to give a shout out to Eric Blomquist who replied to my NiceDLookup article with an updated version of his own. Check it out as bonus content at the end of the article: DLookup and “NiceDLookup” | Access JumpStart

Back to our regular programming. Basically, you can drop declarations for certain variables and they may be automatically set to a default data type based on the first letter of the variable name. Ok, let’s do an example:

Pre Deftype sample code (representing a module)

Option Compare Database
Option Explicit

Dim sMessage As String
Dim sEvent As String
Dim bTrigger As Boolean
Dim bIsTrue As Boolean
Dim sTitle As String
Dim StoreVars As VBA.Collection

Public Sub PrintTypes()
    SetupStoreVars
    PrintType "sMessage"
    PrintType "sEvent"
    PrintType "bTrigger"
    PrintType "bIsTrue"
    PrintType "sTitle"
End Sub

Private Sub SetupStoreVars()
    Set StoreVars = New VBA.Collection
    StoreVars.Add sMessage, "sMessage"
    StoreVars.Add sEvent, "sEvent"
    StoreVars.Add bTrigger, "bTrigger"
    StoreVars.Add bIsTrue, "bIsTrue"
    StoreVars.Add sTitle, "sTitle"
    
End Sub

Private Sub PrintType(sVarName As String)
    Debug.Print sVarName & " is a " & VBA.TypeName(StoreVars.Item(sVarName))
End Sub
Visual Basic

Output of PrintTypes in Immediate Window:

Printtypes
sMessage is a String
sEvent is a String
bTrigger is a Boolean
bIsTrue is a Boolean
sTitle is a String
Plain text

Now we can remove the type declarations and use DefStr S and DefBool B at the top:

Option Compare Database
Option Explicit

DefStr S
DefBool B

Dim sMessage
Dim sEvent
Dim bTrigger
Dim bIsTrue
Dim sTitle
Dim StoreVars As VBA.Collection

Public Sub PrintTypes()
    SetupStoreVars
    PrintType "sMessage"
    PrintType "sEvent"
    PrintType "bTrigger"
    PrintType "bIsTrue"
    PrintType "sTitle"
End Sub

Private Sub SetupStoreVars()
    Set StoreVars = New VBA.Collection
    StoreVars.Add sMessage, "sMessage"
    StoreVars.Add sEvent, "sEvent"
    StoreVars.Add bTrigger, "bTrigger"
    StoreVars.Add bIsTrue, "bIsTrue"
    StoreVars.Add sTitle, "sTitle"
    
End Sub

Private Sub PrintType(sVarName)
    Debug.Print sVarName & " is a " & VBA.TypeName(StoreVars.Item(sVarName))
End Sub
Plain text

Notice that I don’t need to declare the type of sVarName either, but it will still require a string because it starts with an “s”. Otherwise, if there is no deftype active, the default type is a Variant. Entering PrintTypes again in the immediate window will return the same output as before. Note that I have not assigned anything to any of the variables.

Output of PrintTypes in Immediate Window:

PrintTypes
sMessage is a String
sEvent is a String
bTrigger is a Boolean
bIsTrue is a Boolean
sTitle is a String
Plain text

If I change DefBool B to DefInt B, that will change the variables starting with “b” to an Integer.

PrintTypes
sMessage is a String
sEvent is a String
bTrigger is a Integer
bIsTrue is a Integer
sTitle is a String
Plain text

Ultimately, this makes prefixing variables consistently with a single character based on it’s type more than just a nice naming convention, but actually used in the code itself. Something to think about…

Sign up For a Daily Email Adventure in Microsoft Access

Every business day (typically M-F), I'll send you an email with information about my ongoing journey as an advanced Access application developer. It will be loaded with my tips and musings.

    We won't send you spam. Unsubscribe at any time.
    Built with Kit

    Submit a Comment