How to Avoid the Select Method in VBA & Why
The first TV I remember us having as a kid did NOT have a remote control. How inconvenient, right?
Every time someone in the family wanted to change the channel or volume, we had to get up off the couch and walk over to the TV to press a button. You didn’t want to be the one sitting closest to the TV unless you needed some exercise… 🙂
Imagine how many times you pressed the buttons on your TV remote last night. Probably a lot! Especially if you are married or have kids. Now imagine how inefficient it would be to have to walk over to the TV to make every one of those button presses. What a waste of time!
The Select Method is Slow, Error Prone, and Overused
The Select method in VBA can be just as time consuming for Excel, as it is for you to get up off the couch to change the channel.
The Select method is used to select objects in Excel like worksheets, charts, shapes, and ranges. Here are a few examples.
These lines of code are usually followed by another common command to copy/paste values, format a cell, insert formulas, etc.
The problem with the Select method is that it can really slow down your macro. When we use the Select method, VBA has to force the Excel application to update the screen with the selection change (display a new worksheet, scroll to a range/cell, etc.). This screen update takes extra time and is usually unnecessary.
The good news is that 99% of the time, the Select method can be avoided all together.
Don’t Worry, It’s Not Your Fault!
We tend to see the Select method a lot in VBA code, and this is mostly the fault of the macro recorder. Don’t get me wrong, the macro recorder is a great tool, but the code it produces can pretty inefficient.
Most of the tasks we automate with VBA do NOT require us to select a workbook, worksheet, or range first. Let’s look at a simple example of copying and pasting a range of cells from one worksheet to another.
The macro recorder produces the following code when I record my actions to copy range B2:B8 on Sheet1, and paste it on Sheet2.
Sub Macro_Recorder_Copy_Paste() Sheets("Sheet1").Select Range("B2:B8").Select Selection.Copy Sheets("Sheet2").Select Range("B2").Select ActiveSheet.Paste End Sub
The Select method is used 4 times in the code above. The Selection property is also used once, so that’s really 5 references to a selection for a simple copy paste.
This macro can be made a lot more efficient!
We Don’t Need to Select Ranges Before Most Actions (Methods)
We don’t need to select any sheets or ranges for this simple copy and paste action. The following macro accomplishes the same task without the Select method, and only requires one line of code.
Sub Simple_Copy_Paste() Sheets("Sheet1").Range("B2:B8").Copy Sheets("Sheet2").Range("B2") End Sub
Checkout my article and video series on 3 ways to copy & paste with VBA for more code examples and explanations.
What’s the difference between the macros?
Let’s say you want to change the TV channel from 2 to 7. The first example from the macro recorder requires you to press the channel + button five times. The TV has to do extra work to display each channel on the screen as you repeatedly press the button.
The simplified macro requires you to press the 7 button on the remote control once, to jump directly to channel 7. Much faster and easier! 🙂
The Select Method Causes More Errors
I have had quite a few inquiries asking why the following line of code returns an error.
Returns the following error:
Run-time error '1004' Select method of the Range class failed
This is just a rule in VBA that does not allow us to select a sheet and a range in the same line of code. If you really want to select the sheet and the range, then we need to break it up into two lines of code.
The Select method can be prone to lots of other errors as well. It is important to properly qualify the code by first selecting the parent objects in the hierarchy like workbooks and worksheets, before selecting a range.
Therefore, you should only select cells when you absolutely have to. This is usually when you want to direct the user to a new location in the workbook after a macro has run.
How Else Can We Avoid the Select Method?
We want our macros to be as efficient as possible so we can save time with our jobs, and score extra bonus points on the performance reviews! And ditching the Select method is one simple way to really improve our macros.
The macro recorder is an awesome tool for getting snippets of code, and learning how to reference the Objects, Properties, & Methods in Excel. However, it produces the code for the actions we have to take in Excel’s user interface. Those actions are not always necessary when programming the computer to do the same task with a macro.
If you think your macro has some unnecessary Select methods in it, but you’re not sure how to get rid of them, then leave a comment below with your code. It will be great to see some other examples of how we can make our code more efficient. And I know the Excel Campus community has some smart VBA coders that will be happy to help make some suggestions. 🙂
Free Training on Macros & VBA
I will be holding a free webinar on “The 7 Steps to Getting Started with Macros & VBA”.
If you are wondering why you should learn macros & VBA, or are frustrated with the progress you are currently making, then this training is for you.
We are going to write our first macro, and I will explain how it all works in simple terms.
I look forward to seeing you there! 🙂