Excel 2007 VBA：选择带有变量的工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15752055/
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
Excel 2007 VBA: Selecting Sheet With a Variable
提问by broconnor1
I'm trying to reference a sheet based on a variable.
我正在尝试根据变量引用工作表。
From a top level, I have a For loop driven by the variable Num = 1 To 18 The variable "CurrentSN" changes value based on a list of serial numbers on sheet ("SN's").
从顶层来看，我有一个由变量 Num = 1 到 18 驱动的 For 循环变量“CurrentSN”根据工作表上的序列号列表（“SN”）更改值。
Dim Num As Integer
Dim CurrentSN As String
CurrentSN = Sheets("SN's").Cells(Num, 1).Value
18 pre-existing sheets are in the workbook, each named with one of these serial numbers (18 total). I want to refer to the appropriate sheet for each loop so I used...
工作簿中有 18 个预先存在的工作表，每个工作表都以这些序列号之一命名（总共 18 个）。我想为每个循环参考相应的工作表，所以我使用了...
Sheets(CurrentSN).Range("A1").Select
Unfortunately, it is erroring out on this line. Where am I going wrong here?
不幸的是，它在这条线上出错了。我哪里出错了？
回答by Peter Albert
The problem is that a collection like the Sheetscollection accepts two different kind of inputs:
问题是像集合这样的Sheets集合接受两种不同类型的输入：
- A name/key
- An index number
- 名称/键
- 索引号
In your case, you want the key (the serial number), but VBA tries to retrieve the index number, as a number and not a string is provided. Therefore, you need to address your sheet with Sheets(CStr(CurrentSN))!
在您的情况下，您需要密钥（序列号），但 VBA 尝试检索索引号，因为提供的是数字而不是字符串。因此，您需要使用Sheets(CStr(CurrentSN))!
Also, Ross points out, you need to activate the sheet before your select anything in it. Though in 99.999% of the cases, you don't need so use .Select(and therefore .Activate) at all - see http://www.cpearson.com/excel/optimize.htm
此外，Ross 指出，您需要先激活工作表，然后再选择其中的任何内容。尽管在 99.999% 的情况下，您根本不需要使用.Select（因此.Activate） - 请参阅http://www.cpearson.com/excel/optimize.htm
回答by Ross McConeghy
You must activate the sheet before you can select something on it. UseSheets(CurrentSN).Activate
beforeSheets(CurrentSN).Range("A1").Select
您必须先激活工作表，然后才能选择其中的内容。使用Sheets(CurrentSN).Activate
前Sheets(CurrentSN).Range("A1").Select
