vba 使用代号参考工作表

Refer to sheet using codename

提问by user007

I get a "type mismatch" error in this code:

我在此代码中收到“类型不匹配”错误：

With Worksheets(Sheet1)   '* Error here 
   'my code here
End With

My sheet's CodeNameis 'sheet1'.

我的床单CodeName是'sheet1'.

Can someone please help me remove the error?

有人可以帮我删除错误吗？

回答by Wolfie

1) Refer to sheet by Index:

1) 按索引参考表：

With Worksheets(1) 
    '<stuff here>
End With

The `Index' is dependent on the "order of sheets in the workbook". If you shuffle your sheets order, this may not refer to the same sheet any more!

“索引”取决于“工作簿中工作表的顺序”。如果您打乱您的工作表顺序，这可能不再指同一张工作表！

2) Refer to sheet by Name:

2) 按名称参考工作表：

With Worksheets("Your Sheet Name") 
    '<stuff here>
End With

This is the .Nameproperty of a worksheet, and is the name visible in the Excel worksheet tab and in brackets in the VBA Project Explorer.

这是.Name工作表的属性，是 Excel 工作表选项卡和 VBA 项目资源管理器中括号中可见的名称。

3) Refer to sheet by CodeName:

3) 按代号参考工作表：

You suggested you actually wanted to use the .CodeNameproperty of a worksheet. This cannot be reference within brackets like the above two examples, but does exist contrary to some answers above! It is assigned automatically to a sheet on creation, and is "Sheet" then the next unused number in the previously created CodeNames.

您建议您实际上想要使用.CodeName工作表的属性。这不能像上面两个例子那样在括号内引用，但确实存在与上面的一些答案相反！它在创建时自动分配给工作表，并且是“工作表”，然后是先前创建的 CodeName 中下一个未使用的编号。

The advantage of using CodeNameis that it doesn't depend on the sheet order (unlike the Index) and it doesn't change if a user changes the Namesimply by renaming the sheet in Excel.

使用的优点CodeName是它不依赖于工作表顺序（与 不同Index），并且如果用户Name仅通过在 Excel 中重命名工作表来更改它，它也不会改变。

The disadvantage is the code can be more convoluted or ambiguous. Since CodeNameis read-only [1] this cannot be improved, but does ensure the above advantages! See the referenced documentation for more details.

缺点是代码可能更加复杂或含糊不清。由于CodeName是只读 [1] 这无法改进，但确实确保了上述优点！有关更多详细信息，请参阅参考文档。

First way of using it: directly...

第一种使用方式：直接...

With Sheet1
    '<stuff here>
End With

Second way of using it: indirectly, may offer more clarity or flexibility, shows how to use the CodeNameproperty of a worksheet...

第二种使用方式：间接地，可以提供更多的清晰度或灵活性，展示如何使用CodeName工作表的属性......

By looping over sheets and reading the CodeNameproperty, you can first find either the Indexor Nameproperty of your desired sheet. Then your can use this to reference the sheet.

通过循环工作表并阅读CodeName属性，您可以首先找到所需工作表的Index或Name属性。然后您可以使用它来引用工作表。

Dim sh as WorkSheet
Dim shName as String
Dim shIndex as Long

' Cycle through all sheets until sheet with desired CodeName is found
For Each sh in ThisWorkbook.WorkSheets        
    ' Say the codename you're interested in is Sheet1
    If sh.CodeName = "Sheet1" Then            
        ' - If you didn't want to refer to this sheet later, 
        '   you could do all necessary operations here, and never use shName
        '   or the later With block.
        ' - If you do want to refer to this sheet later,
        '   you will need to store either the Name or Index (below shows both)

        ' Store sheet's Name
        shName = sh.Name            
        ' Store sheet's Index
        shIndex = sh.Index           
    End If
Next sh 

' Check if match was found, do stuff as before if it was!
If shName = "" Then
    MsgBox "Could not find matching codename"
Else 
    ' Equally to the next line, could use Worksheets(shIndex)
    With Worksheets(shName)
        '<stuff here>
    End With
End If

[1] https://msdn.microsoft.com/en-us/library/office/ff837552.aspx

[1] https://msdn.microsoft.com/en-us/library/office/ff837552.aspx

回答by Mark Fitzgerald

You can use sheet codenames directly in your code as if they were declared variables:

您可以直接在代码中使用工作表代号，就像它们是声明的变量一样：

Sub UsingSheetCodeName()
    With Sheet1   
       .[a1] = Sheet1.Name
    End With
End Sub

回答by Vityata

There are 3 different propertieswhich could be used to refer to a worksheet:

有3 个不同的属性可用于引用工作表：

• .Nameas Worksheets("SomeNameHere")in Worksheets("SomeNameHere").Range("A1")
• .Indexas Worksheets(2)in Worksheets(2).Range("A1")
• .CodeNameas Sheet3in Sheet3.Range("A1")

• .Name如Worksheets("SomeNameHere")在Worksheets("SomeNameHere").Range("A1")
• .Index如Worksheets(2)在Worksheets(2).Range("A1")
• .CodeName如Sheet3在Sheet3.Range("A1")

To see the difference, run the code below and take a look at the immediate window Ctrl+G:

要查看差异，请运行下面的代码并查看直接窗口Ctrl+ G：

Sub TestMe()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        Debug.Print wks.Name
        Debug.Print wks.Index
        Debug.Print wks.CodeName
        Debug.Print "-----------------------"
    Next wks
End Sub

If the Nameand the CodeNameof the worksheet are not changed, they would be the same.

如果Name和CodeName工作表都没有改变，他们将是相同的。

• CodeName:

• 代码名称：

• Name:

• 姓名：

回答by Dutch Gemini

CodeName is actually read-write at run-time when accessing the property trough Worksheet.Parent.VBProject.VBComponents:

当通过 Worksheet.Parent.VBProject.VBComponents 访问属性时，CodeName 实际上是在运行时读写的：

' ActiveWorksheet both .Name and .CodeName are 'Sheet 1'    
For Each oVBComponent In ActiveWorksheet.Parent.VBProject.VBComponents
    If (oVBComponent.Name = ActiveWorksheet.CodeName) Then oVBComponent.Name = "New Name"
Next oVBComponent
Debug.Print ActiveWorkSheet.Name, ActiveWorksheet.CodeName ' "Sheet1", "New Name"

回答by Storax

Maybe this code helps understand the different names and the index

也许这段代码有助于理解不同的名称和索引

Sub DisplaySheetnames()

    Dim wks As Worksheet
    For Each wks In Worksheets
        Debug.Print "Index", wks.Index, "of sheet with name: " & wks.Name, "and", "codename " & wks.CodeName
    Next

End Sub

回答by Shai Rado

To use the Worksheet.Indexin your code:

要Worksheet.Index在您的代码中使用：

With Worksheets(1) ' << this is the index for Sheet1
    .Range("A1").Value = "Test" ' modify the value in cell A1
End With