What
Rubberduck should be able to locate superfluous dereferencing of certain objects in the Excel object model. However categorizing this under "language opportunities" would be conflating VBA (the language) with the Excel type library / object model, so a new "library opportunities" category might be warranted for it.
Why
The Excel object model is extremely flexible; default members returning a workbook or worksheet's name, can easily be used to get the very same object from a collection object. Such calls are redundant and should be avoided.
Examples
Redundant worksheet dereferencing:
Public Sub DoSomething(ByVal ws As Worksheet)
Worksheets(ws).Range("A1").Value = 42 ' ws is already a Worksheet
End Sub
Redundant workbook dereferencing:
Public Sub DoSomething(ByVal wb As Workbook)
Workbooks(wb).Worksheets(1).Range("A1").Value = 42 ' wb is already a Workbook
End Sub
Redundant range dereferencing:
Public Sub DoSomething(ByVal target As Range)
Range(target.Address).Value = 42 ' target is already a Range
End Sub
Note that this last example assumes the objective is to get a cell on the same sheet as the supplied Range object - but implicitly refers to the active sheet. Inspection info should probably mention that.
QuickFixes
A quickfix should be available to remove the redundant code, depending on the type of object being dereferenced:
Use existing object reference
Example code, after quickfix is applied:
Public Sub DoSomething(ByVal ws As Worksheet)
ws.Range("A1").Value = 42
End Sub
Public Sub DoSomething(ByVal wb As Workbook)
wb.Worksheets(1).Range("A1").Value = 42
End Sub
Public Sub DoSomething(ByVal target As Range)
target.Value = 42
End Sub
Resources
InspectionNames
Redundant Excel object dereferencing
InspectionInfo
An Excel object is being retrieved from an object collection, but the retrieved object is already in local scope, making the dereferencing redundant; consider using the available local object directly.
InspectionResults
Expression '{0}' is retrieving an object that is already in local scope ('{1}').
Where {0} is the collection indexer expression (implicit or not), e.g. Worksheets(ws) or Worksheets(ws.Name) or Worksheets.Item(ws.Name), and {1} is the name of the object variable, e.g. ws.
What Rubberduck should be able to locate superfluous dereferencing of certain objects in the Excel object model. However categorizing this under "language opportunities" would be conflating VBA (the language) with the Excel type library / object model, so a new "library opportunities" category might be warranted for it.
Why The Excel object model is extremely flexible; default members returning a workbook or worksheet's name, can easily be used to get the very same object from a collection object. Such calls are redundant and should be avoided.
Examples
Redundant worksheet dereferencing:
Redundant workbook dereferencing:
Redundant range dereferencing:
Note that this last example assumes the objective is to get a cell on the same sheet as the supplied
Range
object - but implicitly refers to the active sheet. Inspection info should probably mention that.QuickFixes A quickfix should be available to remove the redundant code, depending on the type of object being dereferenced:
Use existing object reference
Example code, after quickfix is applied:
Resources
InspectionResults
Where
{0}
is the collection indexer expression (implicit or not), e.g.Worksheets(ws)
orWorksheets(ws.Name)
orWorksheets.Item(ws.Name)
, and{1}
is the name of the object variable, e.g.ws
.