First VarType only applies to VBA variables, not cells. Cell values are
either Doubles, Errors, or Text. When you test

VarType(Cells(1, 7))

you test the temporary variable that is the repository for the Value
property of the cell. You can see that by testing

VarType(CStr(Cells(1, 7).Value))

Likewise, when VBA passes a value back to XL, it's parsed by XL's parse
engine as if it were entered manually, so if it can be interpreted as a
number, it will be, even if the value passed is a string.

If you want the cell to have a Text format, use

With Cells(1, 7)
.NumberFormat = "@"
.Value = .Text
End With

Here you format the cell as Text, so that the entry is not parsed when
the .Value property is assigned.

Now that the cell is formatted as Text, XL will pass back a Text value
which will be placed in a temporary string variable, so

? VarType(Cells(1, 7).Value)
8



In article ,
Chet Shannon wrote:

I am trying to convert a cell from sub-type 5 to sub-type 8. (Which is to say
from 'vbdouble' to 'vbstring'.) But when I apply the cstr (convert to
string) function the cell itself won't change sub-types as I want. Any ideas
how to change the sub-type value of a cell?

Here is the code I am using to convert the cell sub-type.
If VarType(Cells(1, 7)) = 5 Then
newvar = CStr(Cells(1, 7))
Cells(1, 7).Clear
End If
But when I then try test the value of the cell I've just changed by issuing
the command vartype(cells(1,7)) the value sub-type still shows up as 5 and
not 8 as it should be for a string. What am I doing wrong? I have the sense
that it has some thing to do with cstr working on variables but not on cells
maybe?

Thanks,
Chet