ユーザー定義関数をボタンに登録したい

数式のエラーを回避するユーザー定義関数を作りPERSONAL.xlsに登録しました。
その後アドインに保存して、関数の挿入ダイアログボックスのユーザー定義関数から選んで使用していますが、頻繁に使用するのでボタンに登録したいのです。
どのようにすればいいのでしょうか？

ネットで検索して色々やってみてるのですが分かりません。
ボタンのマクロの登録で「PERSONAL.xla!vlookupエラー回避」と直接打ち込んで登録してみましたが、実際にボタンをクリックすると「コンパイルエラー End Subが必要です」というエラーメッセージが出ます。
どこをどうすればいいのか教えてください。よろしくお願いします。

Sub vlookupエラー回避()
'
' vlookupエラー回避 Macro
' マクロ記録日 : 2006/7/25 ユーザー名 : ********
'
Function myVLookup(Rg As Range, Area As Range, col As Integer, opt As Integer)
Dim vlk As Variant

vlk = Application.VLookup(Rg, Area, col, opt)
If IsError(vlk) Then
vlk = ""
End If

myVLookup = vlk

End Function

No.4 ベストアンサー 回答者： papayuka 回答日時： 2006/07/26 21:43

#3です。

> EQ台数!の部分が消えてしまう

Addressプロパティの引数 External を True にすると外部参照を返してきます。
他の引数はVBAヘルプを見て下さい。

Sub test2()
　　'列のみ絶対参照でローカルアドレスを表示
　　MsgBox ActiveCell.Address(False, True, xlA1, False)
　　'行のみ絶対参照で外部参照アドレスを表示
　　MsgBox ActiveCell.Address(True, False, xlA1, True)
End Sub

ちなみに先のマクロのうち Test1 を直すなら

　s = "VLookup(" & Rg.Address & "," & myArea.Address & _
　　　"," & col & "," & opt & ")"

の部分を下記に変えると上手く行くと思います。

　s = "VLookup(" & Rg.Address(External:=True) & "," & _
　　　myArea.Address(External:=True) & "," & col & "," & opt & ")"

この回答へのお礼

お忙しいところ、本当にありがとうございました！
大変よくわかりました！

***********************************

私の質問に答えてくださった皆様、本当にありがとうございました。
皆様からいただいた回答をもとに、また明日から頭をひねってマクロの勉強頑張ります。

お礼日時：2006/07/26 22:31

No.3 回答者： papayuka 回答日時： 2006/07/26 16:42

結果をセルに直接入れるのでは無く関数として入れたいなら、その式を代入する Sub を作れば良いと思います。

引数を一括指定可能な UserForm を作るとか、下記のように InputBox を使用するとか、、、

Sub Test()
Dim Rg, myArea, col As Integer, opt As Byte, s As String
On Error Resume Next
　Set Rg = Application.InputBox("検索値？" & vbCrLf & _
　　　　　　　"(F4キーで絶対←→相対)", "指定", Type:=8)
　Set myArea = Application.InputBox("範囲？" & vbCrLf & _
　　　　　　　"(F4キーで絶対←→相対)", "指定", Type:=8)
　col = Application.InputBox("列番号？", "指定", 2, Type:=1)
　opt = Application.InputBox("検索の型？", "指定", 0, Type:=1)
　s = "=myVLookup(" & Rg.Address & "," & myArea.Address & _
　　　"," & col & "," & opt & ")"
　Selection.Value = s
End Sub

あと、ユーザー定義関数は便利ですが、別のファイルからの呼出しだと人に渡した時などに問題が生じる可能性があります。
個人的には VLOOKUP のエラー回避くらいなら標準の関数で対応した方が良いと考えます。
同様のやり方で VLOOKUP と IF(ISERROR を組み合わせた例です。
これなら myVLookup が無い環境でも問題無いハズです。

Sub Test1()
Dim Rg, myArea, col As Integer, opt As Byte, s As String
On Error Resume Next
　Set Rg = Application.InputBox("検索値？" & vbCrLf & _
　　　　　　　"(F4キーで絶対←→相対)", "指定", Type:=8)
　Set myArea = Application.InputBox("範囲？" & vbCrLf & _
　　　　　　　"(F4キーで絶対←→相対)", "指定", Type:=8)
　col = Application.InputBox("列番号？", "指定", 2, Type:=1)
　opt = Application.InputBox("検索の型？", "指定", 0, Type:=1)
　s = "VLookup(" & Rg.Address & "," & myArea.Address & _
　　　"," & col & "," & opt & ")"
　Selection.Value = "=IF(ISERROR(" & s & "),""""," & s & ")"
End Sub

この回答へのお礼

ありがとうございます。

＞ユーザー定義関数は便利ですが、別のファイルからの呼出しだと人に渡した時などに問題が生じる可能性があります。

おっしゃる通りです。
データの引き込みから入力・修正まで私が一人でやっていることと、マクロを独学で勉強し始めたので、手のあいたときに勉強がてら色々トライしてみたいというのもありまして・・・。

さっそく教えていただいたマクロを試してみましたが、分からないことがあります。

今作っている表はSheet1から10まであります。
Sheet1がまとめのシートで、各営業所ごとに商品別の台数を入れこみます。
Sheet2からSheet10までは、その各商品ごとの台数データの入ったシートが並んでいます。

たとえば、検索値に「$D6（営業所コード）」、範囲に「EQ台数!$C$2:$E$10」、列番号、検索の型を入力しOKをクリックしますと、
検索値が「$D$6」、範囲が「$C$2:$E$10（EQ台数!の部分が消えてしまう）」となってしまい、入力した値と違ってしまうのです。
これはどのように修正すればよいのでしょうか？
お忙しいところ恐縮ですが、お時間のあるときにでも教えていただけると有難いです。
よろしくお願いいたします。

お礼日時：2006/07/26 17:35

No.2 回答者： keirika 回答日時： 2006/07/26 15:52

回答にならないかもしれませんが・・・

ユーザ定義関数をボタンに登録するのは不自然な感じがするのですが、通常、VLOOKUP関数の代わりにmyVLOOKUPを使用する形をとると思うのですが。

もし、関数として使用する場合は、Sub vlookupエラー回避()を削除してください。

また、ボタンに登録する場合は、FunctionをSUBに変更して、引数を特定のセルに固定して、計算結果も特定のセルに書き出すようにすれば、良いかと思います。

参考になればよいのですが・・・

この回答へのお礼

いただいたアドバイスをもとに、ひとつひとつ試しながら勉強していきます。
ありがとうございました。

お礼日時：2006/07/26 17:39

No.1 回答者： mshr1962 回答日時： 2006/07/26 15:51

ボタンに直にユーザー定義関数（Function～End Function）を登録することは出来ません。

Sub myVlookupSet()
ActiveCell.Value = "=myVlookup(Rg, Area, col, opt)"
End Sub

としてこれをボタンに登録ですね。

この回答へのお礼

ありがとうございました。勉強になりました。

お礼日時：2006/07/26 16:49