That depends on what you want to do with the data. When you declare a variable as a variant the first thing vb does is decide what type of variable to treat it as. With numeric data it will treat the variable as numeric. So, in your situation, if you want to do arithmatic with the numeric data, then you need to use the variant data type. If you want to treat the numeric data just like you do the alpha data, then use a string data type.
I think Jeff may have missed a vital point. In VB if you use a variable VB treats it as a Variant from square 1 but in VBA there is a vast difference between a string and a variant even after the sheer size allocated. Most VB programmers I've seen use only variants and VB doesn't have a problem with that. VBA is a different dialect of the same language and so should be treated differently.
A string is just that, text. Microsoft has that wonderful datatype called Null and there you get a problem. Suppose your user doesn't know how to delete a string and uses blanks (yes I've had them) then your string variable is ok but if they use the delete key then the field is now a null and your string varaible will fall over if you don't test for Null using Nz(Value, Value if Null). Also "If IsNull(Value)" will then fail so you get a double whammy.
You can put anything into a Variant but only text in a String. Strings to carry numbers become important if you want to have the number padded with leading zeroes eg. 001, 002. Then you can convert them back to numerics and still do your arithmetic.
I learned the hard way (too many times) that even though I'm expecting only text to be delivered to my string variable, somehow a user will delete or the source data will be blank, and the code chokes when a Null value is fed to a string variable.
When deciding to use string vs. variant, ask the question - am I absolutely certain beyond any shadow of a doubt that the only thing that will be delivered to this variable is text, and there's no possibility that it will receive a Null value?
If you're absolutely certain, then you'll probably have no problems with a string. But if the possibility exists that you could receive a Null value, then declare a variant and use code as suggested by saltecho, something like ...
If IsNull(varMyVariable) Then
'Code here to bypass the code that will choke on the null value
Else
'Run the code that will use the text
End If
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.