VBA100本ノック 56本目：数式内の自身のシート名を消す

公開日：2020-12-28　最終更新日：2021-04-27

VBA100本ノック 56本目：数式内の自身のシート名を消す

全シートの全数式内での自身のシート参照を消す問題です。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータはご自身でご用意ください。
いろいろな数式のパターンを作成して確認してみてください。

出題

#VBA100本ノック 56本目
数式に自身のシート名が入っていると数式が長く、並べ替えが上手く出来ない等々何かと邪魔です。
そこで全シートの全数式内での自身のシート参照を消してください。
=自身のシート!C2… → =C2…
※シート名に記号が使われている場合を考慮。
※串刺し計算は置換しません。

参考として追記
=SUM(Sheet2!A1:A3) ・・・普通のシート
='Sheet 1'!A1 ・・・シート名にスペース
=Sheet2!J3:J13 ・・・スピル、配列も変わりません
=SUM('Sheet 1:Sheet2'!A1) ・・・串刺し

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

シート名にスペースや記号を含む含まないで、シングルクォートが付いたり付かなかったり、その後に続く!との組み合わせを考えなくてはなりません。
串刺し計算まで考えると結構面倒なVBAになってしまいます。
そこで、シート名を置換して必ずシングルクォートが付くようにしてから置換します。


  
    
      Sub VBA100_56_01()

　　Dim wb As Workbook: Set wb = ActiveWorkbook

　　

　　Dim saveCalc As XlCalculation

　　saveCalc = Application.Calculation

　　Application.Calculation = xlCalculationManual

　　

　　Dim ws As Worksheet, saveName As String, rng As Range

　　For Each ws In wb.Worksheets

　　　　On Error Resume Next

　　　　Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)

　　　　If Err.Number = 0 Then

　　　　　　saveName = ws.Name

　　　　　　ws.Name = ws.Name & vbTab

      　　　　　　Call replaceFormula(rng, ws.Name)

　　　　　　ws.Name = saveName

　　　　End If

　　Next

　　

　　Application.Calculation = saveCalc

　　MsgBox "完了"

End Sub


Sub replaceFormula(ByVal aRng As Range, ByVal aName As String)

　　Dim tName As String

      　　tName = "'" & Replace(aName, "'", "''") & "'!"

　　aRng.Replace What:=tName, Replacement:="", LookAt:=xlPart, MatchCase:=True

End Sub

Tabを後ろに付ければ他のシートと重複することは無いでしょう。
上記ではReplaceメソッドで一発で実行しています。
1セルずつ置換する場合は配列数式の考慮が必要になります。
これについては記事補足に掲載しました。

補足

1セルずつRplace関数でFormulaを置換しても良いでしょう。
ただし、配列数式に対して注意が必要になります。
配列数式は、その一部のセルのみ数式(Formula)を変更することは出来ません。

配列数式かどうかは、RangeオブジェクトのHasArrayプロパティで判定できます。
そして、FormulaArrayプロパティを使えば配列数式を1セルだけで変更できます。

メインのプロシージャーは先のVBAと同じものを使います。
replaceFormula
このプロシージャーの変更だけになります。


  
    
      Sub replaceFormula(ByVal aRng As Range, ByVal aName As String)

　　Dim tName As String

      　　tName = "'" & Replace(aName, "'", "''") & "'!"

      　　
　　Dim rng As Range

　　For Each rng In aRng

　　　　If rng.HasArray Then

　　　　　　rng.FormulaArray = Replace(rng.Formula, tName, "")

　　　　Else

　　　　　　rng.Formula = Replace(rng.Formula, tName, "")

　　　　End If

　　Next

End Sub

サイト内関連ページ

第38回．セルに計算式を設定（Formula）

・計算式を設定できるプロパティ・Valueプロパティ・Formulaプロパティ , FormulaLocalプロパティ・FormulaR1C1プロパティ , FormulaR1C1Localプロパティ・R1C1参照形式・Localが付くプロパティについて・それぞれの違い（Localは除く）・何故、こんなに多くのプロパティが存在しているのか・R1C1形式を使うメリット・たった1行のVBAで複数のセルに計算式を入れる

第47回．VBA関数（文字列操作,Replace,InStr,StrConv）

・文字列操作に関するVBA関数の一覧・Replace関数・InStr関数・StrConv関数・最後に

第99回．Replaceメソッド（置換）

・Replaceメソッドの構文・Replaceメソッドの注意点・Replaceメソッドの使用例・ReplaceメソッドとReplace関数の使い分け

同じテーマ「VBA100本ノック」の記事

53本目：テーブルの扱いと年齢計算

・出題・頂いた回答・解説・補足・サイト内関連ページ

54本目：シートのChangeイベント

・出題・頂いた回答・解説・補足・サイト内関連ページ

55本目：他ブックのマクロを起動

・出題・頂いた回答・解説・補足・サイト内関連ページ

56本目：数式内の自身のシート名を消す
57本目：ファイルの更新日時

・出題・頂いた回答・解説・補足・サイト内関連ページ

58本目：番号リストを簡潔にした文字列で返す

・出題・頂いた回答・解説・補足・サイト内関連ページ

59本目：12ヶ月分のシートを四半期で分割

・出題・頂いた回答・解説・補足・サイト内関連ページ

60本目：「株式会社」の表記ゆれ置換

・出題・頂いた回答・解説・補足・サイト内関連ページ

61本目：「ふりがな」の取得と設定

・出題・頂いた回答・解説・補足・サイト内関連ページ

62本目：独自のZLOOKUP関数を作成

・出題・頂いた回答・解説・補足・サイト内関連ページ

63本目：複数シートの連結

・出題・頂いた回答・解説・補足・サイト内関連ページ

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
56本目：数式内の自身のシート名を消す

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

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

VBA練習問題
VBA100本ノック 56本目：数式内の自身のシート名を消す