Nz Function

I need an Nz function in Excel similar to Access’s Nz function. I have a class with a bunch of strongly typed properties, e.g. a TxnID property that’s typed as a String rather than a Variant. I’m creating a recordset from an Jet database and putting the recordset’s fields into the class. Some of the fields are Null and that doesn’t play well with my strongly typed properties. Specifically, I get an “Invalid Use of Null”.

In Access, it seems that Nz returns a zero-length string when passed a string and a zero when passed anything else. At first, I had an argument called bIsString and used it to determine whether to return a zero-length string or a zero. I didn’t really like that method because it was too easy to forget and it didn’t match Access’s Nz function, which figured it out on its own. Instead of passing a Variant value (that could be Null), I switched to passing the ADODB.Field object. That way I could test the Type property to determine what kind of return value would be appropriate.

This is what I came up with. I haven’t tested the heck out of it, so let me know if you note any mistakes.

See W3Schools List of ADO Type Property Constants

Here’s some code I used to test it:

2 thoughts on “Nz Function

  1. I must admit that i use On Error Resume Next while getting values from recordsets, like

    On Error Resume Next
    MyItem.LngID = Rst(0)
    MyItem.StrName = Rst(1)
    ‘…
    On Error Goto 0
    Rst.MoveNext

    Then numerical properties keeps the default 0 and strings “” on null errors. …It’s ok to call me ignorant and lazy in this thread :-)

  2. Nz(Null) returns Empty in MS Access, so the following Excel vba matches MS Access perfectly.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.