2012-05-23 05:57:58 8 Comments

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?


@Vityata 2016-03-08 10:04:12

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()
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle1").Name = "NewName"
    ActiveCell.FormulaR1C1 = "12"
    Application.CutCopyMode = False
End Sub


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:




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 reference the Worksheet as well

This is probably the most common mistake in . Whenever you copy ranges, sometimes the worksheet is not referenced and thus VBA considers the wrong sheet 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 of when you could be justified in using .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, disregarding which which was the ActiveSheet when the file was closed.

Thus, something like the code below is absolutely OK:

Private Sub Workbook_Open()
End Sub

@Geoff Griswald 2020-02-20 17:53:37

You can use Application.Goto instead of Worksheets.Activate. Somewhat less risky.

@T.M. 2020-03-11 14:17:59

Late reply FYI - a nice and somewhat unexpected example for a needed .Select - as well as my work around - can be found at How to write identical information to all sheets - @Vityata :)

@Vityata 2020-03-11 14:48:08

@T.M. - indeed it is an interesting example, and probably saves some milliseconds for 100+ worksheets, but I would probably discourage it, if I see it somewhere. Anyway, the Selection there is not explicitly written but a result of .FillAcrossSheets, so this is somewhere in between (at least in my idea about VBA taxonomy)

@Geoff Griswald 2020-03-02 13:04:58

The main reason never to use Select or Activesheet is because most people will have at least another couple of workbooks open (sometimes dozens) when they run your macro, and if they click away from your sheet while your macro is running and click on some other book they have open, then the "Activesheet" changes, and the target workbook for an unqualified "Select" command changes as well.

At best, your macro will crash, at worst you might end up writing values or changing cells in the wrong workbook with no way to "Undo" them.

I have a simple golden rule that I follow: Add variables named "wb" and "ws" for a Workbook object and a Worksheet object and always use those to refer to my macro book. If I need to refer to more than one book, or more than one sheet, I add more variables.

for example

Dim wb as Workbook
Dim ws as Worksheet
Set wb = ThisWorkBook
Set ws = wb.sheets("Output")

The "Set wb = ThisWorkbook" command is absolutely key. "ThisWorkbook" is a special value in Excel, and it means the workbook that your VBA code is currently running from. A very helpful shortcut to set your Workbook variable with.

After you've done that at the top of your Sub, using them could not be simpler, just use them wherever you would use "Selection":

So to change the value of cell "A1" in "Output" to "Hello", instead of:

Selection.Value = "Hello"

We can now do this:

ws.Range("A1").Value = "Hello"

Which is not only much more reliable and less likely to crash if the user is working with multiple spreadsheets, it's also much shorter, quicker and easier to write.

As an added bonus, if you always name your variables "wb" and "ws", you can copy and paste code from one book to another and it will usually work with minimal changes needed, if any.

@BigBen 2020-03-02 15:13:13

Not my downvote, but I'm not sure this adds anything new to what has already been proposed in existing answers.

@Geoff Griswald 2020-03-04 10:13:03

Yeah, my answer is slightly redundant, but the other answers are too long, contain too much superfluous stuff and nobody has mentioned using ThisWorkbook to set your worksheet variable upfront. That is something which, if someone had shown me the first time I dipped a toe into VBA, I would have found incredibly helpful. Others have mentioned using a Worksheet variable but don't really explain why very well, and don't offer an example of code with and without using worksheet and workbook variables.

@BigBen 2020-03-04 12:11:38

But the accepted answer definitely discusses ThisWorkbook... I'm not sure your comment is accurate.

@Geoff Griswald 2020-03-04 16:23:49

It does, you're not wrong. But not in the context of using it to set a workbook variable and using that workbook variable going forward, or using that workbook variable to set a worksheet variable, as I'm suggesting. My answer is shorter, simpler and more accessible for beginners than the accepted answer.

@barneyos 2019-10-30 09:45:23

Working with .Parent feature. This example shows how setting only one myRng reference enables dynamic access to the entire environment with no .Select, .Activate, .Activecell, .ActiveWorkbook, .ActiveSheet and so on. (There's no genereic .Child feature)

Sub ShowParents()
    Dim myRng As Range
    Set myRng = ActiveCell
    Debug.Print myRng.Address                    ' an address of the selected cell
    Debug.Print                ' the name of sheet, where MyRng is in
    Debug.Print         ' the name of workbook, where MyRng is in
    Debug.Print  ' the name of application, where MyRng is in

    ' You may use this feature to set reference to these objects
    Dim mySh    As Worksheet
    Dim myWbk   As Workbook
    Dim myApp   As Application

    Set mySh = myRng.Parent
    Set myWbk = myRng.Parent.Parent
    Set myApp = myRng.Parent.Parent.Parent
    Debug.Print, mySh.Cells(10, 1).Value
    Debug.Print, myWbk.Sheets.Count
    Debug.Print, myApp.Workbooks.Count

    ' You may use dynamically addressing
    With myRng

       ' pastes in D1 on sheet 2 in the same workbook, where copied cell is
        .Parent.Parent.Sheets(2).Range("D1").PasteSpecial xlValues
    ' or myWbk.Sheets(2).Range("D1").PasteSpecial xlValues

       ' we may dynamically call active application too
        .Parent.Parent.Parent.CutCopyMode = False
    ' or myApp.CutCopyMode = False
    End With
End Sub

@Geoff Griswald 2020-03-02 13:18:18

Very nice, but not sure what this has to do with OPs question. You don't need "Parent" at all to work in VBA without using Select or ActiveSheet

@chris neilsen 2012-05-23 10:23:22

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 = Range("NamedRange")

or a multi-cell range

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.

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

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.

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.

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.

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

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

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
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
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:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
End Sub

Pass ranges to your Subs and Functions as Range variables

Sub ClearRange(r as Range)
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

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

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
rng.Value = dat ' put new values back on sheet

This is a small taster for what's possible.

@MikeD 2015-01-12 18:07:58

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.

@Logan Reed 2016-07-15 18:20:09

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

@Andrew Willems 2018-02-06 02:12:01

Your excellent answer misses one part of the OP's question: How do you refer to the active cell? e.g. When creating/editing a sophisticated macro for use with a user-defined cell I don't understand how it can be avoided (but correct me if I'm wrong). So, e.g. MsgBox (ActiveCell.Value) works but Dim ac As Range ac = ActiveCell & MsgBox (ac.Value) does not. I've also tried dim'ing as Excel.Range, Object & Variant. So, can you dimension a variable that will eventually contain the active cell, and if so, how? Or is one forced to always use ActiveCell directly?

@Andrew Willems 2018-02-06 02:44:33

OK, humility check: Your answer as it currently stands mentions the keyword Set 17 times, but I still missed it. However in case others are wondering the same thing the short answer is: To refer to the active cell without Select, do the following: Dim ac as Range and Set ac = ActiveCell (and don't forget Set).

@ashleedawg 2018-04-07 11:38:57

@AndrewWillems ...or 48 times in this post, but who's counting. ☺ ...but seriously, it's an easy one to forget when working with variables holding objects. A variant variable doesn't require Set until you assign an object to it. For example, Dim x: x = 1 is okay, but Dim x: x = Sheets("Sheet1") will generate Error 438. However just to confuse/clarify Dim x: x = Range("A1") will not create an error. Why? ...because it's assigned the value of the object to the variable, not a reference to the object itself (since it's the equivalent of Dim x: x = Range("A1").Value)

@ashleedawg 2018-04-07 11:52:23

@chrisneilsen - I would link to this detailed answer when I see people using multiple lines of inefficient code to do something that could be accomplished in one line, except I don't want more people getting into the bad habit of using [sʜᴏʀᴛᴄᴜᴛ ɴᴏᴛᴀᴛɪᴏɴ]. I suspect you know to only use it for debugging, etc (such as in your examples) but I know of at least a couple users that saw it for the first time here and went, "Cool, I'm using that all the time from now on!"

@Zev Spitz 2018-05-03 05:45:58

@Mertinc Range.Find returns a Range object with the first matching cell. This means you should be able to do: Dim match As Range: Set match = Cells.Find(...): match.Value=match.Value to set the value of the cell to the formula result without the formula.

@TylerH 2018-12-13 15:33:52

@user3932000 I'm not aware of a scenario where sheetnames change automatically. As for filenames, it would only do that if there is already a file of that name in the folder. Just use Save... or hard code the file name to save it under as a string. If you can't solve this problem, you should ask a separate question about it instead of commenting.

@user3932000 2018-12-17 22:58:05

@TylerH I have a read-only template xlsx file, which I enter data into then export as tab-delimited text files. Each "Save As" changes the sheet name.

@TylerH 2018-12-17 23:00:26

@user3932000 OK, well changing the file format would necessitate that, but then it's not really an xlsx file anymore, but rather a bunch of text files.

@chris neilsen 2018-12-18 03:42:40

@user3932000 that would make an interesting Q. I'm sure there are ways to handle it.. you've been on SO long enough to know hijacking a comment thread on an old Q isn't the way to go

@PGSystemTester 2018-08-16 20:07:44

I noticed that none of these answers mention the .Offset Property. This also can be used to avoid using the Select action when manipulating certain cells, particularly in reference to a selected cell (as the OP mentions with ActiveCell).

Here are a couple examples.

I will also assume the "ActiveCell" is J4.

ActiveCell.Offset(2, 0).Value = 12

  • This will change the cell J6 to be a value of 12
  • A minus -2 would have referenced J2

ActiveCell.Offset(0,1).Copy ActiveCell.Offset(,2)

  • This will copy the cell in k4 to L4.
  • Note that "0" is not needed in the offset parameter if not needed (,2)
  • Similar to the previous example a minus 1 would be i4

ActiveCell.Offset(, -1).EntireColumn.ClearContents

  • This will clear values in all cells in the column k.

These aren't to say they are "better" than the above options, but just listing alternatives.

@LFB 2018-06-16 14:18:51

These methods are rather stigmatized, so taking the lead of @Vityata and @Jeeped for the sake of drawing a line in the sand:

Why not call .Activate, .Select, Selection, ActiveSomething methods/properties

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.

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

When to call .Activate, .Select, .Selection, .ActiveSomething methods/properties

Basically when you expect the final user to play a role in the execution.

If you are developing and expect the user to choose the object instances for your code to handle, then .Selection or .ActiveObject are apropriate.

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.

@MattB 2014-02-27 21:09:40

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

@marionffavp 2017-05-05 14:30:22

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

I hope this helps someone.

@Geoff Griswald 2020-03-02 13:16:01

No, sorry. That's not what you've done there at all. What you've actually done is to Select cell "A1" using the "Application.GoTo" command, which is no different from using "Select" really, then used clearcontents on your selection. the way to do that without selecting cells would be Workbook(WorkbookName).Worksheets(WorksheetName).Range("A1")‌​.ClearContents which is one line and not two, and actually works without selecting cells.

@Eleshar 2016-11-20 15:02:00

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.

@vacip 2016-11-22 13:25:33

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.

@FinPro.Online 2016-09-08 06:36:33

Quick Answer:

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

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

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

  • Example valTwo = Sheets("Sheet3").Codename

I hope that helps. Let me know if you have any questions.

@user4039065 2015-02-24 15:41:21

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

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.

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

@L42 2015-05-19 22:19:24

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.

@user1644564 2014-08-25 09:54:22

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

For example:


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.


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

@Rick supports Monica 2014-11-23 14:33:00

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

@Geoff Griswald 2020-03-02 13:21:22

Worksheet names can change, sure. But I disagree that you should over-complicate your code to try and mitigate that. If a user changes the name of a sheet and their macro stops working, that's on them. I generally just assume worksheet names are going to be the same. For particularly critical macros, I run a little pre-flight check before launching into the macro proper, which just checks to make sure all the sheets it expects to find are indeed there, and if any are missing it notifies the user which one.

@Rick supports Monica 2014-05-28 14:04:33

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.

Named ranges make your code easier to read and understand.


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.

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.

Named ranges ensure that your macros do not break when (not if!) the configuration of the spreadsheet changes.

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.

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.

@brettdj 2015-02-27 08:15:31

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

@Rick supports Monica 2015-02-27 15:24:42

I wasn't even aware there is a debate.

@brettdj 2015-02-28 07:23:52

@Rick supports Monica 2015-02-28 20:17:08

Interesting. Though it seems like the point of that paper isn't so much that names cause problems, but bad use of names causes problems. In civil engineering (my field), names are absolutely essential and in my experience not using them leads in general to more problems than using them. But as with anything they need to be used correctly.

@DeanOC 2015-03-24 23:08:57

brettdj has totally missed the point on this one. The paper he refers to is concerned with the use of range names in a spreadsheet. This has nothing to do with Rick's recommendation which is basically that when writing code give your variables a name reflecting their purpose (which is best practice in other languages such as VB.NET and C#).

@Rick supports Monica 2015-03-25 01:04:33

Well to be fair, I was recommending both. I still think using them in tandem is usually a good idea but the paper makes some good points worth considering.

@DeanOC 2015-03-25 01:18:53

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.

@Excel Hero 2015-08-21 00:28:31

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.

@brettdj 2016-07-14 08:11:23

From the FAST Standard, Section 3.03-08 Do not use Excel Names. "The FAST Standard advocates a very limited and precise use of Named Ranges."

@Marcus Mangelsdorf 2017-11-03 10:35:25

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

@brettdj 2017-11-05 05:34:36

@MarcusMangelsdorf Given I did say "The FAST Standard advocates a very limited and precise use of Named Ranges." I don't think I misrepresented this. FWIW I think macros should have even more limited used than Names. :)

@nateAtwork 2017-11-29 19:39:49

@brettdj I like the paper you cited (How do Range Names Hinder Novice Debugging Performance?), and I may take that into account in the future, but in my experience it doesn't address the main reason for using named ranges. I typically don't use named ranges to ease debugging, but to prevent it. Named ranges update automatically when rows are added or if cells are cut and pasted, so if you are using a spreadsheet that may get reformatted, you would be crazy not to use named ranges. You would need to go through line by line after cosmetic updates to a form!

@Alex M 2019-09-17 21:49:56

Look at the methodology in the cited paper. First, it's totally non applicable to the discussion in this QA (VBA code, which the paper doesn't address), as has been said. Second, it only purports to claim one thing - that using named ranges hinders the efforts of novice users to debug a spreadsheet that has been set up with errors in the cell reference of the name ranges themselves. Which A) should be no surprise to anyone and B) just isn't the kind of thing that most users here need to worry about.

@Rick supports Monica 2020-02-21 18:54:29

@GeoffGriswald It is impossible to quickly read, understand, and vet a complex Excel sheet full of engineering calculations without using named ranges. Excel is the wrong tool for engineering calculations, but because everyone has it and no other tool is widely used enough to be reliably available to everyone, Excel is what people tend to use. It is at least a tolerable tool with liberal use of named ranges. Without, it is a disaster.

@Geoff Griswald 2020-02-23 01:56:19

Its definitely not impossible. I find the exact opposite, trying to trace someone's formulas through a maze of named ranges and tables is a nightmare I fear I may never escape from. I would favour a simple index sheet or table of contents over named ranges any day.

@Geoff Griswald 2020-03-02 15:46:28

I am definitely on the "never named ranges" side of the fence. Yes, it's possible for users to reformat, add rows and remove rows on a spreadsheet that I've supplied them with. It's also possible for those same users to move the named range scope around, rename it, or add data which falls outside of the scope of the range and then ask me why their macro isn't picking up that data. The reasons for using a range are "Because I don't have to remember where any specific cells are", but in fact all you do is replace that with having to remember what all your ranges are.

@Rick supports Monica 2020-03-02 16:04:59

@GeoffGriswald The entire point is to use them in contexts where there is no "remembering". This formula is directly out of my concrete footing design spreadsheet: Vu/(ϕ*0.0316*β*(fc/1000)^0.5*b_footing*12). If it did not have named ranges, it would be very difficult to read. But with named ranges, any civil engineer familiar with highway code concrete shear design knows what that formula is doing without having to think about it. And I say again: it is impossible to get that level of readability without named ranges.

@Rick supports Monica 2020-03-02 16:13:04

@GeoffGriswald Furthermore, if I am writing VBA or python code using the values associated with named ranges, the readability afforded to that code by using them is very powerful (e.g., foo = book.name_map('foo')[0] in python is very clear if you know the xlrd API). "never named ranges"-- like pretty much any other "never" position-- is much less reasonable than "sometimes named ranges".

@Geoff Griswald 2020-03-04 10:04:21

Ok. For this very, very niche use case, using Excel for design and prototyping, I'm prepared to accept named ranges, where you have ONE sheet with all your tables of calculations on, and each table of calculations is a named range. This helps with a tiny tiny minority of users who have a large number of immutable, complex mathematical formulae they need to work with on a daily basis. For the vast majority of us, who are just using Excel to do financial or productivity calculations, then named ranges are literally the devil.

@Siddharth Rout 2012-05-23 10:33:36

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

  1. It slows down your code.
  2. It is usually the main cause of runtime errors.

How do we avoid it?

1) Directly work with the relevant objects

Consider this code

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

@user3032689 2016-02-02 10:04:14

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.

@Nick 2016-11-25 14:34:57

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.

@robotik 2017-06-20 12:52:00

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

@phrebh 2018-01-09 13:30:59

@Nick You don't need to Activate sheets to paste to them or filter them. Use the sheet object in your paste or filter commands. It becomes easier as you learn the Excel object model through practice. I believe the only time I use .Activate is when I create a new sheet, but I want the original sheet to appear when the code is done.

@GMalc 2019-02-28 18:44:25

@phrebh You don't need to use .Activate to move to the original sheet, just use Application.Goto

@Andras Dorko 2019-09-17 12:57:01

@user3032689 I believe Activate should only be used when during processing you want for example a Worksheet to be visible... for the user... not much else...

@Geoff Griswald 2020-02-20 17:49:52

Filter operations normally require the sheet to be activated. For example removing an autofilter. I tend to avoid using it unless nothing else works.

@Francesco Baruchelli 2012-05-23 06:11:28

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

@BiGXERO 2012-05-23 06:33:06

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?

@Francesco Baruchelli 2012-05-23 06:50:20

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"

Related Questions

Sponsored Content

43 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1095755 View
  • 1892 Score
  • 43 Answer
  • Tags:   c# .net excel file-io

9 Answered Questions

[SOLVED] How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

  • 2014-03-20 19:09:13
  • Portland Runner
  • 889969 View
  • 591 Score
  • 9 Answer
  • Tags:   regex excel vba

41 Answered Questions

[SOLVED] How do I properly clean up Excel interop objects?

7 Answered Questions

[SOLVED] "Can't find Project or Library" for standard VBA functions

22 Answered Questions

[SOLVED] Is there a way to crack the password on an Excel VBA Project?

  • 2009-06-22 10:37:13
  • Jonathan Sayce
  • 1167509 View
  • 485 Score
  • 22 Answer
  • Tags:   excel vba passwords

3 Answered Questions

1 Answered Questions

[SOLVED] Passing object Variables from cells into (Excel) VBA

1 Answered Questions

[SOLVED] Avoiding Select in VBA Excel macros with variable ranges

  • 2015-07-01 15:41:48
  • Anton Gusev
  • 527 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

2 Answered Questions

[SOLVED] Avoiding the use of Activate and Select when working with charts (Excel)

  • 2013-12-09 19:50:52
  • Mr.Kinn
  • 4931 View
  • 5 Score
  • 2 Answer
  • Tags:   excel excel-vba vba

Sponsored Content