Sheet Accessed Using String

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.

Remarks

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

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.

Default severity

Suggestion

Inspection type

LanguageOpportunities

This inspection will only run when the Excel library is referenced

Examples

This example should trigger a result

MyModule (StandardModule)
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

This example should NOT trigger a result

MyModule (StandardModule)
Public Sub DoSomething() Sheet1.Range("A1").Value = 42 ' TODO rename Sheet1 to meaningful name End Sub

Rubberduck.CodeAnalysis.Inspections.Concrete.SheetAccessedUsingStringInspection.cs (Prerelease-v2.5.9.6289)

 Edit this page