Excel VBA - 根据单元格地址获取表格名称

Question

我正在使用Excel，并希望根据单元格地址（例如A3）获取表格的名称，该单元格不会移动。我该如何在Excel的VBA中陈述此问题？

我的计划是编写代码，将一个表格上的数据验证复制到工作簿每个标签页上的单个表格中（除了我的“TOC”和“data”标签）。每个标签页都是“TEMPLATE”工作表的副本（除了“TOC”，“data”和“TEMPLATE（Maint.）”工作表）。工作表“data”，“TEMPLATE”和“TEMPLATE（Maint.）”可能已隐藏。

我“Copy_Data_Validations”子程序中的代码如下：

Dim TotalSheets As Integer
Dim p As Integer
Dim iAnswer As VbMsgBoxResult

With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'
' Move sheet "TOC" to the begining of the workbook.
'
Sheets("TOC").Move Before:=Sheets(1)
'
' Move sheet "data" to be the second sheet in the workbook.
'
Sheets("data").Move Before:=Sheets(2)

iAnswer = MsgBox("You are about to copy data validations!", vbOKCancel + vbExclamation _
+ vbDefaultButton2 + vbMsgBoxSetForeground, "Copying Data Valadations")
For TotalSheets = 1 To Sheets.Count
    For p = 3 To Sheets.Count - 2
'
' If the answer is Yes, then copy data validations from "TEMPLATE (Maint.) to all other.
' sheets minus the "TOC" sheet and the "data" sheet.
'
        If iAnswer = vbYes Then
            If UCase$(Sheets(p).Name) <> "TOC" And UCase$(Sheets(p).Name) <> "data" Then

                ' This chunk of code should copy only the data validations
                ' of "Table1_1" (A4:AO4) from the maintenance tab to all
                ' rows of a single table on each worksheet (minus the
                ' "TOC", "data", & the "TEMPLATE (Maint.)" worksheets.
                ' This is the section of code I am looking for unless
                ' someone has something better they can come up with.

                Selection.PasteSpecial Paste:=xlPasteValidation, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If
'
' If the answer is Cancel, then cancels.
'
        ElseIf iAnswer = vbCancel Then
        ' Add an exit here.
        End If

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

- David S.

---

Debug.Print Range("A3").ListObject.Name 如果该单元格不属于表格，则会出错 - Scott Holtzman

这个问题有点含糊不清：例如，如果单元格（$A$3）存在于3个工作表中，那么VBA函数应该返回哪个名称？顺便说一句，下面给出的解决方案意味着传递Range对象（这意味着父工作表），而不是像问题中所述的单元格地址。最好的问候。 - Alexander Bell

我已经更新了问题，以包含更多的上下文信息，这样我就可以得到更清晰的答案。 - David S.

你的问题仍然不明确，因为通过引用Cell($A$3) ，您已经暗示了它所在的工作表，因此您应该已经将该信息作为设置代码引用到Cell($A$3)的一部分。否则，在感兴趣的每个工作表中放置我展示的工作表函数，并从VBA中引用它（尽管这似乎是一个奇怪的解决方案）。最好的问候。 - Alexander Bell

进一步澄清，希望我比昨天匆忙的工作做得更好，当时感觉不舒服，必须赶回家。如果还有什么没有解释清楚，请告诉我。 - David S.

刚刚意识到我不小心把这篇帖子变成了重复帖子，因为我添加了所有这些其他信息。尽管标题并不真正相关，但这是重复的。这是原始帖子：http://stackoverflow.com/questions/35704246/excel-macro-to-copy-data-validation-only-from-a-template-worksheet-to-several - David S.

Answer 1

如果尝试获取任何单元格的ListObject名称，而该单元格不是表格的一部分，则会导致错误。

Option Explicit

Function CellInTable(thisCell As Range) As String
    Dim tableName As String
    tableName = ""
    On Error Resume Next
    tableName = thisCell.ListObject.Name
    CellInTable = tableName
End Function

- PeterT

---

在顶部使用“Option Explicit”语句会得到额外的加分。 - user4039065

上面的“thisCell”是指哪种数据类型？ - Salih

@Salih thisCell 被定义为 Range，正如参数规范中所示。 - PeterT

Answer 2

原始问题有些模糊，因此回答被扩展以涵盖所有相关用例。

其中一种可能的替代方法是使用以下所示的Worksheet Formula，输入到任何Worksheet Cell（例如$A$3），然后从Excel VBA宏中引用它：

清单1. 使用单元格公式获取Excel Worksheet Name

=MID(CELL("filename",A3),FIND("]",CELL("filename",A3))+1,255)

该公式基本上是从Workbook的完整路径中提取Worksheet Name。

另外，在VBA中也可以通过传递引用Worksheet中那个单元格的Range对象来实现，就像以下演示样例中所示：

清单2. 获取单元格"A3"所在的Excel Worksheet和Table名称的测试子程序

Option Explicit

'Cell "A3" under the test
Sub GetWorksheetAndTableName()
    Dim myCell As Range
    Set myCell = Range("$A$3")
    Debug.Print "Worksheet Name: " & GetWorksheetName(myCell)
    Debug.Print "Table Name: " & GetTableName(myCell)
End Sub

代码清单3. 获取单元格所在的 工作表名称 的函数

'get Worksheet Name from Range object
Function GetWorksheetName(CellRange As Range) As String
    On Error Resume Next
    GetWorksheetName = Split(Split(CellRange.Address(External:=True), "]")(1), "!")(0)
End Function

在其最简单的形式中，可以使用以下语句（替换列表3中的函数中的语句）来获取Worksheet Name：

列表4. 获取单元格/范围对象的父级Worksheet Name的替代方法

GetWorksheetName = CellRange.Parent.Name

为了获取指定 Worksheet Cell 的表格名称，请参考以下代码片段的清单5：

清单5. 获取工作表单元格的 表格名称

Function GetTableName(CellRange As Range) As String
    If (CellRange.ListObject Is Nothing) Then
        GetTableName = ""
    Else
        GetTableName = CellRange.ListObject.Name
    End If
End Function

希望这能有所帮助。

- Alexander Bell

---

我不是在寻找基于单元格地址的选项卡名称，而是通过输入单元格地址来查找该单元格所属的表格名称。我还进一步提供了更多细节，并（希望）已经明确了我需要代码方面的帮助。 - David S.

针对您的2号清单，我正在寻找所在单元格所属的表格名称，而不是工作表名称。您能否更新回复以反映这一点？这将极大地帮助我。 - David S.

请参考我详细的回答，涵盖了您所有的问题。最好的问候。 - Alexander Bell