VBA - returning a reference to a WorkBook

Question

I want to call a function that returns a Workbook or reference to that workbook. However I'm unable to set the value 'wb' with the workbook returned from the function.

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

Gives:

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

score 7 · Answer 1 · edited May 23 '17 at 10:29

You forgot to use the set statement two times:

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

Whenever you are assigning an object to a variable (reference it thereby) then you have to use the set statement (https://stackoverflow.com/a/349636/1153513).

Examples:

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

The key is using `Set` when setting the function name equal to the value to be returned: `Set GetWorkbook = db2`. I just spent like 2 hours struggling with this, not understanding that the `Set` keyword could/should be used in this situation. Thank you! — Abel Wenning, Jul 15 '21 at 03:07

VBA - returning a reference to a WorkBook

1 Answers1