VBA Conditional - Is Nothing

Question

There is an If condition in a VBA application as seen below   If Not My Object Is Nothing Then My Object Compute   When the code is run in debug mode  I found that the If condition returns a true even when My Object has  No Variables    Could somebody please explain this  I want My Object Compute to be executed only when My Object exists

User · Answer

Just becuase your class object has no variables does not mean that it is nothing. Declaring and object and creating an object are two different things. Look and see if you are setting/creating the object.

Take for instance the dictionary object - just because it contains no variables does not mean it has not been created.

Sub test()

Dim dict As Object
Set dict = CreateObject("scripting.dictionary")

If Not dict Is Nothing Then
    MsgBox "Dict is something!"  '<--- This shows
Else
    MsgBox "Dict is nothing!"
End If

End Sub

However if you declare an object but never create it, it's nothing.

Sub test()

Dim temp As Object

If Not temp Is Nothing Then
    MsgBox "Temp is something!"
Else
    MsgBox "Temp is nothing!" '<---- This shows
End If

End Sub

User · Answer

In my sample code  I was setting my object to nothing  and I couldn t get the  not  part of the if statement to work with the object   I tried if My Object is not nothing and also if not My Object is nothing   It may be just a syntax thing I can t figure out but I didn t have time to mess around  so I did a little workaround like this   if My Object is Nothing Then      do nothing Else      Do something End if

User · Answer

Based on your comment to Issun      Thanks for the explanation  In my case  The object is declared and created prior to the If condition  So  How do I use If condition to check for  lt  No Variables    In other words  I do not want to execute My Object Compute if My Object has  lt  No Variables    You need to check one of the properties of the object   Without telling us what the object is  we cannot help you   I did test several common objects and found that an instantiated Collection with no items added shows  lt No Variables gt  in the watch window   If your object is indeed a collection  you can check for the  lt No Variables gt  condition using the  Count property   Sub TestObj   Dim Obj As Object     Set Obj   New Collection     If Obj Is Nothing Then         Debug Print  Object not instantiated      Else         If Obj Count   0 Then             Debug Print   lt No Variables gt   ie  no items added to the collection           Else             Debug Print  Object instantiated and at least one item added          End If     End If End Sub     It is also worth noting that if you declare any object As New then the Is Nothing check becomes useless   The reason is that when you declare an object As New then it gets created automatically when it is first called  even if the first time you call it is to see if it exists   Dim MyObject As New Collection If MyObject Is Nothing Then     lt --- This check always returns False   This does not seem to be the cause of your specific problem  But  since others may find this question through a Google search  I wanted to include it because it is a common beginner mistake

[vba] VBA: Conditional - Is Nothing

Examples related to vba

Examples related to if-statement

Examples related to excel

Examples related to excel-2007

Examples related to nothing