如何使用 VBA 代码添加新电子表格

How to add a new spreadsheet with VBA-Code, using VBA

提问by Hannah

I am creating a macro and part of the macros function is to make VBA create a new spreadsheet. Because of the nature of distribution the name will change. I need to add code to this spreadsheet. Is there anyway I can do this?

我正在创建一个宏，宏功能的一部分是让 VBA 创建一个新的电子表格。由于分发的性质，名称将发生变化。我需要向这个电子表格添加代码。无论如何我可以做到这一点吗？

回答by Siddharth Rout

Jook has already explained how it works. I will take it a step further.

Jook 已经解释了它是如何工作的。我会更进一步。

The syntax of adding a worksheet is

添加工作表的语法是

expression.Add(Before, After, Count, Type)

If you check inbuilt Excel's help then you can see what Before, After, Count, Typestands for

如果您检查内置Excel的帮助下，你可以看到Before，After，Count，Type代表

FROM EXCEL"S HELP

来自 EXCEL"S 帮助

Parameters(All 4 parameters are Optional)

参数（所有4个参数都是可选的）

1. Before- An object that specifies the sheet before which the new sheet is added.
2. After- An object that specifies the sheet after which the new sheet is added.
3. Count- The number of sheets to be added. The default value is one.
4. Type- Specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template. The default value is xlWorksheet.

1. Before- 指定在其之前添加新工作表的工作表的对象。
2. After- 指定在其后添加新工作表的工作表的对象。
3. Count- 要添加的页数。默认值为 1。
4. Type- 指定工作表类型。可以是下列XlSheetType常量之一：xlWorksheet，xlChart，xlExcel4MacroSheet，或xlExcel4IntlMacroSheet。如果您基于现有模板插入工作表，请指定模板的路径。默认值为 xlWorksheet。

Once the sheet is created then you need to use .insertlinesto create the relevant procedure and to also embed the code that you want to run.

创建工作表后，您需要使用它.insertlines来创建相关过程并嵌入要运行的代码。

NOTE - IMP: If you want the code to embed code in the VBA project, you need to ensure that you have "Trust Access to the VBA Project Object Model" selected. See snapshot.

注意 - IMP：如果您希望代码在 VBA 项目中嵌入代码，您需要确保选择了“对 VBA 项目对象模型的信任访问”。见快照。

Here is an example where I am creating a sheet and then embedding a Worksheet_SelectionChangeCode which will display a message "Hello World"

这是一个示例，我正在创建一个工作表，然后嵌入一个Worksheet_SelectionChange代码，该代码将显示一条消息“Hello World”

CODE - TRIED AND TESTED

代码 - 久经考验

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim nLines As Long
    Dim VBP As Object, VBC As Object, CM As Object
    Dim strProcName As String

    Set ws = Worksheets.Add

    Set VBP = ThisWorkbook.VBProject
    Set VBC = VBP.VBComponents(ws.Name)
    Set CM = VBC.CodeModule

    strProcName = "Worksheet_SelectionChange"

    With ThisWorkbook.VBProject.VBComponents( _
    ThisWorkbook.Worksheets(ws.Name).CodeName).CodeModule
        .InsertLines Line:=.CreateEventProc("SelectionChange", "Worksheet") + 1, _
        String:=vbCrLf & _
        "    Msgbox ""Hello World!"""
    End With
End Sub

This is how the new sheet code area looks once you run the above code.

这是运行上述代码后新工作表代码区域的外观。

回答by Jook

the following code will add you a spreadsheet.

以下代码将为您添加一个电子表格。

Public Sub Workbook_Add()
 Dim wks As Worksheet
 Set wks = ThisWorkbook.Worksheets.Add(, , 1, xlWorksheet)
 With wks
   'set codename of wks
   ThisWorkbook.VBProject.VBComponents(.CodeName).Name = "tblWhatever"
   'set tablename of wks
   .Name = "whatever"
   'add code (untested demo)
   'ThisWorkbook.VBProject.VBComponents(.CodeName).CodeModule.InsertLines 1, "Option Explicit"
   'add code (as of example from excel-help)
   'Application.VBE.CodePanes(1).CodeModule.InsertLines 1, "Option Explicit"
 End With
End Sub

If you need to add VBA-Code to this specific spreadsheet, you should further inspect the VBProject object - look for CodeModuleand then i.e. InsertLines.

如果您需要将 VBA-Code 添加到这个特定的电子表格，您应该进一步检查 VBProject 对象 - 查找CodeModule，然后即InsertLines。

A further hint for you - I would try to use the CodeNames of your tables. It is less likely to be changed - BUT it might be not that comfortable to use in your code at first. I had to get used to it, but for me it has many advantages against using a tables name.

给您的进一步提示 - 我会尝试使用您的表的 CodeNames。它不太可能被更改 - 但是一开始在你的代码中使用它可能不太舒服。我不得不习惯它，但对我来说，它比使用表名有很多优点。

Hope this helps ;)

希望这可以帮助 ;）

回答by Neil Dunlop

The default .Addmethod adds a sheet at the start of the list. Often you want to add it at the endbefore adding the code lines, as explained by Siddarth Rout. To do that anywhere you can use:

默认.Add方法在列表的开头添加一个工作表。通常，您希望在添加代码行之前在最后添加它，如Siddarth Rout所解释的那样。要在任何可以使用的地方执行此操作：

    ActiveWorkbook.Worksheets.ADD  After:=ActiveWorkbook.Sheets(ActiveWorkbook.Worksheets.Count)

It is easier to read if you have defined and set WB:

如果您已经定义并设置了 WB，则更容易阅读：

  Dim WB as Excel.workbook
  Set WB = ActiveWorkbook
      WB.Sheets.ADD  After:=WB.Sheets(WB.Sheets.Count)
      Set VBC = ActiveSheet     'If using in Siddarth Rout's code above

Sheetsand Worksheetsare interchangeable, as illustrated.

Sheets并且Worksheets可以互换，如图所示。