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

VBAを100本の練習問題で鍛えます
最終更新日: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)を変更することは出来ません。
マクロ VBA 100本ノック

配列数式かどうかは、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本目:複数シートの連結


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

還暦のVBA:VBAまでたどりつけるか… (2021-09-29)
VLOOKUPを使うことを基本としてシートを設計すべきか|エクセル雑感(2021-08-17)
コンピューターはブラックボックスで良い|エクセル雑感(2021-08-14)
小文字"abc"を大文字"ABC"に変換する方法|エクセル雑感(2021-08-13)
ADOでテキストデータを集計する|VBAサンプル集(2021-08-04)
VBA学習のお勧めコース|エクセル雑感(2021-08-01)
エクセル馬名ダービー|エクセル雑感(2021-07-21)
在庫を減らせ!毎日棚卸ししろ!|エクセル雑感(2021-07-05)
日付型と通貨型のValueとValue2について|エクセル雑感(2021-06-26)
DXってなんだ? ITと何が違うの?|エクセル雑感(2021-06-24)


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

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




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


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



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