Why won't Excel VBA accept my user defined variable

Question

I have recently written some code that finds a value in a worksheet. If said value exists, it offsets the active cell to the first row. If the value doesn't exist, it displays an error message. I'm having an issue where it is rejecting my variable because of either a "runtime 91 error" or a compile error that requires an object. I am new to VBA, per chance might anyone know what this error is asking for. Below is my code for facilitated viewing.

Private Sub CommandButton1_Click()    
    Dim IDNUM As Boolean
    Set IDNUM = Worksheets("Petrobras").Range("V:V").Find(TXTOPPNUM_Insert.Value, , , LookAt:=xlWhole).Select

    If IDNUM = False Then
        MsgBox "This Opportunity has Not Been Registered Yet"
    Else
        ActiveCell.Activate
        ActiveCell.Offset(0, -21).Activate
    End If
End Sub

1) It's a `Boolean`, so is only going to accept `TRUE/FALSE`, 2) You're using `Find()` which results in a `Range()`, except 3) You're trying to `.Select` the same time you're trying to set a variable...I think you want `Dim IDNUM as Range // Set IDNum = Worksheets("Petrobras").Range("V:V").Find(TXTOPPNUM_Insert.Value, , , LookAt:=xlWhole)`, then `If IDNum.Value = False` (or perhaps `"False"`)....And finally, I suggest reading through [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Nov 21 '19 at 22:13

score 1 · Answer 1 · edited Nov 22 '19 at 15:06

Dim IDNUM As Boolean

This declares the identifier IDNUM and allocates it enough memory to store a Boolean value for it. Note that the possible Boolean values are True and False.

Set IDNUM = ...

This is a Set assignment, which means VBA will be expecting the left-hand side of the assignment operator (that's =) to be a reference type (i.e. an object reference). But IDNUM is a Boolean, so the Set assignment is illegal.

You want this to be a value assignment:

Let IDNUM = ...

But then, the Let keyword is redundant/obsolete, so you can just do:

IDNUM = ...

Now, the right-hand side of the assignment is also problematic:

Worksheets("Petrobras").Range("V:V") _
                       .Find(TXTOPPNUM_Insert.Value, , , LookAt:=xlWhole) _
                       .Select

First, the expression has no defined type: the (Range).Select method returns no value, so the expression can't legally appear to the right of an = operator.

(Range).Find does return something though - but it returns a Range object reference, and when it can't find what it's looking for, it returns Nothing - a special reference value that basically means "there's no object here", and any member call (like .Select) made against Nothing will always raise run-time error 91. NEVER assume Range.Find will return a valid object reference.

Instead, capture the search result into an object variable:

Dim findResult As Range
Set findResult = Worksheets("Petrobras").Range("V:V").Find(TXTOPPNUM_Insert.Value, LookAt:=xlWhole)

Note that because you're using named arguments, you don't need to specify the optional empty positional ones. That said Range.Find will use unspecified defaults for every single one of the optional parameters you're not specifying, so the recommendation is to ALWAYS provide every single one of them - otherwise you're at the mercy of whatever the user did last time they hit Ctrl+F.

IF the returned object isn't Nothing, then you can use it:

If Not findResult Is Nothing Then
    IDNUM = findResult.Value 'not sure what you mean to do here
Else
    'no found. now what?
    Exit Sub
End If

Lastly, note that If {bool-expression} = {True|False} Then is redundant: a Boolean value is a Boolean expression, so the comparison to a True or False literal is entirely redundant. Use the Not logical operator instead of comparing to False:

If Not IDNUM Then
    MsgBox "This Opportunity has Not Been Registered Yet"
Else
    ActiveCell.Offset(0, -21).Activate
End If

...and consider avoiding negatives by putting the "nope" case last and reversing the condition:

If IDNUM Then
    ActiveCell.Offset(0, -21).Activate
Else
    MsgBox "This Opportunity has Not Been Registered Yet"
End If

Note that the ActiveCell is already active - ActiveCell.Activate does nothing.

...and you don't need to care for the ActiveCell at all - the cell you want is the findResult range:

If IDNUM Then
    findResult.Offset(0, -21).Activate
Else
    MsgBox "This Opportunity has Not Been Registered Yet"
End If

Recap:

Dim findResult As Range
Set findResult = Worksheets("Petrobras").Range("V:V").Find(TXTOPPNUM_Insert.Value, LookAt:=xlWhole)
If Not findResult Is Nothing Then
    If CBool(findResult.Value) Then
        findResult.Offset(0, -21).Activate
    Else
        MsgBox "This Opportunity has Not Been Registered Yet"
    End If
End If

End If

Why won't Excel VBA accept my user defined variable

1 Answers1