第111回目 計算式で参照されている場所の情報を集めた一覧表

○第111回目　計算式で参照されている場所の情報を集めた一覧表

　計算式の場所に飛ぶだけではなく、その情報を集めて、計算式の明細表を作れないか、というテーマです。
　これは今までのことを考えればとても簡単です。問題は、明細の様式です。
　("とても簡単"ではなく、"それほど難しくありません"と訂正します)
　1.セル座標及び計算式
　2.各項目に数値を入れた計算式
　3.計算式内の各項目の、横見出し(その列の3-5行までとか)、座標、値と計算式
　4.3を場所の数だけ繰り返す。
　ということで、十分そうです。

　さらにとなると、計算式を持つ場所の場合、さらにその計算式の項目の情報を加えるというのが考えられます。
　まずは一重とします。

　感じは、
　1.セル座標及び計算式
　2.各項目に数値を入れた計算式(lookup関数などの場合は非常に難しいので、対応するかしないかも含めて要検討)
　3.計算式内の項目の座標、値と計算式
　　(その項目が計算式である場合、計算式、その中の各項目の値と計算式
　となります。きっちりと対応するためには、二重の配列変数が必要となりますね)

　明細表のシートは新しいものを作りましょう。
　計算式から場所の項目を取り出すのが2回となりますので、この機能をサブルーチン化しておいたほうがいいでしょう。

　まずはもととなる計算式から、シート名、座標をとりだしましょう。まずはこれが最初です。
　これは前回作成した機能をコピーしてください。

　場所の情報が取得できたら、その場所に飛んでいって、その場所の計算式と値を取得します。場所の情報として、範囲指定のものがありますが、その場合は、とりあえず取得しないことにします(一番先頭の場所のものを取得することも考えられます。また、最初と最後の座標がありますので、それぞれの値を取得しコロンでつなげるというのも考えられます)。
　計算式と値の取得は次の通りです。
If InStr(p_xy_A1(i), ":") = 0 Then　　'範囲指定の場合は、:があります。
p_siki(i) = "***" & Selection.Formula
p_val(i) = Selection.Value
Else
p_siki(i) = "@@@"　　'範囲指定の場合は、計算式が取得できません。
p_val(i) = ""
End If

　計算式内容の一覧表は、新しいシートに作成します。
　シート名を指定する必要がありますが、処理のたびごと重複してしまいますので、名前の後に日付と時間をつけて重複を避けます。
b = "計算式内容" & Date & Left$(Time, 6)
とし、シート名に不適な文字を削除します。下のものは /を削除しています。他には:があります。
Do While 1
p1 = InStr(b, "/")
If p1 = 0 Then Exit Do
b = Left$(b, p1 - 1) & Mid$(b, p1 + 1)
Loop

　表示内容は、オリジナルの計算式、その中に使われている項目となり、
　その内訳は、場所の情報、計算式、値となります。それぞれに見出しをつけましょう。
C4から打ち出すことにします。
For i = 0 To p_xy_no
Cells(4 + i * 5, 3) = "シート名及び座標"
Cells(5 + i * 5, 3) = "計算式"
Cells(6 + i * 5, 3) = "値"
Cells(4 + i * 5, 4) = p_sh_name(i) & "---" & p_xy_A1(i)
Cells(5 + i * 5, 4) = p_siki(i)
Cells(6 + i * 5, 4) = p_val(i)
Next

最後に、用紙のスタイルも指定しておきます。結構大きなものとなりますので、A3の横長としました。90%の縮小としました。
　'サイズ、向きの変数値への変換
p_size = xlPaperA3
p_muki = xlLandscape
'サイズ、向きの変更
With ActiveSheet.PageSetup
.Zoom = 90
.PaperSize = p_size
.Orientation = p_muki
End With

　※付加すべき(？)機能
　1.オリジナルの計算式の項目が、計算式であった場合、さらにその先の計算式を分析する
　2.オリジナルの計算式は長い場合がある(分析したい計算式は通常長いものとなります)ので、長さをきって複数行で表示する。
　3.オリジナルの計算式に実際の数値を埋め込んだものも表示する。
　4.3との関係で、特殊な関数の場合、その結果を取得する。例えば、LOOKUP関数は、計算式で三項目は取得できますが、関数全体の結果を持ってくるということです。対象とする関数をどうするかは、ニーズいかんですが、あまり広げすぎると、すべてそれ専用の処理を付加することになるので、大変になるでしょう。ほかには、SUM関数、SUMPRODUCT関数などが考えられます。
　5.その項目のたて及び横見出しを取得する。場所の見当がつかないので、あてずっぽうとなります。例えば、たて見出しは、該当する行のA列からB列、横見出しは該当する列の3行目から5行目とかに、きめ打ちします。