• ベストアンサー

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

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

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

  • ベストアンサー
  • papayuka
  • ベストアンサー率45% (1388/3066)
回答No.4

#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 & ")"

akkomails
質問者

お礼

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

その他の回答 (3)

  • papayuka
  • ベストアンサー率45% (1388/3066)
回答No.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

akkomails
質問者

お礼

ありがとうございます。 >ユーザー定義関数は便利ですが、別のファイルからの呼出しだと人に渡した時などに問題が生じる可能性があります。 おっしゃる通りです。 データの引き込みから入力・修正まで私が一人でやっていることと、マクロを独学で勉強し始めたので、手のあいたときに勉強がてら色々トライしてみたいというのもありまして・・・。 さっそく教えていただいたマクロを試してみましたが、分からないことがあります。 今作っている表はSheet1から10まであります。 Sheet1がまとめのシートで、各営業所ごとに商品別の台数を入れこみます。 Sheet2からSheet10までは、その各商品ごとの台数データの入ったシートが並んでいます。 たとえば、検索値に「$D6(営業所コード)」、範囲に「EQ台数!$C$2:$E$10」、列番号、検索の型を入力しOKをクリックしますと、 検索値が「$D$6」、範囲が「$C$2:$E$10(EQ台数!の部分が消えてしまう)」となってしまい、入力した値と違ってしまうのです。 これはどのように修正すればよいのでしょうか? お忙しいところ恐縮ですが、お時間のあるときにでも教えていただけると有難いです。 よろしくお願いいたします。

  • keirika
  • ベストアンサー率42% (279/658)
回答No.2

回答にならないかもしれませんが・・・ ユーザ定義関数をボタンに登録するのは不自然な感じがするのですが、通常、VLOOKUP関数の代わりにmyVLOOKUPを使用する形をとると思うのですが。 もし、関数として使用する場合は、Sub vlookupエラー回避()を削除してください。 また、ボタンに登録する場合は、FunctionをSUBに変更して、引数を特定のセルに固定して、計算結果も特定のセルに書き出すようにすれば、良いかと思います。 参考になればよいのですが・・・

akkomails
質問者

お礼

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

  • mshr1962
  • ベストアンサー率39% (7418/18948)
回答No.1

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

akkomails
質問者

お礼

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

関連するQ&A

  • 関数の引数に複数のユーザ定義型変数を使いたい

    一つの関数の一つのパラメータが異なるユーザ定義型を受けられるようにしたいのですが、どのようにすればいいのでしょうか? Public Type TypeA strCodeA As String intNumA As Integer End Type Public Type TypeB strCodeB As String intNumB As Integer End Type Public Function funcTest(arg1 As Variant) As Integer (略) End Function と記述して関数を Dim datA As TypeA iResult = funcTest(datA) と呼び出そうとすると、次のようなエラーメッセージが表示されて進みません。 コンパイルエラー: パブリックオブジェクトモジュールで定義されたユーザー定義型に限り、変数に割り当てることができ、実行時バインディングの関数に渡すことができます。

  • EXCELユーザー関数でISERRORを使うには?

    皆様、いつもお世話になっています。 0で除算すると、エラーになるのですが、それを回避するユーザー関数を作ろうと思っています。 下記のように作ったのですが、#VALUE!と、表示され、上手くエラーを回避出来ていません。 どのように、改良したら良いか教えて下さい。 Function DD(A As Integer, B As Integer) Dim RT As Range RT = A / B If Application.WorksheetFunction.IsError(RT) Then DD = "" Else DD = RT End If End Function

  • エクセルのユーザー定義関数呼び出し方法

    エクセルでユーザー定義関数を作成し personal.xls に登録しておいても,関数名だけでは#NAME?エラーとなり呼び出せません.personal.xls!関数名() と入力すれば呼び出せますが,スマートではありません.作成した関数を複数のブックから使いたい場合,関数名だけで呼び出せる方法がないでしょうか.

  • Excelのユーザー定義関数について。

    ネットで調べて「アルファベットのみを半角にする」というユーザー定義関数を発見し使用していますが、 この関数にある特定の文字を変換する条件を追加することはできますでしょうか。 例えば、リンゴ という文字がセル内にある場合、ミカン に変換されて表示されるという条件を下記のユーザー定義関数に加える事ができますか。 もしくは、新たに別のユーザー定義関数を作るしかないのでしょうか。アドバイスよろしくお願いします。m(_ _ )m Function ASC_A(str As String) As String Dim i As Integer For i = 1 To Len(str) If Mid(str, i, 1) Like "[a-zA-Z]" Then Mid(str, i, 1) = StrConv(Mid(str, i, 1), vbNarrow) End If Next ASC_A = str End Function

  • EXCEL VBA ユーザー定義関数について。

    EXCEL VBA ユーザー定義関数について。 例) FUNCTION Test(a as integer, b as integer,c as integer) as integer とした場合、に添付図のような   =Test(数値1, 数値2, 数値3) のような説明は表示できないのでしょうか? ご存知の方よろしくお願いします。

  • ユーザー定義関数の再計算

    ユーザー定義関数を作りました。 ところが、この関数が自動再計算をしてくれません。 どうしたら自動再計算するようになるのでしょうか? よろしくお願いします。 ちなにこの関数は、自分のシートのB2とsheet1~sheet4のB9を比較して、正しければB9の4つ右のセルの値を合計して返すものです。 =SheetLook($B$2,"sheet1,sheet2,sheet3,sheet4",B9,4) コードです。 Function SheetLook(参照元 As Variant, 比較対象シート As String, 比較対象セル As Range, 参照セル位置 As Integer) As Variant   Dim i As Integer   Dim rng As Range   Dim sss As Variant   Dim kei As Variant   Dim cnt As Integer   sss = Split(比較対象シート, ",")   kei = 0   cnt = 0   For i = 0 To UBound(sss)     Set rng = Sheets(sss(i)).Range(比較対象セル.Address)     If 参照元 = rng Then       kei = kei + rng.Offset(0, 参照セル位置)       cnt = cnt + 1     End If   Next   If cnt <> 0 Then     SheetLook = kei   Else     SheetLook = ""   End If End Function

  • 配列を返すVBAユーザー定義関数の書き方

    VBAで、商と余りを返す整数割算のユーザー定義関数を書こうとしています。セルA1に割られる数、B1に割る数が書いてあり、C1とD1に「=xdiv("A1:B1")」をCtrl+Shift+Enterで入力します。C1に商、D1に余りを返すようにしたいと思ってます。 以下のように書いてみたのですが、戻り値の書き方がどうもよくわかりません… Function xdiv(ByRef d() As Integer) As Integer   ............. End Function よろしくお願いします。

  • Excel VBA オプションボタンについて

    こんばんは オプションボタンが5つあり、 登録ボタンが1つあるユーザーフォームを作りました。 このオプションにチェックを入れずに登録ボタンを押したときに 「必ず選択してください。」とメッセージを表示し、再度入力させるようにしたいのですが、どうしたらよいのでしょうか。 Excelのバージョンは2003です。 調べたところ (1)で動きそうだ。ということが判ったのですがチェックを入れ値がtrueになるとエラーが発生して止まってしまいます。(理由がわかりません) Private Sub commandbutton2_click() Dim opt As ControlFormat, flg As Boolean flg = False For Each opt In frame1.Controls If opt.Value = True Then '←ここの行でtrueだった場合のエラーが発生してしまう。 flg = True Selection.Value = opt.Caption End If Next Unload userform1 End Sub (2)この方法で何とか動いたのですが、初めの方に書いたとおり、オプションボタンが選択されずに登録ボタンが押された場合、チェックするように促すメッセージを表示する方法がわかりません。また、できればユーザーホームの×ボタンを押せなくする方法もしくは、閉じられた場合にマクロを抜けるようにするにはどうしたらよいのでしょうか。宜しくお願い致します。 Private Sub commandbutton1_click() Dim i As Integer For i = 1 To 5 If Me.Controls("optionbutton" & i).Value = True Then Selection.Value = Me.Controls("optionbutton" & i).Caption End If Next i Unload userform1 End Sub

  • エクセル 複数シート( VLOOKUP ユーザー定義関数

    複数シート(範囲)を指定できるVLOOKUP関数をユーザー定義で作りたいと思ってます。下記のコードではうまく動かないので教えてください。 Function VLOOKUPM(検索値 As Variant, 対象シート As String, 対象セル As Range, 列番号 As Integer) As Variant Dim i As Integer Dim r As Range Dim sh As Variant Application.Volatile sh = Split(対象シート, ",") For i = 0 To UBound(sh) Set r = Sheets(sh(i)).Range(対象セル) If 検索値 = r Then VLOOKUPM = r.Offset(0, 列番号) Exit Function End If Next End Function

  • VBAでのユーザ定義関数の返り値

    VBAでのユーザ定義関数の返り値をエクセルのセルに表示させる際、 色を変更して表示するにはどうすれば良いのでしょうか? つきのような関数のerrorの時だけ赤色にしたいのです。 functin test(ss as double) if ss >10 then test=ok else test =error! end if end function

専門家に質問してみよう