【過不足調整マクロ（2/2）】物品移動の調整を効率化してボタンひとつで！（その２：コードの作成手順）
当ブログで紹介するコードは筆者が独自に開発して自身の業務のみに使用しているものであり、会社の財産ではありません。また、コードや参考画像等の中で使用している単語や図表なども抽象化したものであり、業務で使用しているものではありません。
当記事ではNoteのシンタックスハイライトの仕様上、コメントアウトされたコードを「//」から始まる形で記述していますが、VBAでは本来「'」によって記述します。コードを準用する場合はご注意ください。
【参考】公式ドキュメント：コード内のコメント
過不足調整マクロの紹介の第２回目です。前回の記事では解決したい課題とマクロの全体像について解説しました。まだご覧になっていない方は、下のリンクからどうぞ。
当ブログは、ただ完成形のマクロを紹介するだけではなく、ご自身の目の前の業務を効率化するために読者様が自分でVBAを書いてマクロを作成できるようになって欲しいという目標をもって執筆しておりますので、第２回目の今回は、コードを完成させるまでの手順について解説します。
要件定義
まずは、何をしたいかという目標を明確にします。
あくまで自分が楽をするための小さな小さなプログラムを作るだけなので、あまり仰々しく考える必要はありませんが、必要な機能は何かを理解し、ゴールを明確にするようにしましょうということです。
今回のゴールは「複数の部署の間での作業服の過不足調整案の作成を自動化したい」ですので、次はさらに小さな課題（タスク）に切り分けていきましょう。これを課題分解と呼びます。
課題分解
効率化に適した Excelの様式を準備する
データベースファーストの原則を遵守できている過不足のとりまとめシートを作成する
各課から回答をとりまとめる
プログラムの処理の手順を日本語で書いて整理する
どのテーブルを扱うかを宣言する
テーブルの上の行から調整の対象とするレコードを探していく
「不足」のレコードが見つかれば、さらにもう一度テーブルの上の行から対応相手の条件にマッチするレコードを探していく
対応相手が見つかればそれぞれに値を入れていく
最後の行まで処理が終わる
またテーブルの上の行から順に見ていって、対応相手が見つかっていない行には「購入」や「保留」を入力する。
これらのことを、VBEを開いてモジュール内にコメントとして書いていくと良いでしょう。こうすることで、どのような手順で処理を書いていくかを整理することができます。あとは、これらの処理をVBAに置き換えていく作業を繰り返すのです。
//どのテーブルを扱うかを宣言する
//テーブルの上の行から調整の対象とするレコードを探していく
//「不足」のレコードが見つかれば、さらにもう一度テーブルの上の行から対応相手の条件にマッチするレコードを探していく
//対応相手が見つかればそれぞれに値を入れていく
//最後の行まで処理が終わる
//またテーブルの上の行から順に見ていって、対応相手が見つかっていない行には「購入」や「保留」を入力する。日本語で書いたプログラムの処理をVBAコードに置き換えていく
どのテーブルを扱うかを宣言する
ここからVBAのコードを書いていきます。まずはおまじないの「Option Explicit」を記述し、Subプロシージャの宣言と、リストオブジェクトのインスタンス化によって、 Excelブック内のどのテーブルを扱うかの宣言をします。
Option Explicit
Sub Adjustmet()
//どのテーブルを扱うかを宣言する
Dim UniformList As ListObject: Set UniformList = Sheet1.ListObjects(1)
//テーブルの上の行から調整の対象とするレコードを探していく
//「不足」のレコードが見つかれば、さらにもう一度テーブルの上の行から対応相手の条件にマッチするレコードを探していく
//対応相手が見つかればそれぞれに値を入れていく
//最後の行まで処理が終わる
//またテーブルの上の行から順に見ていって、対応相手が見つかっていない行には「購入」や「保留」を入力する。
End SubWithステートメントを宣言し、このテーブルに対してオブジェクト名の修飾を繰り返すことなく一連のステートメントを実行できるようにし、コードの可読性を高めます。
Option Explicit
Sub Adjustmet()
//どのテーブルを扱うかを宣言する
Dim UniformList As ListObject: Set UniformList = Sheet1.ListObjects(1)
With UniformList
//テーブルの上の行から調整の対象とするレコードを探していく
//「不足」のレコードが見つかれば、さらにもう一度テーブルの上の行から対応相手の条件にマッチするレコードを探していく
//対応相手が見つかればそれぞれに値を入れていく
//最後の行まで処理が終わる
//またテーブルの上の行から順に見ていって、対応相手が見つかっていない行には「購入」や「保留」を入力する。
End With
End Sub
テーブルの上の行から調整の対象とするレコードを探していく
「a」という数値の変数の宣言と、For文によるループを作成します。
Dim a As Integer
For a = 2 To .ListRows.Count + 1変数「a」はループの中で行番号として扱います。初期値を２とし、それが１ずつ増えていくので、シートの２行目から、２行目、３行目、４行目と上から順に処理していく流れになります。
ループが終わるのは、「UniformList.ListRows.Count + 1」とし、テーブルの最終行まで処理が終わった時ということにします。
【参考】公式ドキュメント：ListRows.Countプロパティ
モジュールの中に書き込むと以下のようになります。
Option Explicit
Sub Adjustmet()
//どのテーブルを扱うかを宣言する
Dim UniformList As ListObject: Set UniformList = Sheet1.ListObjects(1)
With UniformList
Dim a As Integer
//テーブルの上の行から調整の対象とするレコードを探していく
For a = 2 To .ListRows.Count + 1
//「不足」のレコードが見つかれば、さらにもう一度テーブルの上の行から対応相手の条件にマッチするレコードを探していく
//対応相手が見つかればそれぞれに値を入れていく
//最後の行まで処理が終わる
End For
//またテーブルの上の行から順に見ていって、対応相手が見つかっていない行には「購入」や「保留」を入力する。
End With
End Sub「不足」のレコードが見つかれば、さらにもう一度テーブルの上の行から対応相手の条件にマッチするレコードを探していく
a行目5列目の値が「不足」であり、その対応相手がまだ決定していない場合、a行目のレコードの対応相手を見つけるという処理を行うため、「.Range(a, 5).value = "不足"」と、「.Range(a, 7) = Empty」という２つの命題が共に真であるときの処理を記す IF文を作成します。
//不足のレコードが見つかったとき
If .Range(a, 5).Value = "不足" And .Range(a, 7) = Empty Then
//さらにもう一度テーブルの上の行から対応相手の条件にマッチするレコードを探す
End If もう一度テーブルの１番上の行から順にループを回すため、「b」という数値の変数宣言を行い、IF文の中に変数「b」が2からテーブルの行数と同じ数値までの間循環するループを作成します。
For文の中において、変数「b」は「a」と同じく番号として扱います。
Dim b As Integer//不足のレコードが見つかったとき
If .Range(a, 5).Value = "不足" And .Range(a, 7) = Empty Then
//もう一度テーブルの上の行から対応相手の条件にマッチするレコードを探す
For b = 2 To .ListRows.Count + 1
//対応相手が見つかればそれぞれに値を入れていく
End For
End Ifこれらをモジュールに書き込むと、以下のようになります。
Option Explicit
Sub Adjustmet()
//どのテーブルを扱うかを宣言する
Dim UniformList As ListObject: Set UniformList = Sheet1.ListObjects(1)
With UniformList
Dim a As Integer
Dim b As Integer
//テーブルの上の行から調整の対象とするレコードを探していく
For a = 2 To .ListRows.Count + 1
//不足のレコードが見つかったとき
If .Range(a, 5).Value = "不足" And .Range(a, 7) = Empty Then
//もう一度テーブルの上の行から対応相手の条件にマッチするレコードを探す
For b = 2 To .ListRows.Count + 1
//対応相手が見つかればそれぞれに値を入れていく
End For
End If
//最後の行まで処理が終わる
End For
//またテーブルの上の行から順に見ていって、対応相手が見つかっていない行には「購入」や「保留」を入力する。
End With
End Sub対応相手が見つかればそれぞれに値を入れていく
変数「b」を用いたループ内では、b行目のレコードが次の5つの条件を満たす場合のIF文を作成します。
「対応相手」がまだ見つかっていない ＝空欄
「過不足」が「余剰」である
変数「a」によるループで見つけたレコードとは所属が異なっている
変数「a」によるループで見つけたレコードと作業服の種類が同じである
変数「a」によるループで見つけたレコードと作業服のサイズが同じである
IF文の中では、次のとおりそれぞれのレコードに値を入力する処理を記述します。
「不足」のレコードの「対応相手」列には、「b」ループで発見したレコードの「所属名」の値 ＋「から」という文字列を入力する
「余剰」のレコードの「対応相手」列には、「a」ループでで発見したレコードの「所属名」の値＋「へ」という文字列を入力する
両レコードの「パス」列に同じ数字を入力する
さらに、a行目のレコード（「不足」のレコード）の対応相手としてb行目のレコード（「余剰」のレコード）が見つかったということは、変数「b」によるループは離脱し、変数「a」によるループに戻り、次の「不足」のレコードを探しにいきます。「Exit For」というステートメントによりループを離脱します。
//対応相手がみつかったとき
If _
.Range(b, 7).Value = Empty And _
.Range(b, 5).Value = "余剰" And _
.Range(b, 4).Value <> .Range(a, 4).Value And _
.Range(b, 2).Value = .Range(a, 2).Value And _
.Range(b, 3).Value = .Range(a, 3).Value _
Then
//両レコードの「対応相手」列に値を入れる
.Range(a, 7).Value = .Range(b, 4).Value + "から"
.Range(b, 7).Value = .Range(a, 4).Value + "へ"
//両レコードの「パス」列に同じ数値を入れる
.Range(a, 8).Value = a
.Range(b, 8).Value = a
//変数「b」によるループを離脱する
Exit For
End Ifここまでをモジュールに記述すると以下のとおりになります。
Option Explicit
Sub Adjustmet()
//どのテーブルを扱うかを宣言する
Dim UniformList As ListObject: Set UniformList = Sheet1.ListObjects(1)
With UniformList
Dim a As Integer
Dim b As Integer
//テーブルの上の行から調整の対象とするレコードを探していく
For a = 2 To .ListRows.Count + 1
//不足のレコードが見つかったとき
If .Range(a, 5).Value = "不足" And .Range(a, 7) = Empty Then
//もう一度テーブルの上の行から対応相手の条件にマッチするレコードを探す
For b = 2 To .ListRows.Count + 1
//対応相手がみつかったとき
If _
.Range(b, 7).Value = Empty And _
.Range(b, 5).Value = "余剰" And _
.Range(b, 4).Value <> .Range(a, 4).Value And _
.Range(b, 2).Value = .Range(a, 2).Value And _
.Range(b, 3).Value = .Range(a, 3).Value _
Then
//両レコードの「対応相手」列に値を入れる
.Range(a, 7).Value = .Range(b, 4).Value + "から"
.Range(b, 7).Value = .Range(a, 4).Value + "へ"
//両レコードの「パス」列に同じ数値を入れる
.Range(a, 8).Value = a
.Range(b, 8).Value = a
//変数「b」によるループを離脱する
Exit For
End If
End For
End If
//最後の行まで処理が終わる
End For
//またテーブルの上の行から順に見ていって、対応相手が見つかっていない行には「購入」や「保留」を入力する。
End With
End Subここまででマクロを実行してみると、対応相手があるレコードについては、「対応相手」列及び「パス」列に何らかの値が入ります。
対応相手が見つかっていない行には「購入」や「保留」を入力する
最後に、対応相手がなかったレコードに値を入れる処理を記述します。また新しいループを回すので、「c」という数値の変数を宣言します。このときには変数「a」によるループは終了しているので、「a」を再利用してもいいのですが、頭の中で整理がつきやすいので、新たな変数を使うこととします。
「不足」で対応相手なし → 「対応相手」列に「購入」
「余剰」で対応相手なし → 「対応相手」列に「保留」
Dim c As Integer//対応相手が見つかっていない行には「購入」や「保留」を入力する
For c = 2 To .ListRows.Count + 1
If .Range(c, 5).Value = "不足" And .Range(c, 7) = Empty Then
.Range(c, 7).Value = "購入"
ElseIf .Range(c, 5).Value = "余剰" And .Range(c, 7) = Empty Then
.Range(c, 7).Value = "保留"
End If
Next c完成したVBAコードと実行結果
これで全ての処理が記述できました。
Option Explicit
Sub Adjustmet()
//どのテーブルを扱うかを宣言する
Dim UniformList As ListObject: Set UniformList = Sheet1.ListObjects(1)
With UniformList
Dim a As Integer
Dim b As Integer
//テーブルの上の行から調整の対象とするレコードを探していく
For a = 2 To .ListRows.Count + 1
//不足のレコードが見つかったとき
If .Range(a, 5).Value = "不足" And .Range(a, 7) = Empty Then
//もう一度テーブルの上の行から対応相手の条件にマッチするレコードを探す
For b = 2 To .ListRows.Count + 1
//対応相手がみつかったとき
If _
.Range(b, 7).Value = Empty And _
.Range(b, 5).Value = "余剰" And _
.Range(b, 4).Value <> .Range(a, 4).Value And _
.Range(b, 2).Value = .Range(a, 2).Value And _
.Range(b, 3).Value = .Range(a, 3).Value _
Then
//両レコードの「対応相手」列に値を入れる
.Range(a, 7).Value = .Range(b, 4).Value + "から"
.Range(b, 7).Value = .Range(a, 4).Value + "へ"
//両レコードの「パス」列に同じ数値を入れる
.Range(a, 8).Value = a
.Range(b, 8).Value = a
//変数「b」によるループを離脱する
Exit For
End If
End For
End If
//最後の行まで処理が終わる
End For
//対応相手が見つかっていない行には「購入」や「保留」を入力する
For c = 2 To .ListRows.Count + 1
If .Range(c, 5).Value = "不足" And .Range(c, 7) = Empty Then
.Range(c, 7).Value = "購入"
ElseIf .Range(c, 5).Value = "余剰" And .Range(c, 7) = Empty Then
.Range(c, 7).Value = "保留"
End If
Next c
End With
End Sub完成したマクロの処理結果が以下のとおりです。
以上が、過不足調整マクロの課題分解からコードの完成までのプロセスです。解決したい課題とマクロの全体像について解説した前回との２回構成で解説させていただきましたが、いかがでしたでしょうか。
ここで紹介したマクロを参考に、ご自身の業務用のマクロを作成して効率化を実現していただければ、筆者としては非常に嬉しい限りです。
お読みいただきありがとうございました。
参考文献
公式ドキュメント
書籍
この記事が参加している募集
この記事が気に入ったらサポートをしてみませんか？