Multi tool use

How to avoid using Select in Excel VBA

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.

.Select

Select

ActiveCell

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?

Select

ActiveSheet

12 Answers
12

Some examples of how to avoid select

Use Dim'd variables

Dim

Dim rng as Range


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

Set

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(2,10))
Set rng = Range("AnotherNamedRange")


You can use the shortcut to the Evaluate method, but this is less efficient and should generally be avoided in production code.

Evaluate

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

Worksheet

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.

ActiveSheet

Worksheet

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.

ActiveWorkbook

ThisWorkbook

Workbook

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.

ActiveWorkbook

WorkBook

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")


You can also use the ThisWorkbook object to refer to the book containing the running code.

ThisWorkbook

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:PathToSomeClosedBook.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 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:PathToSomeClosedBook.xlsx")
v = wb2.Sheets("SomeSheet").Range("A1").Value
wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
wb2.Close()
End Sub


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

Sub

Function

Sub ClearRange(r as Range)
r.ClearContents
'....
End Sub

Sub MyMacro()
Dim rng as Range
Set rng = ThisworkbookWorksheet("SomeSheet").Range("A1:B10")
ClearRange rng
End Sub


You should also apply Methods (such as Find and Copy) to variables

Find

Copy

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisworkbookWorksheet("SomeSheet").Range("A1:A10")
Set rng2 = ThisworkbookWorksheet("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 = ThisworkbookWorksheet("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.

...

rng1(12, 12)

[A1:A10]

Range

ActiveSheet.[a1:a4]

ws.[b6]

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

.Select

.Activate

Selection

Activecell

Activesheet

Activeworkbook

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


Sheets(2).[C10:D12].Value = Sheets(1).[A1:B3].Value

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:

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.

Months

MonthlySales

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:

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.

A

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.

Months

Sales

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.

Range.Value

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.

"... 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.

.Select

Selection

.Select

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:

Public Sub Run_on_Selected()
Dim rng As Range, rSEL As Range
Set rSEL = Selection 'store the current selection in case it changes
For Each rng In rSEL
Debug.Print rng.Address(0, 0)
'cell-by-cell operational code here
Next rng
Set rSEL = Nothing
End Sub

Public Sub Run_on_Selected_Visible()
'this is better for selected ranges on filtered data or containing hidden rows/columns
Dim rng As Range, rSEL As Range
Set rSEL = Selection 'store the current selection in case it changes
For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
Debug.Print rng.Address(0, 0)
'cell-by-cell operational code here
Next rng
Set rSEL = Nothing
End Sub

Public Sub Run_on_Discontiguous_Area()
'this is better for selected ranges of discontiguous areas
Dim ara As Range, rng As Range, rSEL As Range
Set rSEL = Selection 'store the current selection in case it changes
For Each ara In rSEL.Areas
Debug.Print ara.Address(0, 0)
'cell group operational code here
For Each rng In ara.Areas
Debug.Print rng.Address(0, 0)
'cell-by-cell operational code here
Next rng
Next ara
Set rSEL = Nothing
End Sub


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.

Selection

.Select

ActiveCell

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

.Select

Selection

Selection

Range

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:

Sub Macro1()
ActiveCell.Value = "foo"
End Sub


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:

Sub Macro2()
Range("B2").Select
Macro1
End Sub


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:

Sub SetValue(cellAddress As String, aVal As Variant)
Range(cellAddress).Value = aVal
End Sub


Then you can rewrite Macro2 as:

Sub Macro2()
SetCellValue "B2", "foo"
End Sub


And Macro1 as:

Sub Macro1()
SetValue ActiveCell.Address, "foo"
End Sub


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.

Select

Activate

Select

Activate

Parent

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

Select

Activate

From (code generated with macro recorder):

Sub Makro2()
Range("B2").Select
Sheets.Add After:=ActiveSheet
Sheets("Tabelle1").Select
Sheets("Tabelle1").Name = "NewName"
ActiveCell.FormulaR1C1 = "12"
Range("B2").Select
Selection.Copy
Range("B3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub


To:

Sub TestMe()
Dim ws As Worksheet
Set ws = Worksheets.Add
With ws
.Name = "NewName"
.Range("B2") = 12
.Range("B2").Copy Destination:=.Range("B3")
End With
End Sub


From:

Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste


To:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")


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

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")
Set MonthlySales = Range("MonthlySales")

Set Months =[Months]
Set MonthlySales = [MonthlySales]


The example from above would look like this:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]


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:

select

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

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.

'This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
Dim rng As Range
Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub

'This works always!
Public Sub TestMe2()
Dim rng As Range
With Worksheets(2)
.Range(.Cells(1, 1), .Cells(2, 2)).Copy
End With
End Sub


.Select

.Activate

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:

.Activate

.Select

Private Sub Workbook_Open()
Worksheets("Cover").Activate
End Sub


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

For example:

Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)


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.

Workbooks(1).Worksheets("fred").cells(1,1)


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

.select

selection

.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.

.select

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.

.select

.select

Quick Answer:

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

.Select

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

Cell A1

valOne = Range("A1").Value

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

valTwo = Sheets("Sheet3").Codename

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.

Application.GoTo Reference:=Workbook(WorkbookName).Worksheets(WorksheetName).Range("A1")
Range(Selection,selection(selectiontype)).clearcontents


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:

.Activate

.Select

Selection

ActiveSomething

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.

Selection

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

.Activate

.Select

.Selection

.ActiveSomething

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.

.Selection

.ActiveObject

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.

.Select

.Activate

.Activate

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?