CODEWITHSUNDEEP
excelvba
Spencer Barnes
Spencer Barnes

Reputation: 2877

Check if Workbook variable has been set

I have a sub with an optional workbook argument. I want an If to check whether that variable was passed, and if not set the variable to the active worksheet.

Sub SaveWorkbook(NewPathName As String, Optional Workbook As Workbook)
    'This is very simplified, the real thing has various other parameters

    If IsNull(Workbook) Then Set Workbook = ActiveWorkbook

    'Then loads more stuff
End Sub

Things I have tried include:

IsNull(Workbook)
IsEmpty(Workbook)
Workbook = Nothing

None trigger the If statement so the code attempts to continue with Workbook set to Empty, and then hits errors.

Upvotes: 1

Views: 1891

Answers (2)

MGP
MGP

Reputation: 2551

In VBA the isMissing function only works, if you declare the parameter as Variant. See in the description

This should work:

Sub SaveWorkbook(NewPathName As String, Optional Workbook As Variant)
    'This is very simplified, the real thing has various other parameters

    If isMissing(Workbook) Then Set Workbook = ActiveWorkbook

    'Then loads more stuff
End Sub

Upvotes: 1

braX
braX

Reputation: 11755

Do not use the word "Workbook" as the variable name. Try it like this:

Sub SaveWorkbook(NewPathName As String, Optional wb As Workbook)

  If wb Is Nothing Then
    MsgBox "workbook not set"
    Set wb = ActiveWorkbook
  End If

  MsgBox wb.Name

End Sub

Upvotes: 2

Related Questions