Why do Excel sheets have to be activated before se

问题:

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?

回答1:

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.

回答2:

I know that this is a bit late to the party, but I discovered a hack to do this...

Try this code:

Sheets(1).Activate
Sheets(2).Range("A1").Copy
Sheets(2).Range("A1").PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

Note that it is a hack, but it does the trick!!

回答3:

@Daniel Cook: thanks for your response, but unfortunately Excel itself doesn't play by the same rules imposed on Excel Macros...

To illustrate, I'll briefly present my current problem...

I'm attempting to re-set a table's contents to a common state. This method will be applied to multiple tables across various sheets:

Public Sub restoreTable()
    Dim myTableSheet As Worksheet: Set myTableSheet = Range("Table1").Parent
    Dim myTable As ListObject: Set myTable = myTableSheet.ListObjects("Table1")

    ' --- Clear Table's Filter(s)
    If myTable.ShowAutoFilter Then ' table has auto-filters enabled
        Call myTable.Range.AutoFilter ' disables autofilter
    End If
    myTable.Range.AutoFilter ' re-apply autofilter

    ' --- Sort by Sequence number
    Call myTable.Sort.SortFields.Clear ' if not cleared, sorting will not take effect

    myTable.Sort. _
        SortFields.Add Key:=Range("Table1[[#Headers],[#Data],[Column1]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    myTable.Sort.Header = xlYes
    myTable.Sort.Orientation = xlTopToBottom
    myTable.Sort.SortMethod = xlPinYin
    Call myTable.Sort.Apply

    myTable.Sort.SortFields.Clear
End Sub

For each use-case below, Table1 is found in Sheet1

Use-Case 1:

• Activate Sheet1, select range A1
• Run restoreTable
• observe: range Sheet1 A1 remains selected

Use-Case 2:

• Activate Sheet1, select range A1
• Activate Sheet2
• Run restoreTable
• observe: range Sheet1 A1 is not selected, instead the range Table1[#Data] is selected

Solution

It's absolutely terrible, but this is the best solution I could find

Public Sub resotreTable_preserveSelection()
    Dim curSheet As Worksheet: Set curSheet = ActiveSheet
    Dim tableSheet As Worksheet: Set tableSheet = Range("Table1").Parent

    ' Change Sheet
    tableSheet.Activate

    ' Remember Selection / Active Ranges
    Dim originalSelection As Range:  Set originalSelection = Selection
    Dim originalActiveCell As Range: Set originalActiveCell = ActiveCell

    ' Restore Table
    Call restoreTable

    ' Restore Old Selection
    originalSelection.Select
    originalActiveCell.Activate

    ' Change Back to old sheet
    curSheet.Activate
End Sub

Note: in this case, the original* ranges are not necessary, but you get the point: you can buffer the original selection and restore it when you're finished

I really don't like excel

回答4:

Of course you don't have to select or activate the sheet to select/activate the cell. My way is to use "On Error Resume Next" and "On Error GoTo 0". Code below selects first cell in every worksheet of a workbook without selecting it. The worksheets are even very hidden on this stage.

On Error Resume Next
For i_wks = 1 To wb_macro.Worksheets.Count
    wb_macro.Worksheets(i_wks).Cells(1).Select
Next i_wks
On Error GoTo 0