• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2085
  • Last Modified:

Can a VBA function return a workbook object?

I would like to call a function that will return a workbook object.  Is that possible?  If so how?
In the code below the last line above "ExitHere:" throws the following error.

Error #  91 was generated by VBAProject
Object variable or With block variable not set

Any help would be greatly appreciated.

' This prompts the user for a File Name to open.
Function OpenWorkbook() As Workbook
On Error GoTo ErrHandler
    Dim Msg As String
    Dim vPath As Variant
    Dim wbTwo As Workbook
    vPath = Range("FilePath")
    If vPath = "" Then
        vPath = Application.GetOpenFilename()
        If vPath = False Then
            ' user cancelled, get out
            Exit Function
        End If
        Range("FilePath") = vPath
    End If
    Debug.Print "FileName: " & vPath
    Workbooks.Open Filename:=vPath
    Set wbTwo = ActiveWorkbook
    OpenWorkbook = wbTwo
    Exit Function
If Err.Number <> 0 Then
    Msg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End If
Resume ExitHere
End Function

Open in new window

1 Solution
I would do this:

    Debug.Print "FileName: " & vPath
    Set OpenWorkbook = Workbooks.Open( Filename:=vPath)
dec789Author Commented:
Graham-Skan  Thank you very much that did the trick!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now