请参考使用代号的表 [英] Refer to sheet using codename

问题描述

此代码中出现类型不匹配"错误:

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

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

我的工作表的CodeName是'sheet1'.

有人可以帮我消除错误吗?

Can someone please help me remove the error?

推荐答案

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)按名称引用工作表:

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

这是工作表的.Name属性，并且是在Excel工作表选项卡和VBA Project Explorer中的括号中可见的名称.

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

3)请按代号参考工作表:

您建议您实际上想使用工作表的.CodeName属性.不能像上述两个示例一样在方括号内引用，但确实存在与上述某些答案相反的情况！创建时会自动将其分配给工作表，并且是工作表"，然后是先前创建的CodeName中的下一个未使用的数字.

You suggested you actually wanted to use the .CodeName property 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的优点是它不依赖于工作表顺序(与Index不同)，并且如果用户仅通过在Excel中重命名工作表来更改Name，就不会更改.

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

缺点是代码可能更复杂或含糊不清.由于CodeName为只读[1]，因此无法改进，但可以确保上述优势！有关更多详细信息，请参见参考文档.

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

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

With Sheet1
    '<stuff here>
End With

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

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

通过遍历工作表并读取CodeName属性，您可以首先找到所需工作表的Index或Name属性.然后，您可以使用它来引用工作表.

By looping over sheets and reading the CodeName property, you can first find either the Index or Name property of your desired sheet. Then your can use this to reference the sheet.

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

这篇关于请参考使用代号的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！