Code Validation - Reference to a named range

Question

After starting the code check in Rubberduck 2.0.11.2453 there are 4 range links that are marked as:

Member 'Range' implicitly references ActiveSheet

The ranges indicated are for Named Ranges. Do I need to qualify a reference to a named range?

Private Sub RunORatio(ByVal TabNum As Integer) Dim Start As Integer, Cat As Integer, iMth As Integer, CurrentRow As Integer, Report As Integer Dim wsORatio As Worksheet, wsData As Worksheet, wsMacro As Worksheet Dim sMap As String, Test As String With ActiveWorkbook Set wsMacro = .Worksheets("Macro") Set wsORatio = .Worksheets("ORatio" & TabNum) With wsORatio sMap = "oratio" & TabNum & "map" For CurrentRow = 1 To Range(sMap).Rows.Count '<---1 here Test = Range(sMap).Cells(CurrentRow, 1) '<---1 Here Set wsData = ActiveWorkbook.Worksheets(Test) Start = Range(Range(sMap).Cells(CurrentRow, 2)).Row '<---2 Here Report = wsMacro.Range(sMap).Cells(CurrentRow, 3) For Cat = 0 To 12 For iMth = 1 To 12 wsORatio.Cells(Report + Cat, 7 + iMth) = wsData.Cells(Start + Cat, 37 + iMth) Next iMth Next Cat Next CurrentRow End With End With End Sub 

Answer 1

Disclaimer: I am actively involved in the development of Rubberduck.

Consider this common error:

 lastRow = Worksheets("Sheet12").Cells(1, Rows.Count).End(xlUp).Row 

Rows is unqualified and therefore implicitly refers to the active sheet, and therefore Rows.Count not necessarily the number of rows on "Sheet12". The code may work, but it can also lead to a subtle error, where lastRow does not have the correct value because of this, depending on the contents of the active sheet.

Or this one:

 ActiveWorkbook.Worksheets("SummarySheet") _ .ListObjects("Table1").Sort.SortFields.Add _ Key:=Range("Table1[[#All],["Date]]"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers 

You see? Since the Key parameter is not qualified, the call will fail at runtime with error 1004 - Method "Range" of object "_Global" failed. These are 169 questions . "Error 1004" gives 1465 stack overflow questions .

Implicit links to an active worksheet are a very common cause of errors.

---

Rubberduck VBA code checks, like ReSharper C # static code analysis, tips / suggestions. The tool tells you that there might be something here that might cause problems, or that makes the code less explicit than it could be.

Need to fully qualify every Range challenge? Of course not - Rubberduck just lets you know that in these cases, ActiveSheet implicitly refers to everything there is.

You can always tell Rubberduck “look, I know what I'm doing” using the “Ignore once” quick-fix:

This “fix” inserts a special comment (internally Rubberduck calls them “annotations”) that instructs the inspection to ignore specific results, leaving the check turned on:

  With ActiveWorkbook Set wsMacro = .Worksheets("Macro") Set wsORatio = .Worksheets("ORatio" & TabNum) With wsORatio sMap = "oratio" & TabNum & "map" '@Ignore ImplicitActiveSheetReference For CurrentRow = 1 To Range(sMap).Rows.Count '@Ignore ImplicitActiveSheetReference Test = Range(sMap).Cells(CurrentRow, 1) Set wsData = ActiveWorkbook.Worksheets(Test) '@Ignore ImplicitActiveSheetReference Start = Range(Range(sMap).Cells(CurrentRow, 2)).Row Report = wsMacro.Range(sMap).Cells(CurrentRow, 3) For Cat = 0 To 12 For iMth = 1 To 12 wsORatio.Cells(Report + Cat, 7 + iMth) = wsData.Cells(Start + Cat, 37 + iMth) Next iMth Next Cat Next CurrentRow End With End With 

These annotations have the advantage of telling the reader (the future or anyone who takes your code) that something is happening here.

Future versions will ultimately support specifying @Ignore annotations once at the module level to ignore all the results of a particular check throughout the module.

---

Please note that the verification is carried out in the section "Suitability and readability". Range("DefinedName") not half as explicit and fault tolerant as:

 ActiveWorkbook.Names("DefinedName").RefersToRange 

Which gives you the same range and reads how it actually pulls the named range, limited by the level of the workbook.

Answer 2

Disclaimer: I am also involved in the development of Rubberduck.

As @ Mat'sMug already pointed out, the inspection provides you with code information. What you do with this information is a matter of preference, sytle encoding, etc.

In this case, the check does not tell you that there is an error in your code, and it tells you that there is a potential possibility that implicit object references will cause unpredictable actions of your code. An excerpt from this particular test is that you let Excel decide how to interpret which object you are accessing.

From the documentation for Application.Range :

When used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns the range from the active sheet, if the active sheet is not a sheet, the property fails).

The last sentence is the first reason you shouldn't ignore this check - Range without choosing a qualifier if you selected Chart . This is for the same reason why you should use Workbook.Worksheets(foo) instead of Workbook.Sheets(foo) if you assign Worksheet (this is actually on Rubberduck's wish list ).

The second reason is related to the first. As you correctly point out in the comments, "the named range has the name of the sheet as part of its link" or rephrased the "named range that can be considered unique." But this should not be unique if you have more than one book open. Since ActiveSheet is always returned from an ActiveWorkbook during a call, if the Workbook code expects to be inactive, this code either issues or returns an invalid Range (if another Workbook coincides with a Range with the same name).

Keep in mind that although the With block captures a hard link to an ActiveWorkbook , the Application does not work - and the ActiveWorkbook may change as your code runs. All this is related to why you should avoid using ActiveFoo objects - they assume that errors will occur otherwise by the "correct" code.

The solution is simple. Just add a dot in front of them. This way they will call Workbook.Range through the captured hard link from the With block instead of Application.Range .