Why do Excel sheets have to be activated before selection?

vba excel datamodel
enderland picture enderland · Oct 3, 2012 · Viewed 36.2k times · Source

This code

Sheets(1).Activate
Sheets(2).Range("A1").Select

will fail in VBA because you can only use Select on an object which is Active. I understand this is the case.

What element of the Excel datamodel causes this to be the case? I would think there is an implicit intent from a user/coder to Activate any object immediately prior to using Select - I do not understand why VBA would not make this assumption, and, I am assuming there is a reason this distinction exists.

  • What part of Excel's datamodel prevents selection without activation?

Answer

Daniel picture Daniel · Oct 3, 2012

As brettdj pointed out, you do not have to activate the sheet in order to select a range. Here's a reference with a surprisingly large amount of examples for selecting cells/ranges.

Now as for the why do I have to active the sheet first? I do not believe it is a fault of the datamodel, but simply a limitation of the select method for Ranges.

From experimentation, it looks like there are two requirements to select a range in Excel.

  1. Excel must be able to update the UI to indicate what is selected.
  2. The ranges parent (I.E. the sheet) must be active.

To support this claim, you also cannot select a cell from a hidden sheet.

Sheets(1).Visible = False
Sheets(1).Activate
'The next line fails because the Range cannot be selected.
Sheets(1).Range("A1").Select

Simply put, when it comes to Ranges, you cannot select one you cannot see.

I would have claimed this is a limitation of select all together, except that you can actually select an object in a hidden sheet. Silly Excel.

Related questions

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

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More