CODEWITHSUNDEEP
excelvbaexcel-formula
BOB
BOB

Reputation: 700

IsText and IsNumber functions from VBA got invalid results compared with IsText and IsNumber functions from Excel

I encountered several problems when I wanted to validate a column with dates, when I used IsText or IsNumber function in VBA and then when I noticed the wrong results, I compared the same function directly in Excel.

I was even more surprised to see that the two functions from VBA do not give the same result with the functions from Excel

My VBA code is:

Sub test()
Dim sh As Worksheet: Set sh = ThisWorkbook.Worksheets("Sheet1")

For i = 2 To 5
    sh.Cells(i, 4).Value = Application.WorksheetFunction.IsText(sh.Cells(i, 1).Value)
    sh.Cells(i, 6).Value = Application.WorksheetFunction.IsNumber(sh.Cells(i, 1).Value)

    sh.Cells(i, 10).Value = IsDate(sh.Cells(i, 1).Value)
    sh.Cells(i, 9).Value = IsNumeric(sh.Cells(i, 1).Value)
Next i

End Sub

And below are the results:

In column D I have the result from IsText function from VBA and in column E I have the results from IsText function from Excel.

And similar, in column E I have the result from IsNumber function from VBA and in column G I have the results from IsNumber function from Excel.

As you already see the results are different. So what is the explanation?

enter image description here

Another weird thing was with VBA IsDate function where I have different results from 2 Virtual Machines but with the same OS date format. You can see the results in the clumn J. enter image description here

Upvotes: 0

Views: 605

Answers (1)

RetiredGeek
RetiredGeek

Reputation: 3168

Bob,

In your VBA code you don't declare a type for the return of the ISTEXT therefore it is by default a Variant.

Here's some test code I ran in the Immediate window to demonstrate this:

x = Cells(3,1)
?x
May-10-22
?VarType(x)
 8

If you look up the VarType function you'll see that 8 is String so ISNumber is going to return False and ISText is True as all values will be in when imported into VBA w/o a type declaration.

References: Variant Variable Type VarType Function

Upvotes: 1

Related Questions