【ExcelVBA】自動化ツールのご紹介

catch

みなさんお久しぶりです。最近ワンタンスープ(マルちゃんのやつ)にはまっているまさとぅーです。

今記事では、現在私が仕事で携わっているExcelVBAについて書かせて頂きたいと思います。

いきなりですが、皆さんVBAというものをご存知でしょうか?

VBAとは「Visual Basic for Applications」の略称で、マイクロソフト社によってマクロ言語用に開発されたプログラミング言語のことを言います。

主なVBAは、ExcelVBA、WordVBA、AccessVBA、PowerPointVBA等がありますが、今記事ではExcelVBAについてお話します。

ExcelVBAとは

ExcelVBAはその名の通り、Excelに付属しているプログラム言語で「Visual Basic6.0」とほぼ同等の機能を持っています。
つまり、VBAは本格的なプログラムを作成することができるプログラミング言語なのです。

Excelを使用して定型作業を繰り返し行う場合、ExcelVBAを使用して作業を自動化することで、大幅な作業効率向上を図ることができます。

具体例を挙げると、
・顧客データ等の整形や入力値チェック(全半角変換・空白文字チェック等)
・複雑な給与計算や経費計算
・月毎に行うデータ報告や売れ筋商品分析等の定型化されたデータ処理

上記はほんの一例ですが、まだまだExcelVBAで実現できることは沢山あります。

ExcelVBAの自動化ツール

ExcelVBAでの自動化例を簡単にご紹介させて頂きたいと思います。

例えば、ある会社で事務を担当されているA子さんがいるとします。
A子さんは、社員が入力している移動交通費のExcelファイルを毎日開き、そこに入力されている金額をメモし、社員全体の合計値を集計していたとします。

A子さんはこの作業に、1ファイルにつき10秒ほど時間が掛かっているとしたら、10ファイルだと100秒(1分40秒)も掛かる計算になります。

今回はこれを自動化してみたいと思います。

こちらが自動集計画面です。

自動集計画面
「フォルダ選択」ボタンを押下し、Excelファイルが格納されているフォルダを指定します。

フォルダ選択
フォルダ指定が完了したら、「集計開始!」ボタンを押下します。

集計開始

※集計対象フォルダの指定が無い・指定されたフォルダが存在しない・指定されたフォルダにExcelファイルが存在しない場合は、エラーメッセージを表示し、処理終了します。


しばらく待つと集計処理が終了します。

集計完了
合計値もしっかりと計算され、処理時間も5秒!!!となり、手作業で行うより95秒(約1分半)も作業時間が短縮できたことになります。

週間で考えると 95秒 * 5日 = 475(秒) 約 8分作業時間短縮!
月間で考えると 95秒 * 5日 * 4週 = 1900(秒) 約 31分作業時間短縮!
年間で考えると 95秒 * 5日 * 4週 * 12月 = 22800(秒) 約380分作業時間短縮!

例では10ファイルとしましたが、これが100ファイル・1000ファイルとなった場合、とてもじゃありませんが、手作業でやるのはちょっと現実的ではありません。

上記のように単純なルーチンワークでしたら、ExcelVBAで自動化することにより人が手作業するよりも遥かに早く作業をすることができる上、手作業で発生し得るミスも無くすことができます。

でもなんか難しそう・・・と感じるかもしれませんが、慣れると以外と簡単に作れるので、ぜひ挑戦してみてください。
上記でも述べましたが、ExcelVBAはお手持ちのPCにExcel(※1)がインストールされていれば、いつでも利用することができます。
(※1:Office RTは除く)

Excelを起動して、[Alt]+[F11]を押してみてください!
VBEと呼ばれるExcelVBAで開発を行うためのエディタツールが表示されます。
※VBE(Visual Basic Editor)…マクロのコードを記述・修正するためのエディタツール。
 記述したコードの動作確認を実施する重要なツール。

VBE
最近ではインターネット上にもVBAの情報(ソース)等が充実しているため、独学でもVBAが学べる環境が整ってきています。
一度コツをつかんでしまえば十分習得は可能だと思います。

最後に先ほどの自動集計処理のソースを公開します。
たいしたソースではありませんが、参考にして頂ければ幸いです。
※当ソースは集計対象ファイル・1シート目のD8セルに、合計値が記述してあるのを前提として記述してあります。
※ファイル区切り文字が “\“(バックスラッシュ)で記述されてますが、実際は “¥”(円マーク)です。
※対象ファイルは、xlsファイル形式としています。

'*****************************************************************
'指定フォルダ内の全てのワークブック(.xls)を集計
'*****************************************************************

Option Explicit

'◆集計マクロ
'集計対象フォルダ
Private Const cnsStrlngShukeiTaisyo As String = "B3"
'合計値セットエリア
Private Const cnsStrGoukei As String = "H5"
'値の確認セットエリア
Private Const cnsStrAtaiKakunin As String = "H6"
'処理時間セットエリア
Private Const cnsStrSyoritime As String = "H7"
'シート名
Private Const cnsStrSheetName As String = "集計"

'◆集計ファイル
'合計エリア
Private Const cnsStrlngShukeiArea As String = "D8"

'ファイルパス区切り文字
Private Const cnsStrYen As String = "\"
'集計値保持用
Private lngShukei As Long
'値の確認保持用
Private strAtai As String
'******************************************************************
'指定フォルダ内の全てのワークブック(.xls)を集計
'******************************************************************
Sub 集計開始_Click()
    '宣言
    Dim xlAPP As Application
    Dim strPathName As String
    Dim strFileName As String
    Dim VarStartTime As Single
	Dim VarStopTime As Single
	Dim VarSyoriTime As Single
    
    'エラーハンドリング
    On Error GoTo SyoriEXIT
    
    '処理時間計測開始
    VarStartTime = Timer
    
    '初期値セット
    lngShukei = 0
    strAtai = ""
    strPathName = ""
    strFileName = ""
    
    'リセット
    ThisWorkbook.Worksheets(cnsStrSheetName).Range(cnsStrGoukei).Value = ""
    ThisWorkbook.Worksheets(cnsStrSheetName).Range(cnsStrAtaiKakunin).Value = ""
    ThisWorkbook.Worksheets(cnsStrSheetName).Range(cnsStrSyoritime).Value = ""
    
    'フォルダ名取得
    strPathName = ThisWorkbook.Worksheets(cnsStrSheetName).Range(cnsStrlngShukeiTaisyo)
    
    'フォルダ・ファイル存在チェック
    If strPathName <> "" Then
        'フォルダ存在チェック
        strFileName = Dir(strPathName, vbDirectory)
        If strFileName = "" Then
            MsgBox "指定されたフォルダは存在しません。"
            '処理終了
            Exit Sub
        End If
        strFileName = Dir(strPathName & "\*.xls")
        If strFileName = "" Then
            MsgBox "指定されたフォルダにはExcelファイルが存在しません。"
            '処理終了
            Exit Sub
        End If
    Else
        MsgBox "集計対象フォルダを指定してください。"
        '処理終了
        Exit Sub
    End If
    
    Set xlAPP = Application
    With xlAPP
        '画面描画停止
        .ScreenUpdating = False
        'イベント停止
        .EnableEvents = False
        'カーソルを砂時計にする
        .Cursor = xlWait
    End With
    
    'ファイル名が存在する間
    Do While strFileName <> ""
        DoEvents
        'ファイルを開き、処理実行
        Call FileOpenProc(strPathName, strFileName)
        ' 次のファイル名を参照
        strFileName = Dir
    Loop
    
    '処理時間計測終了
    VarStopTime = Timer
    '処理時間をセット
    VarSyoriTime = VarStopTime - VarStartTime
    
    '値をセット
    ThisWorkbook.Worksheets(cnsStrSheetName).Range(cnsStrGoukei).Value = lngShukei
    ThisWorkbook.Worksheets(cnsStrSheetName).Range(cnsStrAtaiKakunin).Value = strAtai
    ThisWorkbook.Worksheets(cnsStrSheetName).Range(cnsStrSyoritime).Value = Round(VarSyoriTime, 0) & "秒"
    
SyoriEXIT:
    If Not xlAPP Is Nothing Then
        With xlAPP
            '画面描画再開
            .ScreenUpdating = True
            'イベント再開
            .EnableEvents = True
            'カーソルを矢印(デフォルト)にする
            .Cursor = xlDefault
        End With
        'オブジェクト解放
		Set xlAPP = Nothing
	End If
End Sub
'*****************************************************************
'ワークブックを開き、集計
'*****************************************************************
Private Sub FileOpenProc(strPathName As String, strFileName As String)
    'ワークブック用のオブジェクト
    Dim objWBK As Workbook
    ' ワークブックを読み取り専用で開く
    Set objWBK = Workbooks.Open(Filename:=strPathName & cnsStrYen & strFileName, ReadOnly:=True)
    '各ファイルの合計値を記述
    strAtai = strAtai & "[" & strFileName & ":" & objWBK.Worksheets(1).Range(cnsStrlngShukeiArea) & "]"
    '集計値を合算する
    lngShukei = lngShukei + objWBK.Worksheets(1).Range(cnsStrlngShukeiArea)
    'ファイルを保存せず、閉じる
    objWBK.Close SaveChanges:=False
    'オブジェクト解放
    Set objWBK = Nothing
End Sub
'*****************************************************************
'フォルダ選択ボタン押下時処理
'*****************************************************************
Sub フォルダ選択_Click()
    'フォルダダイアログ表示
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = True Then
            '選択されたパスをセット
            ThisWorkbook.Worksheets(cnsStrSheetName).Range(cnsStrlngShukeiTaisyo).Value = .SelectedItems(1)
        End If
    End With
End Sub


手作業で行っていた日々のルーチンワークをExcelVBAで自動化し、作業効率を上げて残業を無くし、アフターファイブ(古っ笑)を満喫しましょう!

最後までお読み頂き、ありがとうございました。

株式会社ライズウィル

株式会社ライズウィル

〒110-0016
東京都台東区台東1-27-11 やわらぎビル2F
TEL : 03-4590-3200
FAX : 03-4590-3201
E-Mail : info@risewill.co.jp
URL : http://www.risewill.co.jp

海上コンテナ輸送管理システム「海コン魂!」
海コン魂!
面接予約・スタッフ管理システム「WebWORKS.」
WebWORKS.