絶対に役に立った! 超お勧め エラー非表示 マクロ

Excelの広場

業務で一番役に立った、
ラー非表示 マクロを紹介します。

下記図は、超あるある!

一人当たりの個数 = C列 / D

配る人数 0人だった場合、収穫数を
0 で割る為、答えが無限大になり
エラー表示 になった表です。

計算的には、正しいけど エラー表示 された表は見苦しいですよね ...

そこで!

一発で エラー表示 を 消したり
0にしたり する マクロを紹介します。

Max
マクロ、VBA、VBE 、不明な方は、
下記を合わせてお読み下さいだワン!
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 ...

続きを見る

Max
データ型 Range の格納範囲が書いてないけど、

Range は、オブジェクト型 だワン!

VBA 処理の 高速化。

Speeding_up= True

Excel VBA (以下 VBA)は、
処理速度が遅いです。

高速化する手法は、色々あります。
VBA 高速化三点セット をマクロ化しました。

ポイント

Speeding_up 変数を True にすれば、高速 になり False にすれば、初期値 に戻ります。

高速化に関しては、
下記を合わせてお読み下さい。

Excel マクロ VBA の 高速化

Excelの広場 Excel VBA を高速化するマクロを紹介します。 このまま、コピペ して使って頂ければ OK です。 Maxマクロ とか VBA に関して?な方は、 下記記事をお読みくださいだワ ...

続きを見る

選択している全ての セル範囲 を For で 命令を回し処理を実行しよう!

下記を日本語でいうと、

For Each A In Selection

Next

選択範囲内 の セル を、
順次 引数A に代入し、

Next まで はさまれている 命令文 を
実行しなさい!という意味です。

なんか日本語がしっくりこないですね..

図で表すと..

For Next に 挟まれている命令1つめ、【選択抽出されているセル の 位置 を知る】

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 に 挟まれている命令 2つめ、【選択セル の 数式を取得 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 の場合、

左から4文字抜き出されて、
あいうえ となります。

For Next に はさまれている命令 3つめ【選択セル の 正確な数式を取得】

数式で頭についている 文字は、不要なのでそれ以降の2文字めから とします。

After計算式 =
Mid(before計算式, 2)

変数 After計算式 の入れ物は、
文字 が入ってくるので
String にしました。

文字抽出 Mid 命令

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 までが 1 セル単位で 次々と レンジB に セット されます。

tate = B.Row 
yoko = B.Column

 tate  yoku 変数にBセルの位置数を格納します。

ここまでの処理で選択されている Bセル の位置が分かりました!

before計算式 = Cells(tate, yoko).Formula

before計算式 変数へ選択されているセルの数式を読込みます。

If Left(before計算式, 1) = "=" Then

before計算式 に読込まれた内容が

数式じゃなかった場合 Emd if まで飛び Next にて E4、E5、E6 と最終 E7セル ま繰り返されます。

Bセル の式を ISERROR関数式 へ書き換えます。

処理内容は..

After計算式 変数に 選択セルの数式が読み込まれるので、

えば ..

E3セルが選択されていて、
内容が、C3 / D3 だった場合、

Cells(tate, yoko) の説明

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計算式 & ")" となります。

Max
爺さん爺さん..
やたらと ”””””” ダブルコーテーションが多いだワン!
ドングリ爺さん
しかたないのぅ..

=IF(ISERROR( までを 文字として で囲み、

After計算式変数を で繋ぎ、)は、文字なので
で囲み、”” は、禁則文字 なので 囲み、

さらに それを文字にするには、” で囲んだら、

””””””6つ もついてしもうた..

こんな事を最後まで続けたら、

だらけになってしもうた、あんまり長いんで _ で改行したわい..

ポイント

VBA は、文字 の頭に をつけれておけば、

コードでは、なく ただの雑記
(コメント)として認識されます。

エラーの場合、空白じゃなくて 0 にしたい場合、

Cells(tate, yoko).Value = "=IF(ISERROR(" & After計算式 & ")," & 0 & "," & After計算式 & ")" 'エラーは、

上記コードに付いている
削除

' Cells(tate, yoko).Value =
"=IF(ISERROR(" & After計算式 & ")," & """""" + "," & After計算式 & ")" 'エラーは、空

上記コードの頭に をつけ 無効 にして下さい

早い話

〇〇 部 エラー にて
”””””

〇〇 部を 上記表のように
書き変えれば、エラー にて
空白 / 0と表示が変わります。

ここまでの まとめ

この マクロは、選ばれたセル範囲内のセル一つ一つの 数式 After計算式 と言う変数に取込み、そのセルの 数式 を ISERROR関数に 順次書き換えていく 処理をしています。

全ての処理が終わったら、

Speeding_up = False を実行し、

初期状態
(画面更新有、イベント有効、計算自動)に戻し 、

マクロを終了 します。

スポンサーリンク

-Excel の広場

Copyright© ドングリ爺のblog , 2021 All Rights Reserved Powered by AFFINGER5.