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 grammgcdata types you can use in Excel VBA.


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

Bytes used Range Example
2 True or False Dim checkVal As Boolean

checkVal = True


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

Bytes used Range Example
1 0 to 255 Dim age As Byte

Age = 56


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 used Range Example 1
8 -922,337,203,685,477.5808 to 922,337,203,685,477.5807 Dim money AS Currency

money = 123.456789


result: 123.4568


Example 2
Dim money

Dim value As Double


value = 123.456789

money = CCur(value)

MsgBox (money)


description: Ccur function converts a value to the currency.


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 need to be placed between two „#” marks.

Bytes used Range Example
8 1 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#


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


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.


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

Bytes used Range Example
12 Largest possible value:


Largest/smallest value with 28 decimal places:


Smallest non-zero value:


Dim value

value = 123.456789

value = CDec(value)





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

Bytes used Range Example
8 -1.79769313486231E+308 to


Dim value As Double

value = 123.456789


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

Bytes used Range Example
2 -32,768 to 32,767 Dim value As Integer

value = 3.5



result: 4


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

Bytes used Range Example
4 -2,147,483,648 to 2,147,483,647 Dim value As Long

value = 32800


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

Bytes used Range Example
8 -2,147,483,648 to 2,147,483,647 Dim value As LongLong

value = 7000000000#


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


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

Bytes used Range Example
4 -3.402823E+38 to -1.401298E-45 Dim value As Single

value = 123.456


Use this data type to store text.

Bytes used Range Example 1
1 per character A variable-length string:

up to 2 billion characters

A fixed-length string:

1 to 65,526 characters

Dim myText As String

myText = “Some text”


Example 2
Dim myText As String * 6

myText = “Some text”



result: Some t


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

Bytes used Range Example
may vary For 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”