Event Macro Throws an Error "Object Required"

Question

I have this simple Event Macro, which for some reason throws me an

Object Required

error on this line If Not AppDate Is Nothing Then. Any idea what might be causing this?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Integer
    Dim ThisRow As String
    Dim AppDate As Variant
    Application.EnableEvents = False

    LastRow = Cells(Rows.Count, "C").End(xlUp).Row
    If Target.Column = 3 Then
        ThisRow = Target.Row
        AppDate = Target.Value
        If Not AppDate Is Nothing Then
            (...) Recalculate Date in Column F
        Else
        End If
    End If

    Application.EnableEvents = True
End Sub

Any suggestions would be greatly appreciated. Thank you!

@peakpeak I'm getting "User-defined type not defined" error... — hod
– hod, Commented Nov 15, 2017 at 17:09
@David -- a tutorial about Set. :-) I have a terriible memory and constantly make mistakes on my Nothings and Sets and such, so I end up cycling through the different syntaxes until it's right. Then it makes perfect sense... until the next time. — ashleedawg
– ashleedawg, Commented Nov 15, 2017 at 17:18

cyboashu · Accepted Answer · 2017-11-15 17:13:48Z

7

Is operator works with objects and not values.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim x As Variant
    Dim y As New Collection
    Dim z As Object

    x = Target.Value

    If IsEmpty(x) Then
        MsgBox "may be use this."
    End If

    MsgBox TypeName(x)

    If y Is Nothing Then
     MsgBox "Works"
    End If

    If z Is Nothing Then
     MsgBox "Works"
    End If

    If x Is Nothing Then '/This won't work

    End If


End Sub

answered Nov 15, 2017 at 17:13

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ashleedawg · Accepted Answer · 2017-11-15 17:10:08Z

4

Use:

…
If Not IsEmpty(AppDate) 
…

To help clarify, here is a lengthy tutorial about Nothing. _lol

answered Nov 15, 2017 at 17:10

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

2 Comments

ashleedawg Over a year ago

You're welcome. This is reminding me of a Seinfeld episode... :)

hod Over a year ago

Yep, writing something about nothing for so many pages requires some talent :)

Collectives™ on Stack Overflow

Event Macro Throws an Error "Object Required"

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related