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

VBAを100本の練習問題で鍛えます
最終更新日:2021-01-13

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


他ブックを開いて、開いたブックのマクロを起動する問題です。


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

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


出題

出題ツイートへのリンク

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


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


頂いた回答

解説

シート名にスペースや記号を含む含まないで、シングルクォートが付いたり付かなかったり、その後に続く!との組み合わせを考えなくてはなりません。
串刺し計算まで考えると結構面倒な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 replaceFormula2(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)を変更することは出来ません。
マクロ VBA 100本ノック

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

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

Sub replaceFormula2(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になります。マクロでは、セルに計算式を設定することは、そんなに多くないと思いますが、決して使わないわけではありません。しかし、この計算式の設定には何種類ものプロパティがあり、結構やっかいなのです。
第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本目:複数シートの連結


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

VBA100本ノック 68本目:全テキストボックスの転記|VBA練習問題(1月16日)
VBA100本ノック 67本目:ComboBoxとListBox|VBA練習問題(1月15日)
VBA100本ノック 66本目:全サブフォルダからファイルを探す|VBA練習問題(1月13日)
VBA100本ノック 65本目:固定長テキスト出力|VBA練習問題(1月12日)
VBA100本ノック 64本目:リンクされた図(カメラ機能)|VBA練習問題(1月11日)
VBA100本ノック 63本目:複数シートの連結|VBA練習問題(1月9日)
VBA100本ノック 62本目:独自のZLOOKUP関数を作成|VBA練習問題(1月8日)
VBA100本ノック 61本目:「ふりがな」の取得と設定|VBA練習問題(1月6日)
VBA100本ノック 60本目:「株式会社」の表記ゆれ置換|VBA練習問題(1月5日)
VBA100本ノック 59本目:12ヶ月分のシートを四半期で分割|VBA練習問題(1月4日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.繰り返し処理(Do Loop)|VBA入門




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


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



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