こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

数式のエラーを回避するユーザー定義関数を作り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

投稿日時 - 2006-07-26 15:14:13

QNo.2300669

困ってます

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

#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 21:43:27

お礼

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

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

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

投稿日時 - 2006-07-26 22:31:32

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

結果をセルに直接入れるのでは無く関数として入れたいなら、その式を代入する 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

投稿日時 - 2006-07-26 16:42:16

お礼

ありがとうございます。

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

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

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

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

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

投稿日時 - 2006-07-26 17:35:51

ANo.2

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

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

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

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

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

投稿日時 - 2006-07-26 15:52:04

お礼

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

投稿日時 - 2006-07-26 17:39:20

ANo.1

ボタンに直にユーザー定義関数(Function~End Function)を登録することは出来ません。
Sub myVlookupSet()
ActiveCell.Value = "=myVlookup(Rg, Area, col, opt)"
End Sub

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

投稿日時 - 2006-07-26 15:51:35

お礼

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

投稿日時 - 2006-07-26 16:49:28

あなたにオススメの質問