I've heard much about the understandable abhorrence of using .Select in Excel VBA, but I 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 I can't find anything on how.
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
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:
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:
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:
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
next
rng.Value = dat ' put new values back on sheet
This is a small taster for what's possible.
Two main reasons why .Select, .Activate, Selection, Activecell, Activesheet, Activeworkbook, etc. should be avoided
How do we avoid it?
1) Directly work with the relevant objects
Consider this code
Sheets("Sheet1").Activate
Range("A1").Select
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
One small point of emphasis I'll add to all the excellent answers given previously:
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 it was glossed over a bit; however, it deserves special attention.
Here are a couple of additional reasons to make liberal use of named ranges, though I am sure I could think of more.
Example:
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.
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 would continue working just fine.
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.
"... 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.)
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:
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
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")
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]
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
This is probably the most common mistake in vba. 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
.Select or .Activate for anything?.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()
Worksheets("Cover").Activate
End Sub
Another good example is when you need to export all sheets into one PDF file, as mentioned in this case - How to avoid select/active statements in VBA in this example?
When a command only works with ActiveWindow like ActiveWindow.Zoom or ActiveWindow.FreezePanes
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
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.
These methods are rather stigmatized, so taking the lead of Vityata and Jeeped for the sake of drawing a line in the sand:
.Activate, .Select, Selection, ActiveSomething methods/propertiesBasically 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:
.Activate, .Select, .Selection, .ActiveSomething methods/propertiesBasically 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/her 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 programmatically select that range.
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.
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.
valOne = Range("A1").Value► For instance, if you want the codename of 'Sheet3you could set a variable equal to theCodename` property of that worksheet.
valTwo = Sheets("Sheet3").CodenameI 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
J6 to be a value of 12 ActiveCell.Offset(0,1).Copy ActiveCell.Offset(,2)
k4 to L4.i4ActiveCell.Offset(, -1).EntireColumn.ClearContents
These aren't to say they are "better" than the above options, but just listing alternatives.
Let's face it: this one appears a lot when recording macros:
Range("X1").Select
Selection.Copy
Range("Y9).Select
Selection.Paste
While the only thing the person wants is:
Range("Y9").Value = Range("X1").Value
Therefore, instead of using copy-paste in VBA macros, I'd advise the following simple approach:
Destination_Range.Value = Source_Range.Value
Working with the .Parent feature, this example shows how setting only one myRng reference enables dynamic access to the entire environment without any .Select, .Activate, .Activecell, .ActiveWorkbook, .ActiveSheet and so on. (There isn't any generic .Child feature.)
Sub ShowParents()
Dim myRng As Range
Set myRng = ActiveCell
Debug.Print myRng.Address ' An address of the selected cell
Debug.Print myRng.Parent.name ' The name of sheet, where MyRng is in
Debug.Print myRng.Parent.Parent.name ' The name of workbook, where MyRng is in
Debug.Print myRng.Parent.Parent.Parent.name ' 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.name, mySh.Cells(10, 1).Value
Debug.Print myWbk.name, myWbk.Sheets.Count
Debug.Print myApp.name, myApp.Workbooks.Count
' You may use dynamically addressing
With myRng
.Copy
' Pastes in D1 on sheet 2 in the same workbook, where the 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
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:
Sheets("Output").Activate
ActiveSheet.Range("A1").Select
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.
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
Selectand/orActiveSheetetc etc is completely unavoidable. Here's an example that I found: stackoverflow.com/questions/22796286/… – Rick supports Monica.Select / .Selectionis required. – BruceWayne