2021/04/27

初回入力日時を保存できるワークシート関数

• １．背景
• ２．システム概要
• ３．プログラムの流れ
• 　よりみち（関数処理中の操作制限）
• ４．標準モジュール（Module1）
• 　よりみち（日時データだけを残す意味）
• ５．フォーム（UserForm1）
• 　５－１．フォームの作成
• 　５－２．フォームモジュール
• 　　５－２－１．標準モジュールからの情報受け取り
• 　　５－２－２．フォームの初期値設定
• 　　５－２－３．TimeCellへの処理実施
• ６．ワークブックモジュール（ThisWorkbook）
• ７．アドインとしてExcelにマクロを登録
• ８．最後に

１．背景

ワークシート上で使用する関数は、それを入力したセルの表示値を変更します。例えば図１－１のようにB2セルに「=IF(A2< >" ",NOW()," ")」と記入すれば、「A2セルに何かが記入されていたら、現在日時をB2セルに表示」という意味になります。

図１－１

YAHOO!知恵袋などを見ていると、「記入した日時をずっと残したい」「何時以降に記入した時には赤字に書式変更したい」などという要望を良く見かけます。
しかし、Excelなどの表計算ソフトは「各セルのデータを常に見張り、いつでも整合性が取れている状態にする」のが基本的な目的ですので、図１－１で言えば、少なくともExcelブックを開き直した時には「再計算」が行われ、表示の日時は「データを入力した日時」ではなく「ブックを開いた日時」に変更されてしまい、ユーザーの望んでいるような「データの記録」にはなりません。

しかし、そのような「Excelにとってはムチャな要望」が時々出てくるということは、「世の中でそういう機能を必要とされている」ことですので、なんとか要望に答えたいと思い考えたのが、今回の「ユーザー定義関数」です。

なお、同じ手法を使ったユーザー定義関数として、以下も参照下さい。
「条件付書式で色変更されたセル値を合計する関数」

２．システム概要

今回作成したユーザー定義関数は、以下のような特徴を持ちます。

・関数の構文は「Ftime(CheckCell,TimeCell)」です。 　・２つの引数は省略不可。第一引数（CheckCell）、第二引数（TimeCell）とも、単一セル範囲の指定が必要。 　・CheckCellの値をチェックし、初めて値が入力された日時をTimeCellに出力。 　・CheckCellの値をその後編集しても、TimeCellの日時は変更されない。 　・CheckCellの値が削除（＝初期状態）されると、TimeCellの日時も削除される。 　・本関数を入力したセルには、CheckCellに値が入力されていれば「済」、入力されていなければ「未」と表示される。 　・複数のCheckCellに同時に値入力（異なるセル範囲の値をコピペ等）しても、先頭のTimeCellしか日時が出力されない。 　・短い間隔で関数を呼び出すと、日時の出力が間に合わなくなる場合あり（約0.5秒/セル以上は間隔要）。

関数の名前は「Ftime」としました。「First Time」の略のつもりです。
関数の入力は、直接セルに数式入力しても良いですし、「数式」タブ→「関数ライブラリ」グループ→「関数の挿入」をクリックすることで図２－１のようなダイアログが表示されますので、その中から選択することも出来ます。
今回は「文字列操作」に登録してありますので、その中から選択します。

図２－１

関数「Ftime」を選択すると、図２－２のようなダイアログが表示されますので、その中で引数を設定していきます。今回の引数は２つあり、両方とも単一セル範囲を設定します。
第一引数（CheckCell）は「値をチェックするセル」、第二引数（TimeCell）は「日時を出力するセル」です。

図２－２

図２－３は一番下に添付したサンプルファイルのものです。数式をD列に入力することで、C列の値の有無をチェックし、初めて入力された日時をE列に出力するようにしています。

図２－３

図２－３の左側で、C6セルに「最初に記入したのは15:26」ですが、そのあとで右側のようにC6セルの内容を修正しても「最初に記入した時刻15:26」は更新されないことが分かります。もちろん、ブックを開き直しても値は変更されません。
なお、もしD列が不要ならば、D列を非表示にしてもＯＫです。

３．プログラムの流れ

今回の関数の構文は「Ftime(CheckCell,TimeCell)」です。（CheckCell＝値をチェックするセル、TimeCell＝日時を書き込むセル）
その引数であるCheckCellの値が変更され場合には、図３－１の上段の標準モジュール内のFtime関数プロシージャが呼び出されます。

図３－１

Ftime関数プロシージャが呼び出された後、引数に指定された「CheckCell」と「TimeCell」の値を調べ、「TimeCell」の値を書き直す必要がある場合は、UserFormを「モードレス」で起動させます。モードレスでフォームを起動させれば、フォームを閉じなくても制御は標準モジュール側に戻ってきますので、「戻り値を返す」処理を行うことができます。
なお、TimeCellの値を書き直す必要がない場合は、すぐに「戻り値を返す」処理を行い、関数としての処理を終了します。

標準モジュール側のFtime関数プロシージャは終了しても、UserFormは起動したままですので、「フォームモジュール側からワークシート上のTimeCellの値を操作することで値を書き換えます。書き換えたら終了します。

ここで、疑問を持たれた方もいると思います。 １つ目は「処理している関数内（標準モジュール内）で、TimeCellに対して処理できないか」です。しかし図３－２のように「関数処理中は、どのセルにもアクセス不可」の状態なので、「TimeCellの値を変更することは出来ない」のです。 図３－２ また「それならば、TimeCellに値を書き込むための別プロシージャを用意し、関数内でOnTimeメソッドを使って、そのプロシージャの実行予約をする」方法を考え付く方もいると思います。 しかしこれもダメそうで、関数処理中のOnTimeメソッドにはエラーは出ないのですが、実際にはマクロ実行予約は行われておらず、プロシージャの実行はされません。「関数処理中の間は、OnTimeメソッドでのプロシージャ予約は発行されない」ようです。どこのサイトを見てもこの説明は見当たらないので、理由やどういう現象が発生しているのかは不明です。 そこで、ワークシートとは別次元のフォームの起動を試してみたら、関数プロシージャ内からでも起動できる事が確認できましたので、今回はフォームモジュールからセル値を変更する方法を使用しました。

４．標準モジュール（Module1）

ワークシート側から呼び出される関数本体が図４－１です。
第一引数が「値の存在をチェック」するセル範囲「CheckCell」で、第二引数が「CheckCellに値が初めて入力された日時を出力」するセル範囲「TimeCell」です。第一・第二引数ともRange型の単一セルを指定することが必要です。

1. '==========　⇩(1)　関数本体　============
2. Public Function Ftime(CheckCell As Range, TimeCell As Range) As String
4. 　If Not CheckCell.Value = "" Then
5. 　　If TimeCell.Value = "" Then Call UserForm1.UFstart(TimeCell, True)
6. 　　Ftime = "済"
7. 　Else
8. 　　If Not TimeCell.Value = "" Then Call UserForm1.UFstart(TimeCell, False)
9. 　　Ftime = "未"
10. 　End If
12. End Function

図４－１

まずCheckCellとTimeCellの２つのセルの状態としては、「両方とも値が入っている」または「両方とも空白セル」のどちらかである、というのを今回の大前提としています。
CheckCellに値が入っているのにTimeCellに日時データが入っていないのでは、「CheckCellに値が入った日時がTimeCellに表示されていない」ことになり、この関数の意義が失われてしまします。またCheckCellが空白なのにTimeCellに日時データが入っているのは、TimeCellに手動で日時データが入力されてしまった等の状況が考えられるため、片方のみ値がある場合は「TimeCellを操作」して正しい姿にすることにしました。

CheckCellが空白なのにTimeCellに日時データが入っている状態は、 「一度CheckCellに入力されたが、後で削除した。TimeCellの日時は、最初に入力した日時（又は、削除した日時）を表す」 という考え方もあると思います。 しかし、何回も『入力⇔削除』を繰り返した可能性もあるため、「何回目の時の日時なのか」を１セル（TimeCell）だけで表すのは難しいので、今回は単純に「初期状態＝削除後の状態」と見なし「CheckCellが空白の時は、TimeCellも空白」としました。

そこで、CheckCellとTimeCellの状態がどのように遷移すべきかを図４－２に整理しました。

図４－２

CheckCellの元の状態（図４－２の①）は、「空白」または「値が入っていた」です。値が入っているか否かで考えると、TimeCellの現状④はCheckCell①と同じ状態になっています。つまり「CheckCellの状態 = TimeCellの状態」です。
この状態からCheckCellへの操作②をした場合、「値が初めて入力」された場合と「入力値を削除」した場合のみ「CheckCellの状態③ ≠ TimeCellの状態④」となります。ですので、④から⑤の間を赤矢印で示したところだけ、TimeCellに対する処理が必要となります。

この内容をプログラムとして処理し易いように、「CheckCellの状態（値が入っているか否か）」「TimeCellの状態（値が入っているか否か）」で分類し、表にしたのが図４－３です。

図４－３

ユーザーによって変更されるのがCheckCell側ですので、「CheckCellの状態」によって最初の分岐をし、その後で「TimeCellの状態」によって二番目の分岐をさせ、それぞれに合った処理を実行させれば良いことになります。

図４－３の表に照らし合わせながら、コードを確認していきます。
４行目の「If Not CheckCell.Value = "" Then」で、「CheckCellの状態が空では無い（＝値 有り）」場合を分岐させています。図４－３で言えば①②です。
そして５行目の「If TimeCell.Value = "" Then」で「TimeCellの状態が空（＝値 無し）②」の場合に、その後ろの「Call UserForm1.UFstart(TimeCell, True)」でフォーム内のUFstartプロシージャ呼び出します。

一方、７行目の「Else」は「CheckCellの状態が空（＝値 無し）」の場合ですので、図４－３で言えば③④になります。そして８行目の「If Not TimeCell.Value = "" Then」で「TimeCellの状態が空では無い（＝値 有り）③」の場合に、その後ろの「Call UserForm1.UFstart(TimeCell, False)」でフォーム内のUFstartプロシージャ呼び出します。

なお、６行目の「Ftime = "済"」、９行目の「Ftime = "未"」は、「CheckCellの状態」を示すものとして「関数Ftimeの戻り値」を設定しています。

５行目、8行目の呼出し内容「UserForm1.UFstart(TimeCell, True)」「UserForm1.UFstart(TimeCell, False)」は、フォームの中のプロシージャ「UFstart（図５－２）」を呼び出しています。通常、フォームを起動するときには「UserForm1.Show」などを使うのですが、今回はフォーム側に「引数を渡す」必要があるため、このような形にしています。

UFstartプロシージャの第一引数には、日時を記入するセル位置である「TimeCell」を指定し、第二引数には「True＝日時を書き込む」「False＝値を削除する」のどちらかを設定します。
図４－３の②が「日時の出力」ですので、５行目は「True」を設定し、③が「空白に」ですので８行目では「False」を設定しています。

なお、引数を渡してフォームを起動する方法にはいくつかあります。引き渡す変数はPublic変数にしておく方法、ワークシートに値を書き込んで受け渡す方法、今回のようにフォーム上のプロシージャを引数付で呼び出す方法　などです。
どれを使っても良いですが、変数の使用範囲をできるだけ狭くしたい思いで、今回のような手法を使っています。

５．フォーム（UserForm1）

５－１．フォームの作成

今回はフォーム上で操作するわけでは無いので、外観は「ユーザーフォームの挿入」で自動作成されたままでＯＫです。

図５－１

５－２．フォームモジュール

５－２－１．標準モジュールからの情報受け取り

標準モジュール側の図４－１の５行目、８行目から呼び出される「UFstart」プロシージャ、及びモジュール内変数宣言部が図５－２です。
UFstartプロシージャでは、２つの引数を受け取ります。第一引数の「TimeCell」は、日時データの出力先セル、第二引数の「OnOff」は日時を書き込む場合はTrueを、空白にする場合はFalseとなります。

14. '==========　⇩(2)　フォーム内で使用する変数の宣言　============
15. Dim TC As Range 　　　 '日時を記入するセル
16. Dim OF As Boolean 　　　 'True：日時を記入、False：空白にする
18. '==========　⇩(3)　Ftime関数から呼び出されるプロシージャ　============
19. Sub UFstart(TimeCell As Range, OnOff As Boolean)
21. 　Set TC = TimeCell
22. 　OF = OnOff
24. 　Me.Show 0
26. End Sub

図５－２

「UFstart」プロシージャで受け取った引数は、２１～２２行目で一旦モジュールレベルの変数（１５～１６行目で宣言）に代入します。
２４行目の「Me.Show 0」で自分（＝UserForm1）をモードレスで起動します。モードレスですので、２４行目を実行したら制御は２６行目の「End Sub」に進んでプロシージャ終了し、呼出し元の標準モジュールに制御が戻ります。

標準モジュール（図４－１）に戻った制御は、Ftime関数としての戻り値（「未」、または「済」の文字列）を返して処理終了します。
しかしUserFormは既に起動済みですので、２１～２２行目でモジュールレベル変数に代入された値を使うことで「ワークシートへの処理が可能」になります。

５－２－２．フォームの初期値設定

フォームが起動する際に最初に発生するイベントが図５－３のInitializeイベントです。

今回は標準モジュールから図５－２のUFstartプロシージャを呼び出し、そのプロシージャ内の２４行目で「Me.Show 0」とフォームを起動させています。だからと言って、実行される順番が「UFstartプロシージャ → Initializeイベント → Activateイベント」の順番になるわけではありません。
UFstartプロシージャは「UserForm1がLoadされていないと呼び出せない」ので、順序としては「Initializeイベント → UFstartプロシージャ → Activateイベント」の順に実行されていきます
つまり「Initializeイベント」は必ず一番初めに発生します。

今回の「Initializeイベント」ではフォームを表示することには意味がありませんので、できるだけ目立たなくするため、「一番端に表示し、かつ一番小さく表示」させるようにします。

28. '==========　⇩(4)　フォームの初期設定　============
29. Private Sub UserForm_Initialize()
31. 　Me.StartUpPosition = 0
32. 　Me.Left = 0
33. 　Me.Top = 0
35. 　Me.Height = 0
36. 　Me.Width = 0
38. End Sub

図５－３

３１～３３行目は、フォームの初期表示位置を指定しています。
３１行目の「Me.StartUpPosition = 0」は、手動（３２～３３行目の設定）でフォームの位置を設定するという意味になります。
なお、StartUpPositionプロパティには、図５－４のように４種類の設定値があります。

値	内容
0	初期設定値を指定しません
1	UserFormが属する項目の中央
2	画面全体の中央
3	画面の左上隅

図５－４

３２行目の「Me.Left = 0」でフォームの「左右方向位置を画面左端一杯」、３３行目の「Me.Top = 0」で「上下方向位置を画面上一杯」 の位置に設定します。なお、この設定だと「Me.StartUpPosition = 3」に設定しているのと同等になります。

表示位置は設定しましたので、次はフォームのサイズ設定です。３５行目の「Me.Height = 0」で、フォームの高さをゼロにし、３６行目の「Me.Width = 0」でフォームの幅をゼロにしています。
しかし、この「高さ × 幅の設定」でフォームが全く見えなくなるわけでは無く、一瞬の表示ですが図５－５のように見えます。

図５－５

ですので、もし完全に見えないようにしたいのであれば、例えば３３行目を「Me.Top = -100」などと設定することで「画面の外に表示」させることは可能です。ただし、何らかのエラーで「フォームがUnloadできずに残ってしまった」ときには、右上×印で強制的に閉じることができなくなりますので、ご注意下さい。

５－２－３．TimeCellへの処理実施

図５－２の２４行目で「Me.Show 0」を実行すると、フォームを表示する途中で「Activateイベント」が発生し、図５－６が呼び出されます。このプロシージャの中で「TimeCellへの処理」を実施します。

40. '==========　⇩(5)　TimeCellへの処理　============
41. Private Sub UserForm_Activate()
43. 　If OF = True Then
44. 　　TC.Value = Now()
45. 　Else
46. 　　TC.Value = ""
47. 　End If
49. 　Unload Me
50. End Sub

図５－６

４３行目の「If OF = True Then」は、書込み／削除のフラグ変数OFで分岐をさせています。「モジュール変数OF ＝ UFstartの引数OnOff」ですので、True＝書込み／False=削除 です。
OF=Trueの時には、４４行目の「TC.Value = Now()」を実行します。TCは「UFstartの引数TimeCell」ですので、関数Ftimeの第二引数に指定した「日時データの出力先セル」に対して、日時（Now()関数）のデータを書き込みます。今回はDate型そのままを出力していますので、書込み先のセルの書式設定次第で、Double型の数値だったり、分までの表示だったり、秒まで表示されたりします。
もし表示を揃える必要があるのでしたら、Format関数などを使用して揃えて下さい。

一方、OF=Falseの時には、４６行目の「TC.Value = " "」を実行し、「日時データの出力先セル」を空白セルにします。
ここまでの処理で「日時データの出力先セル」の状態（値が有るか無いか）と「値をチェックするセル」の状態（値が有るか無いか）が揃うことになります。

最後に４９行目の「Unload Me」でフォームを閉じます。
閉じ方として「Me.Hide」という方法も考えましたが、処理速度も見た目には変わらないため、１回１回確実にUnloadする方を採用しました。

６．ワークブックモジュール（ThisWorkbook）

ここまでのコードで関数としては完成しているのですが、図２－１、図２－２の「関数ダイアログ」で説明文などを表示するためには、図６－１のように「MacroOptionsメソッド」を設定する必要があります。

なお５３行目の「Workbook_AddinInstallイベント」は、ブックがアドインとしてインストールされるときに発生するイベントです。
通常、ブック起動時に変数などを設定する時には「Workbook_Openイベント」を使用しますが、今回の場合MacroOptionsメソッドをWorkbook_Openに記載してしまうと、「Excelアプリ」を起動した時に「表示されていないブックのマクロは編集できません」との実行時エラーが発生してしまいます。ご注意下さい。

52. '==========　⇩(6)　関数情報の設定　============
53. Private Sub Workbook_AddinInstall()
55. 　Application.MacroOptions _
56. 　　　Macro:="Ftime", _
57. 　　　Description:="CheckCellに入力した最初の日時をTimeCellに出力します。", _
58. 　　　Category:=7, _
59. 　　　ArgumentDescriptions:=Array("値をチェックするセル(単一セル)", "日時を書き込むセル(単一セル)")
61. End Sub

図６－１

「MacroOptionsメソッド」には、図６－２のように多くのパラメータがあります。

名前	内容
Macro	ﾏｸﾛ名またはﾕｰｻﾞｰ定義関数の名前を指定
Description	ﾏｸﾛの説明
Hasshortcutkey	True指定で、ｼｮｰﾄｶｯﾄｷｰがﾏｸﾛに割り当てられる
ShortcutKey	ｼｮｰﾄｶｯﾄｷｰ（Hasshortcutkeyとﾍﾟｱ設定）
Category	既存のﾏｸﾛ関数の分類を示す整数値を指定
StatusBar	ﾏｸﾛ実行時に表示されるｽﾃｰﾀｽﾊﾞｰﾃｷｽﾄを指定
HelpContextID	ﾏｸﾛに登録するﾍﾙﾌﾟﾄﾋﾟｯｸのｺﾝﾃｷｽﾄIDを、整数で指定
HelpFile	HelpContextIdで指定したｵﾝﾗｲﾝﾍﾙﾌﾟのﾄﾋﾟｯｸが含まれるﾍﾙﾌﾟﾌｧｲﾙ名を文字列で指定
ArgumentDescriptions	関数の引数ﾀﾞｲｱﾛｸﾞに表示される、引数の説明が含まれる一次元配列

図６－２

今回は、５６行目の「Macro:="Ftime"」でユーザー定義関数の名前（Ftime）を登録し、５７行目の「Description:="CheckCellに入力した最初の日時をTimeCellに出力します。"」で説明文を設定しています。

また、５８行目の「Category:=7」は、図６－３の中から選んで整数値設定をします。今回は「文字列操作」が妥当かなと考え、「７」を設定しました。なお、設定を省略すると「ユーザー定義」に分類されるようです。
なお「Category:="文字列操作"」のように、直接「カテゴリの文字列」で指定してもＯＫのようです。

整数 カテゴリ 1 財務 2 日付/時刻 3 数学/三角 4 統計 5 検索/行列 6 データベース 7 文字列操作

整数 カテゴリ 8 論理 9 情報 10 コマンド 11 ユーザー設定 12 マクロ制御 13 DDE/外部 14 ユーザー定義

図６－３

５９行目の「ArgumentDescriptions:=Array("値をチェックするセル(単一セル)", "日時を書き込むセル(単一セル)")」は、図２－２のダイアログで、引数ボックスにフォーカスを当てた時に表示される「引数の説明」のようです。
配列にするため「Array関数」を使用し、第一引数に「値をチェックするセル(単一セル)」、第二引数に「日時を書き込むセル(単一セル)」の順番で配列化したものを指定しています。

７．アドインとしてExcelにマクロを登録

このマクロをExcelのアドインに登録することで、どのブックでも「ワークシート関数」の１つとして使用することが出来ます。アドイン方法については「年賀状リスト等の宛名検索と追記　アドイン登録」を参照下さい。

なお今回のようにユーザー定義関数を登録する場合には、「開発」タブ→「アドイン」グループ→「Excelアドイン」でチェックを付けるまででＯＫです。ボタンの登録は必要ありません。

８．最後に

セル値の変化を監視して、異なるセルに日時などのデータを書き込むことは「マクロ」を使えば簡単です。但し、実現するには「各入力シートに対応した、専用のマクロ作成」が必要になります。
今回はユーザー定義関数化することで、「監視するセル」と「日時を書き込むセル」をユーザーが自由に設定できることが特徴です。また、コードを工夫すれば「何時以降に記入した時には赤字に書式変更」のようなことも出来ると思います。

但し今回システムではフォームを毎回起動していますので、短時間に連続入力されるような場面では打刻ミスが出てしまいます。図３－２で示した「魔の関数処理時間」の謎と抜け出し方法がわかれば、更に実用的な関数が作れるかもしれません。


初回入力日時を保存できるワークシート関数（it-057.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。