excel - VBA - 返回对工作簿的引用

Question

我想调用一个返回工作簿或对该工作簿的引用的函数。但是，我无法使用从函数返回的工作簿设置值“wb”。

Public Sub TestScript()

    Dim wb As Workbook    
    wb = GetWorkBook()

End Sub


    Function GetWorkBook() As Workbook

    Dim db2 As Workbook
    Dim xlApp As Excel.Application
    Set xlApp = GetObject(, "Excel.Application")

    Dim xlWB As Excel.Workbook
    For Each xlWB In xlApp.Workbooks
        If xlWB.Name = "Test.XLSX" Then
            Set db2 = xlWB
        End If
    Next xlWB

    Set xlApp = Nothing
    Set xlWB = Nothing

    GetWorkBook = db2


    End Function

给予:

Runtime Error 91: Object Variable or With Block Variable not set

Answer 1

您忘记使用 set 语句两次:

Public Sub TestScript()

    Dim wb As Workbook
    Set wb = GetWorkBook()

End Sub

Function GetWorkBook() As Workbook

Dim db2 As Workbook
Dim xlApp As Excel.Application
Set xlApp = GetObject(, "Excel.Application")

Dim xlWB As Excel.Workbook
For Each xlWB In xlApp.Workbooks
    If xlWB.Name = "Test.XLSX" Then
        Set db2 = xlWB
    End If
Next xlWB

Set xlApp = Nothing
Set xlWB = Nothing

Set GetWorkBook = db2

End Function

每当您将一个对象分配给一个变量(从而引用它)时，您必须使用 set 语句(https://stackoverflow.com/a/349636/1153513)。

例子:

1. Set rngYourRangeVariable = Thisworkbook.Worksheets("Sheet1").Range("A1:C4")
2. 设置 shtSomeSheet = Thisworkbook.Worksheet("Sheet1")
3. 设置 conSomeADOconnection = New ADODB.Connection