excel-vba Common Mistakes Qualifying References
Example
When referring to a worksheet, a range or individual cells, it is important to fully qualify the reference.
For example:
ThisWorkbook.Worksheets("Sheet1").Range(Cells(1, 2), Cells(2, 3)).Copy
Is not fully qualified: The Cells references do not have a workbook and worksheet associated with them. Without an explicit reference, Cells refers to the ActiveSheet by default. So this code will fail (produce incorrect results) if a worksheet other than Sheet1 is the current ActiveSheet.
The easiest way to correct this is to use a With statement as follows:
With ThisWorkbook.Worksheets("Sheet1")
.Range(.Cells(1, 2), .Cells(2, 3)).Copy
End With
Alternatively, you can use a Worksheet variable. (This will most likely be preferred method if your code needs to reference multiple Worksheets, like copying data from one sheet to another.)
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
ws1.Range(ws1.Cells(1, 2), ws1.Cells(2, 3)).Copy
Another frequent problem is referencing the Worksheets collection without qualifying the Workbook. For example:
Worksheets("Sheet1").Copy
The worksheet Sheet1 is not fully qualified, and lacks a workbook. This could fail if multiple workbooks are referenced in the code. Instead, use one of the following:
ThisWorkbook.Worksheets("Sheet1") '<--ThisWorkbook refers to the workbook containing
'the running VBA code
Workbooks("Book1").Worksheets("Sheet1") '<--Where Book1 is the workbook containing Sheet1
However, avoid using the following:
ActiveWorkbook.Worksheets("Sheet1") '<--Valid, but if another workbook is activated
'the reference will be changed
Similarly for range objects, if not explicitly qualified, the range will refer to the currently active sheet:
Range("a1")
Is the same as:
ActiveSheet.Range("a1")
