Numerical Data vs Text
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\0
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).
--
Regards,
Tom Ogilvy
Harlan Grove wrote in message
...
"Tom Ogilvy" wrote...
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
Presumably this was only reversing the tests.
Vartype: 1.00
False
IsNumber: 3.01
False
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
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.
...
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.
|