[[20170226010116]]　『VLookupのユーザー関数について』（くま）　ページの最後に飛ぶ

『VLookupのユーザー関数について』（くま）

ある本から、下記の関数でVLookupのユーザー関数ができることまでは、
発見できたのてすが、この関数を、シート名　くま　セル　A1 に入力した
日付までは使用し、その日を過ぎると、値が0と表示されるユーザー関数を
作りたいのですが、どのようにすればよいですか?
ループ関数を使用するとできそうなのですが、理解できませんでした。

Function VLookup1(検索値 As Variant, 範囲 As Range, 列番号 As Integer) As Variant

    Dim myData

    On Error GoTo ErrorHandler
        myData = WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
    On Error GoTo 0

    VLookup1 = myData
Exit Function
ErrorHandler:
    VLookup1 = ""
End Function

＜使用 Excel：Excel2010、使用 OS：unknown ＞

 >その日を過ぎると、値が0と表示されるユーザー関数を 
作りたいのですが、どのようにすればよいですか?

 その日とは、「シート名　くま　セル　A1 に入力した 日付」で、例えば、「2017/2/26」ですね。
 「過ぎる」とありますが、どの日付と比較しているのでしょうか？

 「範囲 As Range」とありますが、どのｼｰﾄの、どのセル範囲なんでしょうか？
 このセル範囲には、どんな値が入力さえているのでしょうか？

 また、「値が0と表示される」とありますが、表示したいのは、
 どこでしょうか？ｼｰﾄのとあるセルでしょうか？それとも、msgboxでの表示などでしょうか？

（マリオ） 2017/02/26(日) 03:24

 こんにちわ。

 ユーザー定義関数じゃないといけないのでしょうか？
 普通に =IF($A$1>TODAY(),0,VLOOKUPの式) ではダメなんですか？

（sy） 2017/02/26(日) 06:58

すでに適切な回答をいただいています。以下は、少し余談めいた話です。
　
ワークシート関数には、IFERRORといったものがあり、それとVLOOKUP1はほぼ同機能でしょう。
あえてユーザー定義関数を使用しようとするのは勉強の一環ということでしょうか？
　　
ところでループが必要だろうという感触はどこから来るのでしょうか。
ループで繰り返し判断しようとしているのは、どんなことなんでしょうか。

（γ） 2017/02/26(日) 07:47

皆さん、おはようごさいます。
かつ、ありがとうございます。
前提が、あいまいで申し訳ありませんでした。
ユーザー定義関数を知り取り組んでみようと思いました。

(前提)
例えば、シート　"ねこ" と、シート　"くま"　があります。

シート"ねこ"
セル　A1 に、任意の日付　が入力されています。例えば、2019/03/15

シート"くま"
セル　A1 に、VLookup1　関数式を利用した式を入力したいと思います。
(但し、Module 1 シート　の設定では、VLookup1　と　Today() 関数(他にやり方があれば、別でもよい)
と組み合わせで、常時、シート"ねこ"　セルA1 と比較しているとします。)
検索値, 範囲　の対象データは、それぞれC列・D列にあり、列番号は、2と設定します。
このような感じでわかりますでしょうか?
前提を作成させていただきましたが、表面上のエクセルシート(シート　"くま"　)にVLookup1　関数が
入力されているのですが、ある日・時間がきたら、値が0と表示されるような設定が可能かどうか、確認
したいと思いました。
ループ関数については、素人ながら初めて本をみて、利用できるのかな?と思ったにすぎませんので、皆さん
のお考えを教えていただければ幸いです。
マクロとか、まったくの素人ですので、上記のような条件の定義が、即使用できるような、プログラムを
書き込んでくだされば、ありがたいです。

（くま） 2017/02/26(日) 08:42

 >ユーザー定義関数を知り取り組んでみようと思いました。

 提示された要件が、必ずしも必要なわけではなく、これからユーザー定義関数というものに取り組んでみたい。
 そのための第１ステップとしての『教材』と理解すればよろしいのですね。

 そのポイントでコメントしますと

 １．アップされたように、もろ、そのシート関数をメインコードとして書くということも悪いことではないですが
 　　ＶＢＡでのユーザー定義関数は、シート関数ではちょっとしんどいなというときに準備するものですので
 　　コードの中身も、『思い切って』ＶＢＡらしく（？）書くほうが勉強になるかもしれません。

 ２．非常に重要なことですが、ユーザー定義関数にしろ、シート上のふつうの数式にしろ、その中で参照している領域の値が変われば
 　　自動的に動いて計算されます。
 　　でも、引数として参照記述がない領域が変化しても動きません。
 　　今回のテーマでは A1 という部分。これを引数の中に記述して、コードでもそれを参照する形であれば
 　　A1 が変更されれば動くんですが、A1 は　あくまで、裏に隠れている　という要件だと、A1 を過去日付に変えたり
 　　あるいは先行き日付に変えたりしても、再計算がされません。

 　　こういう場合は、プロシジャの先頭に、    Application.Volatile　と記述しておきます。
 　　そうすると、シート上のどこかに変更が起これば、必ず再計算されます。
 　　（再計算に不要な領域の変化によっても再計算されてしまいますけど、やむなし）

 ということで一例です。

 Function VLOOKUP2(検索値 As Variant, 範囲 As Range, 列番号 As Integer) As Variant
    Dim f As Range

    Application.Volatile

    If Date > Range("A1").Value Then
        VLOOKUP2 = 0
        Exit Function
    End If

    Set f = 範囲.Columns(1).Find(What:=検索値, After:=範囲.Columns(1).Cells(範囲.Rows.Count), LookAt:=xlWhole)
    If f Is Nothing Then
        VLOOKUP2 = CVErr(xlErrNA)
    Else
        VLOOKUP2 = f.Offset(, 列番号 - 1)
    End If

 End Function

（ β） 2017/02/26(日) 11:32

 ↑　上記例を、判定日付も引数に加えた形にすると、逆に　Application.Volatile　は不要で、関連の領域変化があった時のみ再計算されますので
 以下がおすすめです。

 Function VLOOKUP3(有効日付 As Date, 検索値 As Variant, 範囲 As Range, 列番号 As Integer) As Variant
    Dim f As Range

    If Date > 有効日付 Then
        VLOOKUP3 = 0
        Exit Function
    End If

    Set f = 範囲.Columns(1).Find(What:=検索値, After:=範囲.Columns(1).Cells(範囲.Rows.Count), LookAt:=xlWhole)
    If f Is Nothing Then
        VLOOKUP3 = CVErr(xlErrNA)
    Else
        VLOOKUP3 = f.Offset(, 列番号 - 1)
    End If

 End Function

（ β） 2017/02/26(日) 11:42

 念のためなんですが、私のレスは、『シート上のセルで使うユーザー定義関数』ということを前提にしたコメントです。
 もちろん、このコードをＶＢＡコード内で呼び出して使うことも出きるわけですが、そういう場合は、
 ふつうは　『ユーザー定義関数』とは呼びませんので。

 また、オリジナルコードをよく見ましたら見つからなかった場合は　"" にして返していましたね。
 私がアップしたコードは　エラー値　として返しています。
 不都合なら、いかようにも直してください。

 なお、必ずシート上で使うということを前提にするなら、（本件ということではなく一般論として）
 コード内でエラーになったとしても（オリジナルコードでは WorkSheetFunction.VLOOKUP が失敗したとしても）
 エクセルが倒れることはなく、セルに #VALUE! と表示されるだけです。

 なので、（場合によっては）コード内のエラーバイパス処理は不要ということもあります。

（ β） 2017/02/26(日) 12:18

（ β）さん
早速ためしてみました。
すごいです。今後の楽しみができました。
有難うございました。
（くま） 2017/02/26(日) 15:43

追加の疑問です。

Date で、本日の日付が設定されている感じがするのですが、
例えば、例1.　2017/3/15　例2.　2017/03/15　10時25分　等への変更はできるのでしょうか?
もし、出来るのであれば、上記の追記訂正したものをご記入いただけますか?

また、（ β）が記入してくださった、上記、ユーザー定義関数を、初心者でも理解できるようになるには、どのような書籍を読み進めれば、理解できるようになりますか?
1冊で完結しなくても構いませんし、Webサイトでも・・。アドバイスをお願いします。

正直いいまして、下記からは、理解できない状態でした。
あつかましいお願いですが、初学者でもわかるように解説いただけたら最高です・・・。

 VLOOKUP2 = 0
        Exit Function
    End If

    Set f = 範囲.Columns(1).Find(What:=検索値, After:=範囲.Columns(1).Cells(範囲.Rows.Count), LookAt:=xlWhole)
    If f Is Nothing Then
        VLOOKUP2 = CVErr(xlErrNA)
    Else
        VLOOKUP2 = f.Offset(, 列番号 - 1)
    End If

 End Function

（くま） 2017/02/26(日) 16:12

 指定日までＯＫ　ではなく、指定時刻までＯＫということにしたいということでしょうか？
 この場合、A1(だろうと思いますけど）には、どのような形のデータが入っているのですか？
 =NOW() といったものでセットされる形でしょうか？　それとも yyyy/mm/dd といった
 年月日までの形でしょうか？

 というか、A1(だろうと思いますが）には時刻までいれておかないと、この要件は達成できませんけど。

（ β） 2017/02/26(日) 17:50

 >どのような書籍を読み進めれば、理解できるようになりますか? 
 >1冊で完結しなくても構いませんし、Webサイトでも・・。アドバイスをお願いします。

 この学校の画面の上のほうに　『全文検索』というバナーがあります。
 そこで、『勉強方法』あたりを検索語にして検索すると過去からの様々な回答者さんの回答といいますか
 推奨方法がでてきますので、参照してみてください。

 >>正直いいまして、下記からは、理解できない状態でした。

 『下記』というのが IF文制御も含めてのことでしたら、↑で紹介した検索ででてくる方法で、ＶＢＡの基礎を
 マスターしてもらう過程で、だんだん理解ができてくると思います。

 そうではなく

 Set f = 範囲.Columns(1).Find(What:=検索値, After:=範囲.Columns(1).Cells(範囲.Rows.Count), LookAt:=xlWhole)

 このFindメソッドが、何なのかわからないということでしたら、シート上で検索操作を行いますよね。
 その検索操作をマクロ記録してみてください。
 あぁ、Findメソッドって、これだったんだということがわかりますので。

（ β） 2017/02/26(日) 17:59

 VLOOKUP3 ベースで時刻対応したものを（要件を誤解しているかもしれませんが）

 A1 は　yyyy/mm/dd の年月日でもいいですし、yyyy/mm/dd hh:mm:ss の年月日時刻でもＯＫです。
 yyyy/mm/dd の形であり、かつ引数として時刻指定がされた場合は　yyyy/mm/dd の午前0時と書いてあるとみなされます。

 セル側は VLOOKUP3 と同じく 第１引数に =VLOOKUP(A1,・・・・) といったように　日付セルを指定します。
 で、もう１つ、最後の引数として省略可能の　比較条件を追加します。

 日付のみで経過したか否かを判定する場合は =VLOOKUP4(日付セル,検索値,範囲,列番号,True) または =VLOOKUP(A1,検索値,範囲,列番号) といれます。

 時刻まで含めて経過しているかどうかの判定を行う場合には　=VLOOKUP4(日付セル,検索値,範囲,列番号,False)　と書いてください。

 もし、以下のコードで要件がＯＫであれば、あらためてコードの説明をしますので、とにかく試してみてください。

 Function VLOOKUP4(有効日付 As Date, 検索値 As Variant, 範囲 As Range, 列番号 As Integer, Optional 年月日 As Boolean = True) As Variant
    Dim f As Range

    If 年月日 Then
        If Date > Int(有効日付) Then
            VLOOKUP4 = 0
            Exit Function
        End If
    Else
        If Now() > 有効日付 Then
            VLOOKUP4 = 0
            Exit Function
        End If
    End If

    Set f = 範囲.Columns(1).Find(What:=検索値, After:=範囲.Columns(1).Cells(範囲.Rows.Count), LookAt:=xlWhole)
    If f Is Nothing Then
        VLOOKUP4 = CVErr(xlErrNA)
    Else
        VLOOKUP4 = f.Offset(, 列番号 - 1).Value
    End If

 End Function

（ β） 2017/02/26(日) 20:34

（ β）さん、有難うございます。
本日以降は、式の答えが反映されることを確認できました。
コードの説明をしてもらえるとのことで、ご親切に感謝いたします。
実は、その他に、もしよろしければ、今、レクチャーいただいているワークシート上のセルに
入力されている日付・時刻を参照する方法ではなく、Moduleシート上で参照対象日付・時刻自体を、
指定することは、不可能なのでしょうか?
その場合は、例えば、例1.　2017/3/15　例2.　2017/03/15　10時25分　の場合、どのように
なるのでしょうか?
お手数をおかけして、申し訳ありません。

（くま） 2017/02/26(日) 23:13

 >本日以降は、式の答えが反映されることを確認できました。

 時刻指定のケースも確認いただいたということでよろしいですか？

 >Moduleシート上で参照対象日付・時刻自体を、 指定することは、不可能なのでしょうか?

 それは、コード内で　日付時刻をコンスタントで指定しておく　ということですか？
 それとも、式が再計算される時点で、操作者に対して基準年月日時刻を入れさせるということですか？

 いずれもできますけど、後者は、まず運用できないでしょうね。
 なので、前者ですね？

 やるなら以下ですけど、どうなんでしょうねぇ・・・・
 基準日を変更したい場合はコードを修正するんですか？
 あまり感心しませんね。

 そうしたい理由を教えてください。

 一応コードです。　セル側　は　有効日付を指定しないわけですから　=VLOOKUP5(検索値,範囲,列番号) といった記述になります。

 でも・・・・ほんと？

 Function VLOOKUP5(検索値 As Variant, 範囲 As Range, 列番号 As Integer, Optional 年月日 As Boolean = True) As Variant
    Dim f As Range
    Dim 有効日付 As Date

    有効日付 = "2017/3/1 12:00:00"

    If 年月日 Then
        If Date > Int(有効日付) Then
            VLOOKUP5 = 0
            Exit Function
        End If
    Else
        If Now() > 有効日付 Then
            VLOOKUP5 = 0
            Exit Function
        End If
    End If

    Set f = 範囲.Columns(1).Find(What:=検索値, After:=範囲.Columns(1).Cells(範囲.Rows.Count), LookAt:=xlWhole)
    If f Is Nothing Then
        VLOOKUP5 = CVErr(xlErrNA)
    Else
        VLOOKUP5 = f.Offset(, 列番号 - 1).Value
    End If

 End Function

（ β） 2017/02/27(月) 00:23

（ β）さんへ
深夜にも、関わらず有難うございます。

 >本日以降は、式の答えが反映されることを確認できました。 
 時刻指定のケースも確認いただいたということでよろしいですか？
→　時刻まで、確認いたしました。

 >Moduleシート上で参照対象日付・時刻自体を、 指定することは、不可能なのでしょうか? 
 それは、コード内で　日付時刻をコンスタントで指定しておく　ということですか？
→　そのとおりです。

 やるなら以下ですけど、どうなんでしょうねぇ・・・・
 基準日を変更したい場合はコードを修正するんですか？
→　私の現在の疑問としては、そのとおりです。

そうしたい理由を教えてください。
→　すいません。確かに、可能性として探求しているにすぎません。

せっかく、VLOOKUP5　のコードを教えていただいたので、もう少し、教えていただきたいのですが
このコードでいくと、2017/3/1 12:00:00　の日時以降に、ファイルを開いたら、式の答えが0になる
と考えてよろしいのでしょか?
それとも、ファイルを開いていも、上記日時を経過すれば、式の答えが0になるのでしょうか?
いずれにせよ、私の、興味としての範囲というこで、申し訳ありません。
　　
（くま） 2017/02/27(月) 01:37

横から失礼します。
質問のポイントは、そのマクロはいつ実行されるのか、という点でしょうか。
1. 引数が変更されたときは、再計算がされます。
2. VLOOKUP5は、期限が切れても再計算はされません。
3. 既にご指摘がありましたが、Application.Volatileを挿入すれば、
　ワークシートのいずれかのセルで計算が行われるたびに再計算を行います.
　無関係のセルで変更があっても常に再計算が行われるので無駄は無駄ですし、
　使用している箇所が多ければ、余計な負荷がかかることになります。
　

（γ） 2017/02/27(月) 07:34

 すでにγさんからお答えをいただきましたが、ちょっと実験をしてみましょう。

 新規ブックの標準モジュールに以下を貼り付けてください。

 Function Func1(r As Range) As String
    Func1 = "ABC:" & r.Value & "  " & Now()
 End Function

 Function Func2(r As Range) As String
    Application.Volatile
    Func2 = "XYZ:" & r.Value & "  " & Now()
 End Function

 A1 に　2017/2/27 8:00　と打ち込み、A1 以外の２つのセルに、それぞれ =Func1(A1)、  =Func2(A1)　と打ち込んで、
 ここで表示される値を メモしてください。

 このブックを名前を付けて保存し、閉じてください。

 しばらく（１分ほど）待って、もう一度開いてください。

 =Func2(A1) のセルは、表示内容が変わりましたが、=Func1(A1) のほうは変わりませんね？

 つまり　Func1 は、ブックを開きなおしたときには、自動的に再計算されないということになります。

 次に、この状態でじぃ～っと見つめていてください。
 Func1 も Func2 も、値が変わりません。　つまり、Func2 も、いったん再計算された後では、シートのどこかに変更が加えられない状態では
 動かないということです。

 ★ですから、VLOOKUP5 は、ブックを開きなおしたときに自動的に処理はされません。
 ★また、ブックを開いているだけで、マクロで指定した時刻に到達しても、自動的には動きません。

 もし、ブックを開いたときに、決まった処理をさせたいということであれば、Workbook_Open あたりのイベント処理を使えば可能です。

 また、ある時刻が到達したら、何かを行いたいという要件に対しては、このような通常の構えでは処理不可能です。
 Application.Ontime や API の SetTimer/KillTimer で仕掛けるか、裏で監視ループをがんがん回す、特殊な
 構成が必要です。

 でも、それは、くまさんにとって、もう少し先、ＶＢＡのレベルが上がった時に取り組まれるテーマかと思います。

（ β） 2017/02/27(月) 09:34

（γ）さん、（ β）さん
お忙しい中、お付き合いいただき有難うございました。
またまだ、わからないことがありますが、自分でも研鑽してみようと思いました。
皆さんののような方の存在が、確認できたことが一番の収穫となりました。
本当にお世話になりました。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　森のくま
（くま） 2017/02/27(月) 23:52

コメント返信：

[ 一覧(最新更新順) ]

[[20170226010116]] 『VLookupのユーザー関数について』（くま） ページの最後に飛ぶ

[[20170226010116]]　『VLookupのユーザー関数について』（くま）　ページの最後に飛ぶ