How to avoid using Select in Excel VBA
Some examples of how to avoid select
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 WorksheetSet 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
ThisWorkbook, it is better to Dim a
Workbook variable too.
Dim wb As WorkbookSet 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
Functions as Range variables:
Sub ClearRange(r as Range) r.ClearContents '....End SubSub MyMacro() Dim rng as Range Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10") ClearRange rngEnd Sub
You should also apply Methods (such as
Copy) to variables:
Dim rng1 As RangeDim rng2 As RangeSet 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 VariantDim rng As RangeDim i As LongSet 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 performnextrng.Value = dat ' put new values back on sheet
This is a small taster for what's possible.
Two main reasons why
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").ActivateRange("A1").SelectSelection.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 worksheetSet 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
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.
Dim Months As RangeDim MonthlySales As RangeSet Months = Range("Months")' E.g, "Months" might be a named range referring to A1:A12Set MonthlySales = Range("MonthlySales")' E.g, "Monthly Sales" might be a named range referring to B1:B12Dim Month As RangeFor Each Month in Months Debug.Print MonthlySales(Month.Row)Next Month
It is pretty obvious what the named ranges
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 RangeDim rng2 As RangeSet rng1 = Range("A1:A12")Set rng2 = Range("B1:B12")Dim rng3 As RangeFor 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
Sales columns could be moved around all you like, and your code would continue working just fine.