第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ではありません。

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

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

　今回は力を抜いて、前回で求めたエラー箇所のカウントと、その場所への移動を考えてみます。
　ある場所への移動は、既に計算式中の座標に移動でまとめましたので、計算式中の座標を求める代わりに、エラーの場所の座標を取得することで可能となります。
　しかしながら、エラーを対象とするので、エラー件数の目途がたちません。一定の件数以上になった場合おしまい、とするやり方もありますが、ここではさらに簡単に、検索範囲の一番最初のエラーを見つけたらおしまいとします。なお、エラー件数は全範囲で調べます。したがって、移動したいエラーの色を指定することにします(移動先のエラーとエラー件数カウントでの色の範囲がちぐはぐとなりますが、あまり気にしないでください。エラーの座標の蓄積はあまり意味がないような気がします。実際、計算式チェックをやってみますと、エラーの件数が多くてしょうがないからです)。

　チェック範囲内のエラー箇所－これは色で判別する－の数をカウントします。
　範囲内のセルを一つずつ調べていき、セルの色が該当する色かどうかで件数をカウントします。
　エラーの内容は、
　　46⇒赤色＝計算式を構成する項目数が異なる
　　27⇒黄色＝座標の偏差が基準値と違う
　　44⇒オレンジ色＝チェック1(ニ行目による)
　　43⇒きみどり色＝チェック1(一行目による)

　これまでのチェックは、調べる範囲を指定していましたが、ここでは使い勝手がいいように、自動的にそのシートのおおよその有効範囲を調べます。それをまずはチェック範囲とします。そのうちの一部も可能とします。すなわち、カーソルで指定する位置を先頭として、最後は有効範囲の端とするものです。

　そのシートのおおよその有効範囲とはどうやって求めるのか…。
　先頭の位置をカーソルで指定し、行数と列数を与え、範囲を求める。これでは範囲を指定しているのと同じです。先頭のセルを指定するのは必要ですが、それだけで何とか求められないか…。
　おおよそですので、いい加減な方法をとります。
　ボーリング調査です。
　行を例として取り上げて見ましょう。
　列を6個ほど決めて、その列の最終有効セルを求めます。求められた6つのセルの行数の一番大きいところを行の有効範囲とします。
　全部おこなえば正確な最大有効行数がでますが、それまでやる必要はないのではということです。
　同様に最大有効列は、行を適当に6つほど取り、その中で一番大きな列数を求めればいいのです。
　ある列の行の一番の端はどうやって求めるのでしょうか。上から移動させていったのでは、埒があきません。一番下から上に向かって調べるのです。同様に列は、一番右端の列から左に向かって調べるのです。
Range("h65536").Select
Selection.End(xlUp).Select '表の上端へ
その場所の行数は、 Selection.Row です。
Range("iv50").Select
Selection.End(xlToLeft).Select '表の左端へ
その場所の列数は Selection.Column です。
　このボーリング調査でこのシートのおおよその端がわかります。先頭位置はカーソルで与えますので、これでチェックの範囲は決まります。

　このマクロはかなり簡単だと思われたのですが…。
　該当する色の場所に止まりません。素通りで終了してしまうのです。
　違う色コードを入力しても止まりません。
　はてどうしたのでしょうかと、頭を抱えました。

　そこであることに気がつきました。それは数字に見えるが文字である場合があるということです。
　この場合もそれでした。数字として入れたつもりなのですが、それが文字列となっていて、それを数値変換してくれるのが普通なのですが、ある場合は、その変換がなされずに、文字列のまま認識されてしまい、入力した色コードに一致したセルがないということになったのです。入力値をVALで数値化したら、うまくいきました。
　盲点です。お気を付けください。

　もう一つの失敗は、列と行の二重のfor文であるので、該当するセルを見つけた場合、二重のfor文を抜け出さなければならないことでしょう。一回だけでは該当する場所が最後の列の最初の行の場所になってしまいます。

　今回のマクロは、マクロ的には紹介すべきものがありませんが、おおよそのシートの有効範囲を探す、というのが面白かったかもしれません。