excel-vba Workbooks When To Use ActiveWorkbook and ThisWorkbook


It's a VBA Best Practice to always specify which workbook your VBA code refers. If this specification is omitted, then VBA assumes the code is directed at the currently active workbook (ActiveWorkbook).

'--- the currently active workbook (and worksheet) is implied
Range("A1").value = 3.1415
Cells(1, 1).value = 3.1415

However, when several workbooks are open at the same time -- particularly and especially when VBA code is running from an Excel Add-In -- references to the ActiveWorkbook may be confused or misdirected. For example, an add-in with a UDF that checks the time of day and compares it to a value stored on one of the add-in's worksheets (that are typically not readily visible to the user) will have to explicitly identify which workbook is being referenced. In our example, our open (and active) workbook has a formula in cell A1 =EarlyOrLate() and does NOT have any VBA written for that active workbook. In our add-in, we have the following User Defined Function (UDF):

Public Function EarlyOrLate() As String
    If Hour(Now) > ThisWorkbook.Sheets("WatchTime").Range("A1") Then
        EarlyOrLate = "It's Late!"
        EarlyOrLate = "It's Early!"
    End If
End Function

The code for the UDF is written and stored in the installed Excel add-in. It uses data stored on a worksheet in the add-in called "WatchTime". If the UDF had used ActiveWorkbook instead of ThisWorkbook, then it would never be able to guarantee which workbook was intended.