I’ve heard much about the understandable abhorrence of using
.Select in Excel VBA, but I am unsure of how to avoid using it. I am finding that my code would be more re-usable if I were able to use variables instead of
Select functions. However, I am not sure how to refer to things (like the
ActiveCell, etc.) if not using
I have found this article on ranges, but I can’t find anything on how.
How to avoid using Select in Excel VBA: Answer #1
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 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
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
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
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.
How to avoid using Select in Excel VBA: Answer #2
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").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
How to avoid using Select in Excel VBA: Answer #3
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 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
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
Sales columns could be moved around all you like, and your code would continue working just fine.
Follow Programming Articles for more!