通过codename完全引用工作表 [英] Fully reference a worksheet by codename

问题描述

当我使用CodeName属性引用Excel工作表时，我如何完全符合资格，包括工作簿参考？

When I reference Excel worksheets using the CodeName property, how do I fully qualify them including the workbook reference?

我想防止有两种不同的工作簿打开和两张具有相同CodeName的纸张。我使用 ThisWorkbook ，因为我想引用代码正在运行的工作簿。

I want to guard against a scenario with two different workbooks open and two sheets having the same CodeName. I use ThisWorkbook because I want to reference the workbook the code is running from.

我想像这样：

Dim tgWs As Worksheet
Set tgWs = ThisWorkbook.WsSummary
tgWs.Cells(1,1).Value = "Test"

其中 WsSummary 是表单的代号。

推荐答案

引用其代码名称的表格总是意味着 ThisWorkbook ，即包含正在执行的代码的工作簿。

Referring to a sheet by its codename always implies the sheet in ThisWorkbook, i.e. the workbook that contains the code you are executing.

似乎没有直接的方法来完全限定不同工作簿中的工作表它的代号。

There seems to be no straightforward way to fully qualify a sheet in a different workbook using its codename.

此功能将帮助您执行此操作：

This function will help you do this:

Function GetSheetWithCodename(ByVal worksheetCodename As String, Optional wb As Workbook) As Worksheet
    Dim iSheet As Long
    If wb Is Nothing Then Set wb = ThisWorkbook ' mimics the default behaviour
    For iSheet = 1 To wb.Worksheets.Count
        If wb.Worksheets(iSheet).CodeName = worksheetCodename Then
            Set GetSheetWithCodename = wb.Worksheets(iSheet)
            Exit Function
        End If
    Next iSheet
End Function

使用示例：

GetSheetWithCodename("Sheet1", Workbooks("Book2")).Cells(1, 1) = "Sheet1 in Book2"
GetSheetWithCodename("Sheet1", ActiveWorkbook).Cells(1, 1) = "Sheet1 in ActiveWorkbook"
GetSheetWithCodename("Sheet1").Cells(1, 1) = "Sheet1 in ThisWorkbook" 

请注意，最后一行相当于简单地说：

Note that the last line is equivalent to simply saying:

Sheet1.Cells(1, 1) = "Sheet1 in ThisWorkbook" 

因为，如上所述，通过其代号引用的表格总是暗示 ThisWorkbook 中的表。

because, as mentioned above, referring to a sheet by its codename always imply the sheet in ThisWorkbook.

这篇关于通过codename完全引用工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！