数式内の不要なシート名を削除する（HasFormula）

最終更新日：2020-04-13

数式内の不要なシート名を削除する（HasFormula）

複数のシートにまたがる数式を入力していると、自身のシート名!が数式についてしまいます、
この自身のシート名!は不要であり、式を見づらくしてしまいます、
この不要なシート名を、マクロVBAで一括削除します。

ブックの全シート、全セルを対象として、
数式の中から、自身のシート名!を削除するマクロVBAになります。

使用されている全セルを巡回


  
    
      Sub sample1()

　　Dim ws As Worksheet

　　Dim rng As Range

　　For Each ws In Worksheets

　　　　For Each rng In ws.UsedRange

　　　　　　If rng.HasFormula And _

　　　　　　　 rng.Formula Like "*" & ws.Name & "!*" Then

　　　　　　　　rng.Formula = Replace(rng.Formula, ws.Name & "!", "")

　　　　　　End If

　　　　Next

　　Next

End Sub

全シートの処理

For Each ws In Worksheets
ここは問題ないと思いますが、ForEachについては以下を参照してください。

第59回.コレクション処理（For Each）｜VBA入門

・For Each の構文・Exit For ・For Each の使用例・RangeオブジェクトのFor Each ・For Each サイト内の参考ページ

全セルの処理

For Each rng In ws.UsedRange
書き方はいろいろありますが、使用されているセルだけを対象としています。

最終行・最終列の取得方法（End,CurrentRegion,SpecialCells,UsedRange）

・最終行取得の基本：手動ではCtrl + ↑、VBAではCells(1, 1).End(xlDown) ・最終列の取得・特殊な表の場合・CurrentRegion ・SpecialCells(xlCellTypeLastCell) ・UsedRange ・Findメソッド・サイト内関連ページ

数式の判定

If rng.HasFormula Then
Rangeオブジェクト.HasFormula
数式が設定されている場合は
Trueが返されますので、これで判定しています。

空白セルを正しく判定する方法（IsEmpty,IsError,HasFormula）

・セルの値が空白の判定・計算式が入っていない判定・エラー値の判定・IsEmpty関数：空白を判定するVBA関数・TypeName関数：データ型を判定するVBA関数・RangeオブジェクトのFormulaプロパティ・空白セルを正しく判定する方法続編

自身のシート名!を削除

rng.Formula = Replace(rng.Formula, ws.Name & "!", "")
Rangeオブジェクト.Formula
これは数式のプロパティですが、この他に
FormulaLocal
FormulaR1C1
等々があります。
どれを使用しても構いませんが、
必ず、左辺と右辺で同じものを使用してください。
当たり前ではありますが、結構ミスしやすい部分になります。

数式にシート名が含まれているかは、
InStr関数

・InStr関数・InStrB関数・InStr関数の使用例・InStr関数の応用例1 ・InStr関数の応用例2 ・InStr関数の応用例3 ・InStr関数の練習問題・・

Like演算子

・Like演算子・パターン文字列式（ワイルドカード、文字リスト、文字範囲）・Like演算子の使用例・正規表現について

どちらでも構いませんが、上記VBAではLike演算子にしてみました。。

コードも短く理解しやすいVBAだと思います。

ただし、計算式の入っていないセルまで巡回しているので、大量データが入っていると処理時間がかかってしまいます。
そこで、以下では計算式の入っているセルだけを対象にします。

計算式が入っているセルだけ巡回


  
    
      Sub sample2()

　　Dim ws As Worksheet

　　Dim formulaRng As Range

　　Dim rng As Range

　　On Error Resume Next

　　For Each ws In Worksheets

      　　　　Set formulaRng = ws.Cells.SpecialCells(xlCellTypeFormulas)

　　　　If Err Then

　　　　　　Err.Clear

　　　　Else

　　　　　　For Each rng In formulaRng

　　　　　　　　If rng.Formula Like "*" & ws.Name & "!*" Then

　　　　　　　　　　rng.Formula = Replace(rng.Formula, ws.Name & "!", "")

　　　　　　　　End If

　　　　　　Next

　　　　End If

　　Next

End Sub

先の、「使用されている全セルを巡回」との違いは、
Set formulaRng = ws.Cells.SpecialCells(xlCellTypeFormulas)
これで、数式の入っているセルだけを先に取得している点です。

第135回.ジャンプの選択オプション（SpecialCells）｜VBA入門

・ジャンプの選択オプションとは・RangeオブジェクトのSpecialCellsメソッド・SpecialCellsの使用例

ただし、SpecialCellsは該当するセルが存在しないとエラーになってしまうので、

そこで、On Error Resume Nextで対応しています。

・On Error Resume Next ・Errオブジェクト・On Error Resume Next の使用例・「On Error Resume Next」の最後に

処理時間はこちらの方が少なくて済みます。

同じテーマ「マクロVBAサンプル集」の記事

印刷ダイアログを使用する（xlDialogPrint）
名前定義の一覧と削除（Name）
シートを名前順に並べ替える
数式内の不要なシート名を削除する（HasFormula）
数式の参照しているセルを取得する
 増殖した条件付き書式を整理統合する
 条件付き書式で変更された書式を取得する
 セル結合/解除でセル値を退避/回復
 セル結合なんて絶対に許さないんだからね
 セルの数式をネスト色分けしてコメント表示
 セル結合して表を見やすくする（非推奨）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.マクロって何？VBAって何？｜VBA入門
7.並べ替え（Sort）｜VBA入門
8.Excelショートカットキー一覧｜Excelリファレンス
9.エクセルVBAでのシート指定方法｜VBA技術解説
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門

ホーム
マクロVBA応用編
マクロVBAサンプル集
数式内の不要なシート名を削除する（HasFormula）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBAサンプル集
数式内の不要なシート名を削除する（HasFormula）