SheetAccessedUsingString

Locates ThisWorkbook.Worksheets and ThisWorkbook.Sheets calls that appear to be dereferencing a worksheet that is already accessible at compile-time with a global-scope identifier.

Inspection type: LanguageOpportunities

Default severity: Suggestion

Reasoning

Sheet names can be changed by the user, as can a worksheet's index in ThisWorkbook.Worksheets. Worksheets that exist in ThisWorkbook at compile-time are more reliably programmatically accessed using their CodeName, which cannot be altered by the user without accessing the VBE and altering the VBA project.

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

Remarks

For performance reasons, the inspection only evaluates hard-coded string literals; string-valued expressions evaluating into a sheet name are ignored.

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.
  • AccessSheetUsingCodeName: Modifies a Workbook.Worksheets or Workbook.Sheets call accessing a sheet of ThisWorkbook that exists at compile-time.
  • IgnoreInModule: Adds an '@IgnoreModule annotation to ignore a inspection results for a specific inspection inside a whole module. Applicable to all inspections whose results can be annotated in a module.

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:

MyModule

Public Sub DoSomething()
    Dim sheet As Worksheet
    Set sheet = ThisWorkbook.Worksheets("Sheet1") ' Sheet "Sheet1" exists at compile-time
    sheet.Range("A1").Value = 42
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