How to avoid using Select in Excel VBA

Question

I am confused about how to avoid using it. Select in Excel VBA, despite hearing a lot about its justifiable dislike. I'm finding that if I could use variables rather than Select functions, my code would be more reusable. But if I don't use Select, I'm not sure how to refer to things (such the ActiveCell, etc.).

I have found this article on ranges and this example on the benefits of not using select, but I can't find anything on how.

Answer 1

Every time you record macros and reuse them, you'll receive.select and.activate. A cell or sheet is only made active when you choose it. From that point forward, the active cell and sheet are always used anytime an unqualified reference, such as Range.Value, is used. If you don't pay attention to where your code is placed or if a user clicks on the worksheet, this might potentially be a problem.

So, you can eliminate these issues by directly referencing your cells. Which goes:

'create and set a range
Dim Rng As Excel.Range
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
'OR
Set Rng = Workbooks(1).Worksheets(1).Cells(1, 1)

Or you could

'Just deal with the cell directly rather than creating a range
'I want to put the string "Hello" in Range A1 of sheet 1
Workbooks("Book1").Worksheets("Sheet1").Range("A1").value = "Hello"
'OR
Workbooks(1).Worksheets(1).Cells(1, 1).value = "Hello"

There are various combinations of these methods, but that would be the general idea expressed as shortly as possible for impatient people like me.