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

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

　3.座標の偏位が途中からずれているぞ、というテーマです。
　まずは、どういうことをチェックするか(どのような機能を持たせるか)などを整理しておきます。
　計算式から座標(パス名、シート名を含む)及び定数を抜き出し、それを比較する。座標の列記号は列番号に変換します。上下の形式で、座標の行列番号に規則性があるかどうかをみます。また、パス名・シート名、定数は同じものが使われているかどうかみます。
　規則性の基準は一列ブロックのほぼ中央のセルとその下のセルの間において、列番号の偏差、行番号の偏差、定数などの差を求め、それを偏差の基準値とします。
　以下の順で色設定の優先度が強くなるとしました。
・計算式が入っていないセル
・比較項目の数が上下で異なっているセル。
・偏差(下の計算式から算出)が、基準となる偏差と違うセル(パス名・シート名が異なるものも含む)。
重複している座標はそのまま整理せず使用します。
関数、演算子等は、削除しているのでチェックの対象外となります。
　(関数については、前回のチェックマクロで十分だと思います)
　デバックのため変数値のダンプリストの出力の有無を入れます。出力場所は中で指定します。

　流れです。
　1.(チェックしたい範囲が指定された状態からはじめます)指定された範囲の列番号、行番号、列数、行数を取得します。
　2.(一列単位で処理します)計算式を一つずつ取得して、不要なもの(関数、四則演算子など)を削除して、座標などの必要な項目をカンマで区切ります。残るのは、パス名･シート名、座標、定数です。パス名･シート名は、それ用の配列変数に入れます。座標は、列番号と行番号を求めてそれ用の配列変数に入れます。これも別々の配列変数に入れます。定数は列番号を入れる配列変数にのみに入れます。
　　これを一列の中のすべてのセルについては行います。
　3.ブロックの中央あたりのセルを選び出し、その前の行との列の偏差、行の偏差、定数の偏差(通常は0でしょうね)を求めます。チェックの基準値となります。
　4.先頭の次のセルからチェックです。計算式の入っていないセルはチェックしません。計算式なしのセルは最後に色付けをします。
　　パス名･シート名は等しいことが正当条件です。
　　列番号、行番号、定数は、前のセルとの偏差が基準値と等しいことが正当条件です。

　　ここで問題が起きました。チェックする項目数が異なる場合があることです。したがって、項目数が基準のセルと異なる場合もエラーにします。
　　とりあえず、列番号、行番号、定数用の配列変数は0で初期化しているので、項目数が異なる場合でも、チェックは基準のセルが持っている項目数分だけ行いますので、チェックしてもエラーになりません。
　5.色をつける順番は、偏差等が異なる、チェック項目数が異なる、計算式がないとなります。
　6.一ブロック分が終了したので、必要な配列変数を初期化して、次の列に処理を移します。
　7.全部終了したら、ダンプリストを取るかとらないかの指定をします。出力場所はこの時、指定します。配列変数は、当然最後の列の状態しかありません。ダンプリストは、問題があるところに限定し処理を走らせてから、取ってください。

　具体的にしていきます。(1.は定番なので省略します)
まずは配列の変数の紹介です。
Dim mm_sh(100, 200)…パス名･シート名用(添え字の最初は行をあらわし、次はその計算式の中の項目を表す。以下同じ)
Dim mm1(100, 200)…列番号･定数用
Dim mm2(100, 200)…行番号用
Dim hensa1(100)…列番号･定数用の偏差
Dim hensa2(100)…行番号用の偏差

　2.計算式を一つずつ取得して、座標などの必要な項目を取得する。
　これも前とかでやったものです。
　まずは、パス名にあるコロンを変えます。C:とかのコロンです。ここでは、C:、D:、F:のコロンをC_、D_、F_にします。

'計算式の取得
　　a = "***" & Cells(gyo1 + ii - 1, retu1 + jj - 1).Formula
　　If InStr(a, "=") = 0 Then a = "" '計算式でない場合はクリアする
　　b = a
'ドライブ名のつなぎの置き換え。 :を _へ
　　del_mojis(1) = "C:"
　　del_mojis(2) = "D:"
　　del_mojis(3) = "F:"
　　For i = 1 To 3
　　 If del_mojis(i) <> "" Then
　　　　Do While 1
　　　　 p1 = InStr(b, del_mojis(i))
　　　　 If p1 = 0 Then Exit Do
　　　　 b = Left$(b, p1 - 1) & Left(del_mojis(i), 1) & "_" & Mid$(b, p1 + Len(del_mojis(i)))
　　　　Loop
　　 End If
　　Next
'関数名の削除(省略)
"ROUND"、"LOOKUP"、"IF"、"INT"、"SIGN"、"SUMPRODUCT"、"INDIRECT"
"ADDRESS"、"MAX"、 "MIN"、 "SUM"
　を削除します。
'特殊文字の置き換え(省略)
"("、")"、""""、"<"、">"、"="、"*"、"/"、"-"、"+"
":"、"^"、",,,,,"、",,,,"、",,,"、",,"
　をカンマ一つに置き換えます。
'カンマで区切られた項目の取得
Do loop文でカンマのある位置を元に項目を分解して配列変数に入れます。最後の一個に気をつけてください(文字列中にカンマがない場合は、残っているのが最後の項目になります。これを取得するのに注意してください)。
これはDo loop文の外で取得します。以下の文では省略しています。また、取得した項目数を求めておいてください。
　　Do While 1
　　 p1 = InStr(b, ",")
　　 If p1 = 0 Then Exit Do
　　 If Left$(b, p1 - 1) <> "" Then
　　　　p_xy_A1(i) = Left$(b, p1 - 1)
　　　　i = i + 1
　　 End If
　　 b = Mid$(b, p1 + 1)
　　Loop
'パス名･シート名と座標等の分離。座標でシート名がない場合は、当該シート名
　パス名･シート名があるかないかは、"!"があるかないかで調べます。
　定数の場合は、シート名はヌルにしています。
　　For i = 0 To p_xy_no　　'これは項目数(0からカウント)
　　 p1 = InStr(p_xy_A1(i), "!")
　　 If p1 <> 0 Then
　　　　 p_sh_name(i) = Left$(p_xy_A1(i), p1 - 1)
　　　　 p_xy_A1(i) = Mid$(p_xy_A1(i), p1 + 1)
　　 ElseIf IsNumeric(p_xy_A1(i)) = False Then
　　　　 p_sh_name(i) = ActiveSheet.Name
　　 Else
　　　　 p_sh_name(i) = ""
　　 End If
　　Next
'シート名、列番号、行番号を格納する。mm1(ii, 0)は項目の個数(1からのカウント)です。
今まで取得したものと、この時にこれを入れるmm1(ii, 0)とは、一つずれているので注意してください。
座標の場合、座標から列番号と、行番号を求めます。
　　　　mm1(ii, 0) = p_xy_no + 1　'iiは行の値です。
　　　　For k = 1 To p_xy_no + 1
　　　　 mm_sh(ii, k) = p_sh_name(k - 1)
　　　　 If p_xy_A1(k - 1) <> "" And IsNumeric(p_xy_A1(k - 1)) = False Then
　　　　　　mm1(ii, k) = Range(p_xy_A1(k - 1)).Column
　　　　　　mm2(ii, k) = Range(p_xy_A1(k - 1)).Row
　　　　 ElseIf IsNumeric(p_xy_A1(k - 1)) = True Then 'これは定数の場合です。
　　　　　　mm1(ii, k) = p_xy_A1(k - 1)
　　　　　　mm2(ii, k) = 0
　　　　 Else
　　　　　　mm1(ii, k) = 0
　　　　　　mm2(ii, k) = 0
　　　　 End If
　　　　Next k
※これでやっと一つのセルの計算式の分解が終わりましたので、次のセルに処理を移します。
Next ii

※長くなりましたので、今回はこれでおしまい。