56本目 数式内の自身のシート名を削除

VBA100本ノック 56本目を動画で解説しています。Excelの神髄さんの模範解答はコチラ

---

問題

#VBA100本ノック56本目
式に自身のシート名が入っていると数式が長く、並べ替えが上手く出来ない等々何かと邪魔です。
そこで全シートの全数式内での自身のシート参照を消してください。
=自身のシート!C2… → =C2…
※シート名に記号が使われている場合を考慮。
※串刺し計算は置換しません。
参考として追記
=SUM(Sheet2!A1:A3) ・・・ 普通のシート
=’Sheet 1′!A1 ・・・ シート名にスペース
=Sheet2!J3:J13 ・・・ スピル、配列も変わりません
=SUM(‘Sheet 1:Sheet2’!A1) ・・・ 串刺し

解答

Sub シート名にあえて記号を入れる()
    
    Dim ws As Worksheet
    Dim ws元の名前 As String
    Dim ws処理中の名前 As String
    Dim 消したい文字列 As String
    On Error Resume Next
    
    For Each ws In ThisWorkbook.Worksheets
        ws元の名前 = ws.Name
        ws処理中の名前 = ws.Name & "@"
        ws.Name = ws処理中の名前
        
        消したい文字列 = "'" & Replace(ws処理中の名前, "'", "''") & "'!"
        ws.Cells.SpecialCells(xlCellTypeFormulas).Replace 消したい文字列, ""
        
        ws.Name = ws元の名前
    Next
    
End Sub