Test if WB is <No Variables>

[GlennL]

Hello,

I have found this breaks the runtime

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

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

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.

 

[DanteAmor]

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.

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

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

 

[Micron]

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.

 

[GlennL]

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.

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

 

[DanteAmor]

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:

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.

 

[RoryA]

Sub One, wb is never nothing

It should be, and certainly is here. As soon as the sub starts wb is Nothing.

 

[MARK858]

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