VBA技術解説
空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2021-05-24

空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)


空白セルの判定は、マクロVBAにおいては頻繁に発生しますが、
正しく空白セルを判定する事は以外と難しいものです。


そもそも「空白」とはどのような状態なのか・・・
これが、はっきりしない為に何が正しいのかが判然としないことが問題を複雑にしています。

以下、A1セルが空白かどうか判定する方法をいくつか紹介します。


セルの値が空白の判定

If Cells(1, 1).Value = "" Then
  '空白
End If

If Len(Cells(i, 1).Value) = 0 Then
  '空白
End If

普通に、= ""が最も簡単です。
わざわざLen関数を使っているのを、ネットでは割と多く見かけます。
恐らく、古いVB等の記述からきているのでしょうが、
VBAでは特に使う必要性はないように思います。
厳密にはLen関数で比較したほうが処理速度が速くなります。
僅かな差ではありますが、LenB関数を使うとさらに速くなります。
ただし、いずれにしても計測するのが困難なほどの時間差しかありません。
空文字列の扱い方と処理速度について(""とvbNullString)
・セルの値が空白の判定 ・計算式が入っていない判定 ・エラー値の判定 ・IsEmpty関数:空白を判定するVBA関数 ・TypeName関数:データ型を判定するVBA関数 ・RangeオブジェクトのFormulaプロパティ ・空白セルを正しく判定する方法続編

では、これで空白セルの判定は終わりでしょうか・・・
それでは、この記事の意味がありませんね。(笑)
もし、A1セルに関数が入っていて、その結果が空白の場合です。
この場合に、空白セルと判定したくない場合です。
これは良くある事です。

上記のIfでは、空白と判定されてしまいますので、
何らかの対策が必要になります。


計算式が入っていない判定

If Not Cells(i, 1).HasFormula Then
  If Cells(1, 1).Value = "" Then
    '空白
  End If
End If

HasFormula
HasFormulaは、Rangeオブジェクトのプロパティで、
そのRangeオブジェクトに数式が入力されているかどうかを判定し、
数式が入っている場合はTrue、数式が入っていない場合はFalseが返されます。
つまり上のマクロVBAでは、
数式が入力されていない状態で、""なら空白と判断しています。
これで大抵の場合はOKでしょう。

しかし、もし、セル値が、エラー値の場合はどうでしょう・・・
通常は、数式の結果がエラー値となっているので、
HasFormulaで、はじかれるので問題ないのですが、
エラー値そのものが値としてセル入っている場合は、
If Cells(i, 1) = "" Then
ここで、「型が一致しません」のエラーが発生してしまいます。
コピペで値貼り付けしている場合にはこれがありえます、そしてエクセルではしばしば発生します。
これを避けるように何とかしたいところです。


エラー値の判定

If Not IsError(Cells(i, 1).Value) Then
  If Not Cells(i, 1).HasFormula Then
    If Cells(i, 1).Value = "" Then

      '空白
    End If
  End If
End If

IsError
IsErrorはVBA関数です。
引数の値がエラー値かどうか判定し、
エラー値の場合はTrue、エラー値以外の場合はFalseが返ります。
IsErrorとHasFormulaは、どちらが先でも良いですし、Andで判定しても問題ありません。
ここまで判定すれば問題ないでしょう。

しかし、たかが空白判定に、これほど面倒な事をする必要があるのでしょうか・・・
VBA関数はいろいろ用意されています。


IsEmpty関数:空白を判定するVBA関数

If IsEmpty(Cells(i, 1).Value) Then
  '空白
End If

マクロVBAを上達する為には、VBA関数の習得は必須です。
どんなVBA関数があるかは、一通り見ておく事をお勧め致します。

IsEmpty関数ならエラー値でも判定できますので、IsError関数で事前にエラー値をはじく必要がありません。
ただし、IsEmpty関数は計算結果が空白("")セルを値貼り付けした場合にはFalseを返します。
これは、Excelの機能のジャンプでも、「空白セル」として判定されません。
従って、
IsError、HasFormula、= ""
この3段階判定がやはり必要になります。
やはり、空白判定は、とても面倒なものです。


TypeName関数:データ型を判定するVBA関数

TypeName関数:VBA関数
TypeName関数は、引数で指定された変数に関する情報を文字列で返します。TypeName関数 TypeName(varname) varname 必ず指定します。ユーザー定義型の変数を除く、任意のバリアント型(Variant)の変数を指定します。
TypeName関数の戻り値は上記ページを参照してください。

TypeName関数は、セルが初期状態つまり何も入っていない状態では、"Empty"を返します。

TypeName(Range("A1").Value)

※.Valueを省略してしまうと、"Range"になってしまいます。

A1セルが完全に空欄なら、"Empty"になります。
しかし、計算結果が空白("")セルを値貼り付けしたセルの場合は、"String"が戻ってきます。

If TypeName(Cells(i, 1).Value) = "String" Then
  If Not Cells(i, 1).HasFormula Then
    If Cells(i, 1).Value = "" Then
      '計算結果が空白("")セルを値貼り付けしたセル
      Stop
    End If
  End If
End If


RangeオブジェクトのFormulaプロパティ

メールで頂いた内容で、
Cells(i, 1).Formula = ""
これで判定できるのではないかというご意見をいただきました。
・セル値が空白
・計算式が入っていない
・計算結果が空白("")を値貼り付けした場合は空白判定
確かにFormulaだけで、これらを満たす判定が可能です。

本記事の流れとして、
Cells(i, 1) = ""
これでは、
・数式が入っているセルが判定出来ない → Not Cells(i, 1).HasFormula
・エラー値の場合 → Not IsError(Cells(i, 1))
と解説を進めて、では最終的にどのような判定が良いかという段で、いろいろと意見をもらいました。

ただし、実際に使う場面では単にセル値が空白と言っても、
どれをどのように使うかは、処理内容によってさまざまになると思います。
一番わかれるところは、計算結果が空白の場合どうするかという事でしょう。

上記とは逆に、例えば、
「セル値が空白のセルに色を塗る」とした場合、
計算結果が空白も対象にしたい場合が多いと思います。
この場合は、
Not IsError(Cells(i, 1))
Cells(i, 1) = ""
この組み合わせで判定が必要になってきます。

従って、実際に使う場合は、
ここでの、各プロパティを随時組み合わせて実現したい処理に対応してください。


空白セルを正しく判定する方法続編

「空白セルを正しく判定する方法」の続編として、
さらに別の判定方法も追加して、
セルの状態により、それぞれがどのような判定結果になるのかを一覧にまとめました。
ぜひこちらの追加記事も参考にしてください。

空白セルを正しく判定する方法2
空白セルの判定について、いろいろな方から意見を頂きました。やはり、空白判定は奥が深く結構難しいものとなっています。ここでは、各プロパティや関数が、セルの状態によって返す値を再確認してみます。元記事は、空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula) 上記の記事では、




同じテーマ「マクロVBA技術解説」の記事

ユーザー定義関数の作り方
セルの値について(Value,Value2,Text)
Excelのバージョンを判断して「名前を付けて保存」
空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)
空白セルを正しく判定する方法2
Rangeオブジェクト.Valueの省略について
シート保護でユーザー操作を制限する
シートに数式を設定する時のセル参照の指定方法
標準モジュールとシートモジュールの違い
オートフィルタ(AutoFilter)の使い方まとめ
複雑な条件(複数除外等)のオートフィルター(AutoFilter)


新着記事NEW ・・・新着記事一覧を見る

列全体を指定する時のRangeとColumnsの違い|ツイッター出題回答 (2023-09-24)
シートのActiveXチェックボックスの指定方法|ツイッター出題回答 (2023-09-24)
ByRef引数の型が一致しません。|ツイッター出題回答 (2023-09-22)
シートコピー後のアクティブシートは何か|ツイッター出題回答 (2023-09-19)
Excel関数の引数を省略した場合について|ツイッター出題回答 (2023-09-14)
セル個数を返すRange.CountLargeプロパティとは|VBA技術解説(2023-09-08)
記号を繰り返してグラフ作成(10単位で折り返す)|ツイッター出題回答 (2023-08-28)
シートを削除:不定数のシート名に対応|VBAサンプル集(2023-08-24)
ランクによりボイントを付ける(同順位はポイントを分割)|ツイッター出題回答 (2023-08-22)
OneDrive使用時のThisWorkbook.Pathの扱い方|VBA技術解説(2023-07-26)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロとは?VBAとは?VBAでできること|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.条件分岐(IF)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ