VarType in VBA

The VarType function lets you determine the underlying type of a variable. How does it fit in with TypeName and TypeOf?

VarType in VBA

In my article TypeName vs. TypeOf, I wrote that I use TypeName when dealing with value types (like String, Integer, etc.) because TypeOf...Is expressions only work with object types.  

After I posted the article, Mathieu Guindon (aka, @rubberduckvba) pointed out on Twitter that there is a third alternative to TypeName and TypeOf...Is expressions–the VarType function.

VarType vs. TypeName

Mathieu's point–and it's a good one–is that we should always prefer solutions that provide compile-time checks over those that do not.

What does that even mean?  Why should we care?  I think a quick example is in order.

In the sample code below, I'm checking the underlying type of a Variant argument being passed to my function.  Everything works as you would expect until the last line of my subroutine where I misspelled "String" as "Stirng".  But, since the misspelling occurred within a string literal, the compiler saw no problem:

However, if I were to make a similar typo in my VarType comparison, the code won't even compile:

Compile errors are good!

Because they're easy to catch and easy to fix.

Compile-time checks almost always get caught in development (so long as you remember to regularly compile your code: Debug > Compile).  

Meanwhile, typos in string literals are easily missed.  

And if you miss a typo in a string literal, the best case scenario is you end up with a runtime error.  The worst case scenario is you end up with a logic error that goes unnoticed, silently wreaking havoc with its second- and third-order side effects (such as inconsistent data, invalid program state, etc.).

Revised Rule of Thumb

Use the first item in the following list that you can make work for the situation at hand:

  • TypeOf...Is expression
  • VarType function
  • TypeName function

Image by Jondolar Schnurr from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0