SpecialCells(xlCellTypeVisible)メソッドのバグ？を回避する（Tips-16）

今回は、可視セルのみを取得するSpecialCells(xlCellTypeVisible)メソッド使用時のバグと、バクの回避方法について考えてみたいと思います。

VBA界では有名？なのかどうか分かりませんが、非表示の行または列がシート内に1以上ある状態で、単一セル対してSpecialCells(xlCellTypeVisible)メソッドを使用すると謎のバグが発生するようです。

下のコードで試してみたいと思います。（単純に、選択している可視セルに絵文字を入力するだけのものです）

Sub test()
    Dim myRange As Range

    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
        myRange.Value = "(＠_＠;)"
        DoEvents
    Next myRange

End Sub

このコードを下のシートのB2セル（単一セル）で実行してみます。色付きの行および列は実験で表示⇔非表示を切り替える予定の箇所です。

f:id:kouten0430:20190831170652p:plain

まず、バグが発生しない例からです。

非表示の行または列が1つもないシートで、testコードを実行。

f:id:kouten0430:20190831170734p:plain

処理がB2セルのみに行われます。（予定どおりの結果）

以降、バグが発生する例です。

行番号5を非表示にして、testコードを実行。

f:id:kouten0430:20190831171021p:plain

処理がB2セルのみではなく、シート内のすべてのセルに行われ、応答なしになります。

列番号Eを非表示にし、testコードを実行。

f:id:kouten0430:20190831171106p:plain

処理がB2セルのみではなく、Eの左側すべてに行われ、応答なしになります。

行番号5と列番号Eを非表示にし、testコードを実行。

f:id:kouten0430:20190831171140p:plain

処理がB2セルのみではなく、5の上側とEの左側のすべてに行われ、応答なしになります。

なお、非表示の有無に関らず、2以上のセルを選択した状態で実行すれば、上のようなバグは発生しません。

testコードのようにユーザーが範囲選択してから実行するコードを、うっかり単一セルで実行してしまうと前述のバグに見舞われます。

かといって、シート内に非表示があるかないか、コード内にSpecialCells(xlCellTypeVisible)メソッドが使われているかどうか、を注意し、そのうえでユーザーが単一セルでの実行可否を判断するのは本質の安全化ではないですよね。

ということで、ユーザーに頼らないバグ回避方法（暫定）を考えてみます。testコードの場合であれば、下の赤字箇所を追記すれば、ユーザーがぼんやりしていても回避できます。

Sub testバグ回避版()
    Dim myRange As Range

    If Selection.Count > 1 Then
        For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
            myRange.Value = "(＠_＠;)"
            DoEvents
        Next myRange
    
    Else
        ActiveCell.Value = "(＠_＠;)"

    End If

End Sub

ドヤ顔でいうほどのことではないですが、