シーゴの Excel 研究室

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

トップ > VBA > 【VBA】入力セルをクリアしたい【100本ノック】

【VBA】入力セルをクリアしたい【100本ノック】

VBA

エクセルの神髄様の Twitter 企画「VBA100本ノック」のノック4本目の解答例です。

お題：VBA100本ノック 4本目：セルの消去

出題ページ

方針

計算式の数式セルや表の見出しセルはそのままで、入力セルのみを空白にクリアしたいことはよくあります。

お題の表のように入力セルの範囲が単純な矩形の場合は簡単ですが、もっと複雑な場合、例えば表の中ほどに数式があったり、フォームのように入力セルが飛び飛びで配置されている場合には面倒です。

あるセルが入力セルとみなせるかどうかは、「被参照セル」でかつ「数式でない（定数の）セル」であることでだいたい判定できます。

シート上でのセルの参照関係は、「数式」タブの「参照元のトレース」や「参照先のトレース」という機能で確認できます。

VBA で対応する同等の機能は Range の Precedents プロパティや Dependents プロパティなどになります。

また定数のセルは SpecialCells メソッドで取得できます。

これらを組み合わせて入力セルのみをクリアしてみたいと思います。

解答

Option Explicit

Sub VBA100_004()
    Dim rng As Range
    Set rng = ActiveSheet.Range("A1").CurrentRegion
    
    Call clearInputCells(rng)
End Sub

Function clearInputCells(rng As Range) As Range
    On Error GoTo error
    
    ' 数式ではない定数セル
    Dim cst As Range
    Set cst = rng.SpecialCells(xlCellTypeConstants) ' なければエラー
    
    ' 数式から参照されているセル
    Dim prc As Range
    Set prc = rng.DirectPrecedents ' なければエラー

    Intersect(rng, cst, prc).ClearContents

error:
    Err.Clear
End Function

このマクロでクリアされるセルは、セル範囲内の数式間で参照されているセルのみです。 セル範囲外からのみ参照されているセルは対象外です。

　

　

考察

ドキュメントで探すと、Range オブジェクトにセルの参照関係を取得するプロパティとしては以下の４つがあるようです。

これらは同一シート内で参照関係のあるセルを返します。

もし、対象 Range と参照関係にあるセルが無かった場合はエラーが飛びます。

ところで Microsoft のドキュメントを読んでいると、「参照元」と「参照先」の言葉遣いにちょっと混乱してきてクラクラします。 Excel としては以下の意味に使われているようです。

参照元（Precedents）
あるセルから見て、その数式から参照されているセル
参照先（Dependents）
あるセルから見て、そのセルを参照している数式のあるセル

どうみても、日本語的に言葉の使い方が逆なように思うのは筆者だけでしょうか。

DirectPrecedents プロパティ

Range から直接参照されている全てのセル。
「参照元のトレース」を１回押したときの表示内容に相当します。 f:id:shego:20210318114846p:plain

Precedents プロパティ

Range からの間接的な参照も含めて参照されている全てのセル。
「参照元のトレース」を複数回押して最後まで到達したときの表示内容に相当します。 f:id:shego:20210318115015p:plain

DirectDependents プロパティ

Range へ直接参照している全てのセル。
「参照先のトレース」を１回押したときの表示内容に相当します。 f:id:shego:20210318115159p:plain

Dependents プロパティ

Range への間接的な参照も含めて参照しているセル範囲。
「参照先のトレース」を複数回押して最後まで到達したときの表示内容に相当します。 f:id:shego:20210318115313p:plain

DirectPrecedents プロパティを使ってみる

今回の解答例では DirectPrecedents プロパティを使いました。

DirectPrecedents プロパティ は Range の範囲内のどれかのセルを数式で直接参照しているセルを全て返します。

表（セル範囲）の Range から DirectPrecedents で得られるセルは、セル範囲内のセル間で参照されている全てのセルとなります。 これらには、値のみ（定数）セルと数式のセルが混在しています。

これらのうち、SpecialCells メソッドで数式セルを排除して値のみのセルをクリア対象としています。

ところで参照関係が表のセル範囲内で完結している入力セルのみを対象としていいのかどうかは、仕様の判断が微妙なところです。

表の範囲外のセルを経由して間接的な参照関係にあるセルも対象に含めたい場合は Precedents プロパティを代わりに使えばいいでしょう。

    ' Range 外からの間接参照も含める
    Dim prc As Range
    Set prc = rng.Precedents  ' 19行目差し替え

もし、シートのどこからでも参照されているセルならクリア対象としたい場合は、シートの全セルを対象に DirectPrecedents プロパティを使います。

    ' シート全体のどこかのセルから参照されているセル
    Dim prc As Range
    Set prc = rng.Worksheet.Cells.DirectPrecedents  ' 19行目差し替え

別シートや別ブックから参照されているセルも対象にしたい場合はどうでしょうか。

Precedents プロパティなどで辿れるのは、同一シート内の参照関係だけで、別シートや別ブックから/への参照を取得することはできません。

ネットで調べても、結局、開いている全てのシートにある数式のセル参照を調べるという力わざのプログラムを書くしかないようです。 それでもセルアドレスではなく名前などで参照されていたら対応しきれるものではありません。

参考資料

Excel の参照元・参照先に混乱しているのは筆者だけではないようです。

関連記事

なし