【ExcelVBA】数式内の自シート!を消去するマクロ

SUMIFSやCOUNTIFSを組むときなど、
自シートと他シートを行ったり来たりして数式を組むと、

自シート参照の入った数式

このように「自シート名!」がセル参照式に入ってしまいます。

一度でも他シートを参照してしまうと、
以降は自シートでも参照式が入ってしまう仕様のようですね。

この状態、読みにくくなってしまうだけでなく、
ソートすると式が破損してしまうという罠も生んでしまいますので、
自シート名を""に置換するなどして消す必要が出てしまいます。

今回はこれを選択範囲に実行するマクロを紹介します。

実行すると選択範囲の数式をこんな風↓に修正してくれます。

自シート参照を消去した数式

選択範囲に実行するタイプの便利マクロですので、
Excel起動時に裏で開かれる「個人用マクロブック」などに搭載して使ってください。

クイックアクセスツールバーやショートカットキーへの登録が便利です。

個人用マクロブックの作り方、使い方についてはこちらをどうぞ。
www.limecode.jp

ソースコード
解説

ソースコード

' 自シート参照の削除
Sub 計算式から自シート参照を消去する()
    On Error Resume Next
    
    Call 選択範囲をUsedRangeで切り取って縮小する
    Call エクセルの自動更新を停止する(True)
    
    ' 置換対象の「自シート!」文字列を取得（シングルクォートの有り無しで2種類）
    Dim 自シート参照シングルクォートあり As String: 自シート参照シングルクォートあり = "'" & ActiveSheet.Name & "'!"
    Dim 自シート参照 As String: 自シート参照 = ActiveSheet.Name & "!"
    
    ' 数式のあるセルをループ
    Dim 関数セル As Range
    For Each 関数セル In Selection
        If 関数セル.HasFormula Then
            
            ' 自シート!があれば""に置換
            If InStr(関数セル.Formula, 自シート参照シングルクォートあり) Then
                関数セル.Formula = Replace(関数セル.Formula, 自シート参照シングルクォートあり, "")
            End If
            If InStr(関数セル.Formula, 自シート参照) Then
                関数セル.Formula = Replace(関数セル.Formula, 自シート参照, "")
            End If
                    
        End If
    Next
    
    Call エクセルの自動更新を開始する

End Sub

解説

単に「自シート!」の文字列をセル内から検索して""に置換しているコードです。

シート名に()などが入っていると参照式にシングルクォーテーションが入るので、
まずはそれを検索し、なければシングルクォーテーションの無い版を検索します。

マクロの開始前に

描画・イベント・計算の停止
行・列・シート全体を選択している場合にUsedRangeでカット

の二つの準備を行っています。

こちらについての詳細は以下の記事をご覧ください。
www.limecode.jp

なお、本マクロはスピル式には対応しておりません。

Formulaプロパティに代入をしているため、
スピル式に実行すると「@」が入ってスピルしなくなってしまいます。

Excel365,2021以降でスピル式に対応したい場合は、
左辺のFormulaをFormula2に書き換えて使用してください。

詳しくはこちらの記事をどうぞ。

準備中

和風スパゲティのレシピ

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

数式内の自シート!を消去するマクロ

ソースコード

解説