evaluateを使ったユーザ定義関数でエラー

質問

個人用マクロブックの標準モジュールに

Function Eval(str As String) As Single
Eval = Evaluate(str)
Application.Volatile
End Function

と記入し、文字列を数式に変換する関数Eval()を定義しました。

それを使用して、B7に入力された値によって、計算式を振り分ける数式を

=PERSONAL.XLSB!Eval(VLOOKUP($B7,$AQ$2:$BA$53,2,0))

とし、計算を振り分けていたのですが
振り分け先の式（vlookupで検索した式）が以下の式の時valueエラーになってしまいました。

IF(MONTH(Q1)=5,ROW(),COLUMN())

尚

IF(MONTH(Q1)=5,,COLUMN())
IF(MONTH(Q1)=5,100,COLUMN())
IF(MONTH(Q1)=5,SUM(A1:A2),COLUMN())

としたときはエラーになりません

IF(MONTH(Q1)=5,"100",COLUMN())はエラーになります。

何故エラーになるのか、修正方法はどうしたらいいのかわかりません。

ご存知の方いらっしゃいましたら、どうかご教授願います。

回答No.3

Wendy02 · ベストアンサー

>IF(MONTH(Q1)=5,index($1:$1048576,ROW()-1,COLUMN()-1),COLUMN())

もともと、この数式はアブナイですね。ユーザー定義関数の中で、無限ループが発生しています。結果が再帰的に、再計算させていますので、このような数式は書かないほうがよいのですが、数式上では、循環参照です。なぜ、ROW() や、COLUMN()では、無限ループにならないのかは、良く分かりませんが、たぶん、配列の中に逃がしているのだと思います。以下のような数式の場合、自己の数式位置を参照しないようにしています。また、一回きりなら、無限ループから抜ける方法はあるのですが、常時使う関数ですと、その方法がありません。これに関しては、無理だと思います。

しかし、数式が多岐になるなら別ですが、この数式ですと、完全なユーザー定義関数で書いても良いような気がしますね。

Public Function MyFunc(arg1 As Variant, Optional i As Long = 1, Optional j As Long = 1)
Dim r As Range, r1 As Range
Dim buf As Variant
If VarType(arg1) = vbBoolean Then
　Set r = Application.Caller
　If i = 0 And j = 0 Then　'循環参照を避ける
　　 MyFunc = CVErr(xlErrNA)
　　 Exit Function
　End If
　If arg1 Then
　　Set r1 = r.Offset(i, j)
　　buf = ActiveSheet.Cells(r1.Row, r1.Column)
　Else
　　buf = r.Column
　End If
End If
If Not IsError(buf) Then
　MyFunc = buf
End If
Set r = Nothing
End Function

'//
数式例：（ただし、以下は、アドインにしています）

=MyFunc(MONTH($Q$1)=ROW(A5),-1,-1)

お礼

うわあ…無限ループ発生ですか。
どうやらこのユーザー定義関数を普段使いにするのは、わたしにはとても難易度が高いようです。
しかしおかげさまでこの方法の検討に区切りがつきました。

二度も丁寧に教えて下さって本当にありがとうございました！
お陰さまで大変助かりました。
これからも一層精進します。

回答No.2

Wendy02

こういうユーザー定義関数は、かなり特殊だと思います。

ふつうは、名前登録から作りますが、別のセキュリティ・メッセージが出てきてしまいます。たぶん、ご質問者さんは、VBAには、ある程度、腕に覚えがあるようにお見受けしますので、私が知っている注意点を、書かせていただきます。

余計なお世話だと思えば、無視していただいて良いです。ふつうは、必要のない知識です。

まず、ワークシートで使うユーザー定義関数の戻り値は、本来はVariant型なのです。その理由は、本格的には、CVErr を利用し、エラー値を吐き出すからです。必ず、数値しか返さないなら、Double型になります。

Public キーワードは、あまり意味がありませんが、見かけだけの理由で、ワークシートで使うということになると、頭につけるという習慣があるそうです。（長い間、私は疑問に感じていましたが、その分野の偉い人がそういうなら仕方がありません。）

次に、str という引数名はやめたほうがよいです。strというのは、VBA/VB関数名です。予約語とはならないまでも、やめたほうがよいです。

次に、一旦、引数を、buf というものに入れて、出力させないほうがよいと思います。ご存知かとは思いますが、ワークシートのCOLUMN関数やROW関数は、配列で出力しますから、一旦、配列から値を取り出したほうがよいかと思います。

Volatileは、引数を再定義すれば、再計算しますから、必要がない限りは、いれなくてもよいです。あまり習慣化しないほうがよいです。ワークシート全体に及ぶようになります。

>=PERSONAL.XLSB!Eval(VLOOKUP($B7,$AQ$2:$BA$53,2,0))
ふつうは、グローバル・関数として、特定のブックから呼び出すような関数にはしませんから、それは、アドイン型で作ります。頭にファイル名が不要になります。

Public Function Eval(strTxt As String) As Variant
　Dim buf As Variant
　buf = Evaluate(strTxt)
　If IsArray(buf) Then　'←本来は、IsError を先頭にして、エラーを分岐する。
　　Eval = buf(1)
　Else
　　Eval = buf
　End If
　Application.Volatile　'←本来は、あまり使いません
End Function

補足

丁寧なご回答ありがとうございます！
関数を定義しなおしたところ、お陰さまでエラーがなおりました。
初めてユーザー定義関数を使ってみたのですが、ワークシート関数のデータ型等大変勉強になりました。

直ったところで振り分け先の式

IF(MONTH(Q1)=5,ROW(),COLUMN())

を

IF(MONTH(Q1)=5,index($1:$1048576,row()-1,column()-1),COLUMN())

にしてみたのですが、今度はこの式の真の場合がvalueエラーになりました。
row()-1,column()-1にあたるセルには数値が入っています。
…index関数の中にrow()やcolumn()を入れたのがまずかったのでしょうか…
質問続きですみません。

回答No.1

keithin

Function Eval(str As String) As Single
を
Function Eval(str As String) As Variant
に変えてみてください。

個別関数の内部的な事情で，どうやら返ってくるモノが微妙に違うものがあるようです。

元のsingleの状態で，「"100"」なら（暗黙の型変換が作動して）計算できたと思いますが「abc」では文字列を数値指定の関数の値として格納しようとしてエラーになります。の延長線と考えられます。

お礼

回答ありがとうございます。

なるほど～ワークシートで使うときもデータ型には十分気をつけねばいけないのですね…。