VBA:将工作表作为函数的参数时如何寻址正确的工作簿？

Question

我对 VBA 中工作簿的正确地址有疑问，我对 VBA 还很陌生。

这是我到目前为止所做的:
我编写了一个子程序，除其他外，它还创建了一个代码名称为“table10”的工作表。

然后我定义了一个函数来操作所述工作表的内容:这个函数

Text_To_Numbers(worksheet as worksheet)

需要一个工作表参数。我使用以下行从另一个子程序调用该函数:

Call Text_To_Numbers(table10)

现在，这是我的问题:
当唯一打开的工作簿是我想用我的函数操作的工作簿时，上述方法可以完美地工作。但是，当我有多个打开的工作簿时，该函数将尝试操作不同的工作簿，从而导致错误。

我很确定一定有一种方法来指定要使用的工作簿，但我找不到它。话虽如此，还有另一个复杂之处:我想要操作的工作簿的名称是机器生成的，因此它总是有不同的名称。这意味着不能一次又一次地显式引用同一文件名。

有人可以帮我解决这个问题吗？

Answer 1

您需要在 VBA 中完全限定对象，以避免出现父对象不明确的情况。

在您的情况下，您希望工作表连接到其父工作簿，因此请确保指定它来自给定的工作簿!

您不能通过 CodeName 直接引用其他工作簿中的工作表，这只能对 ThisWorkbook 对象(包含VBA 代码)。查看问题Fully reference a worksheet by codename有关如何通过其代号从另一个工作簿获取工作表的详细信息。我已将该函数包含在答案中以及如何在这种情况下使用它。

您通过以下方式之一创建了工作表 table10:

• 事件工作簿
• 本工作簿
• WB(某些工作簿对象)

因此您可以使用该工作簿对象访问它，而无需名称!

<小时/>

使用 ThisWorkbook.table10 应该提供与 table10 相同的行为，但这里有两个更简洁的调用该函数的示例。

' A neater way to call the function:
Text_To_Numbers worksheet:=ThisWorkbook.table10
' You could also call it simply using
Text_To_Numbers ThisWorkbook.table10

---

如果您的工作表不在ThisWorkbook

中

' Get sheet (from the workbook object you are using, WB) and pass to your Text_To_Numbers 
Text_To_Numbers GetSheetWithCodename("table10", WB)

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