VBA Data types

Visual Basic is a typed language. This means the variables (and procedure arguments) are described as being of a certain type, for example Boolean (true or false) or Integer (numbers). This page presents the most important ones.

Why typing variables is important

Knowing the data type allows for efficient storage and retrieval in memory. In addition it detects errors early - at compile time so that errors don't first appear when you run the program (Compile error: Type mismatch) for example as would occur with the following code:

Most important, knowing the type tells you what you can do with a variable: you can use numbers (Integer, Double) in calculation, but not text. You can join Strings into a sentence, but not numbers. You use Boolean values to make decisions, but not Dates or numbers or text, at least not directly. You plan your agenda with Date and Time, not with numbers, and so on.

This will be the leading thought in the rest of the pages here: know the data types required for your problem and the VBA built-in procedures (functions) that can be be applied to them; this will be the basis of your being able to make the programs you require.

This argument is further extended to making macros for your Office applications. You have to know which Objects (classes) are involved in what you want to do, and their relevant properties and methods.

Tip: the Code VBA add-in makes use of the data type information to propose suitable variables, object properties and functions in case of value assignments, see image at the bottom.

Introducing some basic VBA types

String

Use a string variable to store any text. You can assign string values using any function that returns a built-in, non-object data type. String literals must be enclosed within double quotes ("), for example:

Dim str1 As String, str2 As String
str1 = "Hello World"
str2 = Left(String:=str1, Length:=5)

More info:

• An introduction to using String
• String function overview

Boolean

A Boolean variable can have a value True or False. When other numeric types are converted to Boolean values, 0 becomes False and all other values become True. When Boolean values are converted to numeric data types, False becomes 0 and True becomes -1. When converted to a String variable, "False" or "True". The initial value for a boolean variable is False

More info:

• Boolean function overview
• Testing conditions

Long (Integer, Byte)

For whole numbers you can choose from three types. With the current state of hardware we consider distinguishing not very useful. In most cases we will use either Long, the largest scope of the three, or Integer, a traditional choice for example in loops.

An Integer variable can store positive and negative numbers between -32,768 and 32,767. Use Long if you have larger numbers - up to 2,147,483,647. Use LongLong if you need support for even larger numbers. Byte supports numbers up to 256. The initial value for a numeric variable is 0.

Double (Single)

If your number needs a decimal fraction e.g. 0.33 either use single- or double precision floating-point

More info:

• Math function overview
• Financial function overview

Date

Used to store date and time. You can assign Date values using date functions or with a string literal. Date literals must be enclosed within number signs (#), for example, #January 1, 2013# or #1 Jan 13# .

Dim dt1 As Date, dt2 As Date
dt1 = #12/31/2019#
dt2 = DateAdd(Interval:="d", Number:=1, Date:=dt1)

Object

Object variables are addresses that refer to objects. All objects are instances of classes (built-in, such as Worksheet in Excel, or custom classes) Using the Set statement, a variable declared as an Object can have any object reference assigned to it.

Dim ws As Worksheet
Set ws = Application.ActiveSheet
Dim prsPerson As Person
Set prsPerson = New Person

Variant is a special data type that can contain any kind of data. The Variant data type is the data type for all variables that are not explicitly declared as some other type.

Dim var1 As Variant
Dim var2 'type not specified, therefore Variant'

You can determine how the data in a Variant is treated using the VarType function or TypeName function.

Dim str As String
var1 = 3
str = TypeName(var1) 'Integer
Dim vvt As VbVarType
vvt = VarType(var1) '2 = vbInteger

The value Empty denotes a Variant variable that hasn't been assigned an initial value. When used in a numeric context a Variant containing Empty is interpreted as 0 or a "" if it is used in a string context.

Collection

A Collection is a type that contains a set of related objects - in memory. Collection types are very useful because it is easy to add items to it and iterate over the items in the collection for whatever purpose you have in mind. More info on how to create and use Collections can be found here ...

See 'An overview of the VBA built-in data types' for more technical details.

Image below shows how context sensitive IntelliSense uses the data type information to selectively display appropriate variables and functions.