njtynhj

I've heard much about the understandable abhorrence of using .Select in Excel VBA, but 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 can't find anything on how?

Some examples of how to avoid select

Use Dim'd variables

Set the variable to the required range. There are many ways to refer to a single-cell range

or a multi-cell range

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

Again, this refers to the active workbook, so you may want to be explicit here too.

Pass ranges to your Sub's and Function's as Range variables

You should also apply Methods (such as Find and Copy) 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.

Two Main reasons why .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook etc... should be avoided

How do we avoid it?

1) Directly work with the relevant objects

Consider this code

This code can also be written as

2) If required declare your variables. The same code above can be written as

One small point of emphasis I'll add to all the excellent answers given above:

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 glossed over a bit; however, it deserves special attention.

Here are a couple additional reasons to make liberal use of named ranges though I am sure I could think of more.

Example:

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.

Consider, if the above example had been written like this:

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 will 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:

Or you could

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

"... and am finding that my code would be more re-usable if I were able to use variables instead of Select functions."

While I cannot think of any more than an isolated handful of situations where .Select would be a better choice than direct cell referencing, I would rise to the defense of Selection and point out that it should not be thrown out for the same reasons that .Select should be avoided.

There are times when having short, time-saving macro sub routines assigned to hot-key combinations available with the tap of a couple of keys saves a lot of time. Being able to select a group of cells to enact the operational code on works wonders when dealing with pocketed data that does not conform to a worksheet-wide data format. Much in the same way that you might select a group of cells and apply a format change, selecting a group of cells to run special macro code against can be a major time saver.

Examples of Selection-based sub framework:

The actual code to process could be anything from a single line to multiple modules. I have used this method to initiate long running routines on a ragged selection of cells containing the filenames of external workbooks.

In short, don't discard Selection due to its close association with .Select and ActiveCell. As a worksheet property it has many other purposes.

_{(Yes, I know this question was about .Select, not Selection but I wanted to remove any misconceptions that novice VBA coders might infer.)}

Please note that in the following I'm comparing the Select approach (the one that the OP wants to avoid), with the Range approach (and this is the answer to the question). So don't stop reading when you see the first Select.

It really depends on what you are trying to do. Anyway a simple example could be useful. Let's suppose that you want to set the value of the active cell to "foo". Using ActiveCell you would write something like this:

If you want to use it for a cell that is not the active one, for instance for "B2", you should select it first, like this:

Using Ranges you can write a more generic macro that can be used to set the value of any cell you want to whatever you want:

Then you can rewrite Macro2 as:

And Macro1 as:

Hope this helps to clear things up a little bit.

Avoiding Select and Activate is the move that makes you a bit better VBA developer. In general, Select and Activate are used when a macro is recorded, thus the Parent worksheet or range is always considered the active one.

This is how you may avoid Select and Activate in the following cases:

From (code generated with macro recorder):

To:

From:

To:

You may access them with . Which is really beautiful, compared to the other way. Check yourself:

The example from above would look like this:

Usually, if you are willing to select, most probably you are copying something. If you are only interested in the values, this is a good option to avoid select:

Range("B1:B6").Value = Range("A1:A6").Value

This is probably the most common mistake at the vba. Whenever you copy ranges, sometimes the worksheet is not referred and thus VBA considers the ActiveWorksheet.

The only time when you could be justified to use .Activate and .Select is when you want make sure, that a specific Worksheet is selected for visual reasons. E.g., that your Excel would always open with the cover worksheet selected first, disregading which which was the activesheet when the file was closed. Thus, something like this is absolutely ok:

Always state the workbook, worksheet and the cell/range.

For example:

Because end users will always just click buttons and as soon as the focus moves off of the workbook the code wants to work with then things go completely wrong.

And never use the index of a workbook.

You don't know what other workbooks will be open when the user runs your code.

IMHO use of .select comes from people, who like me started learning VBA by necessity through recording macros and then modifying the code without realizing that .select and subsequent selection is just an unnecessary middle-men.

.select can be avoided, as many posted already, by directly working with the already existing objects, which allows various indirect referencing like calculating i and j in a complex way and then editing cell(i,j), etc.

Otherwise, there is nothing implicitly wrong with .select itself and you can find uses for this easily, e.g. I have a spreadsheet that I populate with date, activate macro that does some magic with it and exports it in an acceptable format on a separate sheet, which, however, requires some final manual (unpredictable) inputs into an adjacent cell. So here comes the moment for .select that saves me that additional mouse movement and click.

Quick Answer:

To avoid using the .Select method you can set a variable equal to the property that you want.

► For instance, if you want the value in Cell A1 you could set a variable equal to the value property of that cell.

► For instance, if you want the codename of 'Sheet3` you could set a variable equal to the codename property of that worksheet.

I hope that helps. Let me know if you have any questions.

This is an example that will clear the contents of cell "A1" (or more if the selection type is xllastcell, etc). All done without having to select the cells.

I hope this helps someone.

These methods are rather stigmatized, so taking the lead of @Vityata and @Jeeped for the sake of drawing a line in the sand:

Basically because they're called primarily to handle user input through the Application UI. Since they're the methods called when the user handles objects through the UI, they're the ones recorded by the macro-recorder, and that's why calling them is either brittle or redundant for most situations: you don't have to select an object so as to perform an action with Selection right afterwards.

However, this definition settles situations on which they are called for:

Basically when you expect the final user to play a role in the execution.

If you are developing and expects the user to choose the object instances for your code to handle, then .Selection or .ActiveObject are apropriate.

On the other hand, .Select and .Activate are of use when you can infer the user's next action and you want your code to guide the user, possibly saving him some time and mouse clicks. For example, if your code just created a brand new instance of a chart or updated one, the user might want to check it out, and you could call .Activate on it or its sheet to save the user the time searching for it; or if you know the user will need to update some range values, you can programatically select that range.

Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).


Would you like to answer one of these unanswered questions instead?