Data types

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). 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:

Const cboo As Boolean = "a"
Note

Unfortunately the VBA compiler does not detect quite as many type errors as it could have. The similar error below is not detected.

Dim boo As Boolean: boo = "a"

As a consequence you will have more need for error handling than with other typed languages.

VBA built-in data types

VBA has built-in (such as Boolean) and application specific data types (such as Range in Excel). Below are the most important ones:

Integer

Integer 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. The initial value for a numeric variable is 0.

String

Use string 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 strAs StringstrAs String
str1 = "Hello World"
str2 = Left(String:=str1, Length:=5)

Boolean

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

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/2016#
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 ...