シーゴの Excel 研究室

タイトル変更しました （旧称：今日を乗り切るExcel研究所）

トップ > VBA > 【VBA】空文字列セルを空白セルにクリアしたい【100本ノック】

【VBA】空文字列セルを空白セルにクリアしたい【100本ノック】

VBA

遅まきながらエクセルの神髄さんのTwitter企画 「VBA100本ノック」の便乗記事、 5 本目課題への解答例です。

お題：VBA100本ノック 5本目：セルの計算

出題ページ

方針

VBA で計算した値と表示形式を条件付きで所定のセルに設定するという課題です。

難しくはないです。

でも実はなかなか Excel の闇を突いています。

普通ならこのような簡単な計算は数式でやればいいと思いますよね。

ところが数式でよくあるのが、見積の明細のような表で未記入の行の計算列に 0 やエラーが表示されてしまうという問題です。

Excel の数式では、空白セルの値が 0 として評価されます。 また、数式のあるセルでは値を空（Empty）にすることがでず、必ず何らかの値を持たざるをえません。

不完全なデータの数式セルを空欄にするテクニックはいくつか知られていますが、一般には IF 関数や IFERROR を使って、空文字列（""）を設定することが広く行われていると思います。

しかしそれでは数式が複雑になるうえ、データとして参照されたときにエラーの原因ともなります。

データ時代にそのようなゴミを挿入するなど許されません。

他にも数値の「表示形式」を書き換えて値が 0 の場合にのみ不可視にしたり、「条件付き書式」で 0 ならフォントを白（背景色）にするなど見かけ上空欄にする方法もあります。

この方法では、本当に計算結果が 0 だった時に分からなくて困ります。

数式のセルを本当に空欄（空白セル）にすることは不可能なのです。

かくしてマクロ VBA の出番です。

解答

Option Explicit

Private Enum 明細
    単価 = 1
    点数
    金額
End Enum

Private Const 金額書式 = "\#,##0;\-#,##0"

Function 金額計算(単価 As Variant, 点数 As Variant) As Variant
    If IsEmpty(単価) Or IsEmpty(点数) Or Not (IsNumeric(単価) And IsNumeric(点数)) Then
        金額計算 = ""    ' パラメータが不完全なら空文字列を返す
        Exit Function
    End If
    
    金額計算 = 単価 * 点数
End Function

Sub VBA100_005()
    Dim data As Range
    Set data = ActiveSheet.Range("B2").CurrentRegion
    Set data = Intersect(data, data.Offset(1))
    If data Is Nothing Then Beep: Exit Sub
    
    Dim r As Range
    Set r = data.Rows(1).Cells
    
    Dim calc As String
    calc = formatA1("=金額計算(@, @)", r(明細.単価), r(明細.点数))  ' "=金額計算(B3, C3)"
    
    Application.ScreenUpdating = False
    With data.Columns(明細.金額)
        .NumberFormatLocal = 金額書式
        .FormulaLocal = calc
        .Value = .Value    ' 数式を固定値にし、空文字列なら削除する
    End With
    Application.ScreenUpdating = True
    
End Sub

' "@"をRengeのA1形式の相対参照に置き換える
Private Function formatA1(ByVal tmpl As String, ParamArray rngs() As Variant)
    Dim r As Variant
    For Each r In rngs
        tmpl = Replace(tmpl, "@", r.Address(False, False), 1, 1)
    Next
    formatA1 = tmpl
End Function

　

　

考察

本解答では、数式で不完全な行の計算結果をいったん空文字列としたうえで、Value プロパティの再代入により一括でそれをクリアし本来の空白セルとしています。

VBA Empty 事情

空白セルの Range.Value は Empty となります。

セルが空白かどうかは、厳密には IsEmpty 関数で判定します。

    Set rng = Range("A1")             ' A1 を空セルとする

    Debug.Print rng.Value             ' ⇒ 
    Debug.Print TypeName(rng.Value)   ' ⇒    Empty
    Debug.Print IsEmpty(rng)          ' ⇒   True

しかし一般に目にするコードでは、空文字列（""）で比較することが多いようです。

    If rng.Value = "" Then
         MsgBox "空白セルです"
    End If

これは Empty を空文字列と比較しても True となるからです。

    Debug.Print Empty = ""   ' ⇒ True
    Debug.Print Empty = 0   ' ⇒ True    0 と比較しても True

厳密にいえば、空白セルを空文字列で判定している時には、値が空文字列の場合も含めて空白扱いとしていることになります。

では空文字列は Empty と等価なのかというと、IsEmpty 関数ではしっかり False になります。

    Set rng = Range("A2")           ' 空文字列を値に持つセル
                                    '  数式「=""」を入力した後コピーして値として貼り付けます
    Debug.Print rng.Value           ' ⇒
    Debug.Print TypeName(rng.Value) ' ⇒ String
    Debug.Print "" = rng.Value      ' ⇒ True
    Debug.Print IsEmpty(rng.Value)  ' ⇒ False

    zero = 0
    Debug.Print IsEmpty(zero)       ' ⇒ False

ちょっと混乱しますが、あえて整理すると、空文字列は Empty ではないが、Empty は文字列とみなせるのだと思われます。

    Debug.Print "ABC" & Empty & "DEF"       ' ⇒ ABCDEF
    Debug.Print Replace("abc", "b", Empty)  ' ⇒ ac

ふむふむ。

では、Range.Value に空文字列を設定したらどうなるでしょうか。

    rng.Value = ""
    Debug.Print rng.Value               ' ⇒
    Debug.Print TypeName(rng.Value)     ' ⇒ Empty
    Debug.Print IsEmpty(rng.Value)     ' ⇒ True

Empty！ すなわち 空白セルになります！

わけがわかりませんね。空文字列はどこ行っちゃったんでしょうか。

ちなみに、ある意味空文字列とみなせるシングルクォートのみのセルはもっと不可解です。

    rng.Value = "'"                     ' シングルクォートのみも空文字列扱い
    Debug.Print rng.Value               ' ⇒
    Debug.Print IsEmpty(rng.Value)      ' ⇒ False
    rng.Value = rng.Value
    Debug.Print IsEmpty(rng.Value)      ' ⇒ True

そもそも Excel でセルにテキストを入力し、あとから Delete で全ての文字を削除すると、それは空白セルになりますね。 空文字列を入力しようとしてもできません。

本来、空文字列のセルはあってはならないのです。

ところが数式が原因で空文字列は生じえます。

あくまでに空文字列セルのほうがおかしなことなので、VBA 側ではできれば Empty に倒しているようです。

色々納得できませんが、結局、空文字列（シングルクォートのみの場合もふくめて）を Empty にしたい場合、Range の Value に Value を代入しなおすというのが、最終的なノウハウとなります。

    Range("A1:D100").Value = Range("A1:D100").Value

数式を温存したい場合には Formula プロパティの方を代入しなおせば OK です。

    Range("A1:D100").Formula = Range("A1:D100").Formula

　

　

関連記事

www.shegolab.jp