excel-vba VBA Best Practices Avoid using ActiveCell or ActiveSheet in Excel

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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").Select and you've forgotten which sheet you were on before you start reading from or writing to it.


Got any excel-vba Question?