问题描述
在VBA中使用这两个功能有何区别?与另一种相比,使用一种在速度上有优势吗?
Whats the different between using these two functions in VBA? Is there a speed advantage of using one over the other?
Application.WorksheetFunction.IsNumber(myVar)
IsNumeric(myVar)
推荐答案
TL; DR 除非您有特定的原因要对函数使用Excel的语义(即复制结果,否则将给出单元格,如果您关心性能,请不要使用IsNumber
.
TL;DR Unless you have a specific reason to use Excel's semantics for the function (i.e. replicating the result it would give in a cell, don't use IsNumber
if you care about performance.
首先,结果(和性能)将根据传递给函数的数据类型而有所不同.如果您要直接从工作表中提取值,则必须意识到以下事实:Excel将根据访问方式的不同而不同地投射值:
First of all, the result (and performance) is going to differ based on the data type that is passed into the function. If you're pulling values directly from the Worksheet, you have to be aware of the fact that Excel is going to cast the value differently depending on how you access it:
Public Sub Foo()
Debug.Print IsNumeric("1e12") 'True
Debug.Print Application.WorksheetFunction.IsNumber("1e12") 'False
[A1] = "1e12"
'The next 2 are the same, but the first is an implicit call to .Value
Debug.Print Application.WorksheetFunction.IsNumber([A1]) 'True
Debug.Print Application.WorksheetFunction.IsNumber([A1].Value) 'True
Debug.Print Application.WorksheetFunction.IsNumber([A1].Text) 'False
End Sub
接下来,对WorksheetFunction
的取消引用调用的开销很小,因此我将其包装在With
块中,以在下面的基准测试中对其进行控制.还要注意处理字符串和数字之间的性能差异(和返回值o_O):
Next, there is a small overhead of the dereferencing calls against WorksheetFunction
, so I'll control for that in the benchmarks below by wrapping it in a With
block. Note also the difference in performance (and return value o_O) between handling a string and a number:
Private Const ITERATIONS As Long = 1000000
Private Sub RunAll()
Test 1000
Test "1000"
End Sub
Private Sub Test(testValue As Variant)
IsNumericBenchMark testValue
IsNumberBenchMark testValue
End Sub
Private Sub IsNumericBenchMark(inputValue As Variant)
Dim start As Single, i As Long
start = Timer
For i = 1 To ITERATIONS
IsNumeric inputValue
Next
Debug.Print "IsNumeric" & vbTab & Timer - start & vbTab & IsNumeric(inputValue)
End Sub
Private Sub IsNumberBenchMark(inputValue As Variant)
Dim start As Single, i As Long
start = Timer
With WorksheetFunction
For i = 1 To ITERATIONS
.IsNumber inputValue
Next
End With
Debug.Print "IsNumber" & vbTab & Timer - start & vbTab & WorksheetFunction.IsNumber(inputValue)
End Sub
结果:
IsNumeric 0.09375 True
IsNumber 5.664063 True
IsNumeric 0.6796875 True
IsNumber 6.796875 False
这是官方的,IsNumber
并非表现最佳.
It's official, IsNumber
is not exactly a top performer.
这篇关于VBA中的WorksheetFunction.IsNumber()和IsNumeric()有什么区别?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!