概要

VBAで以下の画像のような、セルのエラーの種類を判定する方法のメモです。

以下の記事に近い内容です。

Cell Error Values | Microsoft Docs

VBAのエラー値

セルのエラーはVBAでは「エラー値」と呼ばれる特殊な値で表現されます。

まずは、「エラー値」について特徴を確認しておきます。

作成方法

CVErr関数を使用することで作成できます。
関数の引数にはエラー番号となる数値を指定します。

Dim myErr As Variant
myErr = CVErr(1)

VBAでは「エラー値」の型は宣言できず、Variant変数のみに代入可能です。

暗黙的な型変換ができない

「エラー値」を扱う際に注意すべき点として、暗黙的な型変換ができないということがあります。

例えば以下のようにString型や数値型などには直接代入できません

Dim myErr As Variant
myErr = CVErr(1)

Dim errStr As String
errStr = myErr '型が一致しません。

Dim errNo As Long
errNo = myErr '型が一致しません。

CStr・CLngなどのデータ型変換関数を使用して、明示的に変換した場合のみ他の型として扱うことができます。

Dim myErr As Variant
myErr = CVErr(1)

Dim errStr As String
errStr = CStr(myErr) '"エラー 1"

Dim errNo As Long
errNo = CLng(myErr) '1

暗黙的な型変換として上記の例は比較的わかりやすいですが、見落としがちな問題として、エラー値と他の型で比較ができないという問題もあります。

Debug.Print CVErr(1) = 1 '型が一致しません。

判定方法

単純な「エラー値」かどうかの判定であれば、IsError関数や、VarType関数の結果がvbErrorかどうか、で判定できます。

セルのエラーの種類の判定

本題であるセルのエラーの種類の判定の方法です。

単純なエラーかどうかの判定であれば前述のIsError関数を使用すれば良いです。
種類も合わせて判定するには該当のエラー値を取得し比較をします。

該当のエラー値の取得方法ですが、ExcelのタイプライブラリにはExcel.XlCVErrorという列挙型が定義されています。
この列挙型の値をCVErr関数に渡すことで、該当するセルのエラー値を取得できます。

例えば#REF!のエラー値を取得したい場合は、以下のように記述します。

Dim refErr As Variant
refErr = CVErr(Excel.XlCVError.xlErrRef)

このエラー値とセルの値を比較することで、そのセルが特定のエラーかどうか、を判定出来ます。

#REF!エラー判定サンプル

Dim refErr As Variant
refErr = CVErr(Excel.XlCVError.xlErrRef)

'適当な#REF!を作成
ActiveCell.Formula = "=INDIRECT(""定義されていない名前"")"
Debug.Print ActiveCell.Value '->エラー 2023
Debug.Print ActiveCell.Text  '->#REF!

Debug.Print ActiveCell.Value = refErr '->True

例えば、「指定のセル範囲内に特定のエラー値が含まれているかどうか判定する関数」を作ると以下のようになります。

速度は気にしていないので、高速化したい場合はセルの値を配列に入れてください。

Public Function ContainsErrValue(iChkRng As Excel.Range, iErrType As Excel.XlCVError) As Boolean
    Dim errVal As Variant
    errVal = CVErr(iErrType)
    Dim r As Excel.Range
    For Each r In iChkRng
        If VBA.IsError(r.Value()) Then
            If r.Value() = errVal Then
                Let ContainsErrValue = True
                Exit Function
            End If
        End If
    Next r
End Function

使用例

Debug.Print ContainsErrValue(Selection, xlErrRef)

補足

ユーザー定義関数で使用する

VBAでワークシート用の関数を定義する場合は、XlCVErrorのエラー値を出力すると、より具体的なエラーを表示できます。

その他の判定方法

[Range].Textプロパティを参照すると、セルに表示されている文字列を取得できるため、擬似的に判定できます。
ただし、セルの幅が足りない場合には####となるため注意が必要です。

XlCVErrorの値

Excel2016では以下の14種類の値が定義されています。

前のバージョンより増えているようで、.NET用のプライマリ相互アセンブリ（Microsoft.Office.Interop.Excel.XlCVError）の定義より種類が多いです。

Sub SetErrorValues()
    Dim r As Range
    Set r = ActiveCell
    
    r.Value = cellErr(xlErrBlocked):        Set r = r.Offset(1)
    r.Value = cellErr(xlErrCalc):           Set r = r.Offset(1)
    r.Value = cellErr(xlErrConnect):        Set r = r.Offset(1)
    r.Value = cellErr(xlErrDiv0):           Set r = r.Offset(1)
    r.Value = cellErr(xlErrField):          Set r = r.Offset(1)
    r.Value = cellErr(xlErrGettingData):    Set r = r.Offset(1)
    r.Value = cellErr(xlErrNA):             Set r = r.Offset(1)
    r.Value = cellErr(xlErrName):           Set r = r.Offset(1)
    r.Value = cellErr(xlErrNull):           Set r = r.Offset(1)
    r.Value = cellErr(xlErrNum):            Set r = r.Offset(1)
    r.Value = cellErr(xlErrRef):            Set r = r.Offset(1)
    r.Value = cellErr(xlErrSpill):          Set r = r.Offset(1)
    r.Value = cellErr(xlErrUnknown):        Set r = r.Offset(1)
    r.Value = cellErr(xlErrValue):          Set r = r.Offset(1)
    
End Sub

Private Function cellErr(e As XlCVError) As Variant
    cellErr = CVErr(e)
End Function

Microsoft.Office.Interop.Excel.XlCVError

xlErrNull
xlErrDiv0
xlErrValue
xlErrRef
xlErrName
xlErrNum
xlErrNA

.NET Framework での扱い

.NET FrameworkのCOM操作で、エラーとなっているセルの値を取得するとInt32となります（普通の数値はDouble）。

また、直接XlCVErrorの値が取れるわけでは無いので注意が必要です（ビット演算すれば良さそう）。

参考

セルのエラー値 | Microsoft Docs
CVErr 関数 (Visual Basic for Applications) | Microsoft Docs
データ型変換関数 (VBA) | Microsoft Docs
IsError 関数 (Visual Basic for Applications) | Microsoft Docs
VarType 関数 (Visual Basic for Applications) | Microsoft Docs
XlCVError 列挙 (Excel) | Microsoft Docs
Range.Text プロパティ (Excel) | Microsoft Docs