excel-vba VBA Best Practices Avoid using SELECT or ACTIVATE
Example
It is very rare that you'll ever want to use Select or Activate in your code, but some Excel methods do require a worksheet or workbook to be activated before they'll work as expected.
If you're just starting to learn VBA, you'll often be suggested to record your actions using the macro recorder, then go look at the code. For example, I recorded actions taken to enter a value in cell D3 on Sheet2, and the macro code looks like this:
Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Sheet2").Select
Range("D3").Select
ActiveCell.FormulaR1C1 = "3.1415" '(see **note below)
Range("D4").Select
End Sub
Remember though, the macro recorder creates a line of code for EACH of your (user) actions. This includes clicking on the worksheet tab to select Sheet2 (Sheets("Sheet2").Select), clicking on cell D3 before entering the value (Range("D3").Select), and using the Enter key (which is effectively "selecting" the cell below the currently selected cell: Range("D4").Select).
There are multiple issues with using .Select here:
- The worksheet is not always specified. This happens if you don't switch worksheets while recording, and means that the code will yield different results for different active worksheets.
.Select()is slow. Even ifApplication.ScreenUpdatingis set toFalse, this is an unneccessary operation to be processed..Select()is unruly. IfApplication.ScreenUpdatingis left toTrue, Excel will actually select the cells, the worksheet, the form... whatever it is you're working with. This is stressful to the eyes and really unpleasant to watch..Select()will trigger listeners. This is a bit advanced already, but unless worked around, functions likeWorksheet_SelectionChange()will be triggered.
When you're coding in VBA, all of the "typing" actions (i.e. Select statements) are no longer necessary. Your code may be reduced to a single statement to put the value in the cell:
'--- GOOD
ActiveWorkbook.Sheets("Sheet2").Range("D3").Value = 3.1415
'--- BETTER
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myCell As Range
Set myWB = ThisWorkbook '*** see NOTE2
Set myWS = myWB.Sheets("Sheet2")
Set myCell = myWS.Range("D3")
myCell.Value = 3.1415
(The BETTER example above shows using intermediate variables to separate different parts of the cell reference. The GOOD example will always work just fine, but can be very cumbersome in much longer code modules and more difficult to debug if one of the references is mistyped.)
**NOTE: the macro recorder makes many assumptions about the type of data you're entering, in this case entering a string value as a formula to create the value. Your code doesn't have to do this and can simply assign a numerical value directly to the cell as shown above.
**NOTE2: the recommended practice is to set your local workbook variable to ThisWorkbook instead of ActiveWorkbook (unless you explicitly need it). The reason is your macro will generally need/use resources in whatever workbook the VBA code originates and will NOT look outside of that workbook -- again, unless you explicitly direct your code to work with another workbook. When you have multiple workbooks open in Excel, the ActiveWorkbook is the one with the focus which may be different from the workbook being viewed in your VBA Editor. So you think you're executing in a one workbook when you're really referencing another. ThisWorkbook refers to the workbook containing the code being executed.
