7

I'm was recently trying to redefine Access's Nz(Value, [ValueIfNull]) function in Excel, because I find it pretty useful yet it's absent in Excel (as I found to my disappointment when moving a few useful functions over). Access's Nz function checks Value - if this value is null, it returns ValueIfNull (otherwise it returns Value). In Access it's useful for checking the value of input boxes (amongst several other things):

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

Rolling my own Nz function didn't seem difficult:

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 with anything that's actually null, Excel complains about it on the calling line (Run-time error '91': Object variable or With block not set, which I understand to be roughly equivilant to a NullReferenceException in other languages), before even getting to the Nz function body. For example Nz(someObj.Value, "") will only work if someObj.Value isn't null (rendering the function entirely moot).

Am I missing some detail of VBA here? Coming from languages like VB.NET, it seems very confusing - I understand object references to be simply addresses to an actual object residing in memory, and so passing around the reference (not the object) shouldn't cause issue (until you try to actually do something with the non-existant object, of course). For eg:

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 subs and functions in VBA that will accept a null parameter?

Community
  • 1
  • 1
Kai
  • 2,050
  • 8
  • 28
  • 46
  • Quick note: Aware that I can more-or-less replicate `Nz` in Excel by using `Iif` instead, but the question is about defining functions that take null parameters in general rather than a fix for that specific case - it just happened to be a useful scenario – Kai Dec 18 '13 at 15:39
  • do you want to work with Variants or Objects? –  Dec 18 '13 at 15:49

1 Answers1

13

see this and that if anything is still unclear and try

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
Community
  • 1
  • 1