VBA_ Stop using useless Select, Activate, ActiveCell

인터넷에서 읽다가 좋아서 정리해 둔 것들인데 오래되서 몇 개는 소스출처가 없다.

Using useless Select, Activate, ActiveCell

url :: not found

Using Select, Activate and ActiveCell in VBA can bring problems. And shows problems. In general, if you use the macro-recorder and record a macro, then you would see lots of Select, Activate, ActiveCell and etc. And it would work. Then why is it a problem?

It is slow. All these .Activate and .Select actions take time.
It is not necessary – they could be avoided.
Consider this code (generated from a macro recorder):

VBA

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

And compare it with a code, which actually does the same, but with declaring the variables, without using the active ones and depending on them:

VBA

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

My adivece here is to consider .Select() and .Activate() as needless parts of the code and to refactor it until they are completely out. The only reason to leave them in the code is for aesthetics, e.g. at the end of the code, having a specific worksheet selected for display. This is a nice StackOverflow topic with some examples how to avoid them.

---

Stackoverflow _ GOOD

https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba

Some examples of how to avoid select

Use Dim’d variables

VBA

Dim rng as Range

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

VBA

Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")

Or a multi-cell range:

VBA

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.

VBA

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:

VBA

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.

VBA

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.

VBA

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.

VBA

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

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

VBA

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:

VBA

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:

VBA

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 Subs and Functions as Range variables:

VBA

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 Find and Copy) to variables:

VBA

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:

VBA

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.