ユーザー定義関数が入力されたセルを調べたい

質問

Excel でユーザー定義関数を作成し、アドインにして仕事で使っています。
これらの関数を使用したブックを外部に配布する時にユーザー定義関数を値に変換してから配布したいのですが、ユーザー定義関数とワークシート関数の区別の方法が判りません（外部にアドインは配布しません）。
Dim r As Range
Dim f As String
For Each r In Activesheet.UsedRange
If r.HasFormula Then
f = r.Formula
Do
' f がユーザー定義関数の一覧の中に見つかれば、セル r の色を変えてループ脱出
Loop
End If
Next
という方法を試みましたが、UsedRange の大きさやユーザー定義関数の数が多いことも影響して結構な時間が掛かってしまいます。
ユーザー定義関数とワークシート関数を区別する方法があれば、Do～Loop を回さなくて済むので、時間短縮できるのではないかと考えています。
よろしくお願いします。

回答No.3

SI299792 · ベストアンサー

他の方が指摘した通り、SpecialCellsで数式の入ったセルだけに絞ると早くなります。
また、ループで回すより、
関数一覧のシートを作って、Vlookup で検索方法をTrueにすると、２分法での検索なので、早くなります。
「関数一覧」のＡ列に関数一覧を入れて、昇順に並べ替えます。
このプログラムはかんすうが３つになっています。関数の数に合わせて直して下さい。

関数がどのように入っているか判りません。とりあえず、関数が１番左にあり、
=Jibunnnokannsu(A)
のような形だけとしました。
=Sin(Jibunnnokannsu(A))
の様に１番左に関数がない場合は反応しません。このようなものがあるなら、
RegExpなどで分割して１つづつ確認する必要があります。
'
Option Explicit
'
Sub Macro1()
'
　　Dim R As Range
　　Dim F As String
　　Dim Length As Integer
　　Dim Vlookup As String
'
　　For Each R In Cells.SpecialCells(xlCellTypeFormulas)
　　　　F = R.Formula
　　　　Length = InStr(F, "(") - 2
　　　　F = Mid(F, 2, Length)
　　　　On Error Resume Next
　　　　Vlookup = WorksheetFunction.Vlookup(F, [関数一覧!A1:A3], 1)
　　　　On Error GoTo 0
'
　　　　If Vlookup = F Then
　　　　　　R.Interior.Color = vbRed
　　　　End If
　　Next R
End Sub

お礼

回答ありがとうございました。
なるほど，ワークシート関数を使うことでスピードアップするのですね．
自作関数の一覧は，すでにシートにありますので，VLOOKUP関数で対応することにします．

回答No.2

HohoPapa

セルには、
ユーザ定義関数と出来合いの関数を組み合わせた計算式
を埋めることができますので、
>ユーザー定義関数とワークシート関数の区別の方法が判りません
おそらく、区別することはできないと思いますし
ちょっと探して見ましたがなさそうです。

だとすると課題は、
do～loopの外側にあるのではなく内側になりましょう。

内側のコードがわからないので推測ですが
私なら、課題ユーザ定義関数群の名前に共通の
プレフィックス（接頭辞）またはサフィックス（接尾辞）を設け
計算式の文字列にこれらが含まれるかどうかで判定します。

回答No.1

imogasi

長年OKWAVEの質問を見ているが、珍しいタイプの質問ですね。
小生には、力足らずだと思いますが、現状での感想的内容ですが。
（１）システム的（MSが設けたレベル）に、VBAで選別する方法はないのでは。
エクセルのワークシート関数と同じ名前の関数名は、ユーザー関数では作れない（使えない）と思うので、この「関数の名称に頼って探す」、しか方法がないのでは。
（２）やはり仕事（個人利用を越えて）として、もし質問のニーズがあるなら、システムを作った時点で、エクセル利用のための「ノート」「メモ」を作るべきかと。そこにユーザー関数と意義と使い方（引数のこと等）記述しておく、ということ。
（一般にエクセルレベル以上のシステムを作った時（あるいは受託業者）は、ドキュメンテーションとして、紙やワード等やWEBで、資料を残す体制にするはず。エクセル・オンリーやフロム・エクセル止まりの人はこういう習慣は思わないだろうが。やはり必要で、そこに書き残すべきかと。）
ーーー
微力ながら下記をやってみた。WEB記事のコードを一部利用。
標準モジュールに
Sub 数式の入力されているセルを取得選択する()
Dim y As Range
On Error GoTo ErrHandl
k = 1: l = 1
Dim rng As Range
Set rng = Cells.SpecialCells(xlCellTypeFormulas)
rng.Select
'---
For Each cl In Selection
'MsgBox cl.Address & "=" & cl.Formula
x = cl.Formula
x = Replace(x, "=", "")
p = InStr(x, "(") - 1
x = Mid(x, 1, p)
'Cells(k, "G") = x
' k = k + 1
'---
Set y = Range("G:G").Find(x)
If y Is Nothing Then
Cells(l, "H") = x
l = l + 1
End If

Next
'--
Exit Sub
ErrHandl:
MsgBox "アクティブシートに数式の入力されたセルは存在しません。"
Err.Clear
End Sub
これで全セルを総なめするのでなく、数式の入ったセルだけ問題にする。
どれだけ早くなるかわからないが。
G列に既存使用の関数名、H列に今回出現した関数名を表示。
ーー
これで１シートで使っている関数名のリストはが出せる。
G列に、MSが作った、５－６百ある関数名がWEB記事にでも、網羅的にあればコピーできるが、見たことない、が。
代表的な（ユーザー定義以外のMSが作った関数のうち）１００個か、加えて多少の関数名をG列にセットできれば、本件の役に立つのでは。
上記G列は、MSが作ったワークシート関数を１列に並べておく。１００％質問のニーズに、完全でなくても、エクセル経験者なら、利用している関数名リストから、ユーザー関数を排除することは可能だろう。また使用せる番地もシートに作ることはたやすいと思う。
するとH列にそれ以外の関数名が出るので、ユーザー関数名かどうか、判断しやすいと思った。今回のシートで初めて使われたMSの関数も少数は混じるが。
　ただし欠点があって、上記は、先頭の関数だけで、関数を組み合わている場合の第2以下の関数名は捉えてない。これが致命傷かも。＝IF（）関数なら（）内の実質意味のある関数名は拾えてない。
ーー
WEBに
http://mt-soft.sakura.ne.jp/kyozai/excel_mid/150_macro/50_userfunction/index.html#ex-function
ユーザー定義関数の作り方
という記事があって、
Description:=
Category:=
という説明がある。小生などは、ここまで設定して、ユーザー定義関数を使いこなして(他のユーザーに提供して使いやすくして）ないが、もし今後、そういうものを完備しておくと、ユーザー定義関数も、VBAの網に引っかかるかも（テストしてないし、どういうコードで拾えるかはわからず、夢想部分）。

お礼

ご回答ありがとうございます。
作成したユーザー定義関数は、主に社内データベースからデータを参照する関数です。
関数にすることで、検索キーをセルに入れておけば、データベースから引き出せるというのがミソです。
社内で使っているだけなら問題ないのですが、作成したExcel資料を社外に配布する時に関数の結果を値にしておかないと、社外でエラーの嵐になってしまいます。ご指摘のマニュアル作成や使用者への指導は実施しておりますので、そちらの心配はありません。