Difference Between Select and Activate Methods in VBA

Bottom line: Learn the difference between these two commonly used methods in VBA.

Skill level: Intermediate

Select vs. Activate

One question I hear often from new members of our VBA Pro Course is, “What's the difference between Select and Activate? It seems like they do the same thing?”

The short answer is that Select and Activate can perform the same action, but the differences are:

• Select can be used to select multiple objects (sheets, ranges, shapes, etc.) at the same time.
• Activate can be used to active one object within the selection.

Let's take a look at an example to see how this works.

Which Method Should I Use?

First, it's important to note that both methods can be used on many different types of objects in Excel. This includes sheets, ranges, cells, shapes, charts, slicers, etc. We'll use worksheets in this example.

The use case for each method depends on the scenario. And as I explain below, it's usually best to use neither. 😲

When to use the Select Method

The Select method is typically the one we're most familiar with because the macro recorder uses it when generating code.

Select allows us to select a single object OR multiple objects.

The following line of VBA code selects a single worksheet.

  Worksheets("Sheet2").Select

We can also use Select to select multiple objects. Here is an example of selecting multiple ranges.

Range("A1:B10,D1:F10,J1:K10").Select

Here is an example of selecting multiple sheets. The Array function is used to reference the sheets. This same technique can be used for shapes.

  Worksheets(Array("Sheet2", "Sheet3", "Sheet5")).Select

When that line of code is run, the first item in the array is activated. That means that Sheet2 will be the active sheet that the user sees.

What if we want to keep the three sheets selected, but have the user view Sheet3 instead? This is where the Activate method comes into play.

When to use the Activate Method

The Activate method allows us to select a single object. This can be a single object within a selection, if multiple objects are already selected.

The following lines would select the three sheets, then make Sheet3 the active sheet that the user sees.

Worksheets(Array("Sheet2", "Sheet3", "Sheet5")).Select

Worksheets("Sheet3").Activate

If you do not have a group of objects selected, then Activate will just select a single object. It does NOT add to the current selection and will work the same way that Select works.

Be Careful with Activate

You might not always know which objects are selected in the workbook. Therefore, you can't always use Activate to select a single object.

For example, let's say you have a line of code in your macro to activate Sheet1.

If the user has Sheet1 to Sheet3 selected before the macro is runs, then all three sheets will remain selected. You might not want this if your next line of code modifies a cell on Sheet1. Depending on how the code is written, all three sheets could be modified, causing unwanted results. 😬

If you activate an object that is NOT in the selection, then that single object will be selected and the previously selected objects will no longer be selected. For example, if the user has Sheet1 to Sheet3 selected and your code activates Sheet4, then only Sheet4 will be selected.

The main takeaway here is that there are a lot of potential unwanted outcomes when using Activate to select a single object. So, it's best to use Select for selecting single objects.

Activate for Workbooks

The Activate method is also used when activating Workbooks and Windows. There is no Select method for these objects.

ThisWorkbook.Activate

Workbooks("Book3.xlsx").Activate

Windows("Book3.xlsx").Activate

Avoid Select and Activate Whenever Possible

It's great to know the difference between these two methods, but you might not need them as often as you think. Especially if you are just starting out with VBA and using the macro recorder a lot.

The macro recorder code contains a lot of lines with the Select method because it is generating code for every action we take. However, we do NOT need to select an object before we take actions on it. This can slow down our code and make it more prone to errors.

Here is an article on How to Avoid the Select Method in VBA & Why that explains more.

Conclusion

It's best to use Select when you want to select a single object or multiple objects. Activate should be used when you want to activate (view/select) an object within an existing selection. Activate is also used to select Workbooks, as there is not Select method for that object.

Did I miss anything? Please leave a comment below with questions or suggestions. Thank you! 🙂