【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

さて、エクセルVBAを使ってバラバラの経費精算書のデータをデータベースに集約するマクロを作成しています。

前回はこちらの記事です。

【エクセルVBA入門】繰り返しを使ってデータの転記をするときの２つのポイント

エクセルVBAでバラバラの経費精算書からデータを集めるマクロの作り方について解説をしています。今回は、エクセルVBAで繰り返しを使ってデータを転記する方法、またその際の２つのポイントについてお伝えします。

tonari-it.com

2018.06.18

Do While～Loopを使って「経費精算書」シートにに記載されたすべてのデータを「経費データ」シートに転記をすることができました。

今回はWithというステートメントを使って、プログラムをスッキリ書く方法をお伝えします。

前回のおさらい

前回のプログラムはこちらです。

Sub 経費精算データ取り込み()

Dim ws As Worksheet
Set ws = Workbooks("201807経費精算書_経費_3001.xlsx").Worksheets(1)

Dim month As Date: month = ws.Range("G4").Value
Dim departmentName As String: departmentName = ws.Range("G6").Value
Dim staffId As Long: staffId = ws.Range("G8").Value
Dim staffName As String: staffName = ws.Range("G7").Value

Dim i As Long: i = 12
Dim di As Long: di = 2
Do While ws.Cells(i, 1).Value <> ""
    wsData.Cells(di, 1).Value = month '1 対象月
    wsData.Cells(di, 2).Value = ws.Cells(i, 1).Value '2 日付
    wsData.Cells(di, 3).Value = ""  '3 部署No
    wsData.Cells(di, 4).Value = departmentName '4 部署
    wsData.Cells(di, 5).Value = staffId '5 社員No
    wsData.Cells(di, 6).Value = staffName '6 氏名
    wsData.Cells(di, 7).Value = ws.Cells(i, 2).Value '7 科目
    wsData.Cells(di, 8).Value = ws.Cells(i, 5).Value '8 摘要
    wsData.Cells(di, 9).Value = ws.Cells(i, 6).Value '9 金額
    wsData.Cells(di, 10).Value = ws.Cells(i, 7).Value '10 備考
    i = i + 1: di = di + 1
Loop

End Sub

「経費精算書」シートから「経費データ」シートにそれぞれ該当するカラムに値を転記していくプログラムです。

さて、ざっと眺めてみて、何か気づくことありませんか？

確かにちゃんと動くは動くんですが、ワークシート名を何回も何回も書かなくちゃいけなくて、ダルくありませんか？

このように、ワークシートを取り扱うときは、何度もオブジェクト名を書かなくてはいけないときが多くて面倒なことが多いのです。

With文はそんなときに使います。

With文でオブジェクト名を省略する

では、先にWith文の構文からお伝えしましょう。

コチラです。

With文で挟んだ範囲（Withブロックと言います）のプログラムは、指定したオブジェクトを省略して記述することができるようになります。

Withによる省略した書き方

どのように省略して書けるようになるかと言いますと、例えば

wsData.Cells(di, 7).Value = ws.Cells(i, 2).Value '7 科目
wsData.Cells(di, 8).Value = ws.Cells(i, 5).Value '8 摘要
wsData.Cells(di, 9).Value = ws.Cells(i, 6).Value '9 金額
wsData.Cells(di, 10).Value = ws.Cells(i, 7).Value '10 備考

というプログラムにWith文を使います。

さて、指定するオブジェクトを「ws」としますと

With ws
    wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
    wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
    wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
    wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
End With

と書くことができます。

つまり、オブジェクトを省略して、その後のドット「.」から記述すればOKになります。

経費データの収集プログラムをWith文で書く

さて、経費精算データの収集プログラムについて、With文を使用して書き直すと以下のようになります。

Sub 経費精算データ取り込み()

Dim ws As Worksheet
Set ws = Workbooks("201807経費精算書_経費_3001.xlsx").Worksheets(1)

With ws
    Dim month As Date: month =.Range("G4").Value
    Dim departmentName As String: departmentName =.Range("G6").Value
    Dim staffId As Long: staffId =.Range("G8").Value
    Dim staffName As String: staffName =.Range("G7").Value

    Dim i As Long: i = 12
    Dim di As Long: di = 2
    Do While.Cells(i, 1).Value <> ""
        wsData.Cells(di, 1).Value = month '1 対象月
        wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
        wsData.Cells(di, 3).Value = ""  '3 部署No
        wsData.Cells(di, 4).Value = departmentName '4 部署
        wsData.Cells(di, 5).Value = staffId '5 社員No
        wsData.Cells(di, 6).Value = staffName '6 氏名
        wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
        wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
        wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
        wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
        i = i + 1: di = di + 1
    Loop
End With

End Sub

だいぶスッキリしますよね。

どうせなら、「wsData」も省略したい気分になりますが、それは二つ同時はごめんなさい…無理です。

てか、なぜ「wsData」ではなくて「ws」を省略したのか…それには理由がありますが、それは、次回の記事で明らかになります。

まとめ

以上、エクセルVBAでWith文を使って範囲内のオブジェクトを省略する書き方についてお伝えしました。

With文を使うメリットとして、記述するコードの量が減るのはもちろんなのですが、その範囲内が「どのオブジェクトを対象する処理なのか」というのがわかりやすくなるというメリットがあります。

ぜひ、ご活用くださいね。

次回、Openメソッドでファイルを開く方法をお伝えします。

【エクセルVBA入門】他のワークブックをWithで開く＆保存せずに閉じる

エクセルVBAを使ってバラバラの経費精算書のデータをデータベースに集約するシリーズ。今回はエクセルVBAのでWithとOpenメソッドを組み合わせて他のワークブックを開く方法についてお伝えしていきます。

tonari-it.com

どうぞお楽しみに！

連載目次：エクセルVBAで経費データをデータベースに集約する

請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。

1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
2. 【エクセルVBA入門】Do While～Loop文で条件を満たす間繰り返し
3. 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの２つのポイント
4. 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
5. 【エクセルVBA入門】他のワークブックをWithで開く＆保存せずに閉じる
6. 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
7. 【エクセルVBA入門】For Each～Next文でフォルダ内のブック全てを開く方法
8. 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
9. 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
10. 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
11. 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
12. 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
13. 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
14. 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
15. 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する