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

最終更新日：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）

セルに計算式（関数）を設定する場合のマクロVBAになります。マクロVBAでセルに計算式を設定することは、そんなに多くないと思いますが、決して使わないわけではありません。しかし、この計算式の設定には何種類ものプロパティがあり、結構やっかいなのです。

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

文字列操作は、マクロVBAでプログラミングする上で必須です、データ整形、データクレンジング、データクリーニング、これらを行うVBAにおいて不可欠なものが、文字列操作関数です。ここでは、文字列操作に関するVBA関数の一覧と Replace関数、InStr関数、StrConv関数について解説します。

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

Replaceメソッドは、セル範囲内で条件に当てはまるセルの文字列を置換するものです。ReplaceメソッドはRangeオブジェクトのメソッドで、ワークシート操作の「検索と置換」の「置換」の機能をマクロVBAで使うものです。VBA関数のReplace関数とは全く違うものになりますので使い分けが必要です。

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

53本目：テーブルの扱いと年齢計算
 54本目：シートのChangeイベント
 55本目：他ブックのマクロを起動
56本目：数式内の自身のシート名を消す
57本目：ファイルの更新日時
 58本目：番号リストを簡潔にした文字列で返す
 59本目：12ヶ月分のシートを四半期で分割
 60本目：「株式会社」の表記ゆれ置換
 61本目：「ふりがな」の取得と設定
 62本目：独自のZLOOKUP関数を作成
 63本目：複数シートの連結

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

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

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

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

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

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