What type of VBA variable is used when reading values from a cell in Excel?
You can read the cell value in any type you want, VBA (will try) implicitly convert it to that type for you.
There are dozens of questions on this site related to runtime errors raised from read cell values to a specific datatype - perhaps you've seen this error message before?
Type mismatch
This error occurs when you try to read a cell containing an error value (for example #REF!
) into something other than Variant
.
So, if you are reading the value of a cell in, say, Double
everything will work fine as long as you are reading what VBA can coerce into a data type. The problem is that the data is not always 100% clean, worksheets don't work, users delete columns and break formulas, searches fail, and the person who wrote the formula didn't bother with the help wrapper IFERROR
, etc.
This is why you are reading the values of the cells in Variant
.
This does not mean that you are working with 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 a different data type, you are actually applying Variant
to that more specific type - here a String
. And since you like explicit conversions, you use VBA's conversion functions to make the conversion explicit - here CStr
.
Now, in real code, you probably wouldn't even bother to read it in Variant
- you can use IsError
to check the value of a cell:
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)
Here you can see that you are accessing the cell twice. Whether or not it is better to read it in Variant
for you; for performance, it's usually best to avoid accessing ranges as much as possible though.
source to share
The value you get from the cell (which is Range
) is Variant
according to the documentation :
Range.Value Property (Excel)
Gets or sets a Variant value that represents the value in the specified range.
Since a Variant
can represent different data types, you can lose information if you assign a cell value to - for example - a variable of type String
.
The very fact of having information about a data type in Variant
already means that you are losing this type of information. If, for example, the original type was numeric and you stored it in a variable String
, there is no way to know from that string value what the original data type was. You can also lose precision (e.g. Date
milliseconds).
Also, the type value Variant
may not always be passed to the data type of your variable, so you might get a Type Mismatch Error . In practice, this often happens with the Error
sub data type .
Only when you know in advance what datatype a particular cell value has, it would be helpful to define your receiving variable in that datatype.
source to share
Not exactly answering your question, but I thought I'd add this for reference.
With native Excel functions, you can usually provide a range object or value directly to a function. For example, you can write =AVERAGE(A1,A2,A3)
or =AVERAGE(10,20,30)
. If you want to do something like this for any custom functions, you will need to check the type of the 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 can also check for other objects if your function can accept them, but this will make your functions more like the users they expect.
source to share