サイトアイコン 独学エクセル塾

【マクロVBAのエラー対策】エラーが発生したら指定行まで移動!終了までできるエラー処理方法について

エクセルのマクロVBAのプログラムを作ったことがある方は誰もが経験がある
エラーですが、皆さんも悩んでいるのではないでしょうか。

プログラムを作った人は発生したエラーについて理解・把握できますが
作ってない方はエラーが発生したらすぐに理解するのは難しいです。

さらにVBAを触ったことがない人がシステムを利用してエラーが出ると
何を押していいかわかりません。

そのためにもプログラムを作成する方はエラーに対応したプログラムを
作成する能力を求められます。

ぜひ、エラーに強いプログラムを作成できるようにしてみてください。




■マクロVBAで発生するエラーとは

まず発生するエラーについて見てみましょう。
下図をご覧ください。

このような異常はプログラムを作ったことがある方は見たことがあるのではないでしょうか。
ちなみに終了を押せばプログラムは強制終了します。

デバッグを押せばエラーの原因のプログラムの位置で止まってくれます
VBAを知らない方はデバッグを押すと、みたことのない画面が出るため困ってしまいます。

ヘルプはマイクロソフト公式のヘルプに飛んでくれます

今回の目的はこの画面が出ないようにプログラミングしていきます。

■エラーの対応方法

今回は下図のようなシステムを見本で作成しました。

システムとして、身長・体重を打ち込んで更新を押すと
8行以降にデータを蓄積していくシートを用意しました。その際にBMIも算出します。

プログラムも見本で載せておきます

Sub データ集計()

Dim EndRow As Long
Dim Height As Long ‘身長
Dim Weight As Long ‘体重

EndRow = Cells(7, 2).End(xlDown).Row + 1

Height = Cells(3, 2)
Weight = Cells(3, 3)

Cells(EndRow, 2) = Height
Cells(EndRow, 3) = Weight
Cells(EndRow, 4) = Weight / (Height / 100) ^ 2

Range(“B3:C3”).ClearContents

End Sub

では、このプログラムを元に説明していきます。

・①対象範囲内でエラーが起きたら指定位置へ飛ぶ

まず、エラー発生時に指定位置に飛ぶ方法について紹介します。
この方法はエラーの根本的な解決方法にはなってませんが
エラー画面が発生しない1番手っ取り早い方法です。

プログラム構成はこのようになります。

On Error Goto 任意ラベル
  ・
  ・
  ・
通常プログラム
  ・
  ・
  ・
Exit Sub

任意ラベル

エラー時の処理

End Sub

つまり

On Error Goto 任意ラベル」から「任意ラベル:」の範囲内で
異常があった場合、エラー時の処理まで飛んでくれます。

では先ほどのプログラムに織り込んでみましょう。

Sub データ集計①織り込み()

Dim EndRow As Long
Dim Height As Long ‘身長
Dim Weight As Long ‘体重

On Error GoTo エラー1

EndRow = Cells(7, 2).End(xlDown).Row + 1

Height = Cells(3, 2)
Weight = Cells(3, 3)

Cells(EndRow, 2) = Height
Cells(EndRow, 3) = Weight
Cells(EndRow, 4) = Weight / (Height / 100) ^ 2

Range(“B3:C3”).ClearContents

Exit Sub

エラー1:

MsgBox (“誤った数値が入力されています”)

End Sub

図解するとこのようになります。

では実際にエラーが出るように更新してみましょう。

身長に間違えて「A」を打ち込んだとします。
通常は身長を「Cells(EndRow, 2) = Height」で変数「Height」に格納するのですが
変数「Height」の型は「Long」なので文字は格納できません

なので通常は下図のような異常が出ます。

では改善したプログラムを走らせてみます。
結果はこのようになります。

エラー処理にプログラムが飛んで、メッセージボックスの
「誤った数値が入力されています」が表示されました。
この後は「OK」を押せばプログラムは終わってくれます

VBAのエラーを表示させたくない場合はプログラムの先頭に「On Error GoTo 任意ラベル
最後に「任意ラベル:」を置いて
そのあとに「MsgBox (“エラーになりました。見直してください”)」を入れれば
とりあえずVBAの異常は無くなり、メッセージでの出力にすることができました。

・②IFを使用して異常値を検出して指定位置に飛ぶ

今度はIFを使用して異常値を検出し、異常処理行まで飛んでみましょう
この方法は先ほどの型が違うなどのエラーは適応外ですが
エラーの根本を検出できます。

では先ほどの見本を元に進めていきます。

「B3」の身長を見てみましょう。
間違えて「0」を余分につけて「1720」になっています。

このまま更新ボタンを押すとそのまま計算・集計してしまい、正確な計算ができません。
ではIFを使用してエラーを防止してみましょう。

条件として身長の範囲を「120~200」までとします。

ではIFを使用して120~200以外は別の処理を行いたいと思います。
プログラムはこのようになります。

If Height < 120 Or Height > 200 Then
MsgBox (“身長は120から200までで入力してください”)
Exit Sub
End If

Heightは身長データを格納している変数とします。
IFを使い、Heightが120~200の範囲以外だと 「身長は120から200までで入力してください

とメッセージボックスを表示してExit Subでプログラムを終わらせます。
IFの詳しい使用方法は下記リンクを参照してみてください。☟

【IF関数】エクセルで○○だったら△△を表示する~条件分岐を身に着ける~ ► 独学エクセル塾 (dokugakuexcel.com)

ではプログラムに組み込んでみましょう。

Sub データ集計②織り込み()

Dim EndRow As Long
Dim Height As Long ‘身長
Dim Weight As Long ‘体重

EndRow = Cells(7, 2).End(xlDown).Row + 1

Height = Cells(3, 2)
Weight = Cells(3, 3)

If Height < 120 Or Height > 200 Then
MsgBox (“身長は120から200までで入力してください”)
Exit Sub
End If

Cells(EndRow, 2) = Height
Cells(EndRow, 3) = Weight
Cells(EndRow, 4) = Weight / (Height / 100) ^ 2

Range(“B3:C3”).ClearContents

End Sub

実行した結果はこのようになります。

IFで指定範囲外の数値を検出して

設定したメッセージでフィードバックが可能になりました。

・③入力規則で入力データを制限する

最後にVBAプログラムでの対処ではありませんが
エクセルの機能の入力規則を用いたエラーの回避を紹介します。

こちらはエラーの根本を解決する方法になっており、
エラーになる入力をさせない方法です。

では先ほどと同様に身長を「120~200」で制限してみましょう。
入力制限対象を選択して【データ】から【データの入力規則】を選択してください。

ここでは詳しく説明はしませんが
入力値の種類を「整数」にして最小値を「120」、最大限を「200」にします。
データを「次の値の間」にしました。

詳しい入力規則については下記リンクを参照してみてください☟

【リストの作成に便利】データの入力規則の設定・制限から解除まで ► 独学エクセル塾 (dokugakuexcel.com)

これにより入力は「120~200」の整数のみ可能になります。
「A」などといった文字も制限されるのでとても便利です。

⬛︎公式の説明

わかりやすいように説明したため公式と使用する語句が異なりますが
マイクロソフト公式の説明については下記のリンクを参照してください。☟

On Error ステートメント (VBA) | Microsoft Learn

■まとめ

いかがだったでしょうか。

今回は対策を①から③の
①対象範囲内でエラーが起きたら指定位置へ飛ぶ
②IFを使用して異常値を検出して指定位置に飛ぶ
③入力規則で入力データを制限する
で紹介しました。

①~③の知識は、すべてエラーを防止するためには大切なのですが
操作する人が複数であればあるほど自分の想定を超えた処理を行い、
エラーになってしまいます。
そのためにも①の方法は入れておけば汎用性は上がると思います

ぜひ、①から③を織り込んでエラーに強いプログラムを作成してみてください。

それでは次回の記事でお会いしましょう。

モバイルバージョンを終了