Numerical Data vs Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I figure out if a cell contains numerical data or a string (text)? I need to compare some cells, and need my subroutine to be intelligent enough not to try to compare a number to a bunch of text. Thanks.

Jim
 
Jim,

Sub TestMe()
MsgBox isnumeric(Range("B8"))
End Sub

Will return True if it's numeric and False if not

John

Jim Hollis said:
How can I figure out if a cell contains numerical data or a string (text)?
Click to expand...
I need to compare some cells, and need my subroutine to be intelligent
enough not to try to compare a number to a bunch of text. Thanks.
 
Actually:
? isnumeric("88")
True
? application.IsNumber("88")
False

isnumeric tests if the value can be interpreted as a number. the
worksheetfunction, isnumber tests if it is being stored as a number.
 
Tom Ogilvy said:
Actually:
? isnumeric("88")
True
? application.IsNumber("88")
False

isnumeric tests if the value can be interpreted as a number. the
worksheetfunction, isnumber tests if it is being stored as a number.
Click to expand...
 
Tom,

Thanks for the clarification.

John

Tom Ogilvy said:
Actually:
? isnumeric("88")
True
? application.IsNumber("88")
False

isnumeric tests if the value can be interpreted as a number. the
worksheetfunction, isnumber tests if it is being stored as a number.
Click to expand...
 
Tom Ogilvy said:
Actually:
? isnumeric("88")
True
? application.IsNumber("88")
False

isnumeric tests if the value can be interpreted as a number. the
worksheetfunction, isnumber tests if it is being stored as a number.
Click to expand...

If the OP is testing cell values, wouldn't

VarType(Rng.Value) = vbDouble

be more efficient?
 
use the typeName() function in VBasic to test the type of
data in the cell, and then test for text etc before
conitune processing (see under help, reference ->
functions)

It may help to assign the Cell contents to a memory
variable first.

-----Original Message-----
How can I figure out if a cell contains numerical data or
Click to expand...
a string (text)? I need to compare some cells, and need my
subroutine to be intelligent enough not to try to compare
a number to a bunch of text. Thanks.
 
Tom Ogilvy said:
What did your tests show? ....
Click to expand...

I didn't time them - yet.

Interesting looking deeper into this. When dealing with Range objects in
VBA, should one use the .Value or the .Value2 property? If you pass the
IsNumber method the .Value property of a cell containing a positive number
formatted as date/time, it'll return FALSE since the .Value would be passed
to VBA as a Date type. If you pass the IsNumber method the .Value2 property,
on the other hand, it'll return TRUE. More interestingly, the IsNumber
method when passed a range reference alone, so neither the .Value nor the
..Value2 property, it seems to use the .Value2 property. In other words, when
the active cell is initially formatted as General, then the formula =NOW()
is entered into it,

Debug.Print Application.IsNumber(ActiveCell) & Chr(13) & _
Application.WorksheetFunction.IsNumber(ActiveCell)

gives

True
True

Debug.Print Application.IsNumber(ActiveCell.Value) & Chr(13) & _
Application.WorksheetFunction.IsNumber(ActiveCell.Value)

gives

False
False

Debug.Print Application.IsNumber(ActiveCell.Value2) & Chr(13) & _
Application.WorksheetFunction.IsNumber(ActiveCell.Value2)

gives

True
True

Digression: does this mean .Value2 rather than .Value is the default
property? Or does it mean that the IsNumber method when passed a range
reference chooses to use the .Value2 property rather than the .Value
property?

Only the OP could say for sure, but I'd guess for this sort of thing, the
..Value2 property would be what's wanted. So on to profiling.


Given the profiling macro


Sub foo()
Const MAXITER As Long = 500000
Dim i As Long, s As Boolean, dt As Date, et As Date

s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s
End Sub


the results are

IsNumber: 16.04
True
VarType: 4.01
True

on my machine. Looks like VarType plus a comparison operation is
significantly faster than the IsNumber method call. However, this leaves the
deeper question of whether dates/times should be considered numbers.
 
I reversed your code to check vartype first, then isnumber. I also changed
Application.Isnumber to Worksheet.Isnumber

Vartype: 1.00
True
IsNumber: 3.01
True

Vartype: 1.00
False
IsNumber: 3.01
False

Vartype: 2.01
False
IsNumber: 2.01
False

As can be seen I got significanlty different results based on the
combination of how s was initialized (true or false) and whether activecell
contained a number or was blank.

With your initial code, I got results similar to you.

Worksheet as a qualifier vice application seems to be significantly faster.
 
Tom Ogilvy said:
I reversed your code to check vartype first, then isnumber. I also changed
Application.Isnumber to Worksheet.Isnumber

Vartype: 1.00
True
IsNumber: 3.01
True
Click to expand...

Presumably this was only reversing the tests.
Vartype: 1.00
False
IsNumber: 3.01
False
Click to expand...

Presumably this was both reversing and initializing s to False. As yet
little difference (3 to 1 rather than 4 to 1 from my tests).
Vartype: 2.01
False
IsNumber: 2.01
False
Click to expand...

Presumably same as immediately preceding except now using
Application.WorksheetFunction.IsNumber rather than Application.IsNumber (I'm
guessing your 'Worksheet.Isnumber' is actually ...WorksheetFunction...).

Odd that this slowed down VarType. Likely it didn't actually slow it down.
Rather, likely that background processes skewed the results. Profiling
should be done with sufficient iterations so that the results are > 10
seconds for both alternatives combined.
As can be seen I got significanlty different results based on the
combination of how s was initialized (true or false) and whether activecell
contained a number or was blank.

With your initial code, I got results similar to you.

Worksheet as a qualifier vice application seems to be significantly faster.
Click to expand...
....

My time results were run on my wife's 858Mhz PIII CPU PC. I'd guess you were
running your test on a faster P4 or AMD CPU machine. Also, my tests were run
under XL2000, and I'd guess you were using a more recent version.

Here's my revised profiling macro. If your PC is faster, you may need to
increase the MAXITER constant to a number large enough not to be affected by
background processes.


Sub foo()
Const MAXITER As Long = 1000000
Dim i As Long, s As Boolean, dt As Date, et As Date

Debug.Print "HG PC - 858MHz PIII, 128MB, Windows ME, Excel 2000 SP-3"

Debug.Print String(60, "=")

Debug.Print "App.IsN before VT, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "App.IsN before VT, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "App.WF.IsN before VT, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "App.WF.IsN before VT, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And Application.WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "=")

Debug.Print "VT before App.IsN, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "VT before App.IsN, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "VT before App.WF.IsN, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "VT before App.WF.IsN, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And Application.WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "=")
End Sub


And here are my results.


HG PC - 858MHz PIII, 128MB, Windows ME, Excel 2000 SP-3
============================================================
App.IsN before VT, s init True
IsNumber: 36.10
False
VarType: 8.02
False
------------------------------------------------------------
App.IsN before VT, s init False
IsNumber: 35.10
False
VarType: 8.02
False
------------------------------------------------------------
App.WF.IsN before VT, s init True
IsNumber: 17.05
False
VarType: 8.02
False
------------------------------------------------------------
App.WF.IsN before VT, s init False
IsNumber: 17.05
False
VarType: 8.02
False
============================================================
VT before App.IsN, s init True
VarType: 7.02
False
IsNumber: 36.10
False
------------------------------------------------------------
VT before App.IsN, s init False
VarType: 7.02
False
IsNumber: 36.10
False
------------------------------------------------------------
VT before App.WF.IsN, s init True
VarType: 8.02
False
IsNumber: 17.05
False
------------------------------------------------------------
VT before App.WF.IsN, s init False
VarType: 7.02
False
IsNumber: 18.05
False
============================================================


It sure looks like VarType *is* faster that either Application.IsNumber or
Application.WorksheetFunction.IsNumber, and ...WorksheetFunction... is
faster than Application.IsNumber, which isn't surprising. Since
Application.Match generates trappable errors in cases when MATCH returns
#N/A while Application.WorksheetFunction.Match throws runtime errors, it's
pretty clear that Application.WorksheetFunction.<fcn> lacks error checking
code that Application.<fcn> provides, and error checking ain't free.

Also not surprising that initializing s to True or False has no apparent
effect on execution speed since VBA's And operator is a bitwise rather than
logical And, so couldn't provide short-circuit boolean evaluation.

Still leaves open the question whether the OP wants numbers formatted as
dates/times to be considered numbers or not.
 
I ran two sets. First set the cell was blank, and the second contained a
number.

Microsoft Windows 98 SE
AuthenticAMD AMD Athlon(tm) XP 2200+
224MB RAM
Excel 97 SR2
===============================
=== ACTIVE CELL EMPTY =========
============================================================
App.IsN before VT, s init True
IsNumber: 11.03
False
VarType: 3.01
False
------------------------------------------------------------
App.IsN before VT, s init False
IsNumber: 11.03
False
VarType: 3.01
False
------------------------------------------------------------
App.WF.IsN before VT, s init True
IsNumber: 6.02
False
VarType: 2.01
False
------------------------------------------------------------
App.WF.IsN before VT, s init False
IsNumber: 6.02
False
VarType: 3.01
False
============================================================
VT before App.IsN, s init True
VarType: 2.01
False
IsNumber: 12.03
False
------------------------------------------------------------
VT before App.IsN, s init False
VarType: 2.01
False
IsNumber: 12.03
False
------------------------------------------------------------
VT before App.WF.IsN, s init True
VarType: 2.01
False
IsNumber: 6.02
False
------------------------------------------------------------
VT before App.WF.IsN, s init False
VarType: 3.01
False
IsNumber: 6.02
False
============================================================
WF.IsN before VT, s init True
IsNumber: 6.02
False
VarType: 2.01
False
------------------------------------------------------------
WF.IsN before VT, s init False
IsNumber: 6.02
False
VarType: 2.01
False
============================================================
VT before WF.IsN, s init True
VarType: 3.01
False
IsNumber: 6.02
False
------------------------------------------------------------
VT before WF.IsN, s init False
VarType: 2.01
False
IsNumber: 6.02
False
------------------------------------------------------------
=============================
== ACTIVE CELL HAS Number ==
=============================
============================================================
App.IsN before VT, s init True
IsNumber: 11.03
True
VarType: 3.01
True
------------------------------------------------------------
App.IsN before VT, s init False
IsNumber: 12.03 <=== difference
False
VarType: 2.01
False
------------------------------------------------------------
App.WF.IsN before VT, s init True
IsNumber: 6.02
True
VarType: 3.01 <=== difference
True
------------------------------------------------------------
App.WF.IsN before VT, s init False
IsNumber: 6.02
False
VarType: 2.01 <=== difference
False
============================================================
VT before App.IsN, s init True
VarType: 3.01 <=== difference
True
IsNumber: 11.03 <=== difference
True
------------------------------------------------------------
VT before App.IsN, s init False
VarType: 3.01 <=== difference
False
IsNumber: 12.03
False
------------------------------------------------------------
VT before App.WF.IsN, s init True
VarType: 2.01
True
IsNumber: 6.02
True
------------------------------------------------------------
VT before App.WF.IsN, s init False
VarType: 3.01
False
IsNumber: 6.02
False
============================================================
WF.IsN before VT, s init True
IsNumber: 5.01 <=== difference
True
VarType: 3.01 <=== difference
True
------------------------------------------------------------
WF.IsN before VT, s init False
IsNumber: 6.02 <=== difference
False
VarType: 3.01 <=== difference
False
============================================================
VT before WF.IsN, s init True
VarType: 2.01 <=== difference
True
IsNumber: 6.02
True
------------------------------------------------------------
VT before WF.IsN, s init False
VarType: 3.01 <=== difference
False
IsNumber: 5.01 <=== difference
False
------------------------------------------------------------

Your code with addition using just WF rather than APP.WF

Sub foo()
Const MAXITER As Long = 1000000
Dim i As Long, s As Boolean, dt As Date, et As Date

Debug.Print "HG PC - 858MHz PIII, 128MB, Windows ME, Excel 2000 SP-3"

Debug.Print String(60, "=")

Debug.Print "App.IsN before VT, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "App.IsN before VT, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "App.WF.IsN before VT, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "App.WF.IsN before VT, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And Application.WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "=")

Debug.Print "VT before App.IsN, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "VT before App.IsN, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "VT before App.WF.IsN, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "VT before App.WF.IsN, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And Application.WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "=")

'=============

Debug.Print "WF.IsN before VT, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")

Debug.Print "WF.IsN before VT, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "=")



Debug.Print "VT before WF.IsN, s init True"
s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Deug.Print s

Debug.Print String(60, "-")

Debug.Print "VT before WF.IsN, s init False"
s = False
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = False
dt = Now
For i = 1 To MAXITER
s = s And WorksheetFunction.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

Debug.Print String(60, "-")



End Sub

I agree that VarType is faster. If you read my original post, I was
pointing out that isnumeric does not test how the value is stored like the
worksheetfunction IsNumber does - just as a point of contrast. Since they
have a similar name, there functionality often seems to be confused. After
I posted, I did think that it might be appropriate to mention vartype, but
didn't pursue it (efficiency wasn't really my concern although I assumed,
like you, that a vba function would be faster than a worksheet function).
 
Back
Top