【エクセルVBA入門】データの重複を防ぐSubプロシージャの作成

★気に入ったらシェアをお願いします!


複製

みなさん、こんにちは!
このところひたすらVBAと戯れていますタカハシ(@ntakahashi0505)です。

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズもいよいよ今回で8回目です。

前回はこちらの記事で

【エクセルVBA入門】人為的なミスを回避する方法その2~マスタを利用
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務フロー上の人為的なミスを回避する有効なテクニックとしてもう一つ、マスタの活用についてお伝えしていきます。

メンバーが入力ミスをしている経費精算書でも、ファイル名とマスタを使って正しくデータを蓄積する対策についてお伝えしました。

今回は、一連のシリーズの最終回重複チェックとデータの上書きの機能について追加をしていきたいと思います。

よろしくお願いします!

スポンサーリンク

前回までのおさらいと今回の課題

前回完成したプログラムがこちらです。

各メンバーから受け取った経費精算書について、以下図のように「経費データ」シートに蓄積をしていきます。

経費精算書のデータの収集結果

前回は

  • ファイル名objFile.Nameから、Val,Right,Replaceを使って社員番号を抜き出す
  • それをキーにしてWorksheetFunction.VLookupにて社員マスタから部署名、社員の氏名を取得

というお話でした。

今回ですが、例えば同じファイルを誤って複数回取り込んでしまったらどうなるか?ということなのですが、実際にやってみると

経費データが重複して登録されてしまう

このように、重複してどんどんデータが追加されてしまいます。これは危険ですね…。

これを防ぐために、既に同じ対象月の同じ社員のデータが入っていた場合は、それをまず削除してしまうという処理を追加したいと思います。

既にある重複データを削除する処理の追加方針

まず考え方をまとめてみましょう。

削除すべきデータを削除する処理は、対象月と社員番号がわかってからですから、

この後に入れる形になります。

削除の処理は「重複削除」という名前でSubプロシージャを用意して、そのプロシージャに対象月と社員番号を渡して処理をしてもらうということにしましょう。

また、「経費データ」シートの何行目か?をつかさどるカウント変数jですが

今はプログラムの冒頭に実行するようになっていますが、行の削除をした場合は行数が変更になりますから、重複削除プロシージャの後に場所移動します。

さらに、Worksheetオブジェクトの宣言ですが

は重複削除プロシージャでも使うので、プロシージャの外に出してPublic宣言をします。

Subプロシージャの呼び出しとPublic変数についてはこちらの記事も合わせてご覧ください。

【初心者向けエクセルVBA】Public変数の宣言とSubプロシージャの呼び出し
請求データ一覧から請求書を自動で作成するシリーズも第6回目となりました。Public変数の宣言、別のSubプロシージャの呼び出しなどによりプログラムをスッキリしつつ、うっかり発見した不具合を直していきます。

Dim変数を渡す場合のCallによるプロシージャ呼び出し

プロシージャ内でDim宣言した変数は宣言したプロシージャ内でしか使えないので、他のプロシージャでも使いたい場合は変数を渡す必要があります。

このように書きます。

Call プロシージャ名(変数1,変数2,…)

今回の場合は対象月と社員番号を渡しますが、strPeriodは6桁の文字列型でDate型ではありませんので、対象月を表すDate型に変換してあげる必要がありますね。

この上で、対象月datePeriodと社員番号numStaffを重複削除プロシージャに渡せばOKです。

重複削除プロシージャ

重複削除プロシージャは、このように書きます。

Callで渡された変数の受け取り方ですが、冒頭に

Sub プロシージャ名(変数1,変数2,…)

と書きます。変数名は渡されたときと別の名前にすることができますが、わかりやすさのために同じ名前にしています。

その他の部分の解説ですが、まずDo While~Loopで「経費データ」シートの2行目から最後尾までを順番に見ていきます。

If文ですが、今対象となっている行の対象月がdatePiriodと同じでかつ社員番号がnumStaffと等しければ、その行を削除します。

行の削除はもうご想像できていると思いますが

Rows(行番号).Delete

と書きます。

行の削除をした場合は、下の行たちがシフトして詰められますので、カウント変数iを加算せずにDo While~Loopに入っても問題ありません。

If文の条件式にマッチしていなければ、カウント変数iを加算して次の行に移動します。

これで完成です。

まとめ

同じ経費精算書データを取り込む際のデータ重複を防ぐ重複削除プロシージャを作成しました。

なお、元のデータを削除して新たなデータを追加するわけですから、データに修正を加えたいときにも有効に使えますね。

最後に完成したプログラムを載せておきますね。

Publicでのオブジェクト変数宣言

メインプログラム:経費精算データ取り込みプロシージャ

重複削除プロシージャ

ではまた別のシリーズでお会いしましょう!

連載目次:経費精算書のデータをデータベースに集約する

  1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
  2. 【エクセルVBA入門】Do While~Loopで条件を満たす間繰り返し
  3. 【エクセルVBA入門】Withでプログラムをスッキリわかりやすく書く
  4. 【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる
  5. 【エクセルVBA入門】For Each~Nextでフォルダ内のブック全てを参照する
  6. 【エクセルVBA入門】人為的なミスを回避する方法その1~ファイル名を活用する
  7. 【エクセルVBA入門】人為的なミスを回避する方法その2~マスタを利用
  8. 【エクセルVBA入門】データの重複を防ぐSubプロシージャの作成