Test if WB is <No Variables>

GlennL

New Member
Joined
Sep 25, 2018
Messages
37
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hello,

I have found this breaks the runtime

VBA Code:
Dim wb As Workbook

    If Not wb Is Nothing Then
        On Error Resume Next
        wb.Saved = True
        wb.Close
        On Error GoTo 0
    End If

The
VBA Code:
If Not wb Is Nothing Then
is ignored if the variable is not set.

Looking in the locals window, wb says <No Variables>.
How do I test this variable?

I can easily make the code
VBA Code:
Dim wb As Workbook
    set wb = Nothing

    If Not wb Is Nothing Then
        wb.Saved = True
        wb.Close
    End If

But I would like to know how to test the variable if not set.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Open a new book and save it as "book1.xlsx". Don't close the book.
In another book with your macros put the following code and testit.

VBA Code:
Sub Test_object_Workbook()
  Dim wb As Workbook
 
  On Error Resume Next
  Set wb = Workbooks("book1.xlsx")
  On Error GoTo 0
 
  If Not wb Is Nothing Then
    wb.Saved = True
    wb.Close
  Else
    MsgBox "The book is not open"
  End If
End Sub

Another example to test book set on an object.
Close your book1.xlsx workbook, put the following macro in your macro workbook and try it. The macro will open the book book1, check the object, and then close the book1
VBA Code:
Sub Test_object_WB_2()
  Dim wb As Workbook
 
  If Dir(ThisWorkbook.Path & "\" & "book1.xlsx") <> "" Then
    Set wb = Workbooks.Open("book1.xlsx")
 
    If Not wb Is Nothing Then
      wb.Saved = True
      wb.Close
    Else
      MsgBox "The book is not open"
    End If
  Else
      MsgBox "The book does not exists"
  End If
End Sub
 
Last edited:
Upvote 0
Not sure what you're asking about. Object variables must be Set otherwise they will be Nothing. wb.Saved: you are trying to save something that has a placeholder (variable) for it, but it doesn't exist.
 
Upvote 0
Look in the locals window when you run both of these, you'll find wb isn't always nothing.
Sub One, wb is never nothing.

VBA Code:
Sub One()
Dim wb As Workbook

    If Not wb Is Nothing Then
        On Error Resume Next
        wb.Saved = True
        wb.Close
        On Error GoTo 0
    End If
End sub

Sub Two()
Dim wb As Workbook
    set wb = Nothing

    If Not wb Is Nothing Then
        wb.Saved = True
        wb.Close
    End If
end Sub
 
Upvote 0
I explain in steps how the declaration of variables as objects works and in general this is how variables work.

1. You declare the variable as an object type, in your example you declare wb of type workbook.
When you declare a variable, you reserve memory to store values in the variables; and that way you make your code more efficient.
If you don't do this, vba declares all variables of type variant, which is not efficient for allocating memory nor to use variables in the code.

2. All variables declared, at the beginning, are at their initial value, those of type String are blank, the Numeric ones are at 0, and the Objects are at nothing.

3. In your example, putting wb = nothing or not doing it is the same, since as I mentioned in point 2, the variables of the object at the beginning are at nothing.

4. Next, you must set an object to the object variables. If you looked at my examples from post #2 you can see that I am setting up a book. And that way you can ask if the wb object is in nothing or it is not.

5. At the end of the code it is advisable to clear the object variables to free up memory.

Ex:
Rich (BB code):
Sub Test_object_Workbook()
  Dim wb As Workbook
 
  On Error Resume Next
  Set wb = Workbooks("book1.xlsx")
  On Error GoTo 0
 
  If Not wb Is Nothing Then
    wb.Saved = True
    wb.Close
  Else
    MsgBox "The book is not open"
  End If

  Set wb = Nothing
End Sub

I hope the information is useful to you.

;)
 
Upvote 0
Look in the locals window when you run both of these, you'll find wb isn't always nothing.
Sub One, wb is never nothing.

As requested

1726680588365.png
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top