業務で一番役に立った、
エラー非表示 マクロを紹介します。
下記図は、超あるある！
一人当たりの個数 ＝ C列 / D列
配る人数 0人だった場合、収穫数を
0 で割る為、答えが無限大になり
エラー表示 になった表です。
計算的には、正しいけど エラー表示 された表は見苦しいですよね ...
そこで！
一発で エラー表示 を 消したり、
０にしたり する マクロを紹介します。
下記を合わせてお読み下さいだワン！
-
-
目次
Excel マクロ・VBA・VBE ってなぁに？
Excelの広場 爺さんが感じた事なんじゃが、Excelの自動化の事を マクロ と読んだり、VBA と読んだり、VBE と言う言葉もでてきたり..ハッキリしていないので、Max や、説明してくれる ...
続きを見る
-
-
Excel マクロの VBA を 書く VBE の 準備
Excelの広場 Max 最初に、「Excel マクロ の VBA を 書く VBE」って ？ な方は、下記記事を合わせてお読みくださいだワン！ 「Excel マクロ・VBA・VBE ってなぁに？」記 ...
続きを見る
【エラー非表示 マクロ VBA】
Sub err_N ()
Dim A As Range
Dim tate As Long
Dim yoko As Long
Dim before計算式 As String
Dim After計算式 As String
Speeding_up = True '画面更新無、イベント無効、計算手動
For Each A In Selection
tate = A.Row
yoko = A.Column
before計算式 = Cells(tate, yoko).Formula
If Left(before計算式, 1) = "=" Then
After計算式 = Mid(before計算式, 2) '＝ を除いたものが After計算式
Cells(tate, yoko).Value = "=IF(ISERROR(" & After計算式 & ")," _
& """""" & "," & After計算式 & ")" 'エラーは、空白
' Cells(tate, yoko).Value = "=IF(ISERROR(" & After計算式 & ")," _
& 0 & "," & After計算式 & ")" 'エラーは、0
End If
Next
Speeding_up = False '画面更新有、イベント有効、計算自動
End Sub
エラー非表示の VBAマクロ説明。
最初に 引数 データ型 宣言 を書きます。
Dim A As Range
Dim tate As Long
Dim yoko As Long
Dim before計算式 As String
Dim After計算式 As String
err_N マクロでは、
引数を 5つ データー型を 3種類 使いました。
データ型に関しては、
下記を合わせてお読み下さい。
-
-
Excel マクロ に出てくる 変数 とか、宣言のDimとか、データ型って何よ。
Excelの広場 最初にまず？となるのが、Dimで変数を宣言することでしょう。数学で X とか Y とかでてきましたよね。 例えば、 X ＝ 5 Y ＝ 10 とか.. 日本語は難しい言い方をして X ...
続きを見る
Range は、オブジェクト型 だワン！
VBA 処理の 高速化。
Speeding_up= True
Excel VBA （以下 VBA）は、
処理速度が遅いです。
高速化する手法は、色々あります。
VBA 高速化三点セット をマクロ化しました。
ポイント
高速化に関しては、
下記を合わせてお読み下さい。
-
-
Excel マクロ VBA の 高速化
Excelの広場 Excel VBA を高速化するマクロを紹介します。 このまま、コピペ して使って頂ければ OK です。 Maxマクロ とか VBA に関して？な方は、 下記記事をお読みくださいだワ ...
続きを見る
選択している全ての セル範囲 を For で 命令を回し処理を実行しよう！
下記を日本語でいうと、
For Each A In Selection
Next
選択範囲内 の セル を、
順次 引数A に代入し、
Next まで はさまれている 命令文 を
実行しなさい！という意味です。
なんか日本語がしっくりこないですね..
図で表すと..
For Next に 挟まれている命令１つめ、【選択抽出されているセル の 位置 を知る】
For Next で選択している
セルA が選ばれました。
下記は、選択セルA の 縦方向 Row
横方向 Column を 変数 tate,yokoに
代入しなさい！という命令です。
tate = A.Row
yoko = A.Column
（例）D5 が選択されている場合、
tate = 5 yoko = 4 になります。
変数 tate と yoko の入れ物は、
-2,147,483,648 ～ 2,147,483,647
あれば十分なので、
Long（長整数型）にしました。
For Next に 挟まれている命令 ２つめ、【選択セル の 数式を取得 Formula】
例えば、
D5 セルが選択されていたとします。
Formula とは、
日本語で、式 と言う意味ですよね。
下記命令は、上から5つめ、
左から4つめ、（D5）に書いてある 式 を before計算式 変数に代入しなさい！
という命令になります。
before計算式 = Cells(tate, yoko).Formula
注意ポイント
セルが 未記入（空白）だった場合、空白文字 として 取り込まれます。
変数 before計算式 の入れ物には、
文字 が入ってくるので String にしました。
変数 before計算式 に入っているデーターは、数式なの？
Left 命令を使って
変数 before計算式 の先頭文字が ＝ で
あれば 数式なので、数式と判断します。
If Left(before計算式, 1) = "=" Then
この関数式の内容を日本語でいうと、
befor計算式 変数に格納されている
一番左側文字は、＝ ですか？
という命令です。
＝ だった場合、End if までの
命令を実行します。
ポイント
文字抽出 Left 命令
Left（変数 , 抜き出す文字数）
Left関数 は、変数 左側 から
指定された数だけ文字を抜きだします。
例 として、
変数 ＝ あいうえおかきくけこ
抜き出す文字数 ＝ 4 の場合、
左から４文字抜き出されて、
あいうえ となります。
For Next に はさまれている命令 ３つめ【選択セル の 正確な数式を取得】
数式で頭についている ＝ 文字は、不要なのでそれ以降の２文字めから 式 とします。
After計算式 =
Mid(before計算式, 2)
変数 After計算式 の入れ物は、
文字 が入ってくるので
String にしました。
文字抽出 Mid 命令
Mid（変数 , 抜き出す文字位置
抜き出したい文字数）
ポイント
抜き出したい文字数 を 省略 した場合、指定位置から、右側すべての文字を抜き出します。
Excel関数の「エラー表示をさせない」を利用し、【関数文字生成】
エラー表示をさせない Excel関数式
=IF(ISERROR(計算式), "", 計算式)
この Excel関数 は、計算式 変数が エラーだった場合 空白 、そうでなかった場合、算出結果 となります。
「通常の算出」
「ISERROR関数を使用した算出」
上記 二つの表をご覧ください。
ISERROR関数 を 使用した方は、
C7 / D7 は、エラー になる為、
E7セルは、” ”（空白）になっています。
ということは..
この 関数式に E列セル 式を..
ポイント
書き換えてやれば良いわけです！
それを 行っているのが、
下記 コード になります。
Cells(tate, yoko).Value = "=IF(ISERROR(" & After計算式 & ")," _ & """""" ＆ "," & After計算式 & ")"
エラー表示 をさせない Excel関数式変換 の 具体的な解説
エラー表示を 空白 にしたい E3 ～ E7 セル範囲が選択されているとします。
err_N マクロを実行！
Speeding_up = True
マクロ処理を高速化します。
Set A = Selection
選択されている レンジ範囲を 引数 A に セット します。
For Each B In A ～ Next
範囲選択されているセル E3、E4、E5... 最終 E7 までが １ セル単位で 次々と レンジＢ に セット されます。
tate = B.Row
yoko = B.Column
tate yoku 変数にＢセルの位置数を格納します。
ここまでの処理で選択されている Ｂセル の位置が分かりました！
before計算式 = Cells(tate, yoko).Formula
before計算式 変数へ選択されているセルの数式を読込みます。
If Left(before計算式, 1) = "=" Then
before計算式 に読込まれた内容が
数式じゃなかった場合 Emd if まで飛び Next にて E4、E5、E6 と最終 E7セル ま繰り返されます。
Ｂセル の式を ISERROR関数式 へ書き換えます。
処理内容は..
After計算式 変数に 選択セルの数式が読み込まれるので、
例えば ..
Ｅ３セルが選択されていて、
内容が、C3 / D3 だった場合、
Cells(tate, yoko) の説明
セルの場所を示すコードです。
日本語では、セル（縦 , 横）と言う意味で、Cells(3, 4) つまり、
セルE3 を示します。
それに .Value = （Valueとは、値と言う意味）をつける事により セルE3 の値は、"=IF(ISERROR(" & C3 / D3 & ")," & """""" ＆ "," & C3 / D3 ＆ ")"だよ！と言う事になります。
Excelの関数式で書いたら =IF(ISERROR(C3/D3), "",C3/D3) なのに..
なんで VBA で Excelの関数式を書いたら長いの？
答えは、
関数式の 文字の部分 と 変数の部分 と
禁則文字である ”” を これは、文字ですよ！て書かなければいけないからです。
VBA 文字として扱うルール
- VBA は、文字だよ！と認識させる為には、ダブルコーテーション ” で囲む
- 文字と文字を繋ぐには、＆ を使う
- 禁則文字 は、ダブルコーテーション ” で囲む
- ダブルコーテーションで囲んだ 禁則文字を文字認識させる為にさらに ” で囲む
- 記入が長くなった場合 _ にて段差下げ可能
このルールで
=IF(ISERROR(C3/D3), "",C3/D3)
を書くと、
"=IF(ISERROR(" & "C3/D3" & ")," & """""" & "," & "C3/D3" & ")"
となります。
数式 C3/D3 は、マクロ err_N では、 After計算式変数 なので、
"=IF(ISERROR(" & After計算式 & ")," & """""" & "," & After計算式 & ")" となります。
やたらと ”””””” ダブルコーテーションが多いだワン！
=IF(ISERROR( までを 文字として ” で囲み、
After計算式変数を ＆ で繋ぎ、）は、文字なので
” で囲み、”” は、禁則文字 なので ” 囲み、
さらに それを文字にするには、” で囲んだら、
”””””” と ” が 6つ もついてしもうた..
こんな事を最後まで続けたら、
＆ と ” だらけになってしもうた、あんまり長いんで _ で改行したわい..
ポイント
コードでは、なく ただの雑記
（コメント）として認識されます。
エラーの場合、空白じゃなくて ０ にしたい場合、
Cells(tate, yoko).Value = "=IF(ISERROR(" & After計算式 & ")," & 0 & "," & After計算式 & ")" 'エラーは、０
上記コードに付いている ’ を
削除 し
' Cells(tate, yoko).Value =
"=IF(ISERROR(" & After計算式 & ")," & """""" + "," & After計算式 & ")" 'エラーは、空白
上記コードの頭に ’ をつけ 無効 にして下さい。
早い話
| 〇〇 部 | エラー にて |
| ”””””” | 空白 |
| ０ | ０ |
〇〇 部を 上記表のように
書き変えれば、エラー にて
空白 / ０と表示が変わります。
ここまでの まとめ
この マクロは、選ばれたセル範囲内のセル一つ一つの 数式 を After計算式 と言う変数に取込み、そのセルの 数式 を ISERROR関数に 順次書き換えていく 処理をしています。
全ての処理が終わったら、
Speeding_up = False を実行し、
初期状態
（画面更新有、イベント有効、計算自動）に戻し 、
マクロを終了 します。