Is there a range version of IsNumeric just like VBA function HasFormula?

Question

I know that range().HasFormulareturns True only when every cell in the range has formula, otherwise it can return False or Null (when mixed). But there's no function like HasNumber. So to check if a range only contains number, I have to do

Dim all_numeric As Boolean
all_numeric = True
For Each cell In Range()
    If (Not IsNumeric(cell)) Or IsEmpty(cell) Then 'I also want to get rid of empty cell
        all_numeric = False
        Exit For
    End If
Next cell

Besides, there's WorksheetFunction.IsNumber that does similar thing but still needs a loop through the range. I am not sure if this can be very slow if the range contains a lot of numbers. And I wonder if there's any better way to check numeric values over a range object in VBA.

Answer 1

Maybe all_numeric = (r.Cells.Count - Application.Count(r)) = 0 (where r is a Range object)? – YowE3K 35 mins ago

That's indeed beautiful: it leverages Excel's own function that returns the number of numeric values in a range to determine the result:

WorksheetFunction.Count

Counts the number of cells that contain numbers and counts numbers within the list of arguments.

_{https://msdn.microsoft.com/en-us/library/office/ff840324.aspx}

Error cells and empty cells aren't counted, which fulfills your requirement of not counting empty cells.

That makes a nice UDF to expose in a standard module I find:

'@Description("Returns True if all cells in specified range have a numeric value.")
Public Function IsAllNumeric(ByVal target As Range) As Boolean
    IsAllNumeric = target.Cells.Count - Application.WorksheetFunction.Count(target) = 0
End Function

---

Note that I've used Application.WorksheetFunction.Count, not Application.Count:

• The latter is a late-bound call that makes the VBA runtime work much harder than it needs to, to find the Count method. You're working on an extended COM interface, so you don't have compile-time validation either: Application.IDontExist compiles perfectly fine, and blows up with run-time error 438. As with any other late-bound member call, the VBE's IntelliSense can't help you with the parameters:

  

• The former is an early-bound function call, which VBA resolves at compile-time. You're working on the WorksheetFunction interface directly, so the VBE gives you autocomplete and IntelliSense for the parameters.

  Autocomplete:

  

  IntelliSense:

  

The fact that the call is early-bound means no run-time overhead, therefore better performance - even if it ends up being the exact same internal Excel function that executes.

The drawback (if it's one at all) is that the late-bound Application.SomeFunction stuff is compatible with Excel4Macros, the old legacy pre-VBA way of automating Excel. So instead of raising run-time errors like their early-bound counterparts, the late-bound functions return error values such that you can should test them with IsError before you can assume what type you're actually getting.

With an early-bound WorksheetFunction.SomeFunction call, if the result Excel would display is #REF! or #VALUE!, or #N/A or whatever other possible error value, then you'll never be caught with a type mismatch run-time error for treating an error value as a String or a Long, or any other non-error VBA type. Instead, you just handle run-time errors, as you would with any other VBA API function call.

The late-bound call propagates an error value into your code; the early-bound call fails early: there could be 20 lines of code between where a cell value is read and where that value is used in a way that assumes there's no error value, and that instruction throws a type mismatch - and then you need to debug to trace back to the function that returned an error. With the early-bound code the function itself throws the error, so you don't have to dig it up.