Office Space: Excel のオートフィル機能を使用してカレンダーを作成する
.jpg "Office Space")
Office Space へようこそ。Office Space は、Microsoft® Office アプリケーションのスクリプト作成に関するヒントとテクニックを紹介する新しいコラムです。過去のヒントについては、Office Space アーカイブを参照してください。Microsoft Office でのスクリプト作成について質問がある場合は、scripter@microsoft.com (英語のみ) までお送りください。すべての質問に回答することはできないかもしれませんが、可能な限り対応いたします。
Excel のオートフィル機能を使用してカレンダーを作成する
実のところ何の裏付けもありませんが (もちろん私たちは、そのような取るに足らないことで引き下がったりはしません)、現在使用されている Excel スプレッドシートのほとんどが予算や経理などの Excel 本来の用途とは無関係であることは断言できます。Microsoft 社内での経験から判断して、人々は Excel にさまざまな変わった用途を見いだしているようです。それは単に Excel のグリッド形式を使用すると作業しやすいからに過ぎません。確かに、Excel は往々にして本来の目的とは異なることに使用されますが、それを私たちのだれが裁けるというのでしょうか。何しろ、Scripting Guys にはスクリプトを作成するときにメモ帳以外のツールを使用したことのない者が少なくとも 1 人はいるのです。
Excel が好まれる点の 1 つに、カレンダー (日付を配列しただけのリストも含む) を作成できることがあります。たとえば、2005 年のすべての日付を示すリストが必要な場合、365 個の日付を 1 つずつ入力する必要はありません。セル A1 に 1/1/2005 と入力するだけで、残りの 364 日は Excel のオートフィル機能によって自動的に生成されます。最終的に、次のようなスプレッドシートができあがります。おそらく 2 秒もあれば完成するでしょう。
.jpg "Microsoft Excel")
よくできていると思いませんか。でも、この機能はスクリプトを作成するときにどう役立つのか。オートフィル機能はスクリプトでも使用できるのか。カレンダーや日付のリストだけでなくオートフィル機能で作成できるものはすべてスクリプトでも作成できるのか。そうお聞きになると思っていました。
これらの問いに対する答えとして、スプレッドシートの列 A に 1 年のすべての日付を (各セルに 1 日ずつ) 表示するスクリプトを次に示します (このスクリプトが作成するスプレッドシートは上のスクリーンショットに示したものとまったく同じです)。このスクリプトは 1 つの日付しか入力しません。セル A1 に 1/1/2005 という値を書き込みます。その後、AutoFill メソッドを使用し、残りの日付を自動生成します。
まずスクリプトを示し、その後でスクリプトの動作について説明します。
Const xlFillDays = 5
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = #1/1/2005#
Set objRange1 = objExcel.Range("A1")
Set objRange2 = objExcel.Range("A1:A365")
objRange1.AutoFill objRange2, xlFillDays
objRange1.EntireColumn.Autofit
まず、定数 xlFillDays を作成しています。この定数を使用して、AutoFill メソッドに対してスプレッドシートに日付を入力することを指定します。次に、いつものとおり、Excel.Application オブジェクトのインスタンスを作成し、Visible プロパティを True に設定して (これによって操作の状況を画面上で確認できます)、新しいワークブックと操作の対象とする空白のワークシートを作成します。最後に、次のコード行を使用して値 1/1/2005 をセル A1 に書き込みます。
objExcel.Cells(1, 1).Value = #1/1/2005#
日付をシャープ記号で囲んでいることに注意してください。VBScript では、このようにすることで値が確実に日付として扱われ、他のデータと誤解される可能性がなくなります。
ここまでは問題ありませんか。準備が整ったので、次に示すコードを使用して 2 つの Range オブジェクトを作成します。
Set objRange1 = objExcel.Range("A1")
Set objRange2 = objExcel.Range("A1:A365")
1 つ目の Range オブジェクト (objRange1) はリストの始点 (セル A1) を表します。この範囲に 1/1/2005 を入力したため、カレンダーは 2005 年 1 月 1 日から始まります。では、セル A1 に 4/15/2006 と入力すると、どうなるでしょうか。もうおわかりですね。そのとおり、カレンダーは 2006 年 4 月 15 日から始まります。
2 つ目の Range オブジェクト (objRange2) は日付を入力するセルのグループを表します。この範囲がセル A1 からセル A365 までである点に注意してください。2005 年も第 1 日から第 365 日までであるのは偶然の一致ではありません。2005 年の年頭から 100 日目までのカレンダーを作成する場合は、2 つ目の範囲を A1 ～ A100 に設定します。この点に関しては、わかりにくい所はありません。
次にオートフィル機能を使用しますが、そのコードは次の 1 行だけです。
objRange1.AutoFill objRange2, xlFillDays
ご覧のとおり、リストの始点である objRange1 に対して AutoFill を呼び出しています。その後、AutoFill に 2 つのパラメータを渡します。1 つはオートフィル機能によって生成するリストの範囲 (objRange2) であり、もう 1 つは生成するリストの種類 (xlFillDays) です。これで終わりです。最後に列 A のサイズを自動調整するコードを追加しますが、これはスプレッドシートの見栄えを良くしているだけです。先ほど言ったとおり、オートフィル機能自体の実行に必要なコードは 1 行だけです。
もうお気付きかもしれませんが、オートフィル機能では他にもたくさんのおもしろいことができます。たとえば、次に示すスクリプトでは、前のスクリプトと異なる定数 (xlFillWeekdays) を定義し、値を 6 に設定しています。この定数を定義し、それを AutoFill メソッドの呼び出しに際して使用することを除き、このスクリプトは前のスクリプトとまったく同じです。
Const xlFillWeekdays = 6
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = #1/1/2005#
Set objRange1 = objExcel.Range("A1")
Set objRange2 = objExcel.Range("A1:A365")
objRange1.AutoFill objRange2, xlFillWeekdays
objRange1.EntireColumn.Autofit
ただし、作成されるスプレッドシートはまったく異なっています。下の出力結果を見てください。土曜日と日曜日がありません。xlFillWeekdays によって、月曜日から金曜日までの日付だけを表示し、週末の 2 日を除外するように指定しているからです。
.jpg "Microsoft Excel")
平日を除いた土曜日と日曜日だけのカレンダーが必要な場合もあります。このようなカレンダーも作成してみます。
次に示すスクリプトも最初のスクリプトに変更を加えたものです。このスクリプトでは 2 つの値を設定します。セル A1 には 1/1/2005 と入力し、セル A2 には 1/8/2005 と入力します (つまり、ちょうど 1 週間離れた 2 つの日付を入力します)。次に、objRange1 の値をセル A1 とセル A2 の両方にまたがる範囲に設定します。いったいなぜこのようなことをするかというと、Excel はきわめて知能的なアプリケーションであり、2 つの日付が 1 週間離れていることを認識すると、そのパターンを自動的に繰り返します。つまり、次のようなスプレッドシートが作成されます。
.jpg "Microsoft Excel")
よくできていると思いませんか。この妙技とも言える処理を実行するスクリプトを次に示します。
Const xlFillSeries = 2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = #1/1/2005#
objExcel.Cells(2, 1).Value = #1/8/2005#
Set objRange1 = objExcel.Range("A1:A2")
Set objRange2 = objExcel.Range("A1:A365")
objRange1.AutoFill objRange2, xlFillSeries
objRange1.EntireColumn.Autofit
ついでにもう 1 つスクリプトを紹介します。オートフィル機能によって自動的に入力できるのは日付だけではありません。たとえば、単なる番号も自動生成することができます (AutoFill メソッドの詳細と、このメソッドによって実行できる操作については、「Microsoft Excel VBA Language Reference」(英語) を参照してください)。たとえば、次のスクリプトでは、定数 xlFillSeries (値は 2) を使用して、ある範囲に一連の番号を入力しています。コード内の 2 つの Range オブジェクトを指定した箇所を見ると、セル A1 に 1 を入力することにより、セル A365 に 365 が入力されることがわかります。以下にコードを示します。
Const xlFillSeries = 2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = 1
Set objRange1 = objExcel.Range("A1")
Set objRange2 = objExcel.Range("A1:A365")
objRange1.AutoFill objRange2, xlFillSeries
objRange1.EntireColumn.Autofit
次に、このスクリプトに少し手を加えたスクリプトを示します。今度は、セル A1 に 5 を入力し、セル A2 に 10 を入力します。もうおわかりだと思いますが、objRange2 で指定した範囲には 5 ずつ増える値 (5、10、15、20 ...) が入力されます。
Const xlFillSeries = 2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = 5
objExcel.Cells(2, 1).Value = 10
Set objRange1 = objExcel.Range("A1:A2")
Set objRange2 = objExcel.Range("A1:A365")
objRange1.AutoFill objRange2, xlFillSeries
objRange1.EntireColumn.Autofit
このスクリプトを実行し、結果を確認してください。