VBA:使工作表具有动态范围 [英] VBA: Qualify Worksheets for dynamic range

问题描述

我刚刚开始使用VBA.

I am just getting started with VBA.

我有一个工作簿，其中包含许多编号的工作表(1、2、3、4等).

I have a workbook containing many numbered sheets (1,2,3,4 etc).

我现在已经按照以下说明阅读了限定工作表":

I now have read that Qualifying a Worksheet as per below:

Dim 1 As Worksheet: Set 1 = ThisWorkbook.Worksheets("1")

然后将其称为:

1.Range("B15").Value

比激活它(如我已经完成的)要快.

is faster than activating it (as I have done) eg.

Worksheets(1).Activate

我想通过循环动态访问我的工作表，如:

I would like though to access my sheets dynamically through a loop, as in:

Worksheets(i).Activate

可以使用Qualification方法完成此操作吗?我被卡住了.

Can this be done with the Qualifying method? I'm stuck.

推荐答案

一切都很简单:

1)传递字符串(例如"1")时，是指工作表的名称

1) When you pass string (say, "1"), you're referring to sheet's name

2)当您传递数字(例如1)时，您指的是图纸集合中的位置.

2) When you pass number (say, 1), you're referring to position in sheets' collection.

重要的是要了解床单的收藏 .

The important thing is to understand what is sheets' collection.

有几种类型的工作表:工作表，图表工作表，对话框工作表(也是两种类型的宏工作表；您可以通过right-click sheet's tab -> Insert查看全部五个).因此，了解使用哪个 集合非常重要.

There are several types of sheets: worksheet, chart sheet, dialog sheet (also two types of macro sheets; you can see all five of them via right-click sheet's tab -> Insert). So, it is very important to understand which collection to use.

说，您有四张纸(从左到右):

Say, you have four sheets (from left to right):

1. 对话框工作表(名称为"dialog1")
2. 工作表(名称为"sheet1")
3. 图表(名称为"chart1")
4. 工作表(名称为"sheet2")

因此，可以通过Sheets集合枚举所有工作表.因此:

So, all sheets can be enumerated through Sheets collection. Thus:

1. Sheets(1)返回"dialog1"对话框表单
2. Sheets(2)返回"sheet1"工作表
3. Sheets(3)返回图表1"图表表
4. Sheets(4)返回"sheet2"工作表

1. Sheets(1) returns "dialog1" dialog sheet
2. Sheets(2) returns "sheet1" worksheet
3. Sheets(3) returns "chart1" chart sheet
4. Sheets(4) returns "sheet2" worksheet

如果仅需要收集图表表，请使用Charts收集.在我们的例子中，Charts(1)将返回"chart1"图表表.

If you need the collection of only chart sheets, you use Charts collection. In our case Charts(1) will return "chart1" chart sheet.

如果只需要收集工作表，则使用Worksheets收集.在我们的情况下，Worksheets(2)将返回"sheet2"工作表.

If you need the collection of only worksheets, you use Worksheets collection. In our case Worksheets(2) will return "sheet2" worksheet.

对话框工作表也是如此-使用DialogSheets集合(尽管在IntelliSense中不可见，您可以看到它:F2 -> Right-click -> Show Hidden Members).

The same goes for dialog sheets - use DialogSheets collection (although it's not visible in IntelliSense, you can see it: F2 -> Right-click -> Show Hidden Members).

最后但并非最不重要的一点是，每个工作表都具有Index属性.但是要小心，因为它会返回Sheets集合中的index值.这意味着在我们的情况下，Worksheets(2).Index将返回4.

And last but not the least, every sheet has Index property. But be careful with it since it returns the value of index in Sheets collection. This means that in our case Worksheets(2).Index will return 4.

这篇关于VBA:使工作表具有动态范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！