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

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日: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は該当するセルが存在しないとエラーになってしまうので、

VBA マクロ 数式 シート名

そこで、On Error Resume Nextで対応しています。
・On Error Resume Next ・Errオブジェクト ・On Error Resume Next の使用例 ・「On Error Resume Next」の最後に

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



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

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


新着記事NEW ・・・新着記事一覧を見る

シートコピー後のアクティブシートは何か|ツイッター出題回答 (2023-09-19)
Excel関数の引数を省略した場合について|ツイッター出題回答 (2023-09-14)
セル個数を返すRange.CountLargeプロパティとは|VBA技術解説(2023-09-08)
記号を繰り返してグラフ作成(10単位で折り返す)|ツイッター出題回答 (2023-08-28)
シートを削除:不定数のシート名に対応|VBAサンプル集(2023-08-24)
ランクによりボイントを付ける(同順位はポイントを分割)|ツイッター出題回答 (2023-08-22)
OneDrive使用時のThisWorkbook.Pathの扱い方|VBA技術解説(2023-07-26)
列幅不足による###表示や指数表示を判定する|VBA技術解説(2023-07-12)
シートを削除:不定数のシート名に対応|VBAサンプル集(2023-07-04)
シート関数のCOUNTIFS,SUMIFS,MAXIFSと同じ処理|Power Query(M言語)入門(2023-02-28)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロとは?VBAとは?VBAでできること|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.条件分岐(IF)|VBA入門




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

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



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