Why do Excel sheets have to be activated before selection?
datamodel, excel, vba
Solution
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.
- Excel must be able to update the UI to indicate what is selected.
- 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.
Problem
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?