Geek Answers Handbook

Define a function that can take a null parameter

I recently tried to override the Access function Nz(Value, [ValueIfNull])in Excel, because I find it pretty useful, but it is missing in Excel (since I found my frustration at moving a few useful functions). The Nz accessor checks Valueif this value is null, it returns ValueIfNull(otherwise it returns Value). Access is useful for checking the value of input fields (among several other things):

If Nz(myTextBox.Value, "") = "" Then
    MsgBox "You need to enter something!"
End If

Rolling my function Nzdidn't seem complicated:

Public Function Nz(value As Variant, Optional valueIfNull As Variant = "") As Variant
    If IsNull(value) Then
        Nz = valueIfNull
    Else
        Nz = value
    End If
End Function

But as soon as I try to call it what is actually zero, Excel complains about it in the calling line ( Run-time error '91': Object variable or With block not setwhich, as I understand it, is roughly equivalent NullReferenceExceptionin other languages), before it gets into the body of the Nz function. For example, it Nz(someObj.Value, "")will work only when someObj.Valueit is not equal to zero (rendering the function is completely debatable).

Did I miss some VBA details here? Based on languages ​​like VB.NET, this seems very confusing - I understand that references to objects are simply addressed to a real object in memory, and therefore bypassing the link (and not the object) should not cause problems (until you try to actually do something with a non-existent object, of course). For example,

Dim myObj As SomeObject
SomeMethod(myObj)  'the call itself is fine

Public Sub SomeMethod(SomeObject obj)
    myObj.DoSomething() 'but *here* it would crash
End Sub

How can you create subtitles and functions in VBA that will take a null parameter?

+4
1

. this that, -

Sub Main()

    Dim obj As Range
    Debug.Print Nz(obj)

    Dim v As Variant
    v = Null
    Debug.Print Nz(v)

End Sub

Public Function Nz(value As Variant, Optional valueIfNull As Variant = "") As Variant

    ' deal with an object data type, vbObject = 9
    If VarType(value) = vbObject Then
        If value Is Nothing Then
            Nz = valueIfNull
        Else
            Nz = value
        End If

    ' deal with variant set to null, vbNull is a Variant set to null
    ElseIf VarType(value) = vbNull Then
        If IsNull(value) Then
            Nz = valueIfNull
        Else
            Nz = value
        End If
    End If
End Function
+8

More articles:


All Articles