A data type is a structure to store data with given attributes and constraints.
You are probably familiar with some popular data types defined for us in VBA, such as an Integer, String and Date.
An Integer in VBA, for example, is allocated with 2 bytes to store numbers in the range -32,768 to 32,767.
A Date in VBA is allocated with 8 bytes to store numbers representing date and time, in the range January 1, 100 to December 31, 9999.
As data is eventually data, one can argue that all we need is a chunk of memory to be allocated for it, regardless if we store bits that represents a number, an integer, a date or an address pointing to another location in memory.
Java and C, on the other hand, are examples of a strongly typed languages, requiring strict type definition for every variable as it is dimensioned.
Strongly typed languages require that every variable, in any given moment, is registered in a defined data type. Why?
What purpose does it serve to keep a strict definition of a data type?
By declaring a data type, we provide the compiler and the run-time engine with more information that eliminate the need to “guess” (or make assumptions) as manipulations are performed on our variables.
A simple example to illustrate the point would be: 2 + 3. What does the “+” sign tells the run-time engine to do: mathematical adding of two numbers, or concatenating two strings? If the data type of the “2” and the “3” is unknown, and the “+” sign can be used for both concatenation as well as mathematical adding, the problem remains to be guessed by the engine. Should this be resolved to: “23” or to the number 5?
If the two operands here, 2 and 3, are registered as two integers, the engine is instructed to add these two numbers without trouble.
Declaring variables makes the program more readable to us and help us debug unexpected behavior. Strongly typed languages can catch some errors during compile time, instead of letting us ponder upon unexpected calculations and results further down the stream.
Weakly-typed languages, on the other hand, allow us to freely write without thinking too much about the types of our variables, conveniently manipulate and convert variables, letting the run-time engine implicitly guess the type from its circumstantial use.
In VBA, we have several data types defined for us that we can readily use to store and manipulate data as per our requirements. The most common are:
In VBA, we have several built-in functions that coverts between data types, when it makes sense. For example, it does not make sense to convert the string “Happy” to any number. However, the string “October 25, 2020” can be converted to a Date type (which is effectively a number).
VBA supports both modes.
Every module holding VBA code, can be declared to enforce strict strongly typed coding by adding the Option Explicit statement on top, before any Subroutine or Function is declared.
Without Option Explicit, the use of variables does not require explicit declaration for type, and it will assume the Variant type if not specifically defined.
Option Explicit will require a type declaration for every variable before it can be used. This is typically done using the Dim, Private, Public, ReDim or Static statement. For example:
Dim intAge as Integer
If you want Option Explicit to be automatically added for you upon creating a new module, you can check the Require Variable Declaration switch in the Options dialog under the Tools menu in the VBA IDE:
My advice would be to always require variables declarations with the Option Explicit statement, especially if you’re not yet an experienced coder.
Next week we’ll take Data Types to another level, as we discuss user-defined data types – stay tuned!
Lastly, a small request from me to you: please share this Blog post so that we can help more colleagues with Excel VBA.
Join today to the Excel VBA Inner Circle with Mor Sagmon.Get a weekly lesson, weekly live Q&A, monthly deep-dive workshop, monthly challenge and other activities.