• ベストアンサー
※ ChatGPTを利用し、要約された質問です(原文:ユーザー定義関数が入力されたセルを調べたい)

Excelでユーザー定義関数の区別方法とは?

このQ&Aのポイント
  • Excelでユーザー定義関数を使っている際に、ユーザー定義関数とワークシート関数を区別する方法が知りたいです。
  • ユーザー定義関数を値に変換してブックを外部に配布したいときに、ユーザー定義関数を見つける方法を教えてください。
  • ユーザー定義関数を見つけるために、UsedRangeの大きさやユーザー定義関数の数が影響して時間がかかる問題を解決したいです。

質問者が選んだベストアンサー

  • ベストアンサー
  • SI299792
  • ベストアンサー率47% (773/1617)
回答No.3

他の方が指摘した通り、SpecialCellsで数式の入ったセルだけに絞ると早くなります。 また、ループで回すより、 関数一覧のシートを作って、Vlookup で検索方法をTrueにすると、2分法での検索なので、早くなります。 「関数一覧」のA列に関数一覧を入れて、昇順に並べ替えます。 このプログラムはかんすうが3つになっています。関数の数に合わせて直して下さい。 関数がどのように入っているか判りません。とりあえず、関数が1番左にあり、 =Jibunnnokannsu(A) のような形だけとしました。 =Sin(Jibunnnokannsu(A)) の様に1番左に関数がない場合は反応しません。このようなものがあるなら、 RegExpなどで分割して1つづつ確認する必要があります。 ' 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

masnoske
質問者

お礼

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

その他の回答 (2)

  • HohoPapa
  • ベストアンサー率65% (455/693)
回答No.2

セルには、 ユーザ定義関数と出来合いの関数を組み合わせた計算式 を埋めることができますので、 >ユーザー定義関数とワークシート関数の区別の方法が判りません おそらく、区別することはできないと思いますし ちょっと探して見ましたがなさそうです。 だとすると課題は、 do~loopの外側にあるのではなく内側になりましょう。 内側のコードがわからないので推測ですが 私なら、課題ユーザ定義関数群の名前に共通の プレフィックス(接頭辞)またはサフィックス(接尾辞)を設け 計算式の文字列にこれらが含まれるかどうかで判定します。

  • imogasi
  • ベストアンサー率27% (4737/17069)
回答No.1

長年OKWAVEの質問を見ているが、珍しいタイプの質問ですね。 小生には、力足らずだと思いますが、現状での感想的内容ですが。 (1)システム的(MSが設けたレベル)に、VBAで選別する方法はないのでは。 エクセルのワークシート関数と同じ名前の関数名は、ユーザー関数では作れない(使えない)と思うので、この「関数の名称に頼って探す」、しか方法がないのでは。 (2)やはり仕事(個人利用を越えて)として、もし質問のニーズがあるなら、システムを作った時点で、エクセル利用のための「ノート」「メモ」を作るべきかと。そこにユーザー関数と意義と使い方(引数のこと等)記述しておく、ということ。 (一般にエクセルレベル以上のシステムを作った時(あるいは受託業者)は、ドキュメンテーションとして、紙やワード等や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列に今回出現した関数名を表示。 ーー これで1シートで使っている関数名のリストはが出せる。 G列に、MSが作った、5-6百ある関数名がWEB記事にでも、網羅的にあればコピーできるが、見たことない、が。 代表的な(ユーザー定義以外のMSが作った関数のうち)100個か、加えて多少の関数名をG列にセットできれば、本件の役に立つのでは。 上記G列は、MSが作ったワークシート関数を1列に並べておく。100%質問のニーズに、完全でなくても、エクセル経験者なら、利用している関数名リストから、ユーザー関数を排除することは可能だろう。また使用せる番地もシートに作ることはたやすいと思う。 すると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の網に引っかかるかも(テストしてないし、どういうコードで拾えるかはわからず、夢想部分)。

masnoske
質問者

お礼

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

関連するQ&A

専門家に質問してみよう