2022/02/21

共有コメント付きカレンダー(固定長ファイルを使用)




1.背景

以前紹介した「CSVファイルでデータを読み書きする月間予定表」では、1ヶ月カレンダーの各日付の下に予定を表示するものを紹介しました。その時は、データを共有ファイルサーバー等に置くことで使用者間で予定を共有化するもので、データはCSV形式・各月毎にデータファイルを作るというものでした。
前回システムはCSV形式ですので、1日当たりの予定データの長さは自由ですし、また予定の無い日のデータ量はゼロで済みます。それなら固定長のデータ保存方式なら、どうなるんだろう と思って作成したのが今回システムです。

まず固定長のレコード位置を何の値にするかですが、今回は「レコード位置=日付」としました。そうすることで、各日毎のデータ長は固定(=字数制限有り)となりますし、予定の有る日も無い日もファイルサイズ使用量は同じだけ必要となります。しかし、日付さえ分かればデータを一発で取り出せるのは大きな魅力です。

その結果(単純に以前のものと比較は出来ませんが)コード数の非常に少ないシステムとなりました。文字数制限が必要なので、日々の予定がそれほど多くない状況では有効だと思います。

2.システム概要

カレンダーの外観は図2-1です。横方向は日曜~土曜までの7列で、縦方向は「日付用6行+コメント用6行」の合計12行で出来ています(図では、B4~H15のセル範囲)。
カレンダー上側(E2セル)にはカレンダーの表示年月①を、右上に年月を移動するためのスピンボタン②を配置しています。
今回カレンダーの特徴
図2-1

カレンダーの中で、日曜日は赤字③で、土曜日は青字④で表示されます。また、カレンダー枠内での前月分⑤と次月分⑥についても日付とコメント⑨を表示しますが、薄い色にしています。
1つの枠は上下2つのセルで出来ています。上側セルが日付⑦、下側セルがコメント⑧になっています。コメントが複数行に渡っている時⑩は、行高さが自動的に高くなり(Excelの標準の機能)全行が見えるようになります。

カレンダーの日付は、スピンボタン操作により移動可能です。また、コメントについては「日単位で入力」をします。入力方法は図2-2の左側のように、日付の下のセルに「通常のセル入力⑪」と同じように入力します。編集も同じです。
コメントの入力方法とセル内改行のやり方
図2-2

なお、セル内で改行をする場合も、通常操作と同様に「alt+Enterキー⑫」で改行可能です。但し、固定長ファイルにデータ保存をしているために、文字数には制限があります。「サンプルファイル」では「50バイト」に設定してあり、半角文字で50文字以内、全角で25文字以内です。改行も1個当たり半角1文字分(1バイト)を消費します。

制限文字数(50バイト)を超えて入力(図2-3の左図の⑬)した後Enterキーを押すと、セル内には50バイト超の全文字列が入っていますが、注意ダイアログ⑭が表示されます。ダイアログ上には、「保存される範囲の文字列」が表示されています。
コメントの文字数制限
図2-3

この仕様の意図としては、入力した50バイト超の文字列をなんとか編集して「制限文字数以内(=注意ダイアログが出ない)」に収めるように、ユーザーを誘導しているつもりです。
なお、50バイト超の文字列を入力後Enterキーを押した時点で、データとしては50バイトに足切りしてファイルに格納しています。スピンボタンで次月→前月とカレンダーを移動(=その月のカレンダーを更新した事になる)すると、制限文字数しか保存されていない⑮ことが分かります。

データファイルをネットワーク上に置く場合、通信不良が発生するとコメントデータの読み取り・書き込みが出来ません。
その場合、システム起動時(前回保存時点のカレンダー年月を表示)や、スピンボタンをクリックして新たな年月のカレンダーを表示しようとした際に、図2-4の左図のように「読み込み不可」のダイアログ⑯が表示されます。
またコメント書き込み時には、右図のように「書き込み不可」のダイアログ⑰が表示されます。
通信不良等でコメントファイルにアクセスできない場合のエラー
図2-4

また、データファイルの基準日(サンプルファイルでは2022/1/1と設定)よりも前の位置にデータを保存しようとしても不可能なので、図2-5の様なダイアログ⑱を表示します。この時、データは保存されていませんが、セルには入力文字列を残し、コピペなどで別な日に保存出来るようにしています。
基準日よりも前にコメントを追加しようとした場合のエラー
図2-5

3.プログラムの流れ

カレンダーは、シート右上のスピンボタンを操作することで作成開始します。スピンボタンの操作をChangeイベントで拾い、作成するカレンダーの年月を定め、まずはカレンダー本体を作成(makeCal)します。その後で、サーバー等から固定長ファイルを開き、表示するカレンダー日付に対応したコメントデータを読み込み(readSched)ます。
そのカレンダー本体+コメントデータをシート上に貼り付けます。貼り付けた後、土日の彩色と前月・次月分の薄文字化(makeColor)を行います。
プログラムの流れ
図3-1

カレンダー日付の下のセルにコメントを書き込むと、ワークシートのChangeイベントが発生します。書き込まれた内容をチェックした後、書かれた日付を書き込み位置として、固定長ファイルにコメントデータを書き込み(writeSched)ます。

4.標準モジュール(Module1)

4-1.定数・変数の宣言

標準モジュールの宣言部(先頭)では、システム内で使用する定数・変数の宣言を行っています。
  1. '========== ⇩(1) 定数・変数の宣言 ============
  2. Public Const stdD As Date = "2022/01/01"    '←基準日(データファイルの1番とする日)
  3. Public Const StartD As String = "E2"       '←カレンダーの表示年月のセル位置
  4. Public Const Calarea As String = "B4:H15"    '←カレンダーの表示セル範囲(日付・コメント表示部)
  5. Public Calarray(1 To 12, 1 To 7) As Variant    '←カレンダー日付・コメントを格納する配列
  6. Const txtAddress = "¥¥Server¥Data¥sched.dat"    '←②データファイルを共有のファイルサーバー等に置く場合
  7. Public txtAddress As String    '←①データファイルをシステムファイルと同じ場所に置く場合。Workbook_Open内で置く場所を代入要
  8. Type Record
  9.  Sdata As String * 50
  10. End Type
図4-1

2行目「Public Const stdD As Date = "2022/01/01"」は、コメントデータの固定長ファイルの1番目とする日付を定数宣言しています。
今回システムでは、1日=1データ(サンプルファイルでは50バイト分)をファイル保存し、日付のシリアル値を元にデータの場所を探すようにしました。例えば、今日(2022年2月)のシリアル値は約44600ですので、44600番目のデータを見に行けば今日のデータが取得できる、という訳です。
しかしシステムをこれから使い始めるのですから、今日以前のデータは入力しても活用しようが無いので、結局空データとなってしまいます。その無駄なデータ量は、1日当たり今回と同じ50バイトとしても、2.1MBにもなります。
そこである基準日を置き、その日を1番目のデータ、次の日を2番目のデータ、・・・としていけば、無駄データを持たなくても良くなり、またファイルサイズも小さくなるため、ファイルを開く時間も短縮されます。

3行目「Public Const StartD As String = "E2"」は、図4-2のように、カレンダーの年月を表示しているセル範囲を文字列として設定しています。また、4行目「Public Const Calarea As String = "B4:H15"」は、カレンダーの表示範囲(日~土のタイトル行を除く部分)を設定します。
セル範囲を表す定数
図4-2

5行目「Public Calarray(1 To 12, 1 To 7) As Variant」は、カレンダーの日付+コメントのデータを入れておく二次元配列の宣言です。縦方向は日付行+コメント行の「6行×2=12行」で、横方向は日曜~土曜の7列です。4行目で設定したCalareaのセル範囲に、このCalarray配列をそのまま貼り付けますので、同じサイズにすることが必要です。

7~8行目では、コメントデータファイルの場所を指定しています。場所はtxtAddress(定数または変数)に、文字列型で指定します。
7行目「Const txtAddress = "¥¥Server¥Data¥sched.dat"」は、「Server」という名前のサーバーの中に「Data」というフォルダーが存在する場合の「例」です。データを入れるファイル名は「sched.dat(scheduleの略)」としています。このファイル名は任意の名前でOKですが、今回はランダムファイルとしてデータを格納しますので、拡張子は一般的な「.dat」当たりが適当だと思います。

一方、見え消しにしてある8行目「Public txtAddress As String」では、変数txtAddressの宣言しかしていません。この変数にファイル場所を代入するのは、図6-1の202行目「txtAddress = ThisWorkbook.Path & "¥sched.dat"」になります。
この8行目 + 図6-1の202行目のような設定の仕方は、システムファイルとデータファイルが同じ場所にある時に便利です。
今回の「サンプルファイル」では「8行目 + 図6-1の202行目の方を有効」にし、7行目はコメントアウトしてあります。サンプルファイルさえダウンロードすれば、すぐにも試行できるようにする為ですので、御了承下さい。

10~12行目は、ユーザー定義変数の宣言をしています。
10行目「Type Record」は、ユーザー定義型の変数を「Record」と定義しています。
11行目「Sdata As String * 50」は、変数Recordの中身の1つとして、Sdataを「文字列型50バイト」で変数定義します。

なお、今回は「ユーザー定義型変数内の変数は、Sdataのみ」ですので、ユーザー定義変数を使用せずに「Dim Sdata As String * 50」と宣言することも可能です。但し、試してみるとPutでデータを入れるところ(図4-11の86行目)で、データ先頭に「&H03 &H00」という2バイト分の「余計なもの(&H03は、テキスト終了の意味)」が入ってしまいます。
うまく処理をすれば何とかなりそうですが、今回は固定長ファイルを扱う時の一般的なやり方を踏襲し、ユーザー定義変数を使用しました。また、ユーザー定義変数を使った方が、中身の変数を増やす時も改造が楽だと思います。

4-2.カレンダー本体の作成

カレンダーの日付部分を作るのが、図4-3のmakeCalプロシージャです。引数として、表示カレンダーの初日(〇年〇月1日)の日付を受け取ります。
  1. '========== ⇩(2) カレンダー本体の作成 ============
  2. Public Sub makeCal(yymm As Date)
  3.  Dim k As Date     '←カレンダーのセルに入れる日付
  4.  Dim i As Integer    '←行位置のカウンタ変数
  5.  Dim j As Integer    '←列位置のカウンタ変数
  6.  k = DateAdd("d", -1 * (Weekday(yymm) - 1), yymm)
  7.  For i = 1 To 12 Step 2
  8.   For j = 1 To 7
  9.    Calarray(i, j) = k
  10.    k = k + 1
  11.   Next j
  12.  Next i
  13. End Sub
図4-3

今回カレンダーでは、表示年月の日付だけでなく、カレンダー枠内の「前月の月末」と「次月の月初」の日付も表示します。その月の分の日付だけを表示するのであれば、初日の曜日をWeekday関数で求め、得られた曜日値(日曜=1、月曜=2、・・・)を使って書き出す位置をオフセットさせます。
しかし前月分も表示しようとすると、カレンダーの左上端の位置に表示する日付を求める必要が出てきます。
20行目「k = DateAdd("d", -1 * (Weekday(yymm) - 1), yymm)」では、その日付を計算し、変数kに代入しています。
数式の内容を図4-4で説明します。
カレンダー枠の先頭の日を計算する
図4-4

カレンダー表示月の初日は、引数yymmで受け取っています。その初日の曜日をWeekday関数で計算します。図4-4の場合は木曜日なので「5」という曜日値が得られます。木曜日が初日という事はその前月分としては4つ空いているので、左上端に入る日付は、表示初日に対して「曜日値5 - 1 = 4」日だけ前の日 という事になります。
DateAdd関数を使ってその日を計算するのであれば、「〇〇日だけ」には第一引数に日付の「"d"」を設定し、「前の日」には「マイナス」を付ければ良いため、20行目の式となります。

また今回のカレンダーでは、日付は1~31までの「数値」では無く「日付そのもの」を使っています。セルには日付が貼り付けられる事になりますが、初期のセル書式設定(図6-1の198行目)で「〇日の部分だけを表示」するようにしてあるため、見た目は普通のカレンダーのように見えます。
寄り道
今回カレンダーに「数値」では無く「日付そのもの」を使った理由は、以下になります。
 ・カレンダー枠全てに数字を並べる場合、左上角の日付さえ計算すれば、後は要素の数だけ+1ずつ増やしていけば良い。
 ・日付に紐付いたコメントを日付下に表示する場合、配列の1つ上の要素の日付を元にデータを取得すれば良い。
  (吹き出しコメントで文字列を表示させる場合も、そのセルの日付を取得すれば良い。)
 ・コメントを入力した場合に、その1つ上のセルの値(=日付)で、ファイルに書き込む位置が決まる。
  (日付そのものをクリックし現れたダイアログに文字等を入力する場合も、クリックセルの値を取得すれば良い。)

カレンダー配列の各要素が数値の場合、日付~セル位置の関係を計算する為には、表示月初日と配列内の数値を足し合わせれば求まりますが、前月の日付・次月の日付も含めてしまった場合は、「この数値は今月のものなのか、前月・次月のものなのか」を判断しなければならないことになります。この点からもカレンダーを前月・次月を含めた形にする場合は、「日付型」で日付を作った方が処理しやすい気がします。

またセルに「1,2,3・・・」と数値を貼り付けてカレンダーを構成してしまうと、今回のように「日付の下のセルに書き込んだものを保存」する機能を考える場合、「書き込んだものが、いつの日付のものか」を判断するために再度計算をする必要が生じます。
一方、セルに「日付そのもの」が入っていれば、「書き込んだセル」の1つ上のセル値を見に行くだけで事足ります。

但し、カレンダーの日付を削除されたり上書きされたりすると、後の処理が全く出来なくなり非常に困るので、その対応だけは必ず必要になります。
今回システムでは、日付のセル値が変更されたり、コメントが書き込まれたセルの1つ上のセル(コメントと対応するセル)が日付では無い場合には、カレンダーを更新させて「日付だけは常に正常な状態に保つ」ようにしています。

いずれにしても、「値は日付」なのに書式を変えれば「表示は数値」のように出来るのは、Excelワークシートの大きな特徴です。これを活用しない手は無いと思います。

22~27行目のFor~Next内では、カレンダー配列Calarrayの日付用の要素行に日付値を代入しています。
カレンダー配列Calarrayは、図4-5のように「日付用+コメント用の2行1セット×6セット」の合計12行、列方向は日曜~土曜の7列で出来ています。その「日付用の要素行」に日付を埋めていく形になります。
カレンダー配列の使い方
図4-5

22行目「For i = 1 To 12 Step 2」では、配列の行位置を決めているカウンタ変数iを、「Step 2」と2つ飛びにを動かしていきます。実際にiは「1,3,5,7,9,11」と変化します。11の次は13ですが、「1 To 12」と12が上限ですので、13は実行されません。
23行目「For j = 1 To 7」では、配列の列位置を決めているカウンタ変数jを日曜(1)から土曜(7)まで動かしています。

そして24行目「Calarray(i, j) = k」で、20行目で計算した「カレンダー左上角の日付」から、カレンダー配列Calarrayに代入していきます。そして配列要素を一つずつ右にずらしながら、25行目「k = k + 1」で日付も1日ずつプラスしていきます。
以上のコードにより、配列Calarrayの日付行に、引数yymmの年月のカレンダーが「日付型」として書き込まれます。

4-3.コメントの読み込み

図5-3の136行目から呼び出される「カレンダー配列のコメント要素行にコメントを代入」するのが図4-6です。
  1. '========== ⇩(3) コメントの読み込み ============
  2. Public Sub readSched()
  3.  Dim fileNo As Integer    '←ファイル番号
  4.  Dim dataRec As Record   '←ユーザーレコード変数
  5.  Dim i As Integer      '←行位置のカウンタ変数
  6.  Dim j As Integer      '←列位置のカウンタ変数
  7.  fileNo = FreeFile
  8.  On Error Resume Next
  9.   Open txtAddress For Random As #fileNo Len = Len(dataRec)
  10.   If Not Err.Number = 0 Then
  11.    MsgBox "コメントファイルが見つからず、表示できません。"
  12.    Exit Sub
  13.   End If
  14.  On Error GoTo 0
  15.  For i = 1 To 12 Step 2
  16.   For j = 1 To 7
  17.    On Error Resume Next
  18.     Get #fileNo, Calarray(i, j) - stdD + 1, dataRec
  19.    On Error GoTo 0
  20.    Calarray(i + 1, j) = Trim(Replace(dataRec.Sdata, Chr(0), ""))
  21.   Next j
  22.  Next i
  23.  Close #fileNo
  24. End Sub
図4-6

このプロシージャでは、ランダムファイルからデータを読み込み、カレンダー配列Calarrayの中の対応する日付の場所にデータを代入します。
まず38行目「fileNo = FreeFile」では、FreeFile関数を使って「空いているファイル番号」を取得します。これは、何か他のプログラム(Excel以外も含めて)で既に使用されているファイル番号を使用してしまうとエラーが発生しますので、それを避ける為です。
FreeFile関数には引数を渡す事ができます。引数はゼロまたは1で、今回の様に省略している場合はゼロが指定された事になります。そして、FreeFile(0)の場合には「1~255の範囲のファイル番号」を、FreeFile(1)の場合には「256~511の範囲のファイル番号」を戻してくれます。特に事情が無い限りは、引数省略で良いと思います。

41行目「Open txtAddress For Random As #fileNo Len = Len(dataRec)」では、指定したファイル(txtAddress:図4-1の7行目、または図6-1の202行目で指定)を、ランダムファイル("Random")として開き、ファイル番号を「#fileNo」とします。
なお、Forの後ろに指定する「開き方」には、図4-7のように5種類が存在します。ランダムファイル(固定長)として操作するには「"Random"」を指定します。
キーワードモード処理方法
Input入力モード読込
Output出力モード書込
Append追加モード書込
Randomランダムアクセスモード読込/書込
Binaryバイナリモード読込/書込
図4-7

また、ランダムファイルの1データ分の長さを「Len =」として渡します。1データ分の長さは、図4-1の10~12行目で宣言したユーザー定義変数の全体の長さであるため「Len(dataRec)」と指定します。今回は「Sdata As String * 50」だけが内容物となりますので50バイトとなりますが、他の項目も併せたユーザー定義変数の場合には、全ての長さの合計値となります。

このOpenメソッドが失敗する場合があります。それは「txtAddress」で指定したランダムファイルが見つからない場合です。見つからない理由は、ファイル名が間違っている・ネットワーク通信不良・マクロが途中で中断してしまい変数txtAddressの値が空になってしまった 等です。
このエラーを検出するために、40行目「On Error Resume Next」~48行目「On Error GoTo 0」で、Openメソッドを挟んでいます。

そしてエラーが発生(=ランダムファイルがOpen出来ない)した事を43行目「If Not Err.Number = 0 Then」で検出し、44行目「MsgBox "コメントファイルが見つからず、表示できません。"」でコメントを表示し、45行目「Exit Sub」でカレンダーへのコメント書き出しを中止します。
カレンダーの日付部分の処理は完了しているので、コメント無しの状態でワークシート上にはカレンダーが表示されることになります。

正常にランダムファイルがOpenできた時には、50~58行目で「カレンダー配列のコメント要素行にコメントを書き出し」します。
50行目「For i = 1 To 12 Step 2」では、カレンダー配列Calarrayの行位置を1・3・5・7・9・11行目と1行置きに進めて行きます。51行目「For j = 1 To 7」では、カレンダー配列の列位置を1(日曜)~7(土曜)と進めます。

そして53行目「Get #fileNo, Calarray(i, j) - stdD + 1, dataRec」で、Openしたランダムファイル(#fileNo)の、位置番号(Calarray(i, j) - stdD + 1)のデータを取得(Get)し、変数dataRecに代入します。
今回のランダムファイルは、図4-1の2行目で設定した基準日(定数stdD)のデータを「位置番号=1」として保存しています。その次の日のデータは「位置番号=2」です。そして、カレンダーとして表示する日付はカレンダー配列の日付要素行に既に代入済み(Calarray(i, j))ですので、それと基準日との差(Calarray(i, j) - stdD + 1)が、位置番号となります。

しかし、この「Calarray(i, j) - stdD + 1」の値がゼロ以下となる場合があります。カレンダーの日付が「基準日よりも前」の時です。今回サンプルファイルでは「2022/01/01」を基準日としましたが、2022年1月のカレンダーを表示した時にもエラー処理をしています。カレンダー左上の「前月分の表示日付」について処理する時には、2021年12月分の日付となるからです。
そのため、52行目「On Error Resume Next」でエラーをスルーさせています。なおデータが無い場合には、データとしては「全てNULL」を取得する事になります。

なお、51行目を「For j = 7 To 1 Step -1」などと「右から左(新しい日付→古い日付)方向に処理」してはいけません。基準日よりも前の日付の処理をした場合、53行目のGetステートメントはエラーが発生し変数dataRecには値が入ってきません。
変数dataRecは、このプロシージャ内で使いまわしていますので、正しい値が代入されない限り「その前に代入された値が残っている」ことになります。つまり、基準日以前での処理には「基準日のコメント」が適用されてしまうのです。
すると、その基準日の値を使って56行目でカレンダー配列に値を代入していきますので、図4-8のように「基準日と同じコメント」が基準日以前に書き込まれてしまう事になります。
基準日前後の処理の注意点
図4-8

今回は古い日付から新しい日付へと処理が進むように、行(For i ~)、列(For j ~)の方向を昇順にしています。昇順にすれば、例え基準日を跨ぐカレンダーであっても、「変数値が初期状態(全てNULL)の内に、基準日以前の処理」を行ってしまい、その後で「基準日以降の正しいデータ処理」を行うことで、表示不良を無くしています。
もし降順でカレンダーを並べなければいけない場合は、On Errorで発生したErr.Numberを使い、If文などでカレンダー配列に間違ったデータが代入されないようにする必要があります。

取得したデータをカレンダー配列Calarrayのコメント要素行に代入するのが56行目「Calarray(i + 1, j) = Trim(Replace(dataRec.Sdata, Chr(0), ""))」です。ランダムファイルから取得したデータは変数dataRecですが、このdataRecは「ユーザー定義変数Record型」です。その中からコメントを得るには、その中の変数Sdataにアクセスする必要があります。つまりデータを得るには「dataRec.Sdata」を使います。

この取得したデータですが、ユーザーが入力した文字列が50バイト未満である場合は、残りは「スペース(文字コード&H20)」と「NULL(文字コード&H0)」で埋められています。ですので、まずはReplace関数でNULLを「""(長さゼロの文字列)」に置き換え、その後でTrim関数でスペースを削除した上で、カレンダー配列に代入します。

最後に、60行目「Close #fileNo」で、ファイル(#fileNo)を閉じます。
寄り道
ランダムファイル内では、入力した文字列はレコードの先頭から収められています。つまり50文字のレコードに半角10文字を入れたら、残り40文字分は半角スペース(文字コード&H20)で埋められます。また一度もデータを入れていないレコードは、全てNULL文字(文字コード&H0)となっています。
一方全角文字は、ファイル上では2バイト使用しますので、50文字のレコードでは全角25文字が収められる事になります。
全角と半角を混ぜた場合は、例えば全角を2文字+半角2文字の場合は、バイトで言うと4バイト+2バイトなので、合計6バイトを使用し、残りは半角スペースで埋められる事になります。

では、その全角・半角の文字列をランダムファイルから取り出した後、ExcelのVBA内ではどうなるか です。ExcelのVBAでは、文字列は「2バイト文字セット(DBCS:Double-byte Character Sets)」として扱います。つまり半角だろうが全角だろうが2バイトを使用するのです。

今回システムでは50文字ですが、ここでは説明し易いように1レコードを20文字とします。
図4-9の上側は、ランダムファイル内に半角10文字(A~J)が入っている状態です。1レコード20文字ですので、残りの部分はスペースで埋められています。これをExcel内に持ってくると、Excelでは半角でも2バイト使いますので、20文字×2バイト=40バイト分の枠内に10個の文字+10個のスペースが入ります(図4-9の下側)。スペースも2バイト使っています。
半角のみの場合
図4-9

今度は、全角が含まれる場合を考えます(私の推測を含んでいます)。
図4-10の上側のように、全角5文字(あ~お)+半角5文字(A~E)がランダムファイル内に入っているとします。ファイル内の全角1文字の保管には2バイトが必要ですので、全10文字分に15バイト使用し、残りの5バイトはスペースで埋められています。
それをExcel内に持ってくると、10文字分20バイト+スペース5個分の10バイトとなります。しかしExcel側で準備しているのは20文字分の枠(40バイト)ですから、後ろ側の5文字分(10バイト)に対応するランダムファイル上の文字列が無いのです。
全角が入った場合
図4-10

そして「対応する文字列が無い」ところは、String * 20の初期値である「NULL文字」になるようです。図4-10では全角は5個に対し、NULLも5個です。全角の数だけNULL文字が後ろ側に付くことになります。
String型の初期値は「""(長さゼロの文字列)」ですが、図4-1の11行目「Sdata As String * 50」のように文字数指定した場合の初期値はNULLとなっているようです。

今回とは無関係ですが、SQL文でデータを取得した時も空のデータはNULLとなり、NULLがある配列に対してTranspose関数を使用するとエラーが発生してしまいます。NULLの除去にTrim関数は使えませんし、図4-10のような文字列でTrim関数を使っても、NULLが邪魔して11~15文字目のスペースも削除してくれません。
そのため今回は、Replace関数でNULLを「""(長さゼロの文字列)」に置き換えた後、Trim関数で余分なスペースを削除してます。もちろんReplace関数でNULLをスペースに置き換えてもOKです。
セル上にNULLというものが存在しないExcelにとって、NULLはやっかいな存在です。

4-4.コメントをファイルへ書き込み

図5-5の164行目から呼び出されるのが、図4-11です。役目としては、引数として受け取った「書込みコメント:Comm」をランダムファイルの「日付位置:D」に書き込みます。
  1. '========== ⇩(4) コメントの書き込み ============
  2. Public Sub writeSched(D As Date, Comm As String)
  3.  Dim fileNo As Integer    '←ファイル番号
  4.  Dim dataRec As Record    '←指定レコードのデータ
  5.  If D < stdD Then
  6.   MsgBox "基準日(" & stdD & ")以前にはデータ書込みできません。"
  7.   Exit Sub
  8.  End If
  9.  fileNo = FreeFile
  10.  On Error Resume Next
  11.   Open txtAddress For Random As #fileNo Len = Len(dataRec)
  12.   If Not Err.Number = 0 Then
  13.    MsgBox "コメントファイルが見つからず、書込み未処理です。"
  14.    Exit Sub
  15.   End If
  16.  On Error GoTo 0
  17.  dataRec.Sdata = Comm
  18.  Put #fileNo, D - stdD + 1, dataRec
  19.  Close #fileNo
  20. End Sub
図4-11

68~71行目では、引数Dで指示された書込み日が、設定した基準日(定数stdD)以降か否かを確認しています。コメントファイルには基準日以降のデータしか書き込めない(=基準日を1番目のデータにしている)ので、それ以前の日付を指定された場合には「書き込まない処理」が必要になります。
68行目「If D < stdD Then」で指定日(引数D)と基準日(定数stdD)の大きさ(=日付順)を比較し、基準日以前だった場合に69~70行目を実行します。
69行目「MsgBox "基準日(" & stdD & ")以前にはデータ書込みできません。"」ではコメントを出し、70行目「Exit Sub」で書込み処理のプロシージャを抜け出します。なお、表示コメントには、書込み可能な日付も表示させ、ユーザーがコメント記入日を修正できるようにしています。

73行目「fileNo = FreeFile」は、空いているファイル番号を取得しています。
76行目「Open txtAddress For Random As #fileNo Len = Len(dataRec)」では、コメントファイルをランダムファイルとして開いています。図4-7で、開き方「"Random"」は「読込/書込」となっている事から分かる様に、コメントを読み込む時も書き出す時も、ファイルを開く時のコードは同一です。

そして読み込み時と同様に、ファイル名ミスや通信不良等でファイルが見つからない場合にはOpenメソッドでエラーが発生してしまいますので、75行目「On Error Resume Next」でエラーをスルーさせ、78~81行目でエラー処理をします。
78行目「If Not Err.Number = 0 Then」でエラーが発生していた場合は、79行目「MsgBox "コメントファイルが見つからず、書込み未処理です。"」でコメントを出し、80行目「Exit Sub」で書込み処理を中止させます。

ファイルが開けた時は85行目「dataRec.Sdata = Comm」で、引数で得たコメントの文字列( Comm )を、ユーザー定義変数Record内の変数Sdataに代入します。「dataRec」と「Record」は別物のように見えますが、プロシージャ内の変数としてdataRecを「Record型」として66行目で宣言していますので、型は同じですが「このプロシージャ内で使用できるRecord型の変数」という意味になります。なお今回Record内の変数は1つですが、複数存在する場合にはそれぞれ代入していきます。
そして、86行目「Put #fileNo, D - stdD + 1, dataRec」で、書込み位置「D - stdD + 1」に1データ分「dataRec」を書き込みます。書込み位置は基準日(stdD)と書込み日(D)の差分となります。

最後に88行目「Close #fileNo」で、ファイルを閉じます。

4-5.カレンダーの書式調整

図5-3の142行目から呼び出されるのが、図4-12です。ワークシート上に表示したカレンダーの書式を調整します。引数として、表示しているカレンダーの初日の年月日(yymm)を受け取ります。
  1. '========== ⇩(5) カレンダー書式調整 ============
  2. Public Sub makeColor(yymm As Date)
  3.  Dim i As Integer    '←行方向カウンタ変数
  4.  Dim j As Integer    '←列方向カウンタ変数
  5.  With Range(Calarea)
  6.   .Font.ColorIndex = xlAutomatic
  7.   .Columns(1).Font.Color = RGB(255, 0, 0)
  8.   .Columns(7).Font.Color = RGB(0, 0, 255)
  9.   For i = 1 To 12 Step 2
  10.    For j = 1 To 7
  11.     If Not Month(Calarray(i, j)) = Month(yymm) Then
  12.      .Range(Cells(i, j), Cells(i + 1, j)).Font.Color = RGB(128, 128, 128)
  13.     End If
  14.    Next j
  15.   Next i
  16.  End With
  17. End Sub
図4-12

97行目「With Range(Calarea)」は、以下のコードは「ワークシート上のカレンダー枠内」の操作をすることを表しています。
98行目「.Font.ColorIndex = xlAutomatic」では、まずカレンダー枠内の文字色を標準に戻しています。今回のカレンダーでは土日・前月・次月の「文字の色」しか変更させていませんので98行目のように「文字の色だけを標準に戻す」処理をしています。もし文字サイズの変更等を前月・次月に対して行うのであれば、その変更点に対しても標準に戻す必要が出てきます。

100~101行目は土日の日付・コメントの文字色を変更しています。図4-13のように、カレンダーエリア「Range(Calarea)」内での相対的な列位置は「Range(Calarea).Columns(左からの列の番号)」で得られます。
今回のカレンダーでは、日曜日が左端の列ですから「1」、土曜日が右端の列なので「7」となります。
カレンダーエリア内の列の指定
図4-13

100行目「.Columns(1).Font.Color = RGB(255, 0, 0)」では、カレンダー枠内の1列目データ(=日曜日)の文字色を赤色(RGB(255, 0, 0))にします。
101行目「.Columns(7).Font.Color = RGB(0, 0, 255)」は、カレンダー枠内の7列目データ(=土曜日)の文字色を青色(RGB(0, 0, 255))にします。

103~109行目では、カレンダーの表示年月の日付以外(前月の月末、および次月の月初)の日付・コメントを、目立たないように(≒ 表示カレンダー部分が目立つように)薄いグレー色にしています。
103行目「For i = 1 To 12 Step 2」で行方向を走査し、104行目「For j = 1 To 7」で列方向を走査します。走査をすると、Calarray(i, j)がカレンダーの日付を表すことになります。
105行目「If Not Month(Calarray(i, j)) = Month(yymm) Then」では、カレンダーの「日付の月」と、引数で得た「表示年月の月」を比較します。違っていれば、表示年月の「前月の日付」または「次月の日付」ということになりますので、106行目「.Range(Cells(i, j), Cells(i + 1, j)).Font.Color = RGB(128, 128, 128)」で、日付+コメント( Range(Cells(i, j), Cells(i + 1, j)) )の文字色を薄いグレー色( RGB(128, 128, 128) )にします。
100~101行目で土日には色を付けていますが、106行目では「前月・次月分は、全て薄いグレー色」に上書きしています。

5.ワークシート(Sheet1)

5-1.カレンダー外観

サンプルファイルでは、Sheet1に罫線でカレンダー枠を作成しました。その際、それぞれの日の上段を日付、下段をコメントとするために、2セルで1枠としました。また、各曜日を手入力し、右上に年月を移動するためのスピンボタン(ActiveX コントロール)を配置しました。
日付や表示年月は、全てマクロ側から書き込みますし、書式もマクロ側からコントロールします。
シート上のカレンダー外観
図5-1

5-2.シートモジュール

カレンダーを表示するシートのシートモジュールには、シート上を操作した時のイベント関係のプロシージャを置いています。また、宣言部(図5-2)でフラグ変数を宣言しています。
  1. '========== ⇩(6) モジュール内共通変数の宣言 ============
  2. Dim EventOff As Boolean
図5-2

115行目「Dim EventOff As Boolean」では、変数EventOffを宣言しています。このEventOffは、図5-3のスピンボタンのChangeイベント内で使用するフラグ変数です。Changeイベント内でスピンボタンのValue値をマクロ操作した時に、同じChangeイベントが再帰呼び出しされますが、無駄な実行なので実行を中断するのが目的です。
なお、モジュール変数とせず、プロシージャ内でStatic変数として宣言して再帰呼出しを中断する手法(「複数の備品を同時予約可能な貸出台帳」参照)もあります。

5-2-1.カレンダー表示年月の移動

ワークシート上のカレンダー上部のスピンボタンを操作し、カレンダー年月を移動した時に呼び出されるのが、図5-3のChangeイベントです。また、カレンダーを更新するために図5-5(Worksheet_Change)や図6-1(Workbook_Open)からも呼び出されます。
  1. '========== ⇩(7) カレンダー表示年月の移動 ============
  2. Public Sub SpinButton1_Change()
  3.  Dim D As Date    '←新たな表示カレンダーの初日の日付
  4.  If EventOff = True Then Exit Sub
  5.  If IsDate(Range(StartD).Value) = True And Day(Range(StartD).Value) = 1 Then
  6.   D = DateAdd("m", Me.SpinButton1.Value, Range(StartD).Value)
  7.  Else
  8.   D = DateAdd("d", 1 - Day(Date), Date)
  9.  End If
  10.  Range(StartD).Value = D
  11.  EventOff = True
  12.   Me.SpinButton1.Value = 0
  13.  EventOff = False
  14.  Call makeCal(D)
  15.  Call readSched
  16.  Application.EnableEvents = False
  17.   Range(Calarea) = Calarray
  18.  Application.EnableEvents = True
  19.  Call makeColor(D)
  20. End Sub
図5-3

121行目「If EventOff = True Then Exit Sub」は、フラグ変数EventOffがTrueの場合に処理を中止しています。
初期状態および通常はEventOff値はFalseですが、131行目でTrueが代入され、132行目を実行することによりこのプロシージャが再帰呼び出し(自分から自分を呼び出す)された時のみ、同じ処理の繰り返しを省略させる目的で中断させます。

123~127行目は、表示するカレンダーの年月を計算しています。カレンダー上部のスピンボタンをクリックして「カレンダー年月を移動」させようとした時には、操作したスピンボタンのValue値を見て、前月に移動さるのか次月に移動させるのかを計算します。
なお現在表示しているカレンダーの年月は、定数StartD(サンプルファイルではE2セル)の場所に表示されていますが、その値を削除されてしまったり、表示年月の初日の日付を例えば2日に変えられたりしてしまうと、このシステムの全ての計算が狂ってきます。ですので、年月セルの場所に書かれた値が正しいか否かもチェックする必要があります。
そこで様々な値により、どのような結果が得られるかを図5-4にまとめてみました。
StartD値Range(StartD).ValueIsDate(Range(StartD).Value)Day(Range(StartD).Value)
2022/1/12022/1/1True1
2022/1/22022/1/2True2
(空白)""(長さ0文字列)False30
0(ゼロ値)0False30
1(数値)1False31
2(数値)2False1
(文字列)"あ"Falseエラー(型不一致)
図5-4

まずIsDate関数での結果は、もちろん日付値の場合はTrueとなりますし、文字列の場合はFalseとなります。一方、空白(=年月を削除してしまった)の場合は書式が日付型・標準型のどちらでもFalseになります。
また数値が入った場合、書式が標準の場合には図5-4のようにIsDate関数はFalseになりますが、書式が日付型の場合は「0 → 1900/1/0」「1 → 1900/1/1」「2 → 1900/1/2」と表示されるため、IsDate関数はTrueになります。但し、セルの値はシリアル値としてVBAが受け取りますので、書式に関わらず「0、空白 → 1899/12/30」「1 → 1899/12/31」「2 → 1900/1/1」と判断され、図5-4のようなDay値となります。

図5-4の式の結果を踏まえたのが、123行目「If IsDate(Range(StartD).Value) = True And Day(Range(StartD).Value) = 1 Then」です。この式では、2つのチェックをしています。
1つ目は前半の「IsDate(Range(StartD).Value) = True」です。表示されている値が日付であるか否かをチェックします。日付では無い(数値・文字列など)場合はIsDate関数はFalseを戻します。
2つ目は後半の「Day(Range(StartD).Value) = 1」です。カレンダーの日付を変えてしまった場合でも「日」の部分が「1」であれば正常と見なします。

124行目「D = DateAdd("m", Me.SpinButton1.Value, Range(StartD).Value)」は、123行目のIf文が成立している時(=表示日付が正常で、且つ1日付だった時)に実行されます。
操作したのがスピンボタンのどちら側かで「+1 又は -1」がValue値として得られますので、DateAdd関数で「+1ヶ月 又は -1ヶ月」の移動をします。この時点でセル上の日付は「1日付」なので、移動した先の日付も「1日付」となります。

If文で「セル上の日付がおかしい場合」は126行目「D = DateAdd("d", 1 - Day(Date), Date)」を実行し、今月の初日を変数Dに代入します。
なお「今月の初日」を計算するには、一般的に「DateSerial(Year(Date),Month(Date),1)」などとするのが多いようですが、ここではDateAdd関数を使って「今日の日付の分だけ遡った日が初日」という計算方法にしてみました。ちょっとだけ式がスッキリする気がします。

123~127行目の処理で、新たに表示するカレンダー年月を計算しましたので、その日にち「変数D」を129行目「Range(StartD).Value = D」でセル上の年月部に書き込みます。

131~133行目では、スピンボタンを中立位置(Value値=0)に戻し、次の操作に備えます。但し、スピンボタンのValue値を変更するのですから「スピンボタンのChangeイベントが発生」することになり、この図5-3のChangeイベントプロシージャが再帰呼び出しされることになります。再帰呼び出しされてもValue値はゼロですから見掛け上は変化ありませんが、内部的にはもう一度同じカレンダーを作り直すことになり無駄です。
ですので131行目「EventOff = True」でフラグ変数にTrueを設定したのち、132行目「Me.SpinButton1.Value = 0」でValue値を動かすことで、再帰呼び出しされた先のChangeイベントの121行目「If EventOff = True Then Exit Sub」で、すぐにイベントプロシージャを終了させる事ができます。

135行目「Call makeCal(D)」では図4-3を呼出し、カレンダー配列Calarrayに「日付部分」を格納します。引数として123~127行目で作成した「新しい表示カレンダー年月の初日値(変数D)」を渡します。
次に136行目「Call readSched」では図4-6を呼出し、カレンダー配列Calarrayに「コメント部分」を格納します。

この135~136行目の処理で、カレンダー配列Calarrayが完成しますので、139行目「Range(Calarea) = Calarray」で「カレンダー枠にカレンダー配列を貼り付け」る訳ですが、今回システムでは「コメントの書込みもカレンダー枠内で行う」ようにしていますので、シート上に「Worksheet_Changeイベント(図5-5)」を設定しています。ですので、シート上のセル値を書き換えると「Worksheet_Changeイベント」が発生してしまいます。
Worksheet_Changeイベント内では「日付をユーザーが変更すると元に戻す(カレンダーをその月のままデータ更新する)」様にしていますので、新たなカレンダーデータが表示されないことになってしまいます。
ですので、書き込んでいる間は「イベントが発生しない」ように、138行目「Application.EnableEvents = False」でイベント発生を無効にし、書込み終了後の140行目「Application.EnableEvents = True」で有効に戻しています。

最後に142行目「Call makeColor(D)」で、土日の文字色変更および前月・次月の文字色を薄くする書式変更を行っています。なお、書式変更ではWorksheet_Changeイベントは発生しませんので、出来るだけ速やかにイベント有効に戻すために140行目で有効化を行っています。

なおカレンダー配列CalarrayのEraseなどを使ったクリア化は、今回システムでは必要ありません。と言うのは、今回は前月・次月の日付・コメントも表示しているために、カレンダー配列(12行×7列)の「全ての要素を上書き」しているためです。もし当月の日付・コメントのみをカレンダーにする場合は、古いカレンダーのデータがカレンダー配列内に残っている可能性があるので、135行目の前あたりでクリア化するコードが必要になります。

5-2-2.コメントの書き換え処理

シート上のカレンダーのコメントを「手動で書き換えた」時に発生するChangeイベントが図5-5です。引数としては「変更されたセル範囲(Target)」を受取ります。
  1. '========== ⇩(8) コメントの書き換え処理 ============
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.  Dim T As Range    '←変更されたセル範囲内の単一セル
  4.  For Each T In Target
  5.   If Not Intersect(T, Range(Calarea)) Is Nothing Then
  6.     If T.Row Mod 2 = (Range(Calarea).Row) Mod 2 Then
  7.      Call SpinButton1_Change
  8.     Else
  9.      If IsDate(T.Offset(-1, 0).Value) = False Then Call SpinButton1_Change
  10.      If LenB(StrConv(T.Value, vbFromUnicode)) > 50 Then
  11.       MsgBox "制限文字数(50バイト)を超えています。" & vbLf & vbLf & _
  12.           StrConv(LeftB(StrConv(T.Value, vbFromUnicode), 50), vbUnicode)
  13.      End If
  14.      Call writeSched(T.Offset(-1, 0).Value, T.Value)
  15.     End If
  16.   End If
  17.  Next T
  18. End Sub
図5-5

セルを書き換えた時には、Worksheet_Changeイベントが1回発生します。しかし、どこかの複数セルを「コピぺ」したり、連続値を入れる時のように「セル右下のフィルハンドルをドラッグ」した時にも、発生するイベントは1回だけです。
つまり147行目で受け取るセル範囲Targetは、単一セルの場合もありますし、複数セル範囲である可能性もあるのです。
複数セルの場合でも「1つ1つのセルの事情は異なる可能性がある」ため、150行目「For Each T In Target」で1つ1つのセル毎に処理を行います。

152行目「If Not Intersect(T, Range(Calarea)) Is Nothing Then」では、調べる単一セル「T」が「カレンダー枠内(Range(Calarea))」に入っているのか否かを調べています。Intersectメソッドは引数のセル範囲の「重なっているセル範囲」を戻してくれますので、Not ~ Is Nothing で「重なっていない事は無い → 重なっている → カレンダー枠内にTが存在する」ことになります。
セル範囲Tがカレンダー枠内の場合は、154~165行目を実行し、枠外の場合は何もせずに終了します。

154行目「If T.Row Mod 2 = (Range(Calarea).Row) Mod 2 Then」は、処理しようとしているセル範囲Tが「日付の セル」なのかを調べています。
カレンダー枠は、図5-6のようにシート内の「Range(Calarea)」というセル範囲に表示されており、その先頭行が「Range(Calarea).Row」です。また今回は、「日付セル+コメントセル」を縦方向に「2行セット」にしたカレンダーを使用しています
ですので、調べるセルの行位置とカレンダー枠先頭行の行位置を、それぞれ「2行」の「2」で割った余りが同じであれば「各セットの先頭」ということになります。
処理するセルの種類を取得
図5-6

154行目のIf文が成立する場合は「各セットの先頭=日付部」ですので、155行目を実行します。成立しない場合は「各セットの下部=コメント部」となり、157~164行目を実行します。

寄り道
ここで、各セルの「編集のされ方」と「処理の仕方」を図5-7で整理します。 複数セルを一度に変更された場合の処理
図5-7

図5-7の⑤は、正常な方法でコメントを記入した時です。この時は、コメントセルの一つ上のセルの日付に従ってファイルにデータを保存します。
④は、日付のセルを削除した、又は書き換えた時です。今回カレンダーは日付を「日付型」で記入し、その日付値に従ってファイルへ保存等をしています。その日付が変更されてしまったのですから「元の状態に戻す」しかありません。ですのでカレンダーを更新し、日付を元に戻します。

①は、複数セルをまとめて変更(範囲削除、またはコピペ)した場合の内、変更範囲の右上セル(範囲の中の1番目)が日付の場合です。Worksheet_Changeイベント内では、基本的に範囲の中の1番目のセルから処理を行っていきますので、処理としては④と同じ処理(カレンダーの更新)になります。更新された時に、入力したコメントは消える(=保存データで上書きされる)ため、既存のコメントが再保存されます。
②は、複数セルをまとめて変更した場合の内、変更範囲の右上セルがコメントの場合です。最初に処理されるのはコメントセルで、変更したコメントセルの1つ上の日付セルは正常のままですので、⑤の処理と同様にファイルに保存します。次に処理される日付セルは④と同じく、カレンダーを更新します。

やっかいなのは③です。手法としては、以下のようなものが考えられます。
 ・どこかのセルをコピー後、「コメントセル選択」→Ctrlキーを押しながら「日付セル選択」し、ペースト
 ・「コメントセル選択」→Ctrlキーを押しながら「日付セル選択」し、Delキーで削除
この時、Changeイベント内の処理は、まずコメントセルから行われます。その段階で日付が変更されているか否かを判断するのは難しく、今回は「処理しようとしているコメントセルの1つ上の日付セルが日付型であれば処理に進む」ことにしました。Deleteしたり、貼り付ける値が数値や文字列であれば日付型チェックで「変更された」ことが判断できるのですが、「日付を貼り付けられる」と日付型チェックでは異常事態と判断できずに「カレンダーの日付では無く、貼り付けた日付のコメントとしてファイル保存」してしまいます。
すべての条件は洗い出されていないとは思いますが、少なくとも完全で無いことは確かです。今回は「意図的な意地悪操作は許容」してしまう仕様となりました。

日付部を間違って編集した場合は、今回システムの基本部分(日にちを日付型で記入している所)が破壊された事になるので、155行目「Call SpinButton1_Change」で図5-3を呼出し「月を変更しないまま疑似的にスピンボタンを押した」ことにし、カレンダーを更新しています。

コメント部が編集された場合は、まず157行目「If IsDate(T.Offset(-1, 0).Value) = False Then Call SpinButton1_Change」で、変更されたコメントセルの1つ上の日付部を調べ、もし日付以外に変更されていたら、カレンダーを更新します。

日付部が問題ないとしても、コメントの文字数が制限超であればユーザーに注意を促す必要があります。159行目「If LenB(StrConv(T.Value, vbFromUnicode)) > 50 Then」では、まず入力されたセル上の文字列(T.Value)を「システム既定のコード (Shift_JIS)に変換し、バイト数を数えます。Shift_JISだと「全角文字=2バイト」「半角文字=1バイト」となります。
その文字数がファイルに保管できる限界(今回は50バイトに設定)を超えている場合は、160~161行目でダイアログを表示します。表示する内容としては、160行目「MsgBox "制限文字数(50バイト)を超えています。" & vbLf & vbLf & _」という忠告に加え、161行目「StrConv(LeftB(StrConv(T.Value, vbFromUnicode), 50), vbUnicode)」で、保存可能な50バイト分をLeftB関数で先頭側から切り出して表示させます(図2-3の中央の図)。
ユーザーは、この保存可能な文字列を見て、修正後に再保存できるようにしています。

最後に164行目「Call writeSched(T.Offset(-1, 0).Value, T.Value)」で、図4-11を呼出し、ファイルにコメントを保存します。引数には、一つ上のセルの対応日付と、記入したコメント文字列を渡します。
なお、入力文字数が50バイトを超えていてもダイアログを表示した後に164行目が実行されますのでデータ保存されますが、図4-11の85行目「dataRec.Sdata = Comm」で変数に代入した時に50バイトに切り捨てた上で保存されます。

6.ブックモジュール(ThisWorkbook)

システム(Excelブック)を起動する際に実行されるのが図6-1です。カレンダーの書式設定とスピンボタンの設定をし、最後にカレンダーの更新を行い表示コメントを最新状態にしています。
  1. '========== ⇩(9) カレンダーの初期設定 ============
  2. Private Sub Workbook_Open()
  3.  Dim i As Integer    '←カレンダー日付行の行位置カウンタ変数
  4.  With Sheet1
  5.   With .SpinButton1
  6.    .Max = 1
  7.    .Min = -1
  8.    .Value = 0
  9.   End With
  10.   With .Range(StartD)
  11.    .Font.Bold = True
  12.    .NumberFormatLocal = "yyyy""年""m""月"""
  13.    .HorizontalAlignment = xlCenter
  14.   End With
  15.   With .Range(Calarea)
  16.    .Font.Bold = True
  17.    .HorizontalAlignment = xlCenter
  18.    .WrapText = True
  19.    .EntireRow.AutoFit
  20.    For i = 1 To 12 Step 2
  21.     .Rows(i).NumberFormatLocal = "d"
  22.    Next i
  23.   End With
  24.   txtAddress = ThisWorkbook.Path & "¥sched.dat"    '←ファイルをExceと同じ場所に置く時に使用
  25.   Call .SpinButton1_Change
  26.  End With
  27. End Sub
図6-1

177行目「With Sheet1」で、以下の設定をSheet1のカレンダーについて行っていきます。

179~183行目では、179行目「With .SpinButton1」と、カレンダー年月移動用のスピンボタンの設定を行っています。
180行目「.Max = 1」では、スピンボタンの最大値を+1に設定、181行目「.Min = -1」では最小値を-1に設定しています。また182行目「.Value = 0」で中立位置をゼロにしています。
これにより、スピンボタンのプラス方向のクリックで1ヶ月進み、マイナス方向のクリックで1ヶ月戻ることになります。

なお、スピンボタンのValue値の既定値はゼロであり、またシステムを終了した時もValue値はゼロであるため、182行目は実質的には意味ありませんし、実行によりスピンボタンのChangeイベントも発生しません。しかし、もしChangeイベントが発生してしまう状況が起こると、データファイル場所の設定(202行目)の前ですので「ファイルが見つかりません」というコメントが出されてしまう可能性があります。その場合には202行目をこのプロシージャの先頭部分で実行するようにして下さい。

185~189行目では、185行目「With .Range(StartD)」と、表示年月セル(サンプルファイルではE2セル)の設定を行います。
186行目「.Font.Bold = True」では、年月表示を太文字にし、187行目「.NumberFormatLocal = "yyyy""年""m""月"""」では表示形式を「〇〇〇〇年〇月」としています。月は、一桁の時は一桁表示としています。
188行目「.HorizontalAlignment = xlCenter」では、セル内での横方向の位置を中央揃えとします。

191~200行目では、191行目「With .Range(Calarea)」と、カレンダー枠内の設定を行っています。
192行目「.Font.Bold = True」では、表示文字を太文字に設定し、193行目「.HorizontalAlignment = xlCenter」では、セル内での横方向の位置を中央揃えとします。
194行目「.WrapText = True」では、文字列をセル内で折り返して「セル内のみで表示」するようにしています。但しこの設定を行うと、長いコメントの場合は行高さが広がり、全体としてのバランスが崩れるデメリットがあります。
195行目「.EntireRow.AutoFit」は、行高さを自動にしています。この設定により、改行のあるコメントは複数行表示されることになるので、WrapText設定と同様にバランスが崩れるデメリットがあります。なお、この設定をせずに「手動で行高さを変更」してしまうと、改行を含めたコメントを作っても表示されない事になります。

197~199行目は、カレンダー内の日付部分についてのセル書式を「日付のみ」の「"d"」に設定しています。
カレンダー枠全12行の内、日付部分は6列分ですので197行目「For i = 1 To 12 Step 2」で、1・3・5・・と1行飛ばしで設定をしていきます。
そして198行目「.Rows(i).NumberFormatLocal = "d"」で「数字(=シリアル値)は日数(1~31)で」表示させています。もし01・02・・・と常に二桁で表示したいのであれば「"dd"」と設定して下さい。

202行目「txtAddress = ThisWorkbook.Path & "¥sched.dat"」は、図6-1上では見え消しにしていますが、サンプルファイルでは有効にしています。データファイルをシステムのExcelファイルと同じ場所に置く場合は図6-2のように、図4-1の8行目と併せて有効にして下さい。またその際には、定数設定(図4-1の7行目)は無効にして下さい。
データファイル設定の組合せを図6-2にまとめておきます。
データファイルの保管場所データ = Excelデータ ≠ Excel
図4-1 7行目Const txtAddress = ・・・×
図4-1 8行目Public txtAddress As String×
図6-1 202行目txtAddress = ThisWorkbook.Path & "¥sched.dat"×
図6-2

203行目「Call .SpinButton1_Change」では、システム起動時(ブックOpen時)にコメントデータを最新状態にするため、図5-3のスピンボタンのChangeイベントを呼出して、カレンダー更新をします。なお、Changeイベントを呼び出してもスピンボタンのValue値はゼロのままですので、カレンダーの年月が移動してしまうことはありません。

7.最後に

固定長ファイルは、欲しいデータだけを取り出せ、保存も1データだけを保存可能というメリットがある一方で、データの順番(=インデックス)をどううまく設定するかというのがポイントのような気がします。その点、今回のカレンダーの様に「日付=インデックス」と出来るようなシステムであれば、固定長のメリットを有効に使える気がしますが、インデックスをワード(数値以外)にしようとするとなかなか難しそうです。
固定長ファイルとCSVファイルを上手く組み合わせて使うアプリも今後考えていきたいと思います。

また「よりみち」でも説明しましたが、日付の下にコメントを書くという今回の方法は、ユーザーはあまり変な操作はしない ということを前提に成り立つ気がします。もしもう少し防御をするのであれば、日付セルは保護を掛けてしまうとか、日付だけのカレンダーにして、コメントは吹き出し(文字通り、コメント)形式で表示をするとかが必要になるかと思います。
但し入力の手数が増え、Excel特有の気楽さが減り、ユーザー側にはデメリットが増えていく方向になります。
あまり人を疑いたくない性分なのでガチガチにはしたく無いのですが、使われる環境によって強弱を考えるようだと思います。


共有コメント付きカレンダー(it-075.xlsm)
コメントデータ(sched.dat)

サンプルのExcelファイルのみでも使用可能です。Excelファイル起動時にコメントデータファイルは自動的に作成されます。
なお、添付のコメントデータ(適当な内容)を使用する場合は、Excelファイルと同じ場所に保存して下さい。

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