VBAで配列内のエラー値の取得

質問

エクセルのシートのセル範囲を配列に取り込み処理（他のでーたとの比較）をしようとしています。
ところがデータにエラー値があると型が一致しないというエラーになり失敗します。
非常に単純化して書くとこんな場合です。
myW(1, 2)はエラー値です。
Sub test001()
　　Dim myW
　　myW = Selection.Value
　　MsgBox myW(1, 2)
End Sub

しょうがないのでエラー値だった場合は文字列にしてしようと試行錯誤の結果、functionを使って下記のようになりました。
Sub test002()
　　Dim myW
　　myW = Selection.Value
　　If IsError(myW(1, 2)) Then
　　　　MsgBox Emsg(myW(1, 2))
　　Else
　　　　MsgBox myW(1, 2)
　　End If
End Sub

Function Emsg(ByVal x As Variant) As String
　　Select Case x
　　　　Case CVErr(xlErrDiv0): Emsg = "#DIV/0!"
　　　　Case CVErr(xlErrNA): Emsg = "#N/A"
　　　　Case CVErr(xlErrName): Emsg = "#NAME?"
　　　　Case CVErr(xlErrNull): Emsg = "#NULL!"
　　　　Case CVErr(xlErrNum): Emsg = "#NUM!"
　　　　Case CVErr(xlErrRef): Emsg = "#REF!"
　　　　Case CVErr(xlErrValue): Emsg = "#VALUE!"
　　End Select
End Function

現在、これで動いてはおりますが、。もっと簡単にエラー値を文字列として取得できる方法はないでしょうか？たとえばセルの値ならエラー値でも
Sub test0001()
　　MsgBox Cells(1, 2).Text
End Sub
で取得できますが、いったん配列に入れてしまうとできません。

回答No.5

HohoPapa · ベストアンサー

>配列に取り込んでいるので.text では文字列化できないのです。
問題としているところがやっと理解できました。<m(__)m>

そうですね、
セルの値（CellやRangeオブジェクト）ならTextプロパティがありますが
むろん、配列変数にはTextプロパティはありまぜん。
エラーかどうかの判定と
"#DIV/0!"や"#N/A"といった人間の理解できる文字列を得るには
お使いの関数Emsgのような作りこみが避けられません。
更に、エラー値の取りうる値は
列挙されたものだけとは限りませんので厄介です。

繰り返しになりますが
エラー処理はより上流で行うのが鉄則です（と思っています）ので、
私だったら
'MyV = buf(0).Value
といったコードで配列変数に格納するのではなく
地道に次のようなコードを使います。

Sub testa()

　Dim buf(1) As Range
　Dim MyV
　Dim CntR As Long
　Dim CntC As Long

　Set buf(0) = Selection
　MyV = buf(0)

　For CntR = 1 To UBound(MyV, 1)
　　For CntC = 1 To UBound(MyV, 2)
　　　If IsError(buf(0).Cells(CntR, CntC)) = True Then
　　　　MyV(CntR, CntC) = buf(0).Cells(CntR, CntC).Text
　　　Else
　　　　MyV(CntR, CntC) = buf(0).Cells(CntR, CntC).Value
　　　End If
　　Next CntC
　Next CntR

End Sub

お礼

何度もありがとうございます。
MyV = buf(0).Value　と
MyV = buf(0)　の違いは配列の要素が値かRangeかの違いと考えてよいですか？
そして、その要素がエラーであればTEXTで、そうでなければVALUEで置き換えているということですね？

回答No.8

HohoPapa

>MyV = buf(0).Value　と
>MyV = buf(0)　の違いは
>配列の要素が値かRangeかの違いと考えてよいですか？
はい。

提示されたコードを流用したため
Dim MyV
と記述しました。
as以降が省略されていますので、
定義上は、Variantです。

Set buf(0) = Selection
MyV = buf(0)
これは言うまでもなく
Dim buf(1) As Range
Set buf(0) = Selection
ですから
buf(0)の中身は、Rangeですし、
MyV = buf(0)
の場合の左辺の中身もRangeです。

別な質問に対する返答でも触れたんですが
Variantの多様や
Dim MyV
といった記述は、コードを読みにくくするだけでなく
バグの温床になります。

他方、
>MyV = buf(0).Value
とした場合の
左辺の中身は配列です。

お礼

何度も何度もありがとうございました。

回答No.7

m3_maki

横から失礼します。

> いったん配列に入れてしまうとできません。

ということであれば、もう１度適当なセルに書き込んであげるとか。

Sub test0001()
　　Range("Z1") = myW(1, 2))
　　MsgBox Range("Z1").Text
　　Range("Z1") = ""
End Sub

お礼

ありがとうございます。
それも一つの解決策ですね。

回答No.6

HohoPapa

もし、
'MyV = buf(0).Value
が譲れないのであれば、

For i = LBound(myV, 1) To UBound(myV, 1)
　　　　For n = LBound(myV, 2) To UBound(myV, 2)
　　　　　　If IsError(myV(i, n)) Then
　　　　　　　　myV(i, n) = Emsg(myV(i, n))
この時にEmsg関数を使うのではなく
myV(i, n)に格納したセルのtextプロパティを取得する対応でも
いいんじゃないかと思います。

お礼

ありがとうございました。

回答No.4

HohoPapa

とれとも、
Sub test005()
　　MsgBox Selection(1, 2).Text
End Sub
でしょうか。

お礼

ありがとうございました。

回答No.3

HohoPapa

やりたいことがよくわかりませんが
次のようなコードなら期待の内容ですか？

Sub test002()
　Dim myW As Range
　Set myW = Selection
　If IsError(myW) = True Then
　　 MsgBox (myW.Text)
　End If
End Sub

'または

Sub test003()
　Dim MyRange As Range
　Dim TgtCell As Range

　Set MyRange = Range("A1:C4")

　For Each TgtCell In MyRange
　　If IsError(TgtCell) = True Then
　　　 MsgBox (TgtCell.Text)
　　End If
　Next
End Sub

お礼

何度もありがとうございます。
ありがとうございます。
質問文にも書いておりますが、.text でエラー値を文字列化できるのはRangeの値だららですよね？
今回は対象が大きいため、配列に取り込んでいるので.text では文字列化できないのです。
だから

Function Emsg(ByVal x As Variant) As String
　　Select Case x
　　　　Case CVErr(xlErrDiv0): Emsg = "#DIV/0!"
　　　　Case CVErr(xlErrNA): Emsg = "#N/A"
　　　　Case CVErr(xlErrName): Emsg = "#NAME?"
　　　　Case CVErr(xlErrNull): Emsg = "#NULL!"
　　　　Case CVErr(xlErrNum): Emsg = "#NUM!"
　　　　Case CVErr(xlErrRef): Emsg = "#REF!"
　　　　Case CVErr(xlErrValue): Emsg = "#VALUE!"
　　End Select
End Function

で文字列化しているのですが、これをもっと簡単にできないかと思って質問しています。

補足

現在行っているコードです。

Sub 選択範囲データ比較()
　　Dim myV, myW
　　Dim buf(1) As Range
　　Dim i As Long, n As Long, j As Long, k As Long
　　Dim ws(1) As Worksheet

　　Set ws(0) = ActiveSheet
　　Set buf(0) = Selection
　　myV = buf(0).Value
　　If MsgBox(buf(0).Address & " と開いている他のBOOKを参照しますか？" _
　　& vbCrLf & "" _
　　& vbCrLf & "他のBOOKなら「はい」" _
　　& vbCrLf & "このBOOKなら「いいえ」", vbYesNo + vbQuestion) = vbYes Then
　　　　Application.Dialogs(xlDialogActivate).Show
　　　　If ws(0).Parent.Name = ActiveSheet.Parent.Name Then
　　　　　　If MsgBox("他BOOKではないですがよろしいですか？", vbOKCancel + vbQuestion) = vbCancel Then
　　　　　　　　Exit Sub
　　　　　　End If
　　　　End If
　　End If

　　On Error Resume Next
　　Set buf(1) = Application.InputBox(Prompt:="セルを選択してください。", Type:=8)
　　On Error GoTo 0

　　If buf(1) Is Nothing Then
　　　　Exit Sub
　　End If

　　Set ws(1) = ActiveSheet
　　myW = buf(1).Value

　　If UBound(myV, 1) <> UBound(myW, 1) Then
　　　　MsgBox "行数が異なります。", vbCritical
　　　　Exit Sub
　　ElseIf UBound(myV, 2) <> UBound(myW, 2) Then
　　　　MsgBox "列数が異なります。", vbCritical
　　　　Exit Sub
　　End If

　　For i = LBound(myV, 1) To UBound(myV, 1)
　　　　For n = LBound(myV, 2) To UBound(myV, 2)
　　　　　　If IsError(myV(i, n)) Then
　　　　　　　　myV(i, n) = Emsg(myV(i, n))
　　　　　　End If
　　　　　　If IsError(myW(i, n)) Then
　　　　　　　　myW(i, n) = Emsg(myW(i, n))
　　　　　　End If
　　　　　　If myV(i, n) <> myW(i, n) Then
　　　　　　　　If IsNumeric(myV(i, n)) And IsNumeric(myW(i, n)) Then
　　　　　　　　　　If Application.Round(myV(i, n), 13) <> Application.Round(myW(i, n), 13) Then
　　　　　　　　　　　　j = j + 1
　　　　　　　　　　Else
　　　　　　　　　　　　k = k + 1
　　　　　　　　　　End If
　　　　　　　　Else
　　　　　　　　　　j = j + 1
　　　　　　　　End If
　　　　　　End If
　　　　Next n
　　Next i
　　If j > 0 Then
　　　　MsgBox j & " 個、値の相違があります。" _
　　　　& vbCrLf & "" & k & " 個、浮動小数点演算誤差があります。", vbCritical
　　ElseIf k > 0 Then
　　　　MsgBox k & " 個、浮動小数点演算誤差があります。", vbExclamation
　　Else
　　　　MsgBox ws(0).Name & " vs " & ws(1).Name & "" _
　　　　& vbCrLf & "同一データです。" _
　　　　& vbCrLf & "" _
　　　　& vbCrLf & "対象行数：" & UBound(myV, 1) & "" _
　　　　& vbCrLf & "対象列数：" & UBound(myV, 2) & ""
　　End If
End Sub

Function Emsg(ByVal x As Variant) As String
　　Select Case x
　　　　Case CVErr(xlErrDiv0): Emsg = "#DIV/0!"
　　　　Case CVErr(xlErrNA): Emsg = "#N/A"
　　　　Case CVErr(xlErrName): Emsg = "#NAME?"
　　　　Case CVErr(xlErrNull): Emsg = "#NULL!"
　　　　Case CVErr(xlErrNum): Emsg = "#NUM!"
　　　　Case CVErr(xlErrRef): Emsg = "#REF!"
　　　　Case CVErr(xlErrValue): Emsg = "#VALUE!"
　　End Select
End Function

回答No.2

HohoPapa

そもそもシート上に埋めた計算式がエラーとなる可能性があるのなら、
=IFERROR　といった関数を使い、
セルにエラー値を埋めない対応を行っておくのが一般的と思います。
つまり、エラー処理はより上流で行っておくべきものと思います。

そうではなく、
エラー値の埋まったセルを対象にせざるを得ない事情があり、かつ、
マクロがエラーを起こし、中断してしまうことが問題であれば、
それは、
On Errorステートメントを使ったエラー処理を行っていないからです。
http://officetanaka.net/excel/vba/tips/tips104.htm
あたりがわかりやすいと思います。

このエラー処理を組み込むことが事情があって難しいのであれば、
私なら、
対象セル全数の中にエラー値があるかどうかを事前にチェックし、
あれば補正するとか、メッセージを表示して抜ける対応を行います。
つまり、より上流でチェックします。

言い換えれば、
エラー処理を行わないまま下流にきているので、
苦戦し、作りこまなければならない事態を招いているように
読み取れます。

お礼

>エラー値の埋まったセルを対象にせざるを得ない事情があり

はい、その通りです。やりたいことは二つの大きなセル範囲の差異を調べることです。
しかも、エラー値の違いも判定しなければいけないのでOn Errorステートメントで回避するわけにはいかないのです。
ありがとうございました。

回答No.1

kkkkkm

単純に文字列になればいのであれば
CStr(myW(1, 2))
でエラー値の文字列になります…エラー 2042とかのですが…。

お礼

エラーナンバーなんですね。
ありがとうございます。

回答No.1

skp026

以下がご要望の記事のように思います。
https://qiita.com/nukie_53/items/6c2d9d4699149bebe863

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

ご要望と違いましたらごめんなさい。

お礼

ありがとうございます。
でも使い方がよくわかりませんでした。すみません。