在 Excel VBA 中创建和命名工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3840628/
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
Creating and Naming Worksheet in Excel VBA
提问by Sam Cogan
I have some very simple code that adds a new Worksheet, after the current worksheets, to an Excel document, and then changes its name to one entered in a text box on a userform. Works fine on a new workbook, however in a workbook that has a number of existing worksheets it creates the new worksheet, but does not rename it.
我有一些非常简单的代码，它在当前工作表之后向 Excel 文档添加一个新工作表，然后将其名称更改为在用户表单的文本框中输入的名称。在新工作簿上工作正常，但是在具有多个现有工作表的工作簿中，它会创建新工作表，但不会重命名它。
This only happens the first time you run this code, the next time it will run fine. The thing that makes it even stranger is that if you open the VBA editor to try and debug it, it then runs fine as well. This obviously makes finding the error pretty hard.
这只会在您第一次运行此代码时发生，下次它会正常运行。更奇怪的是，如果您打开 VBA 编辑器尝试调试它，它也能正常运行。这显然使得发现错误变得非常困难。
The code I'm using is here:
我正在使用的代码在这里：
Dim WS As Worksheet
Set WS = Sheets.Add(After:=Sheets(Worksheets.count))
WS.name = txtSheetName.value
Pretty simple. I'm wondering if this problem is that it is trying to rename the sheet before it is properly created? Is there a better way to write this code?
很简单。我想知道这个问题是否是它试图在正确创建之前重命名工作表？有没有更好的方法来编写这段代码？
Update:I've started debugging this using msgboxes, as opening the debugger makes the problem stop, and it seems that it just stops processing the code halfway through:
更新：我已经开始使用 msgboxes 调试它，因为打开调试器会使问题停止，而且它似乎只是在中途停止处理代码：
Dim WS As Worksheet
MsgBox (WS Is Nothing)
Set WS = Sheets.Add(After:=Sheets(Worksheets.count))
'***** Nothing after this point gets processed *******
MsgBox (WS Is Nothing)
MsgBox WS.name
WS.name = txtSheetName.value
MsgBox WS.name
回答by Sage
http://www.mrexcel.com/td0097.html
http://www.mrexcel.com/td0097.html
Dim WS as Worksheet
Set WS = Sheets.Add
You don't have to know where it's located, or what it's name is, you just refer to it as WS.
If you still want to do this the "old fashioned" way, try this:
您不必知道它的位置或名称，您只需将其称为 WS。
如果您仍然想以“老式”方式执行此操作，请尝试以下操作：
Sheets.Add.Name = "Test"
回答by Dick Kusleika
Are you using an error handler? If you're ignoring errors and try to name a sheet the same as an existing sheet or a name with invalid characters, it could be just skipping over that line. See the CleanSheetName function here
你在使用错误处理程序吗？如果您忽略错误并尝试将工作表命名为与现有工作表相同的名称或具有无效字符的名称，则可能只是跳过该行。在此处查看 CleanSheetName 函数
http://www.dailydoseofexcel.com/archives/2005/01/04/naming-a-sheet-based-on-a-cell/
http://www.dailydoseofexcel.com/archives/2005/01/04/naming-a-sheet-based-on-a-cell/
for a list of invalid characters that you may want to check for.
获取您可能想要检查的无效字符列表。
Update
更新
Other things to try: Fully qualified references, throwing in a Doevents, code cleaning. This code qualifies your Sheets reference to ThisWorkbook (you can change it to ActiveWorkbook if that suits). It also adds a thousand DoEvents (stupid overkill, but if something's taking a while to get done, this will allow it to - you may only need one DoEvents if this actually fixes anything).
其他要尝试的事情：完全限定的引用、加入 Doevents、代码清理。此代码将您的 Sheets 引用限定为 ThisWorkbook（如果合适，您可以将其更改为 ActiveWorkbook）。它还增加了一千个 DoEvents（愚蠢的矫枉过正，但如果某件事需要一段时间才能完成，这将允许它 - 如果这实际上可以解决任何问题，您可能只需要一个 DoEvents）。
Dim WS As Worksheet
Dim i As Long
With ThisWorkbook
Set WS = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
End With
For i = 1 To 1000
DoEvents
Next i
WS.Name = txtSheetName.Value
Finally, whenever I have a goofy VBA problem that just doesn't make sense, I use Rob Bovey's CodeCleaner. It's an add-in that exports all of your modules to text files then re-imports them. You can do it manually too. This process cleans out any corrupted p-code that's hanging around.
最后，每当我遇到一个毫无意义的愚蠢 VBA 问题时，我都会使用 Rob Bovey 的 CodeCleaner。它是一个插件，可将您的所有模块导出到文本文件，然后重新导入它们。您也可以手动完成。这个过程会清除任何残留的损坏的 p 代码。
回答by Sprague
Are you committing the cell before pressing the button (pressing Enter)? The contents of the cell must be stored before it can be used to name a sheet.
您是否在按下按钮（按 Enter）之前提交单元格？必须先存储单元格的内容，然后才能使用它来命名工作表。
A better way to do this is to pop up a dialog box and get the name you wish to use.
更好的方法是弹出一个对话框并获取您希望使用的名称。
