ApplicationWorksheetFunction

Warns about late-bound WorksheetFunction calls made against the extended interface of the Application object.

Inspection type: CodeQualityIssues

Default severity: Suggestion

Reasoning

An early-bound, equivalent function exists in the object returned by the Application.WorksheetFunction property; late-bound member calls will fail at run-time with error 438 if there is a typo (a typo fails to compile for an early-bound member call); given invalid inputs, these late-bound member calls return a Variant/Error value that cannot be coerced into another type. The equivalent early-bound member calls raise a more VB-idiomatic, trappable runtime error given the same invalid inputs: trying to compare or assign a Variant/Error to another data type will throw error 13 "type mismatch" at run-time. A Variant/Error value cannot be coerced into any other data type, be it for assignment or comparison.

This inspection will only run if the Excel library is referenced.

Quick-Fixes

The following quick-fixes are available for this inspection:

  • IgnoreOnce: Adds an '@Ignore annotation to ignore a specific inspection result. Applicable to all inspections whose results can be annotated in a module.
  • ApplicationWorksheetFunction: Replaces a late-bound Application.{Member} call to the corresponding early-bound Application.WorksheetFunction.{Member} call.

Examples

Each example is a self-contained MCVE showing the feature in a single specific scenario.

Standard Module
Named Module1 by default, these are general-purpose standard procedural modules.
Class Module
Named Class1 by default, these modules define the default interface for an object of that type.
Predeclared Class
A class module with its VB_PredeclaredId attribute set to True (with or without a @PredeclaredId annotation).
Interface Module
Abstract interfaces are class modules (with or without an @Interface annotation) whose default interface is not a class type intended to be instantiated.
UserForm Module
A predeclared class with a design-time visual designer using the MSForms UI framework.
Document Module
Owned by the host application, these class modules belong to the host document and may handle various events fired by the host application.

The following code should trigger this inspection:

Module1

Private Sub Example()
    Debug.Print Application.Sum(Array(1, 2, 3), 4, 5, "ABC") ' outputs "Error 2015" (no run-time error is raised).
    Dim foo As Long
    foo = Application.Sum(Array(1, 2, 3), 4, 5, "ABC") ' error 13 "type mismatch". Variant/Error can't be coerced to Long.
    If Application.Sum(Array(1, 2, 3), 4, 5, "ABC") > 15 Then
        ' won't run, error 13 "type mismatch" will be thrown when Variant/Error is compared to an Integer.
    End If
End Sub

Back to list

The content of this page was generated from xml/comments in the source code compiled into Rubberduck.CodeAnalysis.xml. Edit this page