第156回目 ある条件をもつ計算式の一斉削除

○第156回目　ある条件をもつ計算式の一斉削除

　今回のテーマは、いったいどんなことでしょうか。
　こういうことです。

　ブックがたくさんあります。一つのブックで作ったシートを別のシートに移します。
　すると不都合なことが起こります。
　それは、他のブックを参照する計算式が、そのまま移ってしまうことです。つまり、元のブックのシートを参照してしまうのです。同様なことは、フォルダをコピーするときにも生じます。
　このような時の対応として考えられるのは、置換え機能でブックの部分をヌルに置き換えてしまうことです。すると、ブック内のシートを参照することになります。
　少し似たニーズとしてあるのは、別のシートから参照している計算式を全部削除したいという時です。
　
Q．そんな時ってあるんですか？
＝⇒あります。複雑な仕組みである基礎数値を求めました。その基礎数値を使って簡単な計算で表を作ります。この表の部分だけを誰かに渡したい時があります。
　データだけなら、一シートに表を値コピーし、そのまま渡せばいいのです。
　が、簡単ながら計算式が入っています。この部分だけは生かしたい。つまり、このシートの外部を参照している計算式だけをなくしたい、というニーズです。
　
Q．通常処理の置換えではだめなんですか？
＝⇒だんだん横着になって、めんどくさくなってしまうのです。置換え文字を作るのも結構めんどくさいのです。
　
　通常置換えを実際に試してみるとどういうことか分かると思います。
　
　マクロの機能としては
　1.計算式から外部ブックの部分を削除する。
　2.外部ブック及び外部シートから参照している計算式を削除する。
　となります。

　下の計算式(例)から考えて見ましょう。
　xxx='D:\111\222\333\[AB7500.xls]010'!$C$6
　xxx=乱数!B1
　外部のブックは、]で分かりますね。
　シートは、!でわかりますね。
　でも、
　'はどうしたらいいでしょうか。
　＝⇒無条件に、''で囲んでしまうのがいいかもしれません。
　複数の項目があった場合、削除する最初をどう判断しましょうか。
　＝⇒: の位置と、[ と ] の位置関係で何とかなりそうですね。
　
　まずは、簡単なほうから
　2.外部ブック及び外部シートから参照している計算式を削除する。
　これの場合、必ずシート名が出てきますので、シート名は、!があるかないかで分かりますので、計算式を取得して、!があるかないかを探します
　流れです。
　0.処理したい範囲を指定してから実行
　1.指定範囲のセルごとに計算式があるかないか調べる
　2.あった場合は、計算式を取得し、！が含まれているかを調べる
　　siki = cells(yy,xx).Formula　
　　含まれているかいないかは、instr(siki,"!") で判断
　3.！が含まれている場合は、セルの値を当該セルに入れる。
　　この処理は、cells(yy,xx).value = cells(yy,xx).value　でよろしい。
　
　
　1.計算式から外部ブックの部分を削除する。
　これは少しめんどくさい
　流れです。
　0.処理したい範囲を指定してから実行
　1.指定範囲のセルごとに計算式があるかないか調べる
　2.あった場合は、計算式を取得し、'を取り除く。
　3.2.の処理後のものに対して、削除すべき場所を求め削除する。
　　計算式に、:があるか　p1
　　:以降ではじめて出てくる!を探す　p3
　　その部分の中で]の場所を探す　p2
　　削除すべき箇所は、p1-1からp2までとなる
　4.シート名を''で囲う。
　　シート名は、p2+1からp3-1となるので、その部分を抜き出し、''で囲う。
　5.3-4を繰り返す
　6.全部のセルに対して処理を繰り返す
　　
　このくらいの構想で大丈夫でしょう。
　注意点
　1.については、計算式の適格性のチェックがありますので、範囲を広く設定すると、加工した計算式が適切でない場合(マクロが100%正しければいいのですが…)は、そのつど聞いてきます。最初は狭い範囲で試してから全体に広げてください。　
　1.の処理は、結構時間がかかりますので、単純なもので広範囲な場合は、通常の置換えでやったほうがいいかもしれません。
　参照元ブックを開いていると、:の取得ができませんで、当該ブックを閉じてから行いましょう(計算式に振るパス名がつく)。