CODEWITHSUNDEEP
vbaexcel
Munkeeface
Munkeeface

Reputation: 409

Why is my If [Range] Is Nothing statement not detecting that the variable is Nothing?

I have an If statement testing to see if Range CCAddedGPSum Is Nothing, which is the case, but when it tests, it determines it to be otherwise.

When I use a Debug.Print CCAddedGPSum.Value, I receive an error claiming that an Object is required, which indicates the variable has not been Set. Why is this not returning as Is Nothing?

Here is the code: 

If CCAddedGPSum Is Nothing Then 'Once here, ignores the test and continues to "END IF"
    Set CCAddedGPSum = Range(CCGPSum.Offset(1, -3), CCGPSum.Offset(1, 1))
    CCAddedGPSum.Insert shift:=xlDown

    Set CCAddedGPSum = Range(CCGPSum.Offset(1, -3), CCGPSum.Offset(1, 1))
    CCAddedGPSum.Interior.ColorIndex = 0
    CCAddedGPSum.Insert shift:=xlDown

    Set CCAddedGPSum = Range(CCGPSum.Offset(1, -3), CCGPSum.Offset(1, 1))
    CCAddedGPSum.Interior.ColorIndex = 0

    Set CCAddedGPTitle = Range(CCGPSum.Offset(1, -2), CCGPSum.Offset(1, -1))
    With CCAddedGPTitle
        .MergeCells = True
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
    End With

    CCAddedGPTitle.Value = "Removed from Deposit:"
    Set CCAddedGPSum = CCGPSum.Offset(1, 0)
    If CCAddedGPSum2 Is Nothing Then
        CCAddedGPSum.Borders(xlEdgeBottom).LineStyle = xlContinuous
    End If

    If CCGPSum.Offset(-1, 0).Text = "" Then
        Set CCGPSubtotal = CCGPSum
        Set CCGPSum = CCAddedGPSum.End(xlDown).Offset(1, 0)
        Range(CCGPSum.Offset(0, -1), CCGPSum.Offset(0, -2)).MergeCells = True
        CCGPSum.Offset(0, -1).HorizontalAlignment = xlRight
        CCGPSum.Offset(0, -2).Value = "Total:"
        CCGPSum.Interior.ColorIndex = 6
    End If
End If

Upvotes: 0

Views: 1459

Answers (3)

David Zemens
David Zemens

Reputation: 53663

I observe some similar problems if the Public declaration is made in a Worksheet module, it is not available to the UserForm module unless qualified to the sheet. Please let me know if this is the case.

If you have not done so, put Option Explicit on top of your UserForm module and it may show you that the variable is not defined.

enter image description here

I also suspect there is an On Error Resume Next statement within the UF module, which allows the form to display, otherwise it may fail silently. To diagnose further need to see which event handler is firing the code. If the variable is in an event handler like a command button, etc., and the form remains active, the variable may remain in scope and that might explain why you are experiencing intermittent problems.

An On Error Resume Next statement in the UserForm event handler would cause the test to appear to return True (technically, it's not returning anything, the If statement errors and the error handler takes over resuming on the next line, so the body of the If/EndIf block executes unexpectedly.

Note: If your Public declaration is in a standard module, this solution may not work.

Example code in Sheet1 module:

Option Explicit
Public r As Range

Sub Main()

    UserForm1.Show

End Sub

Example code in UserForm1 module which will raise the exact 424 error: Object required, against the Public variable r:

Private Sub CommandButton1_Click()
If r Is Nothing Then
    Debug.Print r.Address
    MsgBox "'r' is Nothing"
    Set r = Range("A1")
Else:
    MsgBox r.Address
End If

MsgBox "end of UserForm_Initialize"
End Sub

To resolve it, qualify r to Sheet1.r or assign to a procedure scoped variable:

Private Sub CommandButton1_Click()
Dim r As Range
Set r = Sheet1.r
If r Is Nothing Then
    Debug.Print r.Address
    MsgBox "'r' is Nothing"
    Set r = Range("A1")
Else:
    MsgBox r.Address
End If

MsgBox "end of UserForm_Initialize"
End Sub

Upvotes: 2

Slai
Slai

Reputation: 22886

one example of why Is Nothing is not that reliable with Range

Dim r As Range
Debug.Print r Is Nothing    ' True
Set r = [a1]
Debug.Print r.Value         ' ok
[a1].Delete                 ' !!!
Debug.Print r Is Nothing    ' False!!!
Debug.Print r.Value         ' error!!!

Upvotes: 0

Travis Peterson
Travis Peterson

Reputation: 420

See... VBA: Conditional - Is Nothing .

Dim MyObject As New Collection
If MyObject Is Nothing Then  ' <--- This check always returns False

This is assuming CCAddedGPSum is declared as a New Object

Upvotes: 0

Related Questions