Rectangle 27 1

How to avoid using Select in Excel VBA?


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

@brettdj: Your citation is correct, but you forgot to mention that it is followed by six "Except..." phrases. One of them being: "Except as a substitute for cell references in macro coding Always use Excel Names as a substitute for cell references when constructing macros. This is to avoid errors arising from the insertion of additional rows or columns whereby the macro coding no longer points to the intended source data."

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

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

I agree with your development philosophy; however I think the paper is nonsense. It talks about how range names can confuse novices who are debugging spreadsheets, but anyone who uses novices to look at complex spreadsheets gets what they deserve! I used to work for a firm who reviewed financial spreadsheets, and I can tell you that it is not the sort of job you give to a novice.

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.

It is pretty obvious what the named ranges Months and MonthlySales contain, and what the procedure is doing.

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.

The debate about whether named ranges are good or bad spreadsheet design continues - I'm firmly in the no camp. In my experience they increase errors (for standard users who have no need of code).

There is no meaningful debate. Anyone who argues against defined names has not taken the time to fully understand their ramifications. Named formulas may be the single most profound and useful construct in all of Excel.

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.

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.

Note
Rectangle 27 1

How to avoid using Select in Excel VBA?


Sub Macro1()
    ActiveCell.Value = "foo"
End Sub
Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub
Sub Macro2()
    Range("B2").Select
    Macro1
End Sub
Sub Macro2()
    SetCellValue "B2", "foo"
End Sub
Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

I'm not sure I understand what you mean, but you can create a Range with a single instruction (e.g. Range("B5:C14")) and you can even set its value at once (if it has to be the same for every cell in the range), e.g. Range("B5:C14").Value = "abc"

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:

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:

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.

Thanks for the excellent response so quickly. So does that mean that if i would normally add cells to range, name the range, and iterate through it, i should jump straight to creating an array?

Then you can rewrite Macro2 as:

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:

Note
Rectangle 27 1

How to avoid using Select in Excel VBA?


Dim Months As Range
Dim MonthlySales As Range

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

Set Months =[Months]
Set MonthlySales = [MonthlySales]
Range("B1:B6").Value = Range("A1:A6").Value
Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste
Windows(1)
Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]

@DarrenBartrup-Cook - true, I was wondering how to do it here and I could not, thus I updated - stackoverflow.com/questions/47162375/

@T.M. - Yup, because of the link from Darren Bartup-Cook I have removed that part.

Estimated your "Sometimes it is unavoidable" part, helps to clarify the question in OP.

The example from above would look like this:

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

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:

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

Note
Rectangle 27 1

How to avoid using Select in Excel VBA?


.Activate
.Select
Activecell
Activesheet
Activeworkbook
Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With
Selection
Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"
With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With
  • It is usually the main cause of runtime errors.

1) Directly work with the relevant objects

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

I find that you may sometimes need to activate a sheet first if you need to paste or filter data on it. I would say its best to avoid activating as much as possible but there are instances where you need to do it. So keep activating and selecting to an absolute minimum as per the answer above.

That's a good answer, but what I am missing on this topic is when we actually need Activate. Everyone says it is bad, but no one explains any cases where it makes sense to use it. For example I was working with 2 workbooks and could not start a macro on one of the workbooks without activating it first. Could you elaborate a bit maybe? Also if for example I do not activate sheets when copying a range from one sheet to another, when I execute the program, it seems to activate the respective sheets anyways, implicitly.

This code can also be written as

i think the point is not to completely avoid using them, but just as much as possible. if you want to save a workbook, so that when someone opens it a certain cell in a certain sheet is selected, then you have to select that sheet and cell. copy/paste is a bad example, at least in the case of values, it can be done faster by a code such as Sheets(2).[C10:D12].Value = Sheets(1).[A1:B3].Value

Note
Rectangle 27 1

How to avoid using Select in Excel VBA?


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

I'm going to give the short answer since everyone else gave the long one.

So, you can elinate these issues by directly referencing your cells. Which goes:

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

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.

Note
Rectangle 27 1

How to avoid using Select in Excel VBA?


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

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

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 can be anything in the worksheet so might as well test first the type of the object before assigning it to a variable since you explicitly declared it as Range.

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.

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.

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.

Note
Rectangle 27 1

How to avoid using Select in Excel VBA?


Dim
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
next
rng.Value = dat ' put new values back on sheet
Dim rng as Range
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = [A1:A10]
Set rng2 = [B1:B10]
rng1.Copy rng2
Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1,1)
Set rng = Range("A1")
Set rng = Cells(1,1)
Set rng = [A1]
Set rng = Range("NamedRange")
Set rng = Range("A1:B10")
Set rng = Range(Cells(1,1), Cells(2,10))
Set rng = [A1:B10]
Set rng = Range("AnotherNamedRange")
Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

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

@chrisneilsen Chris, I believe you can also use worksheet prefix before shorthand cell reference notation to save you from typing Range like this: ActiveSheet.[a1:a4] or ws.[b6].

@mertinc yes you tagged correctly but if you have a question you should ask a question, not comment on an old thread

@qbik not advocating using a Sub to just clear a range (as the ... indicates), rather it's a demo of passing a Range as a parameter to a Sub.

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

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

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

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

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

adding to this brilliant answer that in order to work wit a range you don't need to know its actual size as long as you know the top left ... e.g. rng1(12, 12) will work even though rng1 was set to [A1:A10] only.

or a multi-cell range

Note
Rectangle 27 1

How to avoid using Select in Excel VBA?


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

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

And never use the index of a workbook.

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.

The names of worksheets can change, too, you know. Use codenames instead.

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

Note
Rectangle 27 1

How to avoid using Select in Excel VBA?


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

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.

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.

While you are right, there is at least one thing implicitly wrong with select: it is slow. Very slow indeed compared to everything else happening in a macro.

Note
Rectangle 27 1

How to avoid using Select in Excel VBA?


Dim
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
next
rng.Value = dat ' put new values back on sheet
Dim rng as Range
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = [A1:A10]
Set rng2 = [B1:B10]
rng1.Copy rng2
Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1,1)
Set rng = Range("A1")
Set rng = Cells(1,1)
Set rng = [A1]
Set rng = Range("NamedRange")
Set rng = Range("A1:B10")
Set rng = Range(Cells(1,1), Cells(2,10))
Set rng = [A1:B10]
Set rng = Range("AnotherNamedRange")
Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

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

@chrisneilsen Chris, I believe you can also use worksheet prefix before shorthand cell reference notation to save you from typing Range like this: ActiveSheet.[a1:a4] or ws.[b6].

@mertinc yes you tagged correctly but if you have a question you should ask a question, not comment on an old thread

@qbik not advocating using a Sub to just clear a range (as the ... indicates), rather it's a demo of passing a Range as a parameter to a Sub.

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

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

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

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

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

adding to this brilliant answer that in order to work wit a range you don't need to know its actual size as long as you know the top left ... e.g. rng1(12, 12) will work even though rng1 was set to [A1:A10] only.

or a multi-cell range

Note