什么Excel VBA可以在隐藏的工作表或工作簿上执行？ [英] What Excel VBA actions are possible on hidden worksheets or workbooks?

问题描述

隐藏的工作表/工作簿在VBA代码中可以做些什么，像大多数选择和选择语句以及来自 ActiveSheet 的任何内容，但我似乎找不到任何限制条件的列表。

Google，帮助系统中的内置文档和MSDN的网站都使我失败。任何人都可以指向正确的方向？

编辑：
工作簿打开与

 设置WB_Master = Workbooks.Open（文件名：= PATH_Master，ReadOnly：= False）
  

然后隐藏

  WB_Master.Windows（1）.Visible = False 
  

解决方案

从Visual Basic for Applications帮助：

当一个对象被隐藏时，它从屏幕中删除，其Visible属性设置为False。隐藏对象的控件无法访问用户，但是它们可以以编程方式运行到正在运行的应用程序，以及可能通过自动化，以及Windows中的应用程序与定时器控制事件进行通信的其他进程。

我恐怕没有多少帮助，我通过Google找不到其他的东西。

如你所说，Select方法和Selection属性在隐藏的Worksheet上不起作用，但它们应该在隐藏的Workbook上工作。 （如果我错了，请更正我。）然而，一般来说，在工作表中选择范围并不总是那么有效，你最好使用Range属性（它在隐藏的工作表上工作）。

编辑：

以下代码将A1：A8的颜色更改为青色，即使工作表不可见：

  Dim book2 As Workbook 
设置book2 = Workbooks.Open（C：\Book2.xls）
 
 book2.Worksheets（Sheet1）。Visible = False 
 book2.Windows（1）.Visible = False 
 
使用book2.Worksheets（Sheet1） .Range（A1：E8）。内部
 .ColorIndex = 8 
 .Pattern = xlSolid 
 .PatternColorIndex = xlAutomatic 
结束
 
 book2 .Windows（1）.Visible = True 
 book2.Worksheets（Sheet1）。Visible = True 
  

Hidden worksheets/workbooks have some limitations to what can be done in VBA code, like most Select and Selection statements, and anything coming from ActiveSheet, but I can't seem to find any list of what the limitations are.

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?

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

解决方案

From the Visual Basic for Applications help:

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.

Not much help there I'm afraid, and I couldn't find much else through 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).

EDIT:

The following code will change the color of A1:A8 to Cyan even when the Worksheet is not visible:

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

这篇关于什么Excel VBA可以在隐藏的工作表或工作簿上执行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！