title: Define function that can take null parameter
body: <p>I'm was recently trying to redefine Access's <code>Nz(Value, [ValueIfNull])</code> 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 <code>Value</code> - if this value is null, it returns <code>ValueIfNull</code> (otherwise it returns <code>Value</code>). In Access it's useful for checking the value of input boxes (amongst several other things):</p>
  
  <pre><code>If Nz(myTextBox.Value, "") = "" Then
      MsgBox "You need to enter something!"
  End If
  </code></pre>
  
  <p>Rolling my own <code>Nz</code> function didn't seem difficult:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But as soon as I try to call it with anything that's actually null, Excel complains about it on the calling line (<code>Run-time error '91': Object variable or With block not set</code>, which I understand to be roughly equivilant to a <code>NullReferenceException</code> in other languages), before even getting to the Nz function body. For example <code>Nz(someObj.Value, "")</code> will only work if <code>someObj.Value</code> isn't null (rendering the function entirely moot).</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>How can you create subs and functions in VBA that will accept a null parameter?</p>