What VBA variable type to use when reading values from a cell in Excel?

Question

According to this answer one should always use Variant when assigning values in a cell to a variable in the code. Is this correct? I seem to recall reading elsewhere that using Variant indiscriminately is not a good practice.

Answer 1

You can read a cell value into any type you want, VBA will (try to) implicitly convert it to that type for you.

There are dozens of questions on this site involving run-time errors raised from reading cell values into a specific data type - perhaps you've seen this error message before?

Type mismatch

That's the error you get when you try to read a cell containing an error value (e.g. #REF!) into anything other than a Variant.

So if you read a cell value into, say, a Double, everything will work fine as long as you're reading something that VBA can coerce into that data type. The problem is that, well, data is never 100% clean, worksheets do break down, users delete columns and break formulas, lookups fail and the person that wrote the formula didn't bother wrapping it with IFERROR, etc.

That's why you read cell values into a Variant.

That doesn't mean you work with a Variant.

Dim cellValue As Variant
cellValue = someRange.Value

If IsError(cellValue) Then Exit Sub 'bail out before we blow up

Dim workingValue As String
workingValue = CStr(cellValue)

By assigning to another data type, you effectively cast the Variant to that more specific type - here a String. And because you like explicit type conversions, you use VBA's conversion functions to make the conversion explicit - here CStr.

Now, in real code, you probably wouldn't even bother reading it into a Variant - you can use IsError to test the cell value:

If IsError(someRange.Value) Then Exit Sub 'bail out before we blow up

Dim cellValue As String
cellValue = someRange.Value ' or cellValue = CStr(someRange.Value)

The flipside here is that you're accessing the cell twice. Whether or not that's better that reading it into a Variant is for you to decide; performance-wise, it's usually best to avoid accessing ranges as much as possible though.

Answer 2

The value you get from a cell (which is a Range) is a Variant according to the documentation:

Range.Value Property (Excel)

Returns or sets a Variant value that represents the value of the specified range.

Since a Variant can represent different data types, you could loose information if you would assign a cell's value to -- for instance -- a variable of type String.

The mere fact that there is data type information in a Variant already means you lose that type of information. If for instance the original type was numeric and you store it in a String variable, there is no way to know from that string value what the original data type was. You could also lose precision (on Date milliseconds for instance).

Furthermore, a Variant type value cannot always be cast to the data type of your variable, and so you could get a Type mismatch error. In practice this often happens with the Error sub data type.

Only when you know beforehand what the data type is of a certain cell's value, it would be good to define your receiving variable in that data type.

Answer 3

Not strictly answering your question, but thought I'd add this for reference anyway.

With native Excel functions you can usually provide either a range object or a value directly to a function. For example, you can either write =AVERAGE(A1,A2,A3) or =AVERAGE(10,20,30). If you want to do something similar for any user defined functions, you will need to check the type of object passed to your function:

Function test(input As Variant)

Dim var As Variant
If TypeName(input) = "Range" Then
    var = input.Value
Else
    var = input
End If

You may also want to check for other objects if your function can accept them, but doing this will make your functions behave more like users expect them to.