vba 重命名工作表后下标超出范围错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9923439/
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
Subscript out of range Error after renaming sheets
提问by neobee
I have done a small project, which consists of 5 excel sheet in, code is working fine and I am getting exact result also, but if I rename sheets from sheet1 to some other name I am getting Subscript out of range Error.
我做了一个小项目，其中包含 5 个 excel 工作表，代码工作正常，我也得到了准确的结果，但是如果我将工作表从 sheet1 重命名为其他名称，我会收到下标超出范围错误。
What is the reason for this and what needs to be done to overcome this. Please help.
这是什么原因，需要做什么来克服这个问题。请帮忙。
Below is the code
下面是代码
Public Sub amount_final()
Dim Row1Crnt As Long
Dim Row2Crnt As Long
With Sheets("sheet4")
Row1Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
Row1Crnt = 2
With Sheets("sheet3")
Row2Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
回答by Siddharth Rout
There is nothing wrong with the code per se. You will get Subscript out of rangeerror if Excel is not able to find a particular sheet which is quite obvious since you renamed it. For example, if you rename your sheet "Sheet3" to "SheetXYZ" then Excel will not be able to find it.
代码本身没有任何问题。Subscript out of range如果 Excel 无法找到特定的工作表，那么您将收到错误消息，这在您重命名后非常明显。例如，如果您将工作表“Sheet3”重命名为“SheetXYZ”，则 Excel 将无法找到它。
The only way to avoid these kind of errors is to use CODENAME of the sheets. See Snapshot
避免此类错误的唯一方法是使用工作表的 CODENAME。查看快照
Here we have a sheet which has a name "Sample Name before Renaming"
在这里，我们有一个名称为“重命名前的示例名称”的工作表
So consider this code
所以考虑这个代码
Sheets("Sample Name before Renaming").Range("A1").Value = "Blah Blah"
The same code can be written as
相同的代码可以写成
Sheet2.Range("A1").Value = "Blah Blah"
Now no matter how many times you rename the sheet, the above code will always work :)
现在无论您重命名工作表多少次，上面的代码将始终有效:)
HTH
HTH
Sid
锡德
回答by Ed Bolton
The basic issue is that you are referring to sheets using their common names and not their codenames. Whenever you refer to Sheets("sheet4"), you are relying on the sheet having that name in Excel. Codenames are the names assigned in Visual Basic so the end user does not interact with them/as a developer you can change the Excel names any time you like
基本问题是您使用的是通用名称而不是代号来引用工作表。每当您引用 Sheets("sheet4") 时，您都依赖于 Excel 中具有该名称的工作表。代号是在 Visual Basic 中分配的名称，因此最终用户不会与它们交互/作为开发人员，您可以随时更改 Excel 名称
Using code names is covered at around 9:40 in this Excel help video. You'll note they are quicker to type than the Excel names as do not require the 'Sheets()' qualifier
此Excel 帮助视频在 9:40 左右介绍了使用代码名称。您会注意到它们比 Excel 名称的键入速度更快，因为不需要“Sheets()”限定符
I couldn't see Sheets("Sheet1") in your code sample but you can switch to codenames for all sheets very quickly by finding/replacing all examples of e.g. 'Sheets("Sheet2").' with 'Sheet2.'
我在您的代码示例中看不到 Sheets("Sheet1")，但是您可以通过查找/替换所有示例（例如“Sheets("Sheet2")）来非常快速地切换到所有工作表的代号。使用“Sheet2”。
回答by mattboy
Refer to each sheet by their code names instead. They are set to Sheet1, Sheet2 etc as default, but you can rename them in the Properties window for each sheet if you want. This way you can write your code like below instead, regardless of what you name the sheets.
请改为通过代码名称来引用每张纸。它们默认设置为 Sheet1、Sheet2 等，但如果需要，您可以在每个工作表的“属性”窗口中重命名它们。这样你就可以像下面这样编写代码，而不管你给工作表起什么名字。
With Sheet1
Row1Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
Row1Crnt = 2
With Sheet2
Row2Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
etc...
回答by ritesh
I wanted to share my experience battling this problem. Here is the mistake I committed:
我想分享我解决这个问题的经验。这是我犯的错误：
Dim DailyWSNameNew As String
lastrow = Sheets("DailyWSNameNew").Range("A65536").End(xlUp).Row + 1 -- This is wrong as I included a placeholder worksheet name in quotes
Correction:
更正：
lastrow = Sheets(DailyWSNameNew).Range("A65536").End(xlUp).Row + 1
This solved it.
这解决了它。
回答by SwedishProgrammer
I encountered this error earlier today but could not use any solution above, I did however eventually managed to solve it myself.
我今天早些时候遇到了这个错误，但无法使用上述任何解决方案，但我最终设法自己解决了这个问题。
My situation was that I had a list contained in column A. For each cell with a value I stored the value in a variable, created a new sheet and named the sheet according to the value stored in the variable.
我的情况是我有一个包含在 A 列中的列表。对于每个带有值的单元格，我将值存储在一个变量中，创建一个新工作表并根据存储在变量中的值命名工作表。
A bit later in the code I tried to select the newly created sheet by using the code:
稍后在代码中，我尝试使用以下代码选择新创建的工作表：
Sheets(ValueVariable).Select
I encountered the "Subscript out of range" error and I couldn't figure out why. I've used similar code before with success. I did however solve it by casting the variable as a string. Declaring the variable as a string did not seem to work for me.
我遇到了“下标超出范围”错误，但我不知道为什么。我之前成功使用过类似的代码。然而，我确实通过将变量转换为字符串来解决它。将变量声明为字符串似乎对我不起作用。
So, if anyone else encounter this error and want something to try, perhaps this will work for you:
因此，如果其他人遇到此错误并想要尝试一些东西，也许这对您有用：
Sheets(Cstr(ValueVariable)).Select
