VBA中的WorksheetFunction.IsNumber()和IsNumeric()有什么区别? [英] Whats the difference between WorksheetFunction.IsNumber() and IsNumeric() in VBA?

问题描述

在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()有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！