Nullの代わりに他の値を使う『Nz関数』をExcelで使う方法

　　
対象がNullの場合、Null以外（指定した値）を返せるNz関数。
IfまたはIIfを使わず、コードも短くできてとっても便利です。
しかしこの関数にも欠点があって、それは『Accessでしか使えない』ということ。

IsNullやIfまたはIIfを組み合わせれば同じことができますが、Nzと比較するとコードは長くなります。
1回だけならともかく、頻繁に使うなら短くした方が入力も楽になるし読みやすさもアップ。
ということで、ユーザー定義関数として「Excelで使えるNz関数」を作成しました。

Excelで使えるNz関数「ENz」のコード

2021/12/23追記
「xl～」はExcel VBAの定数名に使われているパターンなので、関数名として使うのはちょっと違うかな～と今更ながら思い、関数名を「ENz」に変更しました。
変わったのは名前だけで動作に変更はありません。

これを標準モジュールに貼り付けます。

Function ENz(tgtVal As Variant, Optional VIN As Variant = Empty) As Variant
    ENz = IIf(IsNull(tgtVal), VIN, tgtVal)
End Function

ENzの使い方

使い方はNzと同じ。
Nullが返ると困る場面や、Nullが返る可能性のある場面で使えます。

ENz(値, Nullの場合に返す値)

例えば…
計算式の中にNullがあると答えがNullになってしまうので、Nullの代わりに0を返すようにするとか。

n=Null:?1+n '←1+NullはNullになる
n=Null:?1+ENz(n, 0) '←ENzでNullが0に変換され、1+0で1になる
n=1:?1+ENz(n, 0) '←nはNullではないので1がそのまま使われ、1+1で2になる

Nullの時はNullであることを教えてもらうとか。

n=Null:?Msgbox(ENz(n,"Nullですよ")) '←「Nullですよ」のメッセージボックスを表示
n="Nullじゃないですよ":?Msgbox(ENz(n,"Nullですよ")) '←「Nullじゃないですよ」のメッセージボックスを表示

上のコードをイミディエイトウィンドウに貼り付けて実行すると、1行下に結果が表示されます。
ちなみにイミディエイトウィンドウはCtrl＋Gで開けます。
実行する場合は、実行したいコードの行にカーソルがある状態でEnterキー。

テストコード

↓のコードをExcelの標準モジュールに貼り付けて実行すると、イミディエイトウィンドウに結果が表示される。
AccessのはENzをNzに変換し、同じ結果が出るかをテストしたもの。

Sub Nzテスト_Excel()
    Debug.Print ENz(Null) & " ←(Empty)"
    Debug.Print IsEmpty(ENz(Null)) & " ←True"
    Debug.Print ENz(Null, 0) & " ←0"
    Debug.Print ENz(Null, "ﾇﾙ") & " ←ﾇﾙ"
    Debug.Print ENz(123) & " ←123"
    Debug.Print ENz(123, 0) & " ←123"
    Debug.Print ENz(Null, 0) & " ←0"
    Debug.Print ENz("abc") & " ←abc"
    Debug.Print ENz("abc", "") & " ←abc"
    Debug.Print ENz(Null, "") & " ←(空白)"
    Debug.Print IIf(ENz(Null, "") = "", True, False) & " ←True"
        Rem 各返り値(イミディエイトウィンドウに表示)
    Rem  ←(Empty)
    Rem True ←True
    Rem 0 ←0
    Rem ﾇﾙ ←ﾇﾙ
    Rem 123 ←123
    Rem 123 ←123
    Rem 0 ←0
    Rem abc ←abc
    Rem abc ←abc
    Rem  ←(空白)
    Rem True ←True
End Sub

Sub Nzテスト_Access()
    Debug.Print Nz(Null) & " ←(Empty)"
    Debug.Print IsEmpty(Nz(Null)) & " ←True"
    Debug.Print Nz(Null, 0) & " ←0"
    Debug.Print Nz(Null, "ﾇﾙ") & " ←ﾇﾙ"
    Debug.Print Nz(123) & " ←123"
    Debug.Print Nz(123, 0) & " ←123"
    Debug.Print Nz(Null, 0) & " ←0"
    Debug.Print Nz("abc") & " ←abc"
    Debug.Print Nz("abc", "") & " ←abc"
    Debug.Print Nz(Null, "") & " ←(空白)"
    Debug.Print IIf(Nz(Null, "") = "", True, False) & " ←True"
End Sub

ENz関数の説明

Function ENz(tgtVal As Variant, Optional VIN As Variant = Empty) As Variant
　　ENz = IIf(IsNull(tgtVal), VIN, tgtVal)
End Function

Nz関数の構文は
Nz(Value, ValueIfNull)

Value（値）

• 必須
• データ型がVariant（As Variant）

名前をtgtValにし、「tgtVal As Variant」

ValueIfNull（ValueがNullだった場合の値）

• 省略可能（Optional）
• データ型がVariant（As Variant）
• 省略するとEmptyが返る（規定値を設定）（= Empty）

名前をVINにし、「Optional VIN As Variant = Empty」

Nz関数の戻り値はVariantなので、ENzの戻り値もVariantになるように最後に「As Variant」をつける。

動き

ENz = IIf(IsNull(tgtVal), VIN, tgtVal)

まず、IsNull(tgtVal)でtgtValがNullか否かを判定。
Null（IsNull(tgtVal)=True）ならVIN（Nullの場合に返す値）を返し、NullでないならtgtVal（値）を返す。
このIIfの処理を関数名（ENz）に代入することで、呼び出し元（ENzを使ったところ）に値を返せる。