VBAの罠-単セル.SpecialCellsはシート全体から実行とみなされる

知らずに落ちると抜け出せなくなるVBAの落とし穴です。

SpecialCells(○○).Countの値が、よくわからない巨大な値になった。
SpecialCellsを使ったら、シート全体の値が書き換わった。
SpecialCellsでセルを絞ったつもりが、元のRangeよりなぜか広くなった。
オートフィルターで表示されたセルだけ操作しようとしたら、フィルター範囲をはみ出して処理される。

あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。

一刻も早く問題を解決したい人へ3行で説明
詳しい解説
- SpecialCellsを使う処理のサンプル
- 発生するバグ
解決策
おまけ

一刻も早く問題を解決したい人へ3行で説明

Range("A2:A" & i).SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 3

↑A列(2～ i 行目まで)の中で、空白のセルを赤くするよ

Range("A2:A2").SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 3

↑たまたま「 i = 2」のとき、単一のセル.SpecialCellsになるよ

このとき、
「SpecialCellsを単独のセルから実行した場合は、シート全体(UsedRange)から実行したものとみなす」
というクソ仕様が発動して、 A列どころか、シート全体が真っ赤っかになるよ。

というお話です。

解決策は、「If 実行するRange.Cells.Count = 1 Then で例外処理」です。

お急ぎでしたら詳しい解説は飛ばして解決策へどうぞ

詳しい解説

SpecialCellsを使う処理のサンプル

↓の表で、処理中列の●をTRUE、FALSEに変換します。

SpecialCellsで処理する元の表

やり方はいろいろありますが、SpecialCellsを使って、「定数セル」をTRUE、「空白セル」をFALSEにしましょう。

Range("B2:B5").SpecialCells(xlCellTypeConstants).Value = TRUE ' 定数セルをTRUE
Range("B2:B5").SpecialCells(xlCellTypeBlanks).Value = FALSE ' 空白セルをFALSE

SpecialCellsで処理を完了した表

SpecialCellsはコードが短くなるのでなかなか便利ですね。

とりあえずこれでやりたい処理は実現できたので、
あとはB2:B5を自由に指定できるようにして、マクロに組み込みます。

Sub 値のあるなしをTRUEFALSEに書き換える(書換エリア As Range)
    
    On Error Resume Next ' SpecialCellsがNothingを返す時のエラーを回避
    書換エリア.SpecialCells(xlCellTypeConstants).Value = True
    書換エリア.SpecialCells(xlCellTypeBlanks).Value = False
    
End Sub

全部●、または全部空白だった時に、どちらかの式がNothingを返して、
.Valueが「オブジェクトが必要です」になるエラーは回避しました。

エラー回避はこれでＯＫ？

発生するバグ

このマクロをたくさんのシートに実行していくとしましょう。
たまたま「レコードが1つしかない表」に遭遇した時、タイトルの地雷が炸裂します。

SpecialCellsの罠が炸裂する表

↑の表で起動すると…

SpecialCellsの罠が炸裂した表

！？

タイトルの通りSpecialCellsには、
「単独のセルから呼び出したときは、シートのセル全体から呼び出されたとみなす」
というびっくり仰天な仕様があります。

例えば、Range("B2:B" & シートの最終行)のようにデータエリアを指定したとしましょう。

すると、データが1行の時にB2:B2となって、単独のセルになります。
その結果、↑の仕様に従い、シートのすべてのセルをTRUE/FALSEに置き換えてしまいました。

実際、笑えるバグではありません。

指定した範囲を破壊するバグならまだしも、
わざわざ引数で制御したはずの範囲の外まで破壊するとんでもないバグです。

解決策

セルの個数が1の時用に、別の処理を書いてあげる必要があります。
今回の「TRUE/FALSEに変換」は、そのセルにTRUE/FALSEを直接入力すればいいので、

Sub 値のあるなしをTRUEFALSEに書き換える(書換エリア As Range)
    
    ' ◇ 書き換える範囲が単独セルならば、そのセル値を書き換えてExit
    If 書換エリア.Cells.Count = 1 Then
        書換エリア.Value = (書換エリア.Value <> "")
        Exit Sub
    End If

    On Error Resume Next ' SpecialCellsがNothingを返す時のエラー回避
    書換エリア.SpecialCells(xlCellTypeConstants).Value = True
    書換エリア.SpecialCells(xlCellTypeBlanks).Value = False
    
End Sub

これでＯＫ。

SpecialCellsを扱う際は、この仕様を必ず頭に入れておきましょう。

おまけ

なぜこんな仕様なの？

VBA目線だとすごく余計でお節介に感じる仕様ですが、これはおそらくエクセル目線で決められた仕様です。

SpecialCellsのエクセル上での機能は「F5:ジャンプ⇒セル選択」が該当します。
ひとつのセルから起動するような機能ではないので、ひとつのセルから起動したときにはシート全体で起動した扱いしたほうが、機能としては便利ということなのでしょう。

~~F5のジャンプを使うレベルのユーザーなら、全選択のCtrl+Aは余裕で使えるだろうから、エクセル目線でもやっぱり余計な仕様だと思うけど。~~

SpecialCellsの使いどころ

そもそもこんなトリッキーな書き方しなくてもと思うかもしれませんが、
SpecialCellsでの一括処理は、すべてのセルをループで処理するのに比べて10倍以上速いことも多いです。

他には、オートフィルターで絞られているデータをSpecialCells(可視セル)で処理するのもとても便利ですし、こちらは代わりになるような他のオブジェクト・プロパティはありません。

VBAはプログラミング言語単体としては貧弱ですが、ワークシートやシート関数をはじめとしたエクセル固有の機能を組み合わせることで、ものすごく強力な言語になります。

せっかくVBAを使うなら、愚直にFor文を書くよりも、エクセルの機能をフルに活用する術を学んでいくと面白いと思います。

ユーザー辞書を使おう

SpecialCellsは、「よく使う。文字数が多い。引数の文字数も多い。自動メンバーが出ないことが多い」のでユーザー辞書に登録しておきましょう。
読みは「すぺ」とかでもいいですが、実はユーザー辞書は「ｓｐ」のように、アルファベットも読みとして使えます。

ｓｐｃ ⇒ .SpecialCells(xlCellTypeConstants)
ｓｐｖ ⇒ .SpecialCells(xlCellTypeVisible)
ｓｐｆ ⇒ .SpecialCells(xlCellTypeFormulas)
ｓｐｂ ⇒ .SpecialCells(xlCellTypeBlanks)

私の登録はこれ↑

日本語入力だと「.」が打ちづらいので、「.」も単語に入れちゃうのがミソです。

でーたえりあｓｐｂ　⇒　データエリア.SpecialCells(xlCellTypeBlanks)

日本語入力をONのまま、一発で変換できます。
すばらしい。

和風スパゲティのレシピ

日本語でコーディングするExcelVBA

単セル.SpecialCellsはシート全体から実行とみなされる