連載
» 2018年07月23日 05時00分 公開

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

同じ要件を満たすプログラムでも、その書き方はさまざまです。今回は、過去に作成した「バラバラのデータを1箇所に集約する」マクロを、メンテナンスの効率やプログラムの実行時間を改善した形に修正します。

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

Excel VBAとGAS

 業務で発生するちょっとした面倒くさい業務。特定の操作を繰り返すだけなので、今のままでもいいけど、楽になったらいいなと思うこともあるのではないでしょうか。例えば業務を自動化すると時間が空くため、他の業務に時間を充てたり、残業時間を減らしたりできるかもしれません。

 本連載「働く価値を上げる“VBA/GAS術”」では、業務を効率化する手段として「Microsoft Excel」(マイクロソフトエクセル)で使える「VBA」(Visual Basic for Applications)と「G Suite」の「GAS」(Google Apps Script)の使い方を説明し、よくある業務課題の解決策を紹介します。


Excel VBAで経費精算書のデータを収集するマクロをより良くする

 第7回第8回ではVBAおよびGASで個別の経費精算書ファイルからデータを抽出して、1つのデータベースにまとめるプログラムを紹介しました。実務では、同様に「バラバラのデータを、1つのまとまったデータベースとして集約する」業務は数多く発生します。このプログラムを活用することで、そのようなルーティンの業務を自動化できます。

 ところで、「業務自動化」というゴールを満たすプログラムの作り方は、何通りも存在し、プログラムを構成する要素や書き方などを、幾つものパターンから選択できます。「動かす」ことを目的にすると、どんな作り方でも問題ありません。しかし、作成したプログラムの再利用、追加、修正といった「メンテナンスの効率」や、「プログラムの実行時間」などは、プログラムの作り方によって大きく変わります。

 今回は、以前作成したVBAによる「経費データ収集マクロ」について、幾つかの改善を施していきます。具体的には「テーブル」「配列」「列挙体」を活用した作り方に直していきます。これらにより、プログラムとしてシンプルに記述できたり、メンテナンスしやすくなったり、プログラム実行を高速にしたりといったメリットを享受できます。

 なお、本稿で使用している一部の命令については以下の記事で詳しく解説しているので、都度参照してください。

第7回の経費データ収集マクロ

 第7回では、以下のプログラムを「経費収集.xlsm」に作成、保存しました。

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 i As Long, j As Long '経費精算書の行数,経費集計の行数
j = 2
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  '氏名
      
      i = 8
      Do While .Cells(i, 1).Value <> ""
        Sheet1.Cells(j, 1).Value = month        '対象月
        Sheet1.Cells(j, 2).Value = .Cells(i, 1).Value  '日付
        Sheet1.Cells(j, 3).Value = department      '部署
        Sheet1.Cells(j, 4).Value = fullname       '氏名
        Sheet1.Cells(j, 5).Value = .Cells(i, 2).Value  '科目
        Sheet1.Cells(j, 6).Value = .Cells(i, 5).Value  '摘要
        Sheet1.Cells(j, 7).Value = .Cells(i, 6).Value  '金額
        Sheet1.Cells(j, 8).Value = .Cells(i, 7).Value  '備考
        i = i + 1
        j = j + 1
      Loop
    End With
    .Close
  End With
Next f
End Sub

 フォルダ「経費精算書」内に保存してある各スタッフの経費精算書ファイルを全て開き、以下の「経費収集.xlsm」の「経費収集」シートに経費精算データを集めるというものでした。

図1 「経費収集.xlsm」の「経費収集」シート

 なお、各スタッフから収集する経費精算書のフォーマットは以下となります。

図2 経費精算書フォーマット
       1|2|3 次のページへ

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。