在Excel VBA中创建和命名工作表 [英] Creating and Naming Worksheet in Excel VBA

问题描述

我有一些非常简单的代码，在当前工作表之后添加一个新的工作表到Excel文档，然后将其名称更改为在用户窗体的文本框中输入的名称。在新的工作簿中工作正常，但是在具有现有工作表的工作簿中，它会创建新的工作表，但不会重命名。

这只会发生在第一个运行这段代码的时候，下次运行正常。使它更加陌生的事情是，如果打开VBA编辑器来尝试调试它，那么它也会运行良好。这显然是非常困难的发现错误。

我使用的代码是这里：

  Dim WS As Worksheet 
 
设置WS = Sheets.Add（After：= Sheets（Worksheets.count））
 WS.name = txtSheetName.value 
  

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

更新：
我已经开始调试这个使用msgbox，打开调试器使问题停止，似乎它只是停止处理代码中途：

  Dim WS As Worksheet 
 MsgBox（WS is Nothing）
 
设置WS = Sheets.Add（After：= Sheets（Worksheets.count））
'*****此点后没有什么被处理** ***** 
 MsgBox（WS is Nothing）
 MsgBox WS.name 
 
 WS.name = txtSheetName.value 
 MsgBox WS.name 
  

解决方案

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

  Dim WS as Worksheet 
设置WS = Sheets.Add 
  

您不必知道它在哪里，或者它的名字，你只是把它称为WS。

如果你还想做thi以老式的方式，尝试这样：

  Sheets.Add.Name =Test
  




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.

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.

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:

  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

解决方案

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:

Sheets.Add.Name = "Test"

这篇关于在Excel VBA中创建和命名工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！