第118回目 計算式をチェックする(その5)

○第118回目　計算式をチェックする(その5)

ファイル等を超えた計算式の同一性チェック
　一つの計算システムをシートに作っておき、それを必要な数だけコピーして使うとか、ファイルそのものをコピーして別のパラメータにかえて使うということがあります。
　その場合、普通に考えると問題はないのですが、実際運用していくといろいろな問題が出てきます。その一つに修正があります。修正点があると、すべてのシートを同じように修正なければなりません。やっていくうちに、すべて修正したのかはっきりしなくなってしまうことがあります。また、ある一部分を修正し忘れているということもあります。それをチェックしようというものです。
　計算システムのコピーには、三つのケースが考えられます。
　1.ファイルそのものをコピーした
　2.同一ファイルの別シートにコピーした
　3.同一シートの別箇所にコピーした

　これらはすべて応用でできますので、1.のファイル同士で計算式をチェックするを取り上げてみます。

　前提；
　1.ファイルをコピーしたので、シートの順番が同じになっている。
　2.シート名は二つのファイルで違っている可能性がある。
　3.計算システムは、ファイル外からのリンクが張られているものではない。

　実行上の前提；
　1.チェック元、チェック先の両方のファイルは開かれている
　2.チェック元のチェックしたいシートにチェック先のファイル名が記されており、その場所をカーソルで指定した状態から実行する。

　チェックの機能；
　1.該当するシートから先の複数のシートを一気に処理できる
　2.シートのおおよその範囲内(ボーリング調査で自動的に判断する)の計算式をチェックする
　3.チェック範囲の先頭の位置は指定する
　4.1回のチェックでは、最大で150列、200行の範囲をチェックする。チェックすべき範囲がそれより大きい場合は、複数回のチェックとなる。自動的に複数回チェックの処理をする。
　5.チェックの前に、計算式上のシート名を変換しておく。変換表を作っておく。
　6.エラーの種類によって色を変える。また、チェックした範囲に色をつける
　　　計算式が、有り有りで違う＝⇒3　赤色
　　　計算式が、有り無し　　　＝⇒7　濃い桃色
　　　計算式が、無し有り　　　＝⇒40　肌色
　　　チェック範囲 ＝⇒36 アイボリ
　7.エラーの種類ごとに件数を表示する。これは一チェック単位ごととなる

【流れ】
　0.相手のファイル名のあるセルを指定してから実行
　1.自分のファイル名とシート名の取得。現在のシートの番号を取得
　2.相手のシート名の取得(1で取得したシート番号と同じという前提)
　3.チェックするシート数の入力
　4.チェックの範囲のボーリング調査により自動取得。
　5.チェックする範囲の先頭位置の指定
　6.元のシートでの計算式を取得
　7.元のシートでの計算式の中のシート名をチェック先のシート名に置き換える(変換表のある範囲を指定)
　8.チェック先のファイルにおける同一番号のシートを指定して、チェック範囲内の計算式と、既に取得した計算式との同一チェック
　計算式が、有り有りで違う＝⇒3　赤色
　計算式が、有り無し　　　＝⇒7　濃い桃色
　計算式が、無し有り　　　＝⇒40　肌色
　 チェック範囲内に色を付ける＝⇒36 アイボリ
　9.チェック単位でエラー件数を表示する
　10.チェック単位の回数だけ繰り返す。チェック単位数の最大は100回とする(時間が分単位でかかる)
　11.次のシートへの処理にいき、繰り返す

思ったよりは長丁場になります。

　1.自分のファイル名とシート名の取得。現在のシートの番号を取得
'ファイル名等、現在表示のブックの基本情報取得
F_name1 = ActiveWorkbook.Name
sh_name1 = ActiveSheet.Name
ii_max = Worksheets.Count
f_name2 = Selection.Value　'チェック先のファイル名の取得
'シート名(ワークシート)の取得、全部
For i = 1 To ii_max
ww1(i) = Worksheets(i).Name
Next i
'現在のシート名番号の把握
For i = 1 To ii_max
If ww1(i) = sh_name1 Then ii0 = i: Exit For
Next i

　早くもここで問題が起きました。この部分だけでは正しいのですが、テストをしたときに、問題が生じました。シート名がマッチせず、計算式中のシート名の変換ができなかったのです。
　原因を調べたところ(原因がわかるまで2時間程度かかってしまいました)、取得したシート名の中の括弧が全角になっていたのでした(実際のシート名は半角の括弧です)。
　計算式中のシート名では、括弧は半角です。これではマッチしません。

'シート名の（）を()に変換する。
rep_moji1(1) = "（"
rep_moji1(2) = "）"
rep_moji2(1) = "("
rep_moji2(1) = ")"
For i = 1 To 2
For n = 1 To ii_max
Do While 1
p1 = InStr(ww1(n), rep_moji1(i))
If p1 = 0 Then Exit Do
ww1(n) = Left$(ww1(n), p1 - 1) & rep_moji2(1) & Mid$(ww1(n), p1 + 1)
Loop
Next n
Next
　※2.で取得するチェック先のシート名においても同様の処理をお忘れずに。

　2.相手のシート名の取得
　 Workbooks(f_name2).Activate
'シート名(ワークシート)の取得、全部
For i = 1 To ii_max
ww2(i) = Worksheets(i).Name
Next
　※以下省略。

　3.チェックするシート数の入力
ck_sh_cnt = InputBox("チェックするシート数を入れてください。例えば　　", , 1)

　以下は、シート単位での繰り返しになります。
　4.チェックの範囲の検索。ボーリング調査
　元のシートに戻って、シートの有効範囲のボーリング調査をします。前回参照。

　5.チェックする範囲の先頭位置の指定
　4.で求めた有効範囲に一時的に色をつけ、有効範囲を明示した上で、チェックの先頭位置を指定します。指定後、色は消しておきます。
　 Dim セル範囲 As Range
Set セル範囲 = Application.InputBox(Prompt:="チェックする先頭のセルの座標を入れてください。", Type:=8)

　6.元のシートでの計算式を取得
　前処理
　パラメータはすべて取得が終わったので、そのパラメータをもとに、チェック範囲、チェック回数を求めます。
　チェック元シートの計算式は、チェック単位でその都度配列変数に格納します。
　チェック範囲の先頭と最後の座標が与えられているので、たてに何回、横に何回チェックをしなければならないのかを求めます。これを掛けたものが、チェック回数となります。
　たての回数…チェックの行数-1　割る　200の商　+1　となります。
　よこの回数…チェックの列数-1　割る　150の商　+1　となります。
　チェックは上から下、終わったら、横へという流れでおこないます。
　配列変数は二次元の配列変数、mm(j, i)とします。最初の添え字は列を表し、次の添え字は行をあらわしています。Cells()とは、逆ですので注意願います。

　さて、問題です。シート内でｎ回目のチェックをしています。チェック元のシート上の座標はわかります。では、そのセルの計算式を格納する配列変数の添え字は行列それぞれいくつでしょうか。
　配列変数では添え字1から有効とします。0ではありません。