excel-vba VBA Best Practices Avoid using ActiveCell or ActiveSheet in Excel
Example
Using ActiveCell or ActiveSheet can be source of mistakes if (for any reason) the code is executed in the wrong place.
ActiveCell.Value = "Hello"
'will place "Hello" in the cell that is currently selected
Cells(1, 1).Value = "Hello"
'will always place "Hello" in A1 of the currently selected sheet
ActiveSheet.Cells(1, 1).Value = "Hello"
'will place "Hello" in A1 of the currently selected sheet
Sheets("MySheetName").Cells(1, 1).Value = "Hello"
'will always place "Hello" in A1 of the sheet named "MySheetName"
- The use of
Active*can create problems in long running macros if your user gets bored and clicks on another worksheet or opens another workbook. - It can create problems if your code opens or creates another workbook.
- It can create problems if your code uses
Sheets("MyOtherSheet").Selectand you've forgotten which sheet you were on before you start reading from or writing to it.
