Vba 型が一致しません（エラー13）のセルを特定する方法を教えてください

いつもお世話になります

今、沢山の数値を扱うプログラムを社内で提供していますが、たまにエラー13が出て
その場所特定に呼ばれます。
プログラムにパスワードが掛かっているので、解除して再計算で場所を特定します

そこでOnError Gotoからエラーメッセージを出そうと考えましたが
その場所が特定できればと思い相談しました

エラーの箇所は単純で
Dim ABC as Double の変数に代入するセルに文字が入っているという時です

もし、そのセルの特定方法が分かれば教えてください
以上、宜しくお願いいたします

No.5 ベストアンサー 回答者： Qchan1962 回答日時： 2024/11/29 16:48

Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

なるほど、関数エラーとは値型の不一致なので違いますね

配布をするような限定的な処理のVBAの場合、メイン処理実行前にエラーチェックをした方が良いかな
今更改修できないかもですが・・・
そもそも数値以外入力できないようにする方法（値の取得方法が分からないので割愛）

入力時のエラーチェック　
Changeイベントを使った簡単な例（CDbl関数は適時使用前提、範囲は適当）
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:D10")) Is Nothing Then
With Target
If Not IsNumeric(.Value) Then
MsgBox .Address(0, 0) & "の値 " & .Value & "は適切ではありません", 48, "入力エラー"
End If
End With
End If
End Sub
チェック範囲が多い場合はIntersect範囲を分けるかUnionなどを使う

メイン処理実行前にエラーチェック
Sub CHK_v()
Dim rng As Range
Dim c As Range
Dim strMsg As String
Set rng = Range("B2:D8, B11:B13, E5")
Set rng = Union(rng, Range("F2:F8, Y11, AA5"))
For Each c In rng
If Not IsNumeric(c.Value) Then
strMsg = strMsg & c.Address(0, 0) & "の値 " & c.Value & vbCrLf
End If
Next
If strMsg <> "" Then
MsgBox strMsg & "は数値で入力してください", 48, "入力エラー"
End If
End Sub
ユーザーフォーム、コントロールからなら適切なイベントトリガーで・・

型指定の変数に値を入れているのですから対象範囲は分かっていると思うのですがいかがでしょう・・

この回答へのお礼

早速返信ありがとうございます

ちょっと検討しますね
これからもよろしくお願いいたします

お礼日時：2024/11/29 16:56

No.6 回答者： shut0325 回答日時： 2024/11/29 16:56

shut0325です。

お礼拝見しました。

＞今は使用者にデータのエラー箇所を知らせたいだけ

ですよね、、なんでそんな難しくするんだろうって感じですが。

エラーが出る原因が数値であるところに文字列が入っている場合と特定できているのだから、セルの値が文字列だったら、そのセルが該当のエラーとなったセル＝それ見つけたら表示、、だけでいいかと。

当然、それに色付けたり、別のセルにしたりの処理も簡単なIf文やらFor文の中に盛り込むだけで済ませればいいかと。「知らせる」が具体的にはどんなことなのかもよくわかりませんが。

この回答へのお礼

ご意見、ありがとうございます

何本ものプログラムの開発と解析作業をしているので
効率の良い方法を考えようとしましたがうまく行きませんでした

休日返上で少し改良します
ありがとうございました

お礼日時：2024/11/29 17:59

No.4 回答者： naktak 回答日時： 2024/11/29 16:46

> 　　Dim rng As Range

> 　　Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
> 　　rng.Select

えっと、ユーザーはセルに任意の値を入力できているわけですから、
Excelが受け付けている以上、Excelはエラーだなんで返してきません。

あなたが求めているのはExcelのエラーではなく業務のエラーですよね。
システムの操作として誤った値が入力されている時、誤った入力箇所にセルを遷移することで、ユーザーに誤りを分からせたい、ということですよね。

例えば数値しか受け付けないのなら
If Not IsNumeric(対象セル.Value) Then
　対象セル.Select
　Exit Sub
End If
Dim lValue As Decimal
lValue = CDec(対象セル.Value)

みたくしたらいいのでは？
もしくはExcelの入力規則によって入力可能な値を制限するとか。

この回答へのお礼

早速の返信ありがとうございます

そうですね、ちょっと検討します
これからもよろしくお願いいたします

お礼日時：2024/11/29 17:00

No.3 回答者： tatsumaru77 回答日時： 2024/11/29 15:07

以下のようにされてはいかがでしょうか。

on errorのとび先に
Stop
Resume
としておきます。
エラー発生時、Stopで止まりますので、
F8キーで1ステップずつ進めます。
Resumeの次に、元の位置に戻ります。
そこがエラー発生個所です。
下記は、A1～A10のセル中に、数字でない文字があったとき、
そこで停止するサンプルです。
--------------------------------------------------
Public Sub エラー箇所特定()
Dim ABC As Double
Dim i As Long
On Error GoTo ERROR99
For i = 1 To 10
ABC = Cells(i, 1).Value
Next
MsgBox ("エラーなし")
Exit Sub
ERROR99:
Stop
Resume
End Sub

この回答へのお礼

早速ありがとうございます
ちょっとテストしてみました
勉強になりました、ありがとうございます

でも今は、質問の仕方が悪かったようです
今は使用者にデータのエラー箇所を知らせたいだけなので
下記のサンプルのようなものが欲しかったのですが

　　Dim rng As Range
　　Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
　　rng.Select

ただし、xlCellTypeFormulas は数式によるエラーみたいで
Type UnMatchの時は何かな？っと思って聞きました

以上、宜しくお願い致します

お礼日時：2024/11/29 16:17

No.2 回答者： naktak 回答日時： 2024/11/29 14:57

え、変数ABCにセルの値を入れてるところを探せばいいんじゃないんですか？

カレントプロジェクトをターゲットにして検索すれば済むと思うんですけど。

見つかったら、そこに設定しているセルを追いかけるか、
ブレークポイントを貼って、エラーになる操作（セルに文字を入れて何かを実行する）をすればよいかと思います。

それかそもそも文字が入っている状態では実行してはならないのなら、実行直後にバリデーション処理を追加すれば、追っかける必要なくなりますね。

この回答へのお礼

早速ありがとうございます

ちょっと、質問の仕方が悪かったようです
今は使用者にデータのエラー箇所を知らせたいだけなので
下記のサンプルのようなものが欲しかったのですが

　　Dim rng As Range
　　Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
　　rng.Select

ただし、xlCellTypeFormulas は数式によるエラーみたいで
Type UnMatchの時は何かな？っと思って聞きました

以上、宜しくお願い致します

お礼日時：2024/11/29 16:16

No.1 回答者： shut0325 回答日時： 2024/11/29 14:54

特定は簡単ですけど、文字列だった場合はどのように処理するんですか？

判別した上で文字列じゃなかったら、ABCに値を代入すればいいかと。あとは文字列だった場合はどうするか　です。

そのときに単にセルを特定したいのだったらメッセージボックスで、そのセルを表示すればいいです。

まずはそこをやるというならば
IF
（セルの値が文字列化の判別式：文字列ならFalse）

Then
（ABCにセルの値を代入）

Else
Msgbox（セルの行列：見やすいように））

End　If

としておいて、文字列の場合の処理があるなら、MsgBoxをコメントアウトして、やりたい処理を入れるとよいと思います。

この回答へのお礼

早速ありがとうございます

ちょっと、質問の仕方が悪かったようです
今は使用者にデータのエラー箇所を知らせたいだけなので
下記のサンプルのようなものが欲しかったのですが

　　Dim rng As Range
　　Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
　　rng.Select

ただし、xlCellTypeFormulas は数式によるエラーみたいで
Type UnMatchの時は何かな？っと思って聞きました

以上、宜しくお願い致します

お礼日時：2024/11/29 16:15