| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numerical Data vs Text
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
Jim,
Sub TestMe() MsgBox isnumeric(Range("B8")) End Sub Will return True if it's numeric and False if not John "Jim Hollis" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
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. -- Regards, Tom Ogilvy John Wilson wrote in message ... Jim, Sub TestMe() MsgBox isnumeric(Range("B8")) End Sub Will return True if it's numeric and False if not John "Jim Hollis" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
"Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy John Wilson wrote in message ... Jim, Sub TestMe() MsgBox isnumeric(Range("B8")) End Sub Will return True if it's numeric and False if not John "Jim Hollis" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
Tom,
Thanks for the clarification. John "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy John Wilson wrote in message ... Jim, Sub TestMe() MsgBox isnumeric(Range("B8")) End Sub Will return True if it's numeric and False if not John "Jim Hollis" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
"Tom Ogilvy" wrote...
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. If the OP is testing cell values, wouldn't VarType(Rng.Value) = vbDouble be more efficient? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
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 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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
What did your tests show?
-- Regards, Tom Ogilvy Harlan Grove wrote in message ... "Tom Ogilvy" wrote... 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. If the OP is testing cell values, wouldn't VarType(Rng.Value) = vbDouble be more efficient? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
"Tom Ogilvy" wrote...
What did your tests show? .... Harlan Grove wrote in message "Tom Ogilvy" wrote... .... ? application.IsNumber("88") False .... VarType(Rng.Value) = vbDouble 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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
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. -- Regards, Tom Ogilvy Harlan Grove wrote in message ... "Tom Ogilvy" wrote... What did your tests show? ... Harlan Grove wrote in message "Tom Ogilvy" wrote... ... ? application.IsNumber("88") False ... VarType(Rng.Value) = vbDouble 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. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numerical Data vs Text
"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. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need my formula to analyse numerical and text data | Excel Discussion (Misc queries) | |||
| Conditional Formatting; finding numerical data in mixed text. | Excel Discussion (Misc queries) | |||
| Text and Numerical data in a Pivottable without summarising? | Excel Discussion (Misc queries) | |||
| Numerical Value to text | New Users to Excel | |||
| Prevent non-numerical data (e.g. text) from plotting on a chart | Charts and Charting in Excel | |||
Linear Mode
