VBA Data Types

VBA can take care of data types automatically, but it does it at a cost of speed and memory. When your application grows, sometimes you will need to explicitly define the best data type for the particular variable.

The following list shows the data types you can use in Excel VBA.

Boolean

It holds only one of two logical values: True or False. It is often used in logical statements.

Bytes usedRangeExample
2True or FalseDim checkVal As Boolean

checkVal = True

Byte

You can use this type when you work with small values (e.g. people’s ages).

Bytes usedRangeExample
10 to 255Dim age As Byte

Age = 56

Currency

The currency format allows you to store numbers as the decimal fraction that has 15 digits to the left and 4 digits to the right.

Bytes usedRangeExample 1Example 2
8-922,337,203,685,477.5808 to 922,337,203,685,477.5807Dim money AS Currency 

money = 123.456789

result: 123.4568 

Dim money Dim value As Double
value = 123.456789
money = CCur(value)

MsgBox (money)
description: Ccur function converts a value to currency.

Date

The date format is useful when you need to perform some kind of operations on dates (e.g. calculate the number of days between two dates). Each date or time needs to be placed between two „#” marks.

Bytes usedRangeExample
81 January 100 to

31 December 9999

Dim today As Date

Const myDate As Date

myDate = #12/1/2014#

Dim myTime As Date

myTime = #12:45:12#

Caution

VBA displays your time and date according to your system’s date and time format.

Caution

When you write VBA code you have to remember that you must use the U.S. date formats (e.g. mm/dd/yyyy) even if your system’s time format is set differently.

Decimal

The Dim value as the Decimal notation is not correct. You cannot declare a variable to be a Decimal. But you can declare it as a Variant first and then use the CDec function to convert it to Decimal.

Bytes usedRangeExample
12Largest possible value:

+/-79,228,162,514,264,337,593,543,950,335

Largest/smallest value with 28 decimal places: +/-7.9228162514264337593543950335

Smallest non-zero value: +/-0.0000000000000000000000000001

Dim value

value = 123.456789

value = CDec(value)

Double

The Double (double-precision floating-point) variables are used to store large floating-point numbers.

Bytes usedRangeExample
8-1.79769313486231E+308 to 

-4.94065645841247E-324

Dim value As Double

value = 123.456789

Integer

The Integer is used to store whole numbers. Floating point numbers are rounded to the nearest integer.

Bytes usedRangeExample
2-32,768 to 32,767Dim value As Integer 

value = 3.5 MsgBox(value)

result: 4

Long

If you need to store larger numbers and the Integer type is not enough, try using Long.

Bytes usedRangeExample
4-2,147,483,648 to 2,147,483,647Dim value As Long 

value = 32800

LongLong

If you need to store even bigger numbers (for example to show the world population), you can use the LongLong format.

Bytes usedRangeExample
8-2,147,483,648 to 2,147,483,647Dim value As LongLong 

value = 7000000000#

NOTICE

If you enter a number that is larger than Long, VBA will add a # mark at the end of the number.

Single

Single (single-precision floating-point) is the smallest data type that you can use to store decimal fractions.

Bytes usedRangeExample
4-3.402823E+38 to -1.401298E-45Dim value As Single 

value = 123.456

String

Use this data type to store text.

Bytes usedRangeExample 1Example 2
1 per characterA variable-length string:

up to 2 billion characters

A fixed-length string:

1 to 65,526 characters

Dim myText As String

myText = “Some text”

Dim myText As String * 6  myText = “Some text”

MsgBox(myText) 
result: Some t

Variant

The Variant type is assumed if you don’t declare the data type explicitly.

Bytes usedRangeExample
may varyFor negative values:

-1.797693134862315E308 to -4.94066E-324

For positive values:

4.94066E-324 to 1.797693134862315E308

Dim myVar1 As Variant

myVar = 123.456

Dim myVar2

myVar2 = “This is simple text”

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.