How to avoid using Select in Excel VBA
Two main reasons why .Select, .Activate, Selection, Activecell, Activesheet, Activeworkbook, etc. should be avoided
- It slows down your code.
- It is usually the main cause of runtime errors.
How do we avoid it?
1) Directly work with the relevant objects
Consider this code
Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"
This code can also be written as
With Sheets("Sheet1").Range("A1")
.Value = "Blah"
.NumberFormat = "@"
End With
2) If required declare your variables. The same code above can be written as
Dim ws as worksheet
Set ws = Sheets("Sheet1")
With ws.Range("A1")
.Value = "Blah"
.NumberFormat = "@"
End With
That's a good answer, but what I am missing on this topic is when we actually need Activate. Everyone says it is bad, but no one explains any cases where it makes sense to use it.
Situation when you can't avoid using .Activate/.Select. (Will add more links as and when I come across them)
- When you want to present a worksheet to a user so that the user can see it.
- Scenarios like Working macro returns error when run from form control where you are forced to use
.Activate - When usual methods of
Text To Columns/.Formula = .Formuladoesn't work then you may have to resort to.Select
Some examples of how to avoid select
Use Dim'd variables
Dim rng as Range
Set the variable to the required range. There are many ways to refer to a single-cell range:
Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")
Or a multi-cell range:
Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1, 1), Cells(10, 2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10, 2)
You can use the shortcut to the Evaluate method, but this is less efficient and should generally be avoided in production code.
Set rng = [A1]
Set rng = [A1:B10]
All the above examples refer to cells on the active sheet. Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheet variable too:
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
With ws
Set rng = .Range(.Cells(1, 1), .Cells(2, 10))
End With
If you do want to work with the ActiveSheet, for clarity it's best to be explicit. But take care, as some Worksheet methods change the active sheet.
Set rng = ActiveSheet.Range("A1")
Again, this refers to the active workbook. Unless you specifically want to work only with the ActiveWorkbook or ThisWorkbook, it is better to Dim a Workbook variable too.
Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")
If you do want to work with the ActiveWorkbook, for clarity it's best to be explicit. But take care, as many WorkBook methods change the active book.
Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
You can also use the ThisWorkbook object to refer to the book containing the running code.
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")
A common (bad) piece of code is to open a book, get some data then close again
This is bad:
Sub foo()
Dim v as Variant
Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
v = ActiveWorkbook.Sheets(1).Range("A1").Value
Workbooks("SomeAlreadyOpenBook.xlsx").Activate
ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
Workbooks(2).Activate
ActiveWorkbook.Close()
End Sub
And it would be better like:
Sub foo()
Dim v as Variant
Dim wb1 as Workbook
Dim wb2 as Workbook
Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
v = wb2.Sheets("SomeSheet").Range("A1").Value
wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
wb2.Close()
End Sub
Pass ranges to your Subs and Functions as Range variables:
Sub ClearRange(r as Range)
r.ClearContents
'....
End Sub
Sub MyMacro()
Dim rng as Range
Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
ClearRange rng
End Sub
You should also apply Methods (such as Find and Copy) to variables:
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2
If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that:
Dim dat As Variant
Dim rng As Range
Dim i As Long
Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet
This is a small taster for what's possible.
One small point of emphasis I'll add to all the excellent answers given previously:
Probably the biggest thing you can do to avoid using Select is to as much as possible, use named ranges (combined with meaningful variable names) in your VBA code. This point was mentioned above, but it was glossed over a bit; however, it deserves special attention.
Here are a couple of additional reasons to make liberal use of named ranges, though I am sure I could think of more.
Named ranges make your code easier to read and understand.
Example:
Dim Months As Range
Dim MonthlySales As Range
Set Months = Range("Months")
' E.g, "Months" might be a named range referring to A1:A12
Set MonthlySales = Range("MonthlySales")
' E.g, "Monthly Sales" might be a named range referring to B1:B12
Dim Month As Range
For Each Month in Months
Debug.Print MonthlySales(Month.Row)
Next Month
It is pretty obvious what the named ranges Months and MonthlySales contain, and what the procedure is doing.
Why is this important? Partially because it is easier for other people to understand it, but even if you are the only person who will ever see or use your code, you should still use named ranges and good variable names because you will forget what you meant to do with it a year later, and you will waste 30 minutes just figuring out what your code is doing.
Named ranges ensure that your macros do not break when (not if!) the configuration of the spreadsheet changes.
Consider, if the above example had been written like this:
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")
Dim rng3 As Range
For Each rng3 in rng1
Debug.Print rng2(rng3.Row)
Next rng3
This code will work just fine at first - that is until you or a future user decides "gee wiz, I think I'm going to add a new column with the year in Column A!", or put an expenses column between the months and sales columns, or add a header to each column. Now, your code is broken. And because you used terrible variable names, it will take you a lot more time to figure out how to fix it than it should take.
If you had used named ranges to begin with, the Months and Sales columns could be moved around all you like, and your code would continue working just fine.
I'm going to give the short answer since everyone else gave the long one.
You'll get .select and .activate whenever you record macros and reuse them. When you .select a cell or sheet it just makes it active. From that point on whenever you use unqualified references like Range.Value they just use the active cell and sheet. This can also be problematic if you don't watch where your code is placed or a user clicks on the workbook.
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.