Search code examples
excelvbastringtypeof

How to use " TypeOf ___ is ___ Then " when comparing with a String

I would like to check the user Input. If I have a cell in myRange which contains "2..4" it passes the IsNumeric test with true, that is why I am looking for other options.

If I run a function containing

MsgBox (TypeName(myRange.Cells(i, j).Value))

I get a message that 2..4 is a String but for each of the following things my code does not compile:

' expects Object or Typename
If TypeOf myRange.Cells(i, j).Value is String Then

' expects Then or GoTo
If TypeOf myRange.Cells(i, j).Value is GetType(String) Then

' expects Then or GoTo
Dim word As String
word = "Hello World"
If TypeOf myRange.Cells(i, j).Value is GetType(word) Then
Solution

  • TypeOf is used for objects only.

    For checking a String variable use TypeName or VarType:

    Debug.Print TypeName(myRange.Cells(i, j).Value)
Debug.Print VarType(myRange.Cells(i, j).Value) 'VarType returns 8 for a string type

    Please, see here all variable types returned values in case of using VarType...