重命名 ActiveSheet 时出现 Excel VBA 运行时错误 1004
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18338604/
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 VBA Runtime Error 1004 when renaming ActiveSheet
提问by Thomas Hansen
I'm at a loss when trying to figure out where this code is tripping up. I am looking to rename the activesheet by using a concat of two ranges on the activesheet and some static text. When only one worksheet is in the workbook, the code works great. As soon as a second worksheet is added, I get a Runtime Error 1004. I'll highlight the line of code where it is breaking. This code currently resides in a normal module.
当我试图找出这段代码在哪里绊倒时，我不知所措。我希望通过在活动表和一些静态文本上使用两个范围的连接来重命名活动表。当工作簿中只有一张工作表时，代码效果很好。一旦添加了第二个工作表，我就会收到运行时错误 1004。我将突出显示其中断的代码行。此代码当前驻留在普通模块中。
Option Explicit
Sub updateName()
Dim fNumber
Dim pCheckNumber
Dim asName As String
Dim tempASName As String
Dim worksheetName As Object
If ActiveSheet.Name = "Launch Page" Then Exit Sub
fNumber = ActiveSheet.Range("FlightNumber").Value
pCheckNumber = ActiveSheet.Range("PerformanceCheckNumber").Value
If fNumber <> "" And pCheckNumber <> "" Then
tempASName = "Flight " & fNumber & " | Run " & pCheckNumber & " (0.0%)"
asName = tempASName
MsgBox ActiveSheet.Name & vbCr & asName
ActiveSheet.Name = asName
worksheetName.Caption = asName
Else
Exit Sub
End If
End Sub
I'm in the process of adding error checking to ensure that I don't have duplicate sheet names. However, due to the nature of the field names, this will never occur.
我正在添加错误检查以确保我没有重复的工作表名称。但是，由于字段名称的性质，这永远不会发生。
I appreciate all of the insights!
我感谢所有的见解！
回答by varocarbas
The error you are reporting is, most likely, provoked because of trying to rename a Worksheet by using a name already in use. Here you have a small code to avoid this kind of situations:
您报告的错误很可能是由于尝试使用已在使用的名称重命名工作表而引起的。这里你有一个小代码来避免这种情况：
Dim newName As String: newName = "sheet1"
Dim addition As String: addition = "_2"
Do While (Not sheetNameFree(newName))
newName = newName & addition
Loop
Where sheetNameFreeis defined by:
其中sheetNameFree定义为：
Function sheetNameFree(curName As String) As Boolean
sheetNameFree = True
For Each Sheet In ActiveWorkbook.Sheets
If (LCase(Sheet.Name) = LCase(curName)) Then
sheetNameFree = False
Exit Function
End If
Next Sheet
End Function
You can adapt this code to your specific needs (for example, by converting additioninto a number which grows after each wrong name).
您可以根据您的特定需求调整此代码（例如，通过转换addition为在每个错误名称后增长的数字）。
In your code I see one other problem (although it shouldn't be triggering a 1004 error): you are accessing the property Captionfrom an non-instantiated object (worksheetName), whose exact functionality is not too clear. Just delete this line.
在您的代码中，我看到了另一个问题（尽管它不应该触发 1004 错误）：您正在Caption从非实例化对象 ( worksheetName)访问该属性，该对象的确切功能不太清楚。只需删除此行。
NOTE: good point from KazJaw, you might be using an illegal character. If fNumberand pCheckNumberare numbers or letters, it would be OK.
注意：来自 KazJaw 的好点子，您可能使用了非法字符。如果fNumber和pCheckNumber是数字或字母，就可以了。
NOTE2: if with worksheetNameyou want to refer to an ActiveX Labelin your workSheet, better do: ActiveSheet.Label1.Caption(where Label1is the name of the Label). You cannot define worksheetNameas a Label, because it is not a "conventional Label".
NOTE2：如果worksheetName你想ActiveX Label在你的工作表中引用一个，最好这样做：（标签的名称ActiveSheet.Label1.Caption在哪里Label1）。您不能定义 worksheetName为标签，因为它不是“常规标签”。
