EXCEL VBA エラーかどうがを判定します。数式エラー・数値表示によるエラー（###）列幅調整（ISERROR関数）
EXCEL VBA エラーかどうがを判定します。数式エラー・数値表示によるエラー（###）列幅調整（ISERROR関数）
今回説明するのは、セルに登録されている数式又は関数がエラー発生しているかどうかを判定する方法を説明いたします。ISERROR関数は、セルに登録されている数式や関数がエラーかどうかを判定する事ができます。値（セル）がエラーであれば【True】・正常であれば「False」を返します。ワークシート内に登録した数式が間違えて登録されている場合など、数式がエラー（＃DIV/！）等を探す時に便利だと思います。また、数値表示によエラー（＃＃＃：セル幅が狭い時に表示される）数値がセル内に入り切れない場合の対処方法、列を特定してセル幅を自動調整する方法も併せて説明いたします。それでは、サンプルプログラムを交えて順番に説明いたします。
●【ISERROR関数 (Excel)については、下記を参照して下さい】（Microsoft社 様）】
https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/iserror-function
● ISERROR関数を利用するには、下記の通りに設定を行います。
ISERROR（値）
※値（セル）がエラーの場合は、【True】を返します。正常の場合は、【False】を返します。
※戻り値は、ブール型 (Boolean)に返します。
【使用例】
● セル（A1）のエラーの原因をメッセージボックスに表示します。
【サンプルプログラム】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
' ' Sub IsError00() 'セル（A1）に登録されている数式がエラーかどうかを調べる。 Dim Ans As Boolean Ans = IsError(Range("A1").Value) 'セル（A1）が数式エラーか判定します。 If Ans = True Then 'エラー判定結果が、「True」の場合はエラーが発生。「False」の場合はエラーが発生していない。 MsgBox Range("A1").Formula & vbCrLf & "はエラーです。’エラー原因の数式をメッセージボックスに表示します。" End If End Sub ' |
【EXCELで発生するエラー表示の一覧 （一部）】
| エラー表示 | エラー原因 |
|---|---|
| #DIV/0 | 割り算を０で割っている。 |
| #N/A | 数式や関数に使用できる値がない。 |
| #VALUE! | 関数の引数や演算の形式に誤りがある。 |
| #REF! | 参照先のセルが見つからない。 |
| #NAME? | 関数名やセル範囲の名前に誤りがある。 |
| #NULL!！ | 指定した２つのセル範囲に共通部分が無い。 |
| #NUM! | 数値の指定に誤りがある。また、範囲を超えている。 |
EXCEL VBA エラーかどうかを判定します。各種エラーに対して原因をメッセージボックスに表示します。（ISERROR関数）
下記のサンプルプログラムは、A列に数式や関数が登録されています。登録した数式や関数の設定に誤りがあるため、数式エラー等が発生しています。今回のプログラムでは、そのエラー原因を順番にメッセージボックスへ表示するサンプルプログラムです。ISERROR関数を使う事で、様々なエラーについてもセルのエラー判定を行うので、エラー箇所を特定する場合は便利だと思います。
【プログラムの流れ】
① A列の最終行を取得します。
② A列の2行目から①で取得した最終行まで繰り返します。
③ 該当するセルの数式・関数にエラーかどうか判定します。
④ ③で該当するセルの数式・関数にエラーが発生している場合は、原因の数式をメッセージボックスに表示します。
⑤ A列の最終行まで繰り返します。②へ
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
' ' Sub IsError01() 'セルに登録されているセルの数式がエラーがどうかを調べる・ Dim Ans As Boolean Dim I, lRow As Long lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を把握します。 For I = 2 To lRow 'A列の最終行まで繰り返します。 Ans = IsError(Cells(I, "A").Value) '該当セルの数式がエラーか判定します。 If Ans = True Then '該当セルの判定結果が「True」エラーが発生している場合は、以下の処理を実行、 MsgBox Cells(I, "A").Formula & vbCrLf & "はエラーです。" 'エラー原因の数式をメッセージボックスに表示します。 End If Next I End Sub ' |
（画面クリックして拡大）
EXCEL VBA エラーかどうかを判定します。エラーに対して原因を別のセルに表示します。（ISERROR関数）
下記のサンプルプログラムは、D列に登録されている数式エラーがあるか判定します。判定の結果、誤りがある場合はF列にエラー原因を表示します。また、エラーが発生したD列セル（数式エラーが登録されている）は、エラー発生セルを分かりやすくするために、背景色が赤で塗りつぶされます。
【プログラムの流れ】
① D列の最終行を把握します。
② D列の背景色を白に戻す。
③ 数式エラーを表示するF列をクリアーする。（削除）
④ D列の最終行を取得します。
⑤ 当該セルの数式がエラーか判定します。
⑥ 判定結果に「False」数式エラーが無い場合は、F列に【問題無し」と表示します。
⑦ 判定結果に「True」数式エラーが有る場合は、F列に【数式エラー原因】を表示します。かつ、エラー原因のセルに背景色を赤に塗りつぶします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
' ' Sub IsError02() '数式エラーには背景色と隣のセルにエラー内容を表示する。 Dim Ans As Boolean Dim I, lRow As Long lRow = Cells(Rows.Count, "D").End(xlUp).Row 'D列の最終行を把握します。 Range("D2:D" & lRow).Interior.ColorIndex = 0 'D列の背景色を元にもどす。（白） Range("F2:F" & lRow).ClearContents 'エラー原因を表示するF列をクリアします。 For I = 2 To lRow 'D列の最終行まで繰り返します。 Ans = IsError(Cells(I, "D").Value) '該当セルの数式がエラーか判定します。 If Ans = False Then '該当セルの判定結果が「False」エラーが無い場合は、以下の処理を実行 Cells(I, "F") = "問題なし" Else Cells(I, "F") = "'" & Cells(I, "D").Formula & "はエラーです。" 'エラー原因の数式をF列に表示します。 Cells(I, "D").Interior.ColorIndex = 3 'エラー原因のセルの背景色を赤にします。 End If Next I End Sub ' |
（画面クリックして拡大）
EXCEL VBA セルの内容が正しいのに「###」が表示されるときの対処法・セル幅の自動調整(セル幅が狭い)
下記のサンプルプログラムは、関数・数値エラーが発生しているわけでは無いが、セルの幅が狭く数値が入り切れない場合に表示される「＃＃＃」をISERRORを使い、該当するセルの列幅を自動調整を行うサンプルプログラムです。
【プログラムの流れ】
① アクティブのワークシートのセル（A1～F1）まで順番に繰り返します。
② セル（A1⇒F1：まで）順番にセルがび数式エラーが発生しているか判定します。
③ 数式エラーが発生及び該当のセルの文字列の左側1文字目が”＃”か判定します。
④ 判定結果、”＃＃＃”になっているセル位置のセル幅を自動調整します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
' ' Sub IsError03() 'セルに###が表示されている列のセル幅を自動調整します。 Dim Rng As Range For Each Rng In Range("A1:F1") 'セルA１～F1まで繰り返します。 If Not IsError(Rng.Value) Then '該当のセルにエラーが発生しているか判定します。 If Rng.Value <> Rng.Text And Left(Rng.Text, 1) = "#" Then '該当のセルの左側が”＃”か判定します。 Rng.EntireColumn.AutoFit 'エラーが発生および”#”の表示せるに該当する列幅を自動調整する。 End If End If Next Rng End Sub ' |
（画面クリックして拡大）
EXCEL VBA ブック全体のワークシートに対して、セルの内容が正しいのに、セル幅が縮小され「###」表示されるときの対処法・セル幅の自動調整②
下記のサンプルプログラムは、サンプルプログラム③の応用になります。今回のプログラムではブック全体が対象となり全てのワークシートのセル「A1」を起点とする表の範囲に対して、セル幅の狭い「＃＃＃」と表示されている列に対してセル幅の自動調整を行います。
【プログラムの流れ】
① 全てのワークシートに対して繰り返します。
② エラーが発生してもプログラム実行を続行します。
③ 各ワークシートのセル「A1」を起点に表範囲を取得します。※この表の範囲が列幅の調整する範囲になります。
④ ワークシートを順番にアクティブにします。
⑤ 現在のアクティブのセル「A1」を起点に取得した表範囲を順番に繰り返します。
⑥ 表の範囲として取得したセル範囲を順番に繰り返します。
⑦ 該当のセルが数式エラーが発生しているか判定します。かつ、該当のセルの左側１文字目が”＃”になっているか、判定します。
⑧ ⑦の２つの条件が一致していれは、その列番号をコレクションとして登録します。（一意の番号として列番号を管理します。）
⑨ 表の範囲として取得した最後のセルまで繰り返します。⑤へ
⑩ コレクションに登録された列番号を数分繰り返します。
⑪ 縮小表示された列番号を指定して、列幅を自動調整します。
⑫ 最後のワークシートまで繰り返します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
' ' Sub IsError05() '全てのワークシートの表を対象にのセルに###が表示されている列のセル幅を自動調整します。 Dim Ws As Worksheet Dim ColTemp As New Collection Dim Rng, HaniRng As Range Dim I As Long For Each Ws In Worksheets '全てのワークシートを繰り返します。 On Error Resume Next 'エラーが発生しても続行する。 Set HaniRng = Ws.Range("A1").CurrentRegion '各ワークシートのセル「A1」を起点に表範囲を取得します。 Ws.Activate 'ワークシートを順番にアクティブにします。 For Each Rng In HaniRng 'セル「A1」を起点に取得した表範囲を順番に繰り返します。 If Not IsError(Rng.Value) Then '該当のセルにエラーが発生しているか判定します。 If Rng.Value <> Rng.Text And Left(Rng.Text, 1) = "#" Then ' セルに"###" ColTemp.Add Rng.Column, "Key" & Rng.Column 'コレクションに登録する。（一意の列を登録します） End If End If Next Rng For I = 1 To ColTemp.Count 'コレクションに登録した分繰り返す。 Ws.Columns(ColTemp(I)).EntireColumn.AutoFit '該当した列に対して列は幅を調整します。 Next I Next Ws '最後のワークシートまで繰り返します。 End Sub ' ' |
（画面クリックして拡大）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。