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関数を利用するには、下記の通りに設定を行います。

ISERROR(値)
※値(セル)がエラーの場合は、【True】を返します。正常の場合は、【False】を返します。
※戻り値は、ブール型 (Boolean)に返します。

 

【使用例】
● セル(A1)のエラーの原因をメッセージボックスに表示します。


【サンプルプログラム】


【EXCELで発生するエラー表示の一覧 (一部)】

エラー表示エラー原因
#DIV/0割り算を0で割っている。
#N/A数式や関数に使用できる値がない。
#VALUE!関数の引数や演算の形式に誤りがある。
#REF!参照先のセルが見つからない。
#NAME?関数名やセル範囲の名前に誤りがある。
#NULL!!指定した2つのセル範囲に共通部分が無い。
#NUM!数値の指定に誤りがある。また、範囲を超えている。

 

 

 

 

 

EXCEL VBA エラーかどうかを判定します。各種エラーに対して原因をメッセージボックスに表示します。(ISERROR関数)

 

 

 ●プログラム説明 (サンプル①)

下記のサンプルプログラムは、A列に数式や関数が登録されています。登録した数式や関数の設定に誤りがあるため、数式エラー等が発生しています。今回のプログラムでは、そのエラー原因を順番にメッセージボックスへ表示するサンプルプログラムです。ISERROR関数を使う事で、様々なエラーについてもセルのエラー判定を行うので、エラー箇所を特定する場合は便利だと思います。

【プログラムの流れ】
①   A列の最終行を取得します。
② A列の2行目から①で取得した最終行まで繰り返します。
③ 該当するセルの数式・関数にエラーかどうか判定します。
④ ③で該当するセルの数式・関数にエラーが発生している場合は、原因の数式をメッセージボックスに表示します。
⑤ A列の最終行まで繰り返します。②へ

 

 

 

 

 

●実行前~実行後 ※プログラム実行後、A列に登録されている数式や関数にエラーが発生している場合、発生しているセルのエラー内容を順番にメッセージボックスに表示します。
(画面クリックして拡大)

 

 

 

EXCEL VBA エラーかどうかを判定します。エラーに対して原因を別のセルに表示します。(ISERROR関数)

 

 

 ●プログラム説明 (サンプル②)

下記のサンプルプログラムは、D列に登録されている数式エラーがあるか判定します。判定の結果、誤りがある場合はF列にエラー原因を表示します。また、エラーが発生したD列セル(数式エラーが登録されている)は、エラー発生セルを分かりやすくするために、背景色が赤で塗りつぶされます。

 

【プログラムの流れ】
① D列の最終行を把握します。
② D列の背景色を白に戻す。
③ 数式エラーを表示するF列をクリアーする。(削除)
④ D列の最終行を取得します。
⑤ 当該セルの数式がエラーか判定します。
⑥ 判定結果に「False」数式エラーが無い場合は、F列に【問題無し」と表示します。
⑦ 判定結果に「True」数式エラーが有る場合は、F列に【数式エラー原因】を表示します。かつ、エラー原因のセルに背景色を赤に塗りつぶします。

 

 

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、D列に登録されている数式がエラーかどうかを判定します。数式エラーが発生した場合は、該当するD列のセルの背景色が赤に塗りつぶされます。またエラー原因の数式ががF列に表示されます。なお、数式にエラーが発生しない場合は、F列に「問題無し」と表示します。
(画面クリックして拡大)

 

 

EXCEL VBA セルの内容が正しいのに「###」が表示されるときの対処法・セル幅の自動調整(セル幅が狭い)

 

 

 ●プログラム説明 (サンプル③)

下記のサンプルプログラムは、関数・数値エラーが発生しているわけでは無いが、セルの幅が狭く数値が入り切れない場合に表示される「###」をISERRORを使い、該当するセルの列幅を自動調整を行うサンプルプログラムです。

【プログラムの流れ】
① アクティブのワークシートのセル(A1~F1)まで順番に繰り返します。
② セル(A1⇒F1:まで)順番にセルがび数式エラーが発生しているか判定します。
③ 数式エラーが発生及び該当のセルの文字列の左側1文字目が”#”か判定します。
④ 判定結果、”###”になっているセル位置のセル幅を自動調整します。

 

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、セルのA1⇒F1まで順番に数式エラーが発生及び、該当セル左1文字目が”#”のセルに対して、セル幅が縮小表示されているので、セル幅を自動調整します。今回該当したセルは、B列・C列・E列が該当するので、このセル幅を自動調整しました。
(画面クリックして拡大)

 

 

EXCEL VBA ブック全体のワークシートに対して、セルの内容が正しいのに、セル幅が縮小され「###」表示されるときの対処法・セル幅の自動調整②

 

 

 ●プログラム説明 (サンプル④)

下記のサンプルプログラムは、サンプルプログラム③の応用になります。今回のプログラムではブック全体が対象となり全てのワークシートのセル「A1」を起点とする表の範囲に対して、セル幅の狭い「###」と表示されている列に対してセル幅の自動調整を行います。

【プログラムの流れ】
① 全てのワークシートに対して繰り返します。
② エラーが発生してもプログラム実行を続行します。
③ 各ワークシートのセル「A1」を起点に表範囲を取得します。※この表の範囲が列幅の調整する範囲になります。
④ ワークシートを順番にアクティブにします。
⑤ 現在のアクティブのセル「A1」を起点に取得した表範囲を順番に繰り返します。
⑥ 表の範囲として取得したセル範囲を順番に繰り返します。
⑦ 該当のセルが数式エラーが発生しているか判定します。かつ、該当のセルの左側1文字目が”#”になっているか、判定します。
⑧ ⑦の2つの条件が一致していれは、その列番号をコレクションとして登録します。(一意の番号として列番号を管理します。)
⑨ 表の範囲として取得した最後のセルまで繰り返します。⑤へ
⑩ コレクションに登録された列番号を数分繰り返します。
⑪ 縮小表示された列番号を指定して、列幅を自動調整します。
⑫ 最後のワークシートまで繰り返します。

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、全てのワークシートに登録されているセル「A1」を起点とする表の列幅が縮小表示されている該当セルのみ列幅を自動調整しました。コレクションを利用して該当セルの列番号を纏める事で、自動調整を際、最低限の回数で列幅を自動調整する事ができます。
(画面クリックして拡大)

 

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。