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

Question

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

Can you post more code? The issue clearly isn't with anything after the line you have issues on. We would need to see the previous spots that your range variable is in the code. — Kyle, Aug 12 '16 at 18:16
Object required means that the variable isn't an Object type. It's probably a variant type which hasn't been properly declared and/or set, so therefore it's an Empty. If the range simply hadn't been *set*, you'd get a 91 error: Object, variable, or with block not set. — David Zemens, Aug 12 '16 at 18:27
@Kyle That's the thing. The only thing about this code that involves the variable other than what is captioned is `Public CCAddedGPSum As Range`. — Munkeeface, Aug 12 '16 at 18:28
Right, but doesn't that still mean that `CCAddedGPSum` isn't an object? OP should use the `Locals` window to query the value of this variable when the error raises... — David Zemens, Aug 12 '16 at 18:30
@SiddharthRout Yes. But for this specific variable, it has not been referenced once before, meaning it would not be associated with a `.Find` in the first place. I actually thought of this at first, but like I said previously, it has ONLY been declared in the `Public [variables]` block in the main module — Munkeeface, Aug 12 '16 at 18:35
@DavidZemens I tried opening the Local's window, but it only showed Local variables (obviously) which this is being called in a `UserForm`. The original variable declaration is located in the module that call's the Form. I restarted the macro and it then read the "Object, variable, or with block not set" error, and properly returned as nothing... this error is showing once in a handful of tests with the SAME EXACT testing data. Same files, nothing changed. I know this seems like a small user error, but there's no input required than two original source files. They are consistent and unchanged — Munkeeface, Aug 12 '16 at 18:38
@SiddharthRout Would testing a range that has not been set on whether or not it `Is Nothing` not return as true? The variable is technically nothing at the point of initialization — Munkeeface, Aug 12 '16 at 18:38
Without setting the object, I get a `91` error, not a `424`. @Munkeeface can you clarify what is the *exact* error message you see? — David Zemens, Aug 12 '16 at 18:42
I understood the problem. The condition `If CCAddedGPSum Is Nothing Then` is `False` that means `CCAddedGPSum` has something. But `Debug.Print CCAddedGPSum.Value` is giving an error. Hmmm, That is strange... — Siddharth Rout, Aug 12 '16 at 18:44
Can you do me a small test? Change `Public CCAddedGPSum As Range` to `Dim CCAddedGPSum As Range`. Now test it? — Siddharth Rout, Aug 12 '16 at 18:47
To unclutter, I have deleted my previous messages as they are now irrelevant. — Siddharth Rout, Aug 12 '16 at 18:49
OK, another guess.. do you have error handlers in your code? Are you resuming somewhere? — cyboashu, Aug 12 '16 at 18:52
Can you please post more code @Munkeeface? Obviously something else is going on here and maybe there is something in your code that you are missing that others might catch. — Kyle, Aug 12 '16 at 18:56
@Munkeeface: You say "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." But if you do Debut.Print on an object that has not been Set you actually get a "Object variable ... not set". What you get is what you get on a non object. My twocents: have you spellec CCAddedGPSum correctly in the debug.print call? — Mats Lind, Aug 12 '16 at 19:00
@MLind see my comment above to same effect: http://stackoverflow.com/questions/38924142/why-is-my-if-range-is-nothing-statement-not-detecting-that-the-variable-is-n/38924810#comment65205446_38924142 — David Zemens, Aug 12 '16 at 19:01
@David, public variable in worksheet, then it errors out at the If condition rather than going to End if, i.e evaluating it false. — cyboashu, Aug 12 '16 at 19:06
That's a good point @cyboashu ... yes, mine fails absolutely. Perhaps there is an `On Error Resume Next`.. — David Zemens, Aug 12 '16 at 19:11
Can you show full code for the event handler in your UserForm? — David Zemens, Aug 12 '16 at 19:29
Also, when you say it *ignores the test and continues to "END IF"*, do you mean that it will execute ALL code within the IF block? — David Zemens, Aug 12 '16 at 19:34

David Zemens · Answer 1 · 2016-08-12T19:48:00.803

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.

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

If the declaration of `CCAddedGPSum` is in a Worksheet or Workbook module, it doesn't matter if there is error handling or not. The Worksheet and Workbook modules obviously *never go out of scope* until the Workbook is closed. Member variables will retain their values between macro runs. My guess is that the first time the macro is run the value gets set when it passes the `If` condition. Unless it is set to `Nothing` in code, the value persists and it fails the `If` test until the Workbook is closed and reopened. — Comintern, Aug 13 '16 at 04:42

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

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

edited May 23 '17 at 12:23

Community

1
1

answered Aug 12 '16 at 18:24

Travis Peterson

373
3
15

Well the only time this was referred to other than what is visible in this block is `Public CCAddedGPSum As Range` in the variable declaration block in the top of my main module. There is literally nothing other than this. Why is it not returning nothing in this instance? – Munkeeface Aug 12 '16 at 18:29

score 0 · Answer 3 · answered Aug 12 '16 at 19:38

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!!!

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

3 Answers3