Excel VBA: Data Types and Option Explicit

Why do we need to declare data types for our variables, what purpose does it serve? * Why do some programming languages not care about it too much and what data type are available for us in VBA?

What are data types?

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.

Indeed, some languages are very permissive as to declaring data types for variables (and functions), and they’re happy to accommodate any data type we actually assign to the variable. This will be like the Variant data type we have in VBA. Python and JavaScript are two good examples for such weakly-typed languages.

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.

Why are we expected to declare data types for our variables?

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.

The VBA data types

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:

Numeric Data Type

Non-Numeric Data Types

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 Data Types Conversion Functions

Is VBA a Strongly Typed, or Weakly-Typed Language?

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.