excel-vba VBA Best Practices Never Assume The Worksheet


Example

Even when all your work is directed at a single worksheet, it's still a very good practice to explicitly specify the worksheet in your code. This habit makes it much easier to expand your code later, or to lift parts (or all) of a Sub or Function to be re-used someplace else. Many developers establish a habit of (re)using the same local variable name for a worksheet in their code, making re-use of that code even more straightforward.

As an example, the following code is ambiguous -- but works! -- as long the developer doesn't activate or change to a different worksheet:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Cells(1, 1).Value = Now()  ' don't refer to Cells without a sheet reference!
End Sub

If Sheet1 is active, then cell A1 on Sheet1 will be filled with the current date and time. But if the user changes worksheets for any reason, then the code will update whatever the worksheet is currently active. The destination worksheet is ambiguous.

The best practice is to always identify which worksheet to which your code refers:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Dim myWB As Workbook
    Set myWB = ThisWorkbook
    Dim timestampSH As Worksheet
    Set timestampSH = myWB.Sheets("Sheet1")
    timestampSH.Cells(1, 1).Value = Now()
End Sub

The code above is clear in identifying both the workbook and the worksheet. While it may seem like overkill, creating a good habit concerning target references will save you from future problems.