可以对隐藏的工作表或工作簿执行哪些 Excel VBA 操作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/892659/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What Excel VBA actions are possible on hidden worksheets or workbooks?
提问by Andrew Scagnelli
Hidden worksheets/workbooks have some limitations to what can be done in VBA code, like most Selectand Selectionstatements, and anything coming from ActiveSheet, but I can't seem to find any list of what the limitations are.
隐藏的工作表/工作簿对 VBA 代码中可以完成的操作有一些限制，例如大多数Select和Selection语句，以及来自ActiveSheet.
Google, the built-in documentation in the help system, and MSDN's website have all failed me. Can anyone point me in the right direction?
谷歌、帮助系统中的内置文档和 MSDN 的网站都让我失望。任何人都可以指出我正确的方向吗？
Edit: The workbook is opened with
编辑：工作簿是用
Set WB_Master = Workbooks.Open(Filename:=PATH_Master, ReadOnly:=False)
and then hidden with
然后隐藏
WB_Master.Windows(1).Visible = False
回答by Patrick McDonald
From the Visual Basic for Applications help:
来自 Visual Basic for Applications 帮助：
When an object is hidden, it's removed from the screen and its Visible property is set to False. A hidden object's controls aren't accessible to the user, but they are available programmatically to the running application, to other processes that may be communicating with the application through Automation, and in Windows, to Timer control events.
当一个对象被隐藏时，它会从屏幕上移除，并且它的 Visible 属性设置为 False。用户无法访问隐藏对象的控件，但它们可以通过编程方式供正在运行的应用程序、可能通过自动化与应用程序通信的其他进程以及在 Windows 中用于计时器控件事件。
Not much help there I'm afraid, and I couldn't find much else through Google.
恐怕没有太多帮助，而且我无法通过 Google 找到更多其他帮助。
As you said yourself, the Select method and Selection Property don't work on a hidden Worksheet, they should work on a hidden Workbook though. (Please correct me if I'm wrong.) In general however, it's not always all that efficient to select ranges in worksheets anyway, you are better off working with the Range property (which does work on a hidden worksheet).
正如您自己所说， Select 方法和 Selection 属性不适用于隐藏的工作表，但它们应该适用于隐藏的工作簿。（如果我错了，请纠正我。）但是，总的来说，在工作表中选择范围并不总是那么有效，您最好使用 Range 属性（它确实适用于隐藏的工作表）。
EDIT:
编辑：
The following code will change the color of A1:A8 to Cyan even when the Worksheet is not visible:
即使工作表不可见，以下代码也会将 A1:A8 的颜色更改为青色：
Dim book2 As Workbook
Set book2 = Workbooks.Open("C:\Book2.xls")
book2.Worksheets("Sheet1").Visible = False
book2.Windows(1).Visible = False
With book2.Worksheets("Sheet1").Range("A1:E8").Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
book2.Windows(1).Visible = True
book2.Worksheets("Sheet1").Visible = True
回答by Jon Crowell
You can get around any limitations on hidden sheets by unhiding them without the user realizing it, doing whatever you need to, and then hiding them again.
您可以通过在用户没有意识到的情况下取消隐藏隐藏工作表，做您需要做的任何事情，然后再次隐藏它们来绕过对隐藏工作表的任何限制。
This example assumes that Sheet2 is hidden.
此示例假定 Sheet2 已隐藏。
Sub DoStuffToAHiddenSheetWithoutTheUserKnowingIt()
'turns off screen repainting so the user can't see what you're doing
'incidentally, this dramatically speeds up processing of your code
Application.ScreenUpdating = False
'note that if you're stepping through your code, screenupdating will be true anyway
'unhide the sheet you want to work with
Sheets("sheet2").Visible = True
'do whatever you want here, including selecting cells if you want
'Scagnelli is right though, only select cells if you have to
'when you're finished, hide the sheet again
Sheets("sheet2").Visible = False
'make sure you turn screenupdating back on, or Excel will be useless
Application.ScreenUpdating = True
End Sub
Another useful trick if you want your sheets hidden is to set them to xlVeryHidden, which will prevent them from being listed to the user if they try to unhide them through the menu or ribbon.
如果您希望您的工作表隐藏，另一个有用的技巧是将它们设置为 xlVeryHidden，这将阻止用户尝试通过菜单或功能区取消隐藏它们时将它们列出。
