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.
