メンテナンス性や実行時間を改善――複数のExcelデータを1箇所に集約するマクロの別解とは：働く価値を上げる“VBA／GAS”術（9）（3/3 ページ）

[高橋宣成，プランノーツ]

テーブルを使った経費データを収集するプロシージャ

　まず、Subプロシージャ「経費データ収集()」について解説します。処理のおおまかな流れは以下のようになっています。

1. FileSystemオブジェクトの生成
2. 各変数の宣言および準備
3. 「経費収集」シートの「ListRows」コレクションを取得
4. フォルダ「経費精算書」内の全てのファイルに以下の処理を繰り返し
   　┣対象となっているファイルを開く
   　┣データ「対象月」「部署」「氏名」を変数に格納
   　┣開いたファイルの全ての「ListRow」オブジェクトについて繰り返し
   　┃┣「経費収集」シートのListRowsコレクションにListRowオブジェクトを追加
   　┃┗追加したListRowオブジェクトに各データを転記する
   　┗開いたファイルを閉じる

　今回、新たに登場したポイントを解説します。

ListObjectオブジェクトとListRowオブジェクト

　VBAでは「テーブル」を「ListObject」オブジェクトとして操作できます。オブジェクト名が機能名とリンクしていませんが、Microsoft Office Excel 2003では「リスト」と呼ばれていたものが、Microsoft Office Excel 2007以降に「テーブル」という名称に変わったためです。

　ListObjectオブジェクトは、シート上に複数存在し得るので、Worksheetオブジェクトから「ListObjects」プロパティを使ってコレクションとして取得します。

Worksheetオブジェクト.ListObjects

　また、ListObjectsコレクションから、特定のListObjectオブジェクトを取り出すには、「インデックス」を使用して、以下のように指定します。

ListObjectsコレクション(インデックス)

　多くの場合、シート上にテーブルは1つしか存在しませんので、その際はインデックスを「1」と指定します。また、インデックスではなく、テーブル名を文字列で指定することも可能です。

　さて、ListObjectオブジェクトの配下のオブジェクトとして、「ListRow」オブジェクトがあります。このオブジェクトは、テーブルのヘッダ行、合計行ではない純粋なデータ行を表すオブジェクトです。

　ListRowオブジェクトは単体の行を表すものですから、テーブルに複数存在しています。従って、ListObjectオブジェクトから「ListRows」プロパティでコレクションとして取得します。

ListObjectオブジェクト.ListRows

　プロシージャ内の以下の部分は、「経費収集」シートのテーブルのListRowsコレクションを、変数rowsSheet1に格納するものです。

Dim rowsSheet1 As ListRows
Set rowsSheet1 = Sheet1.ListObjects(1).ListRows

　Sheet1上の唯一のテーブルをListObjectオブジェクトとして取得、さらにそのテーブルのデータ行の集合をListRowコレクションとして取得したことになります。

テーブルのデータ行についてのループ

　続いて以下の部分を解説します。

Dim row As ListRow
For Each row In .ListRows
　	'処理
Next row

　「.ListRows」の直前にあるWith文により省略されているオブジェクトは、「Workbooks.Open(f)で開いたワークブックの、インデックス(1)のWorksheetオブジェクトの、インデックス(1)のListObjectオブジェクト」です。つまり、「.ListRows」は、経費精算書のテーブルのデータ行コレクションとなります。

　そのListRowsコレクションの全データ行について、取り出すループ処理を行います。注目すべき点は、「For Each」文でListRowsコレクションのループを使うのであれば、「開始行数や終了行数などの値を指定する必要がない」ということです。テーブルがシート上の何行目から配置されていても、テーブルの行数が何行であっても、上記のステートメントで対応できます。この点は、テーブルを使用する大きなメリットです。

テーブルに新たなデータ行を追加する

　さて、ループ処理の内容について見ていきましょう。まず、以下のような処理があります。

Dim rowSheet1 As ListRow
Set rowSheet1 = rowsSheet1.Add

　「経費収集」のListRowsコレクションである「rowsSheet1」に「Add」メソッドを実施し、それをrowSheet1という変数にセットしています。ListRowsコレクションにAddメソッドを使うことで、新たなデータ行を追加します。

ListRowsコレクション.Add

　その戻り値として、今まさに追加したListRowオブジェクトが得られますので、それをすぐさま変数にセットします。

　変数rowsSheet1と変数rowSheet1は非常に似ているので混乱するかもしれません。rowsSheet1はListRowsコレクションをセットする変数、rowSheet1はListRowオブジェクトをセットする変数です。「s」があるかないかで単体とコレクションを区別しています。

ListRowオブジェクトのセル範囲を取得する

　ListRowオブジェクトに対して直接データの読み書きができないので、読み取り元である経費精算書の現在のデータ行も、書き込み先である「経費収集」シートの新規追加したデータ行も、いったんRangeオブジェクトとして取得する必要があります。

　ListRowオブジェクトにRangeプロパティを使うことで、そのセル範囲を取得できます。

ListRowオブジェクト.Range

　なお、ListRowオブジェクトは「単体の行」ですから、取得したRangeオブジェクトの行数は1、列数はテーブルの列数と等しくなります。

　経費精算書のListRowオブジェクトからRangeオブジェクトを取得する部分は、プロシージャ内の以下の箇所で行います。

Dim rng As Range
Set rng = row.Range

　一方、「経費収集」シートのListRowオブジェクトからRangeオブジェクトを取得し、そこにデータを書き込む部分が以下の部分です。

rowSheet1.Range = Array( _
　　month, _
　　rng(e.日付), _
　　department, _
　　fullname, _
　　rng(e.科目), _
　　rng(e.内容), _
　　rng(e.金額), _
　　rng(e.備考) _
)

　この部分に関しては幾つかポイントがあります。

セル範囲にまとめて値を書き込む

　Rangeオブジェクトの行数が1であれば、セル範囲に複数の値をまとめて書き込むことができます。以下のように、「Array」関数を使います。

Rangeオブジェクト = Array(値1, 値2,……)

　シートやセルへのアクセスは、処理時間が長くなる傾向があります。セル一つ一つに処理をするよりも、まとめて実行することで処理時間を短縮できます。今回のテストデータの数であれば、さほどその差は感じられないかもしれませんが、データが多くなるに従って、その差がはっきりしてくるでしょう。

Rangeオブジェクトから単体セルを取り出す

　また、Array関数内の引数に以下のような指定しています。

rng(e.日付)

　Rangeオブジェクトに対して以下のようにすることで、インデックスに対応した単体セルをRangeオブジェクトとして取り出すことができます。

Range(インデックス)

　Rangeオブジェクトのセルには左から右へ、そして次に上から下へという順番に1から始まるインデックスが付与されています。変数rngは経費精算書のデータ行から取り出したRangeオブジェクトで、その行数は1です。従って、インデックスに指定した数値が、そのまま取得するセルの列数になります。例えば、Range(1)は対象行の1列目のセルということになります。

　ところで、Rangeオブジェクトから値を取得するにはValueプロパティを使いますが、今回のコードでは省略しています。RangeオブジェクトのValueプロパティは、「既定のプロパティ」といって省略できます。可読性を考えた場合、省略し過ぎも良くない場合がありますが、今回は一つ一つ「.Value」を指定するとくどくなるので、省略しています。

　なお、変数rngの値を配列にいったん格納することで、その値の取り出しの処理も高速化することができます。今回、その方法は解説しませんが、興味があればチャレンジしてみてください。

列挙体を使用して列番号を指定する

　Rangeオブジェクトのインデックスとして「e.日付」「e.科目」といった内容を指定している箇所があります。これは列挙体というもので、変数のグループのような仕組みです。

　列挙体はプロシージャ内ではなくて、モジュールの宣言セクション（モジュールの一番上の領域）に以下のように宣言をします。これにより、モジュール内で「列挙体名.メンバー名」として、メンバー名に対応した値を呼び出すことができます。

Enum 列挙体名
	メンバー名1 = 値1
	メンバー名2 = 値2
	…
End Enum

　値1,値2,...は整数値を指定します。値1を省略すると0が入り、値2以降を省略すると、直前のメンバーの値にプラス1した整数が入ります。

　今回の例のように、列挙体に列番号を割り当てておくことで、コードの可読性を高くしつつ、列の挿入などメンテナンスもしやすくなります。

プロシージャ全体を確認

　解説は以上です。再度プロシージャ「経費データ収集」の内容を確認してみましょう。

Enum e
　　日付 = 1
　　科目
　　訪問先名
　　支払先名
　　内容
　　金額
　　備考
End Enum
　
Sub 経費データ収集()
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim pass As String
pass = ThisWorkbook.Path & "\経費精算書"
Dim month As Date, department As String, fullname As String '対象月,部署,氏名
　
Dim rowsSheet1 As ListRows
Set rowsSheet1 = Sheet1.ListObjects(1).ListRows
　
Dim f As file
For Each f In fso.GetFolder(pass).Files
　　With Workbooks.Open(f)
　　　　With .Worksheets(1)
　　　　　　month = .Range("G1").Value　　　'対象月
　　　　　　department = .Range("G3").Value '部署
　　　　　　fullname = .Range("G4").Value　 '氏名
　　　　　　
　　　　　　With .ListObjects(1)
　　　　　　　　Dim row As ListRow
　　　　　　　　For Each row In .ListRows
　
　　　　　　　　　　Dim rowSheet1 As ListRow
　　　　　　　　　　Set rowSheet1 = rowsSheet1.Add
　
　　　　　　　　　　Dim rng As Range
　　　　　　　　　　Set rng = row.Range
　　　　　　　　　　rowSheet1.Range = Array( _
　　　　　　　　　　　　month, _
　　　　　　　　　　　　rng(e.日付), _
　　　　　　　　　　　　department, _
　　　　　　　　　　　　fullname, _
　　　　　　　　　　　　rng(e.科目), _
　　　　　　　　　　　　rng(e.内容), _
　　　　　　　　　　　　rng(e.金額), _
　　　　　　　　　　　　rng(e.備考) _
　　　　　　　　　　)
　　　　　　　　Next row
　　　　　　End With
　　　　End With
　　　　.Close
　　End With
Next f
Set fso = Nothing
　
End Sub

次回はGoogle Apps Scriptの経費精算スクリプトを改善

　今回は、Excel VBAの経費精算データを収集するマクロの改善テクニックについて紹介しました。スキルや知識が必要になりますが、より再利用やメンテナンスがしやすくなり、実行速度に関して改善も行いました。今回紹介した以外にもより良いテクニックがあるかもしれません。プログラムの作り方、書き方について、こだわって取り組んでみてください。

　次回は第8回で紹介した、GASで作成した同様のツールについて、そのスクリプトを改善します。どうぞお楽しみに。

著者プロフィール

高橋宣成

プランノーツ 代表取締役

「ITを活用して日本の『働く』の価値を高める」をテーマに、ExcelやVBA、G Suite、Google Apps Script、クラウドなどによる企業または個人事業主向けのシステムおよびツールの開発やコンサルティング、セミナー講師などを務める。「IT×働き方」をテーマに運営するブログ「いつも隣にITのお仕事」は月間60万PV達成。

書籍紹介

ExcelVBAを実務で使い倒す技術

高橋宣成著 秀和システム 1800円（税別）

動くコードが書けたその先、つまり「ExcelVBAを実務で使う」という目的に特化した実践書。ExcelVBAを楽に効果的に使いこなし続けるための知恵と知識、そしてそのためのビジョンと踏み出す勇気を提供する1冊。

詳解！ Google Apps Script完全入門 〜Google Apps & G Suiteの最新プログラミングガイド〜

高橋宣成著 秀和システム 2600円（税別）

Google Apps Scriptの完全入門書として、JavaScriptの基本から自作ライブラリまでを徹底解説。これ一冊だけで基礎から実践まで体系的にマスターできます。