使用代号参考工作表 [英] 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'.

My sheet's CodeName is '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 项目资源管理器中括号中可见的名称.

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 属性.这不能像上面两个例子那样在括号内引用，但确实存在与上面的一些答案相反！它在创建时自动分配给工作表，并且是工作表"然后是先前创建的代码名称中下一个未使用的编号.

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 不同)并且如果用户更改Name 只需在 Excel 中重命名工作表即可.

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屋！