Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 37 ยท Language Basics

Variables and Data Types in VBA

Declare variables with Dim, choose between Integer, Long, Double, String, Boolean, Date and Variant, and learn naming rules, defaults and type conversion.

A variable is a named box that holds a value while your code runs. In VBA you create one with Dim (short for "dimension") and give it a data type that says what kind of value it holds. Choosing the right type saves memory, catches mistakes and makes code faster.

Declaring variables

VB
Dim itemCount As Long
Dim price As Double
Dim customerName As String
Dim isPaid As Boolean
Dim dueDate As Date

After declaring you assign with =:

VB
itemCount = 12
price = 19.99
customerName = "Ada Lovelace"
isPaid = True
dueDate = #12/31/2026#

Dates are written between # signs in US month/day/year order. Strings use double quotes; to put a quote inside a string, double it: "She said ""hi""".

You can declare several variables in one line, but each needs its own type:

VB
Dim a As Long, b As Long, c As String

Dim a, b As Long makes only b a Long. a becomes a Variant. This is one of the most common beginner traps.

The main data types

Type Holds Range / notes Default
Byte whole number 0 to 255 0
Integer whole number -32,768 to 32,767 0
Long whole number about -2.1 to 2.1 billion 0
LongLong whole number 64-bit systems only 0
Single decimal about 7 digits of precision 0
Double decimal about 15 digits of precision 0
Currency fixed decimal 4 decimal places, exact for money 0
Decimal exact decimal only via CDec and Variant 0
String text up to about 2 billion characters ""
Boolean True or False stored as -1 and 0 False
Date date and time year 100 to 9999 12:00 AM on 0
Object any object holds a reference Nothing
Variant anything flexible, slower, default type Empty

Rules of thumb: use Long instead of Integer for counters and row numbers (Excel has over a million rows, which overflows Integer; modern VBA converts internally so Long is not slower). Use Double for ordinary decimals and Currency for money amounts. Use String, Boolean and Date for what they say.

Variant: the default type

If you write Dim x without As, the variable is a Variant. It can store a number, then a string, then a date. It is flexible but uses more memory, hides mistakes and is slower. A Variant starts as Empty, which behaves as 0 in numbers and "" in text. Use TypeName(x) to see what it holds:

VB
Dim v As Variant
v = 42
Debug.Print TypeName(v)    ' Integer
v = "text"
Debug.Print TypeName(v)    ' String
v = Now
Debug.Print TypeName(v)    ' Date

Variants are right for values from cells, where the content may be text, a number, a date or an error (Range("A1").Value).

Naming rules and style

  • Start with a letter; use letters, digits and underscores; no spaces or punctuation; at most 255 characters.
  • Do not use reserved words such as Sub, End, Date or Name (even where VBA allows it).
  • Be descriptive: lastRow, not x. Many teams use camelCase for variables and PascalCase for procedures.
  • Some people prefix the type: strName, lngCount. The style is optional but be consistent.

Type conversion

VBA converts silently in many cases, but explicit conversion functions are clearer:

Function Converts to
CInt, CLng whole number (rounds to even: CInt(2.5) is 2)
CDbl, CSng decimal
CStr string
CBool Boolean (0 is False, anything else True)
CDate date
CCur, CDec Currency or Decimal
Val reads leading digits of a string: Val("12abc") is 12

Conversion fails with run-time error 13 (Type mismatch) when the content cannot convert, for example CLng("abc"). Test first with IsNumeric, IsDate or IsEmpty.

Overflow is the other classic error:

VB
Dim small As Integer
small = 40000      ' Run-time error 6: Overflow

Stand-in: how typing changes results

JavaScript has no declared types, but you can imitate what VBA does when it converts. This stand-in (not VBA) shows the rounding rules of CInt (banker's rounding), the Val function and the type mismatch check.

JS Stand-in: CInt, Val and IsNumeric

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Default values and initialisation

Declared but unassigned numbers are 0, strings are "", Booleans False, objects Nothing. VBA does not warn you when you forget to assign, so initialise explicitly where it matters (total = 0).

Variables declared with Dim inside a procedure live only while it runs. Scope and lifetime are covered in Option Explicit, Constants and Scope.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What type is a in the statement Dim a, b As Long?

    Only b gets the As Long type. a is declared without a type, so it is a Variant.
  2. 2. Which type should you choose for a worksheet row number?

    Excel has more than a million rows, more than an Integer (32,767) can hold.
  3. 3. What does CInt(2.5) return?

    VBA rounds halves to the nearest even number (banker's rounding), so 2.5 becomes 2.
  4. 4. What error do you get from CLng("abc")?

    Text that is not a number cannot convert: run-time error 13, Type mismatch.
  5. 5. Which literal is a valid VBA date?

    Date literals are enclosed in # signs, in month/day/year order.

Summary

  • Dim name As Type declares a variable; omit the type and you get a Variant.
  • Prefer Long, Double, Currency, String, Boolean and Date.
  • Convert with CLng, CDbl, CStr and friends; guard with IsNumeric.
  • Beware Dim a, b As Long, overflow and banker's rounding.

Next: Option Explicit, Constants and Scope