概要
VBAで以下の画像のような、セルのエラーの種類を判定する方法のメモです。
以下の記事に近い内容です。
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)
このエラー値とセルの値を比較することで、そのセルが特定のエラーかどうか、を判定出来ます。
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
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