How to avoid using Select in Excel VBA

Question

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?

Answer 1

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:

---

Adding a new Worksheet and copying a cell on it:

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

When you want to copy range between worksheets:

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

---

Using fancy named ranges

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]

---

Not copying values, but taking them

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

---

Try always to refer the Worksheet as well

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

---

Can I really never use .Select or .Activate for anything?

• A good example, 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 the code below is absolutely

ok:

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

• Another good example is when you need to export all sheets in one PDF file, as mentioned in this case - How to avoid select/active statements in VBA in this example?

Answer 2

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.

Answer 3

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.

Answer 4

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:

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

Answer 5

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.

Answer 6

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.