【Excel VBA】 セルの数式結果がエラーなっているかを判定して、処理を分岐する方法

はじめに

こんにちは。IMMRです。

プログラム実行中にエラーが発生するとプログラムが一時中断となり、

全てのデータに対して行うことが出来なくなってしまいます。

例えば、セルの中の数式結果を参照して、その値を判別する処理の場合、

数式結果がエラーだとプログラムが止まってしまい、

まだ、データが残っていたとしても処理中断になってしまいます。

そのため、エラーが発生した時のことを考慮してエラー対策が必要です。

今まで、下記のようにいくつかのエラー対処法をいくつか説明してきました。

・【Excel VBA】エラーが発生した時だけ、別の処理を行う方法

・【Excel VBA】エラーが発生しても無視して処理を中断しない方法

今回は、これまで説明したエラー対処方法とは違う「セルの数式結果がエラーなっているかを判定して、処理を分岐する方法」を

説明します。

1.参照するセルの数式結果がエラーだと処理が中断してしまう

例えば、下記ブックのデータがあったとします。

図1　ブック(サンプル実行前)

上記データのE列(売上)の値を参照し、値が20000より大きかった場合は

A列に「ノルマ達成」という文字を記載したいとします。

ちなみに売上値が記載してあるセルには、数値が記載されているのではなく「=C4*D4」のように関数式が記載されています。

上記のような処理をしたい場合のサンプルコードです。

Sub test31_smp()

    Dim i       As Integer
    For i = 4 To 13
        If (Cells(i, 5) > 20000) Then
            Cells(i, 1) = "ノルマ達成"
        End If
    Next

End Sub

以下は実行結果です。

予定通り20000より大きい場合は「ノルマ達成」と記載されています。

図2　ブック(サンプル実行後)

では、下記のブックの場合はどうでしょうか。

図3　ブック(エラーサンプル実行前)

この場合、E列の結果がエラーになっております。 理由は、個数の値が80ではなく、8O(オー)となっているからです。

そして、このまま実行すると

下記のようにエラーになります。

図4　メッセージ(エラーサンプル実行後)

ブックの方も確認してみるとエラーが起きる前までは、正常に処理しましたが

エラーのところで止まっています。

図5　ブック(エラーサンプル実行後)

このようにエラーが起きるとその時点で処理が終了してしまい、

残りのデータは、手つかずで終わってしまいます。

2.数式結果がエラーかどうか判定する

セル内に関数式等のエラーが入っているかどうかは、IsError関数で確認できます。

        If IsError(対象のセル) Then
　　　　　　　エラーだった時に行う処理
        End If

上記のように対象のセルがエラーだったら「エラーだった時に行う処理」が行われて

エラーが無ければ何も行われないことになります。

下記のようIf Elseにすると

エラーだったら「エラーだった時に行う処理」を行い、

エラーではない場合は「通常の処理」を行うこともできます。

        If IsError(対象のセル) Then
            エラーだった時に行う処理
        Else
            通常の処理
        End If

3.数式結果がエラーかどうか判定するサンプルプログラム

一つ一つのセルを参照し、その中に関数式が入っているとします。

その参照したセルの関数式の結果がエラーの場合は、エラーとしての処理

その参照したセルの関数式の結果がエラーではなく、ある規定値より大きい場合は、正常処理をしたい場合は

以下のサンプルコードを使用することが出来ます。

サンプルコード

Sub test31()
    Dim i       As Integer
    For i = 4 To 13
        If IsError(Cells(i, 5)) Then
                Cells(i, 1) = "エラー"
                Cells(i, 1).Font.Color = RGB(255, 0, 0)
        Else
            If Cells(i, 5) > 20000 Then Cells(i, 1) = "ノルマ達成"
        End If
    Next
End Sub

これは、図1や図3のようなブックデータを使用した時のサンプルコードです。

E列には関数が入っています。E列のセルを参照して、

エラーならA列に"エラー"を記載して、Font.Colorより赤文字にします。

エラーでなく、かつ20000より大きい場合はA列に"ノルマ達成"を記載します。

4.数式結果がエラーかどうか判定するサンプルプログラムの実行

では、セルの数式結果がエラーなっているかを判定して、処理を分岐するするか。

エラーだとしても処理が中断しないかを確認します。

上記サンプルコードを実行していきます。

使用するのは図3で説明した関数式がエラーになっているブックです。

図6　関数式がエラーのブック(実行前)

では実際に実行していきます。

実行の結果は下記になります。

図7　関数式がエラーのブック(実行後)

関数式がエラーになっている箇所は、A列に「エラー」という文字が記載されています。

また、エラーでなく、かつ20000より大きい場合はA列に「ノルマ達成」という文字がされました。

関数式でエラーになっている箇所でも処理を止めずに行うことが出来ました。