在Excel VBA中创建和命名工作表

Question

我有一些非常简单的代码，可以在Excel文档中添加一个新工作表，并将其名称更改为在用户窗体上输入的名称，添加在当前工作表之后。在新工作簿上运行得很好，但是在具有多个现有工作表的工作簿中，它会创建新工作表，但不会将其重命名。

这只会在第一次运行此代码时发生，下一次它会正常运行。让事情变得更奇怪的是，如果您打开VBA编辑器尝试进行调试，那么它也会正常运行。这显然使查找错误变得相当困难。

我使用的代码在此处：

     Dim WS As Worksheet

     Set WS = Sheets.Add(After:=Sheets(Worksheets.count))
     WS.name = txtSheetName.value

很简单。我想知道这个问题是否是因为它在正确创建之前尝试重命名工作表？有没有更好的方法来编写这段代码？

更新： 我已经开始使用消息框进行调试，因为打开调试器会使问题停止，而且似乎它只会在处理代码的一半时停止：

  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

- Sam Cogan

---

Excel有很多方法可以让你感到有趣。通常你无法对此做任何事情。尝试各种相对无用的事情，比如使用Worksheets集合而不是Sheets，或者先将txtSheetName.value赋值给一个字符串变量。 - GSerg

看起来你是在txtSheetName中的名称设置之前进行操作。你是在txtSheetName_Change事件中运行这段代码吗？如果不是，那么这可能会解决问题。 - Marc Thibault

@Marc 该事件是从按钮事件中运行的，它会验证文本框是否已设置。 - Sam Cogan

准备好热情迎接编程的世界了吗？看起来是时候用山羊血和鸡毛来祭奠一下了。你能提供一个下载 xls 文件的地方吗？ - Marc Thibault

@marc，很遗憾，这是因为它充满了数据，并且仅出现在该工作表中（或具有类似数据量的工作表），较小的工作表没有此问题。 - Sam Cogan

尝试添加工作表而不将其分配给工作表变量，Sheets.Add After:=Sheets(Worksheets.Count)，然后设置 ActiveSheet.Name。您是否收到相同的错误？ - David Zemens

Answer 1

http://www.mrexcel.com/td0097.html

Dim WS as Worksheet
Set WS = Sheets.Add

你不需要知道它的位置或名称，你只需称之为WS。
如果你仍然想以“老派”的方式完成这个任务，请尝试以下方法：

Sheets.Add.Name = "Test"

- Sage

---

那跟原帖中的做法有什么不同呢？ - GSerg

我没有写这篇文章，我只是向他指出了一个来源。当我测试了文章中的代码，特别是 [Sheets.Add.Name = "test"] 时，它完美地运行了。 - Sage

是的，我已经尝试过了。问题是，第二次它并不完美地工作，或者如果你打开调试器或表格为空，或者月亮处于第三季度... - Sam Cogan

Answer 2

你是否使用错误处理程序？如果你忽略了错误并尝试给现有工作表命名相同的名称或包含无效字符的名称，它可能会跳过该行代码。请参见此处的CleanSheetName函数。

http://www.dailydoseofexcel.com/archives/2005/01/04/naming-a-sheet-based-on-a-cell/

请查看无效字符列表，您可能想检查一下。

更新

其他尝试方法：完全限定的引用、添加 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

最后，每当我遇到一个很难理解的愚蠢的VBA问题时，我就会使用Rob Bovey的CodeCleaner。它是一个插件，可以将您的所有模块导出为文本文件，然后重新导入它们。您也可以手动执行此操作。此过程会清除任何挂起的损坏的p代码。

- Dick Kusleika

---

很遗憾，我没有忽略错误，并且名称第二次运行时工作正常，这表明它没问题。 - Sam Cogan

Answer 3

你是否在按下按钮（按下Enter键）之前提交了单元格？必须在使用它来命名工作表之前存储单元格的内容。

更好的方法是弹出对话框并获取您想要使用的名称。

- Sprague

---

它将从用户表单上的文本框中获取名称。 - Sam Cogan