When referring to a
range or individual
cells, it is important to fully qualify the reference.
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
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:
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
range objects, if not explicitly qualified, the
range will refer to the currently active sheet:
Is the same as:
If you want to delete rows (or columns) in a loop, you should always loop starting from the end of range and move back in every step. In case of using the code:
Dim i As Long With Workbooks("Book1").Worksheets("Sheet1") For i = 1 To 4 If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete Next i End With
You will miss some rows. For example, if the code deletes row 3, then row 4 becomes row 3. However, variable
i will change to 4. So, in this case the code will miss one row and check another, which wasn't in range previously.
The right code would be
Dim i As Long With Workbooks("Book1").Worksheets("Sheet1") For i = 4 To 1 Step -1 If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete Next i End With
ThisWorkbook sometimes get used interchangeably by new users of VBA without fully understanding which each object relates to, this can cause undesired behaviour at run-time. Both of these objects belong to the Application Object
ActiveWorkbook object refers to the workbook that is currently in the top-most view of the Excel application object at the time of execution. (e.g. The workbook that you can see and interact with at the point when this object is referenced)
Sub ActiveWorkbookExample() '// Let's assume that 'Other Workbook.xlsx' has "Bar" written in A1. ActiveWorkbook.ActiveSheet.Range("A1").Value = "Foo" Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints "Foo" Workbooks.Open("C:\Users\BloggsJ\Other Workbook.xlsx") Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints "Bar" Workbooks.Add 1 Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints nothing End Sub
ThisWorkbook object refers to the workbook in which the code belongs to at the time it is being executed.
Sub ThisWorkbookExample() '// Let's assume to begin that this code is in the same workbook that is currently active ActiveWorkbook.Sheet1.Range("A1").Value = "Foo" Workbooks.Add 1 ActiveWorkbook.ActiveSheet.Range("A1").Value = "Bar" Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints "Bar" Debug.Print ThisWorkbook.Sheet1.Range("A1").Value '// Prints "Foo" End Sub
Be aware that Microsoft Excel 2013 (and higher) uses Single Document Interface (SDI) and that Excel 2010 (And below) uses Multiple Document Interfaces (MDI).
This implies that for Excel 2013 (SDI), each workbook in a single instance of Excel contains its own ribbon UI:
This raise some important issues if you want to migrate a VBA code (2010 <->2013) that interact with the Ribbon.
A procedure has to be created to update ribbon UI controls in the same state across all workbooks for Excel 2013 and Higher.
Note that :
To get more details, see the source of this example: MSDN.
This also causes some trouble with modeless userforms. See Here for a solution.