【excel vba】エクセルファイル内にある数式の内「関数名(IF,SUM等)」のみを、同ファイル内の新しいシートに一覧化したいです。

質問

vba初心者です。(ネットからコードを拾ってきてちょっと改造できる程度)
excel 2003を使用しています。

【前提】
・「数式」「数値」「文字列」等がセルに入力されたエクセルファイルを使用する
　⇒「関数名」のみを表示する。(文字列や数値が入力されたセルは無視)
・「数式」セルには「関数」が使われているものと、そうでないものがある
・１セル内に複数の関数が使用されている場合あり(新出の関数名であればすべて抽出したい)
・検索対象シート：ブック内のすべてのシート

【質問】
findメソッドで「IF」や「SUM」というように直接関数名を指定して検索するのではなく、「関数」というククリで検索はできるのでしょうか？

その検索結果を同ファイル内に新しいシート(Sheet1)を作成し、「関数名」を一覧表示するという流れ(以下にまとめました)にしたいです。

【手順】
(1)Book1内で「関数」検索をする
(2)「関数」が見つかった場合は「Sheet1」シートを作成(関数が見つからない場合は,msgbox "該当なし")
(3)検索した「関数名」をSheet1のA1セルに入力する
(4)Book1内すべて(複数シート有り)の関数名を抽出するまで連続検索をする
　⇒A1→A2→A3→…の様に、A列の上から順に入力していく
※関数名の重複がないように一覧化できれば最高です。
※シート毎に、抽出した関数名を分けなくて大丈夫です。(あくまでファイル全体で使用されている関数名の一覧)
(5)msgbox "終了"

解決方法をご存知の方、ご教示願えませんでしょうか。

宜しくお願いいたします。

回答No.3

KenKen_SP · ベストアンサー

#1 です。

なんか質問の形式をとった作成依頼っぽい気がしなくもない。一応
突っ込み入れときます。

こんな感じでできる気がする。適当に参照設定をして試してみて下さい。
ユーザー定義関数（日本語名を含む）があっても大丈夫かと。

余談：

vArray = Range("A1:C1").Formula

で Value 同様数式の2次元配列が得られますから、速度面が問題になる
ようなら配列処理に改造してみて。

Sub sample()

　　Dim dic　　　　 As Dictionary
　　Dim reg　　　　 As RegExp
　　Dim regMatch　　As Match
　　Dim s　　　　　 As String
　　Dim rHasFormula As Range
　　Dim r　　　　　 As Range
　　Dim sh　　　　　As Worksheet

　　Set reg = New RegExp
　　Set dic = New Dictionary

　　reg.Global = True
　　reg.Pattern = "([^!-@\[\]]+)"

　　For Each sh In ActiveWorkbook.Worksheets
　　Do
　　　　On Error Resume Next
　　　　' 23: xlErrors or xlLogical or xlNumbers or xlTextValues
　　　　Set rHasFormula = sh.Cells. _
　　　　　　　　　　　　　SpecialCells(xlCellTypeFormulas, 23)
　　　　On Error GoTo 0
　　　　If rHasFormula Is Nothing Then Exit Do

　　　　For Each r In rHasFormula.Cells
　　　　　　' // 数式のセル参照文字をR1C1相対参照に固定する
　　　　　　s = Application.ConvertFormula(r.Formula, _
　　　　　　　　　　　　　　　　　　　　　 xlA1, _
　　　　　　　　　　　　　　　　　　　　　 xlR1C1, _
　　　　　　　　　　　　　　　　　　　　　 xlRelative)
　　　　　　For Each regMatch In reg.Execute(s)
　　　　　　　　Select Case UCase$(regMatch.Value)
　　　　　　　　' // セル参照文字やブール値を除外
　　　　　　　　Case "R", "C", "RC", "TRUE", "FALSE"
　　　　　　　　Case Else
　　　　　　　　　　' // Dictionary でカウントしつつ重複のないリストにする
　　　　　　　　　　 dic(regMatch.Value) = dic(regMatch.Value) + 1
　　　　　　　　End Select
　　　　　　Next
　　　　Next

　　　　Exit Do
　　Loop
　　Next sh

　　' // 出力(面倒なので適当）
　　With ThisWorkbook.Worksheets("Result")
　　　　.Activate
　　　　.Cells.Delete
　　　　With .Range("A1:B1")
　　　　　　.Font.Bold = True
　　　　　　.Value = Array("Function", "Count")
　　　　End With
　　　　.Range("A2").Resize(dic.Count).Value = Application.Transpose(dic.Keys)
　　　　.Range("B2").Resize(dic.Count).Value = Application.Transpose(dic.Items)
　　　　.Columns("A:B").AutoFit
　　End With

　　Set reg = Nothing
　　Set dic = Nothing

End Sub

お礼

KenKen_SP さん

ご回答ありがとうございます。
お礼が遅くなってごめんなさい。

作成までしていただいてありがとうございます。
実は#1のご回答を基に作成していたところ、自分の知識ではうまくいかず途方にくれていました…

#3でご回答いただいたコードを実行した結果、ほぼ私の意図通りの処理内容でした。
超感謝しています。ありがとうございました。

しかし、関数の中身で別ファイルのセルを参照していると"Result"シートの"function"列に意図しない文字列が抽出されるのです。

具体的には…
数式：=CONCATENATE([ジャンル.xlsx]Sheet1!$D$2,'[問題集.xlsx]Sheet2(2)'!$A$1)

"function"列
CONCATENATE
ジャンル
xlsx
Sheet
問題集

という感じです。
もれなくすべての関数名を抽出できていますし、「意図しない文字列」は目視でも確認できるのですが、ちょっと気になって。

正直、KenKen_SPさんのコードもまだ完全には解析できていないので、vbaの勉強も兼ねて上記問題を解決できるように考えてみます。

お手数お掛けしてすみませんでした。
本当に助かりました。ありがとうございました。

回答No.2

mt2008

何を関数とするかが難しそうですね。
アドインで使用できる関数は増やせますし、ユーザ関数も作れます。

こんなのはどうでしょう。
予めSheet1を追加して、A列に検索したい関数一覧を入れておきます。
Excel標準で使用できる関数一覧ならネットでいくらでも見つかりますからそこからコピペで……。それ以外の関数は地道に入力。

その上で、下記のマクロで検索。使用している関数の隣に１が入ります。
但し、このマクロは不完全です。関数名+「(」の有無で検索していますが、例えばセル式が「TODAY()」の場合、TODAYでもDAYでもカウントしてしまいます。
この辺りは正規表現をうまく使えば何とかなりそうな気がします。

Sub SampleMacro1()
　Dim countsheet As String
　Dim ws As Worksheet

　countsheet = "Sheet1" '<-- 集計シート名
　Application.ScreenUpdating = False

　For Each ws In Worksheets
　　If ws.Name <> countsheet Then
　　　Call SelectFormula(ws, countsheet)
　　End If
　Next
　Worksheets(countsheet).Select
　Application.ScreenUpdating = True
　MsgBox "終了"
End Sub

Sub SelectFormula(wws As Worksheet, wCountsheet As String)
　Dim rng As Range
　Dim targetRange As Range

　On Error GoTo Errtrap
　'数式セルのみ選択、数式セルが無い場合はErrTrapへ
　Set targetRange = wws.Cells.SpecialCells(xlCellTypeFormulas, 23)
　For Each rng In targetRange
　　'- Sheet1 A列の関数一覧との比較
　　For i = 1 To Worksheets(wCountsheet).Range("A65536").End(xlUp).Row
　　　rtn = InStr(rng.Formula, Worksheets(wCountsheet).Cells(i, 1) & "(")
　　　'-- 使用されている関数があればB列に1をたてる
　　　If rtn > 0 Then
　　　　Worksheets(wCountsheet).Cells(i, 2) = 1
　　　End If
　　Next i
　Next
Errtrap:
End Sub

お礼

mt2008 さん

ご回答ありがとうございます。
お礼が遅くなってしまってすみません。
コードを読み解くのに時間が掛ってしまいました…

> 何を関数とするかが難しそうですね。
そうなんです。散々考えてアイディアが浮かびませんでした…

> Excel標準で使用できる関数一覧ならネットでいくらでも見つかりますからそこからコピペで……。それ以外の関数は地道に入力。
私も最初は、すべての関数を検索すれば良いかなっと思っていたのですが、網羅するのが難しいなと。
今回検索対象となるファイルが約800あるのですが、全ファイルのユーザー関数を地道に抽出するのは大変そうです。(ユーザー関数が使用されているかも今確認できません)

でも全関数の一覧ができれば、'- Sheet1 A列の関数一覧との比較 でうまいことフラグが立ちますね。(自分で適当にファイルを作成して試してみました。)

自分なりに対応策を考えてみます。
マクロを組んで頂きありがとうございます。
勉強になりました。とても感謝しています。

回答No.1

KenKen_SP

こんにちは。

数式をもったセルは SpecialCells で参照できます。

　　On Error Resume Next
　　' 23: xlErrors or xlLogical or xlNumbers or xlTextValues
　　Set rHasFormula = ActiveSheet.Cells. _
　　　　　　　　　　　SpecialCells(xlCellTypeFormulas, 23)
　　On Error Goto 0

この結果、オブジェクト変数 rHasFormula が Nothing であれば、

　　「関数を使ったセルは無し」

とみなせます。あとは、数式をもったセル rHasFormula を For Each で
順次処理していくわけです。

数式から関数を抽出するには正規表現（RegExp）を使うのが楽でしょう。
このとき数式内の A1、B1 などのセル参照式はマッチングに都合が悪い
ですから、予め

　　s = Application.ConvertFormula(数式, xlA1, xlR1C1, xlRelative)

と相対参照の R1C1 形式に変換しておきます。これでセル参照文字は RC[]
といった自明の文字に固定できますから、除外し易くなりますよね。
マッチングパターンは、

　　reg.Pattern = "([a-zA-Z]+)"　　　または
　　reg.Pattern = "([^!-@\[\]]+)"

とかでしょうか、、工夫してみて下さい。

重複なしのリストは、Scripting.Dictionary を使います。