VBAの型エラーについて

Question

VBAにて条件一致したものを合計するコードを作成しております。
if分の中のsum = sum + Cells(i,11)で型エラーを起こしてしまいます。
debug.print VarType（Cells(2,11))で該当セルの型を表示させ変数sumの型を合わせました。
お知恵をお貸しください。

Sub test()
Dim i As Long 'forループ用変数
Dim sum As Variant '合計格納用
Dim lastCell As Long '右下終端セル格納用変数宣言（数値型）
Dim dataSheet As Excel.Worksheet
     Set dataSheet = ActiveWorkbook.Worksheets.Item("明細") '元データ(sheet「明細」）を変数に代入
     
lastCell = dataSheet.Cells(Rows.Count, 1).End(xlUp).Row '右下終端セル検索
Debug.Print lastCell

dataSheet.Activate
Debug.Print Cells(2, 11) 'セル内容確認
Debug.Print VarType(Cells(2, 11)) 'セル型確認

For i = 2 To lastCell
  If Cells(i, 18).Value = "ＩＹ" Then
    sum = sum + Cells(i, 11)
  End If
 Next

Debug.Print sum

End Sub

Comments

[ExcelVBAer]

型をちゃんと勉強してから出直してきなよ。 話はそれからだね。

[shinyakita]

はい。sumは数値を入れる変数なので、当初longにしてました。（Integerだと桁数が足りない ）しかしエラーがでるのでVariantに変更しました。それでもエラーがでるのでcellsの型を確認したところDoubleだったのでsumをDoubleに変更しました。それでもエラーが出るので調べたのですがわからずでした。

[ExcelVBAer]

数値の計算でデバッグが起きるという事は、セルの値が数値(Int,Long,Double等)じゃないってことですよ。つまり、数値に変換できない文字列か、数式等によるエラー(#N/A)ってこと。セルの値を文字列型の変数に格納して、IsNumericでTrueなら合算、Falseなら Stop するようにしてみれば？

[shinyakita]

ご教授ありがとうございます。元データの数値列の一部に文字列が存在していることを気が付かなく、想定外だった為エラーでした。初歩的な事ですね。上記のコードを参考にさせて頂きエラー時対処方法を検討します。

[ExcelVBAer]

補足ですが、当初･･･の流れでInteger → Long → Variant → Double と変わっていますが、小数点以下が含まれる時点でDouble型、Currency型等を検討すべきかと。そういう計算には詳しくないので、こちらを参考にhttps://thom.hateblo.jp/entry/2015/07/04/232318

[shinyakita]

参考ページありがとうございます。助かります。

Answer 1

for分コードを以下のように変更しイミディエイトウィンドウで確認したころ途中に文字列が存在している事がわかり
ました。
元データのエラー対象行を削除したところうまく動きました。
コードの問題ではなく、元データの精度の問題でした。

For i = 2 To lastCell
  If Cells(i, 18).Value = "ＩＹ" Then
    Debug.Print VarType(Cells(i, 11)) 'セル型確認
    Debug.Print i　'対象行
    sum = sum + Cells(i, 11)
  End If
 Nextコード

Comments

[退会済みユーザー]

上で私は「セル内容確認、セル型確認の位置はsum = sum + Cells(i, 11)の直前に持ってこないと意味がないですよ。」と言いましたが、それはデバッグ中の場合の話です。Debug.PrintはForの中で処理させると地味に重いのでプログラムが遅くなります。したがって完成形ではIf IsNumeric(Cells(i, 11).Value) Thenで機械的に検査した上で出力させるとかon error goto labelでエラーを検知した場合のみに出力するようにしたほうが良いですよ。（遅くなると言っても数千回以上回さないと体感出来ないかもしれませんが）

[shinyakita]

ありがとうございます。
はい。ものすごく時間がかかりました。
本番ではコメントアウトしておきます。
エラー処理としては、先に検査しエラーの原因となっている元データを修正する警告を出す等必要ですね。
元データに不具合がある事を想定しておりませんでした。

Answer 2

セル内容確認、セル型確認の位置はsum = sum + Cells(i, 11)の直前に持ってこないと意味がないですよ。

たとえば、こうすると異常箇所で停止するので分かりやすいです。

    For i = 2 To lastCell
        If Cells(i, 18).Value = "ＩＹ" Then
            If IsNumeric(Cells(i, 11).Value) Then
                Sum = Sum + Cells(i, 11).Value
            Else
                Cells(i, 11).Select
                MsgBox Cells(i, 11).Address & "のデータ型(" & TypeName(Cells(i, 11).Value) & ")が異常！"
                Exit Sub
            End If
        End If
    Next

Comments

[shinyakita]

ありがとうございます。
試してみます。

[shinyakita]

元データの途中で文字列になっているものが存在しました。
その為そこでエラーで止まっていました。
文字列になっている行をスキップするコードを考えないとなりません。

[sazi]

スキップじゃなくて、元を正さないと、別なところでも発生する可能性が残ったままですよ。

[退会済みユーザー]

元データが数値でないものを発見した時にどうするかを考えて欲しくて、上記コードでは問題箇所でプログラムが止まるようにしています。
本当にスルーして良いなら、Elseの中をコメントアウトしてしまえば良いですし、数値でない場合に元データの修正を（マクロ実行者に）指示するのであれば上記のまま残すと良いでしょう。解決した方法として記載された方法でも良いですが、エラーが出たらデバッグに入る想定でコーディングする場合は、Exit SubをStopに変えるとか、Debug.Assert IsNumeric(Cells(i, 11).Value)でマクロを一旦停止させるのが無難だと思います。

[shinyakita]

元データが原因でエラーが出るケースをすべて想定できないので実行者にアラートを出して元データを修正して頂く形がスマートでしょうか？
まさか数値列の中に文字列が含まれていたとは想定外でした。

[退会済みユーザー]

そうですね。機械的に判断出来ないようなデータの異常は利用者に修正を促すしかないと思います。あまりにエラー箇所が多くなる場合はその場でメッセージを出すのではなく、背景色を塗り潰してカウントアップしていき、処理が終わってから何件のエラーが有りましたので赤色のセルのデータを修正して下さい。と指示するのも手ですね。

[shinyakita]

ありがとうございます。
元データを実行前に異常チェックするプログラムを作ってアラート出すようにしてみようかと思います。
目的のプログラムよりもエラーチェックの方が手前がかかりそうですが、これも勉強ですね。