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 Select
.
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.
Best Answer
Some examples of how to avoid select
Use
Dim
'd variablesSet
the variable to the required range. There are many ways to refer to a single-cell range:Or a multi-cell range:
You can use the shortcut to the
Evaluate
method, but this is less efficient and should generally be avoided in production code.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:If you do want to work with the
ActiveSheet
, for clarity it's best to be explicit. But take care, as someWorksheet
methods change the active sheet.Again, this refers to the active workbook. Unless you specifically want to work only with the
ActiveWorkbook
orThisWorkbook
, it is better to Dim aWorkbook
variable too.If you do want to work with the
ActiveWorkbook
, for clarity it's best to be explicit. But take care, as manyWorkBook
methods change the active book.You can also use the
ThisWorkbook
object to refer to the book containing the running code.A common (bad) piece of code is to open a book, get some data then close again
This is bad:
And it would be better like:
Pass ranges to your
Sub
s andFunction
s as Range variables:You should also apply Methods (such as
Find
andCopy
) to variables: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:
This is a small taster for what's possible.