VBA VarType and TypeName in Excel — Ask a Value What It Is Before You Use It

TL;DR — Every Variant carries a type tag. VarType returns it as a number (vbDouble is 5, vbString is 8), and TypeName returns it as a word ("Double", "String", "Range"). In Excel the tag on a cell's value is set by the cell, not by your code: a whole number arrives as a Double, a currency-formatted number as a Currency rounded to four decimals, a date-formatted one as a Date, an error as an Error, and a multi-cell range as an array. Ask the tag at the edges of your code, where values come from cells, the user's selection or a Variant parameter, and declare real types everywhere else.

Debug.Print TypeName(Range("A1").Value)   ' Double, String, Date, Boolean, Error, Empty...
Debug.Print VarType(Range("A1").Value)    ' 5, 8, 7, 11, 10, 0...
Debug.Print TypeName(Selection)           ' Range, or ChartArea, Rectangle, Picture...

This is the second article in a cluster on asking a value what it is: IsEmpty tests for one tag, here VarType and TypeName read any tag, and IsNull tests for the tag that means "mixed or unknown". The cluster idea is that a VBA value carries a type tag separately from its content, and these functions read the tag, not what the value looks like.

What you'll learn

• The mental model: the cell sets the tag, not your code
• VarType constants and TypeName strings
• Why a whole number is a Double, and a currency format rounds your data
• VarType looks through objects; TypeName names them
• The single-cell versus multi-cell array trap
• Checking what the user selected
• Where to ask, and where to declare instead

The mental model: the cell sets the tag, not your code

When you write Dim total As Double, you decide the type. When you read Range("A1").Value, you do not: Excel hands you a Variant, and the cell decides what is inside it. The same line of code receives a number on one row, text on the next, an error on the third and nothing on the fourth.

VarType and TypeName let you look at that decision before you act on it. They do not convert anything, and they never fail: they only report the tag.

VarType constants and TypeName strings

The tags you meet in Excel work, with both names:

A Long variable gives 3 (vbLong), an Integer 2, and an object variable gives "Range", "Worksheet", "Workbook" or "Nothing" from TypeName. TypeName always returns the English name, in every Office language, so it is safe to compare with a string.

The natural way to use the tag is a Select Case that handles each kind of cell:

Sub DescribeCell(ByVal c As Range)
    Dim v As Variant
    v = c.Value
    Select Case VarType(v)
        Case vbEmpty:              Debug.Print c.Address, "empty"
        Case vbString:             Debug.Print c.Address, "text: " & v
        Case vbDouble, vbCurrency: Debug.Print c.Address, "number: " & v
        Case vbDate:               Debug.Print c.Address, "date: " & Format$(v, "yyyy-mm-dd")
        Case vbBoolean:            Debug.Print c.Address, "logical: " & v
        Case vbError:              Debug.Print c.Address, "error: " & c.Text
    End Select
End Sub

Why a whole number is a Double, and a currency format rounds your data

Two surprises come from the same fact: Excel stores every number as a Double, and .Value dresses it up according to the cell's format.

A whole number is a Double. A cell containing 42 returns vbDouble, not vbLong. Code that checks If VarType(c.Value) = vbLong never matches a cell. To ask "is this a whole number", check the tag first and the value second, in two steps, because VBA's And evaluates both sides and Int fails on text: If VarType(v) = vbDouble Then isWhole = (v = Int(v)).

The format changes the tag. If a cell is formatted as currency, .Value returns a Currency; if it is formatted as a date, it returns a Date. A colleague who reformats a column changes what your code receives, without touching a single value. Currency also holds only four decimal places, so a currency-formatted cell containing an exchange rate of 1.234567 is read as 1.2346.

.Value2 does not do this dressing: it returns a Double for numbers, dates and currency alike, with full precision. For calculation code, read .Value2:

Debug.Print TypeName(Range("B2").Value)    ' Currency  (B2 formatted as $)
Debug.Print TypeName(Range("B2").Value2)   ' Double

The Value2 guide covers the date side of the same difference.

VarType looks through objects; TypeName names them

Pass a Range itself, not its value, and the two functions disagree:

Debug.Print TypeName(Range("A1"))     ' Range
Debug.Print VarType(Range("A1"))      ' 5 if A1 holds a number, not vbObject

VarType looks through an object that has a default property and reports the type of that property. A Range's default property is its value, so VarType(Range("A1")) describes the cell's content, and only an object with no default property returns vbObject (9). TypeName reports the object itself.

So the rule is simple: use TypeName (or TypeOf) to ask what an object is, and VarType to ask what a value is.

The single-cell versus multi-cell array trap

Reading a range into a Variant is the fast way to process data, and it hides a trap:

Dim data As Variant
data = Range("A2:A" & lastRow).Value
Debug.Print data(1, 1)                ' Run-time error 13 when lastRow = 2

When the range is more than one cell, data is a two-dimensional array. When lastRow is 2 and the range is a single cell, data is just that cell's value, and indexing it fails with Type mismatch. Reports work for months and then crash on the day there is only one row.

Check with IsArray and wrap the single value yourself:

If Not IsArray(data) Then
    Dim one(1 To 1, 1 To 1) As Variant
    one(1, 1) = data
    data = one
End If

IsArray(v) is the same test as (VarType(v) And vbArray) <> 0. The array guide covers reading ranges into arrays.

Checking what the user selected

The most common real use of TypeName is a guard at the top of a macro that works on the selection. The user may have clicked a chart, a shape or a picture instead of cells:

Sub FormatSelectedCells()
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select some cells first."
        Exit Sub
    End If
    Selection.NumberFormat = "#,##0.00"
End Sub

Without the guard, Selection.NumberFormat raises error 438 (Object doesn't support this property or method) when a chart is selected. If Not TypeOf Selection Is Range Then does the same check, and the compiler catches a typo in Range where it cannot catch one in the string "Range". Use TypeOf in logic and TypeName when you want to show or log the name. The Selection guide covers the rest.

The judgment call: ask at the edges, declare everywhere else

VarType and TypeName are border controls. Use them where values enter your code from outside: a cell's value, the user's selection, a Variant parameter, an item from a Collection, a result from Application.Match. Inside your own code, declare Long, String, Double and Range, and let the compiler do the checking.

If you find VarType checks spread through the middle of a macro, the real fix is to read each value once, check its tag once at the border, and convert it to a declared variable there. One Select Case VarType at the entrance is clearer and faster than a dozen If TypeName checks deeper down.

How ExcelMaster helps

Type surprises come from the data, not the code: a column reformatted as currency, a lookup that returns #N/A, a filter that leaves a single row.

ExcelMaster reads the actual cells, formats and errors in your workbook before it writes code, so the macro handles the types your sheet really contains, including the one-row case and the error rows.

Frequently asked questions

What is the difference between VarType and TypeName in VBA?

VarType returns the type as a number, such as 5 for Double or 8 for String. TypeName returns it as a word, such as "Double" or "Range". For objects, TypeName names the object, while VarType reports the type of its default property.

How do I check the data type of a cell in VBA?

Use TypeName(Range("A1").Value) or VarType(Range("A1").Value). A number returns Double, text String, a date-formatted number Date, a currency-formatted number Currency, an error Error, and an untouched cell Empty.

Why does VarType return Double for a whole number?

Excel stores all numbers as Double, so a cell containing 42 returns vbDouble. To check for a whole number, test v = Int(v) after confirming the value is numeric.

How do I check if the selection is a range in VBA?

Use If TypeName(Selection) = "Range" Then or If TypeOf Selection Is Range Then. If a chart or shape is selected, TypeName returns its object name instead.

What does VarType 8204 mean?

8204 is vbArray + vbVariant (8192 + 12): an array of Variants. It is what you get from the value of a multi-cell range.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-10-04.

Related guides: VBA IsEmpty · VBA IsNull · VBA Data Types · VBA Value2 · VBA Array · VBA Selection · VBA Select Case · VBA IsNumeric