vba 为什么在选择之前必须激活Excel工作表？

Why do Excel sheets have to be activated before selection?

提问by enderland

This code

这段代码

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

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

将在 VBA 中失败，因为您只能Select在Active. 我明白是这种情况。

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 Activateany 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.

Excel 数据模型的哪个元素导致这种情况？我认为Activate在使用之前，用户/编码员对任何对象都有隐含的意图Select- 我不明白为什么 VBA 不会做出这个假设，而且，我假设存在这种区别是有原因的。

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

• Excel 数据模型的哪一部分会在未激活的情况下阻止选择？

回答by Daniel

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.

正如 brettdj 所指出的，您不必为了选择范围而激活工作表。这是一个参考，其中包含大量用于选择单元格/范围的示例。

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 中选择范围似乎有两个要求。

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.

1. Excel 必须能够更新 UI 以指示所选内容。
2. 范围父级（即工作表）必须处于活动状态。

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.

简而言之，当涉及到 Ranges 时，您无法选择一个您看不到的。

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.

我会声称这是一起选择的限制，除了您实际上可以选择隐藏工作表中的对象。愚蠢的Excel。

回答by John Bustos

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!!

请注意，这是一个黑客，但它确实有效！！

回答by FraggaMuffin

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

@Daniel Cook：感谢您的回复，但不幸的是，Excel 本身并没有按照对 Excel 宏强加的相同规则进行操作...

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, Table1is found in Sheet1

对于下面的每个用例，Table1可以在Sheet1

Use-Case 1:

用例 1：

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

• 激活Sheet1，选择范围A1
• 跑 restoreTable
• 观察：范围Sheet1A1保持选中状态

Use-Case 2:

用例 2：

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

• 激活Sheet1，选择范围A1
• 启用 Sheet2
• 跑 restoreTable
• 观察：范围Sheet1A1是不选择，而不是范围Table1[#Data]被选择

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

注意：在这种情况下，original* 范围不是必需的，但您明白了：您可以缓冲原始选择并在完成后恢复它

I really don't like excel

我真的不喜欢excel

回答by Eswemenasja

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”和“On Error GoTo 0”。下面的代码选择工作簿的每个工作表中的第一个单元格而不选择它。在这个阶段，工作表甚至非常隐藏。

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