メンテナンス性や実行時間を改善――複数のExcelデータを1箇所に集約するマクロの別解とは：働く価値を上げる“VBA／GAS”術（9）（3/3 ページ）
同じ要件を満たすプログラムでも、その書き方はさまざまです。今回は、過去に作成した「バラバラのデータを1箇所に集約する」マクロを、メンテナンスの効率やプログラムの実行時間を改善した形に修正します。
テーブルを使った経費データを収集するプロシージャ
まず、Subプロシージャ「経費データ収集()」について解説します。処理のおおまかな流れは以下のようになっています。
- FileSystemオブジェクトの生成
- 各変数の宣言および準備
- 「経費収集」シートの「ListRows」コレクションを取得
- フォルダ「経費精算書」内の全てのファイルに以下の処理を繰り返し
┣対象となっているファイルを開く
┣データ「対象月」「部署」「氏名」を変数に格納
┣開いたファイルの全ての「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達成。
書籍紹介
高橋宣成著 秀和システム 1800円（税別）
動くコードが書けたその先、つまり「ExcelVBAを実務で使う」という目的に特化した実践書。ExcelVBAを楽に効果的に使いこなし続けるための知恵と知識、そしてそのためのビジョンと踏み出す勇気を提供する1冊。
詳解！ Google Apps Script完全入門 〜Google Apps & G Suiteの最新プログラミングガイド〜
高橋宣成著 秀和システム 2600円（税別）
Google Apps Scriptの完全入門書として、JavaScriptの基本から自作ライブラリまでを徹底解説。これ一冊だけで基礎から実践まで体系的にマスターできます。
Copyright © ITmedia, Inc. All Rights Reserved.
関連記事
中級レベルの「Excel VBA開発者」になるために必要な考え方
Excel VBAを使って業務効率化を実現。しかし、自分で書いたコードをメンテナンスするとき、コードの解読に時間がかかってしまったり、エラーが解消できなかったりといった苦い経験はないだろうか。どうすれば将来も楽になるコーディングができるのだろうか。
タイプミスをゼロにするのは不可能？ VBAコーディングを速めるツールとは
どんな上級プログラマーでも、タイプミスをゼロにできないまでも、「素早く正確にコーディングできる」能力を持っています。その能力を下支えする、マスターしても損のないVBEの機能を紹介します。
税率変更にも簡単に対応、無駄を減らすコーディングのコツとは
消費税率変更のように、「将来変わる可能性のある数値」がコード内に複数含まれている場合、修正に時間がかかってしまいます。メンテナンスを楽にするコーディングのコツとは何でしょうか。