How to avoid using Select in Excel VBA macros

Some examples of how to avoid select

Use Dim‘d variables

Dim rng as Range

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

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

or a multi-cell range

Set rng = Range("A1:B10")
Set rng = Range(Cells(1,1), Cells(2,10))
Set rng = [A1:B10]
Set rng = Range("AnotherNamedRange")

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

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1,1)

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

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

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

Sub ClearRange(r as Range)
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = [A1:B10]
    ClearRange rng
End Sub

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

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = [A1:A10]
Set rng2 = [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 = [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
rng.Value = dat ' put new values back on sheet

This is a small taster for what’s possible.

(Visited 32 times, 1 visits today)
The Use of Multiple JFrames: Good or Bad Practice? [closed]
How to fix android.os.NetworkOnMainThreadException?



Leave a Reply

Your email address will not be published. Required fields are marked *