Why does "If Value is Nothing" throw "Object Required (Error 424)"

Question

The following code throws Object Required (Error 424) at the if statement comparing with Nothing independent of the value given. Why?

Public Function SqlizeCellValue(ByVal Value As Variant) As Variant
    If Value Is Nothing Then
        SqlizeCellValue = Nothing
    ElseIf Value = Null Then
        SqlizeCellValue = Null
    ElseIf Value = "" Then
        SqlizeCellValue = Null
    ElseIf Value = "0" Then
        SqlizeCellValue = Null
    ElseIf Value = "---" Then
        SqlizeCellValue = Null
    ElseIf LCase(Value) = "n.c." Then
        SqlizeCellValue = Null
    Else
        SqlizeCellValue = Value
    End If
End Function
Public Sub TestSqlizeCellValue()
    Debug.Assert SqlizeCellValue("") Is Null
    Debug.Assert SqlizeCellValue("0") Is Null
    Debug.Assert SqlizeCellValue("---") Is Null
    Debug.Assert SqlizeCellValue(Nothing) Is Nothing
    Debug.Assert SqlizeCellValue(Null) Is Null
End Sub

Because `Value` is a `Variant` and only an `Object` can be set to `Nothing`. — Dave, Jul 06 '16 at 15:55
Thanks @Dave. Could you provide this as an answer please, so I can mark it as accepted/ correct? — Torsten Knodt, Jul 06 '16 at 16:00

score 2 · Accepted Answer · answered Jul 06 '16 at 16:02

2

Because Value in your function definition is set to type Variant and only an Object can be Set to Nothing

answered Jul 06 '16 at 16:02

Dave

4,328
2
24
33

I don't think this is true. `Variant` can be set to anything, including objects and nothings, but the `is` operator throws a type error, when it's not either an object or `Nothing`. – m93a Aug 24 '17 at 12:45

Why does "If Value is Nothing" throw "Object Required (Error 424)"

1 Answers1