業務改善ラボ

[エクセルVBA]ログを取って業務管理してみよう【業務改善の意識改革】

VBAテクニック編

みなさん、こんにちはケンケンです。

 

普段、エクセルで資料を作った際に、作成済みデータのログをとっていますか。

ログとは簡単に言うと、データを記録することです。

ほとんどの方は、ログをとらずに、シートを上書きして使っていると思います。

 

なぜ、ログを取って記録したほうが良いかは明確です。

情報を残すことによって、あとで無用なトラブルを避けることができるからです。

そうは言っても、いちいち作った資料の記録を取ることは容易ではありませんね。

そこで、今回はログを取る仕組みをVBAで自動化する方法をご紹介していきます。

 

 

目次

ログをとる下準備をしよう

 

過去に何度もお見せしていますが、シートの追加とForNextを使った繰り返しからお話ししていきます。

なぜ、何回も同じことを紹介していくかと言うと、

重要かつ覚えるとメリットが多いから

です。

この基本をマスターすると

  • 応用が効くようになる
  • プログラミング脳になる
  • 関数との差別化を図れる指標を持てる

という利点があります。

 

是非、空でコードを書けるようになるまで何度も復習してください。

逆に言うと、基本的なこと以外は、分からなくなったら調べる、くらいで良いです。

僕も、ネット検索や自動検索で調べることが多くあります。

じゃあ、基本的なことって何なの？ってことですが、それは、僕の今までの記事を読んでいただければわかると思います。

 

しつこいようですが、基本が大事なので何度も復習しましょう。

 

■シートの追加と繰り返し

前置きが長くなりましたが、はじめていきます。

まず、今回やることを列挙していきます。

  1. シートを追加する
  2. 追加したシートの名前をリストにある国名にする
  3. 追加したシートのA1セルに国名を入力する
  4. 追加したシートのB1セルに作成日を入力する
  5. ログシートに追加したシートの国名・作成日時・作成者を記載する

順番に解説していきます。

 

その前に、まずシートの内容をご覧ください。

以下の２枚のシートを用意します。

 

次に、上記１〜４の手順を実現するコードを書きます。

 

Sub loglog()

    Dim cnt
    Dim Lrow
    
    Lrow = Worksheets("リスト").Range("A" & Rows.Count).End(xlUp).Row
    
    For cnt = 2 To Lrow
        Worksheets.Add after:=Worksheets(Sheets.Count)
        ActiveSheet.Name = Worksheets("リスト").Range("A" & cnt).Value
        ActiveSheet.Range("A1").Value = Worksheets("リスト").Range("A" & cnt).Value
        ActiveSheet.Range("B1").Value = Format(Date, "yyyymmdd")
    Next

End Sub

 

上記コードをVBEにコピペして実行してください。

すると、以下のようにそれぞれの国名のシートと該当のセルに国名と作成日が入力されます。

ここまでは、以前の記事でお伝えした内容でできますので、自信がない方はこちらで復習してください。

 

それでは、作ったシートのログをとってみましょう。

 

 

ログをとってみよう

 

■ログって何だろう？

そもそもログって何なんだろう？と思いませんか。

冒頭で触れましたが、ログ(log)とはPC側で行われている様々な記録のことです。

例えば、アクセスログだとホームページに訪問してきた人が、

  • いつやってきて
  • 何をクリックして
  • いつ帰ったか

あたりをPCがメモしているというイメージでしょうか。

 

この記事では、普段PC側が内部で行っていて我々が普段目にすることがないログをVBAを使って疑似的に再現し、通常業務に役立てるのが目的です。

正確に言うとログのようなもの、と認識してもらった方が良いでしょうか。

 

■ログを書き出してみよう

それでは、実際に書き出してみましょう。

先ほど書いたコードを以下のように変更してください。

Sub loglog()

    Dim cnt
    Dim Lrow
    
    Lrow = Worksheets("リスト").Range("A" & Rows.Count).End(xlUp).Row
    
    For cnt = 2 To Lrow
        Worksheets.Add after:=Worksheets(Sheets.Count)
        ActiveSheet.Name = Worksheets("リスト").Range("A" & cnt).Value
        ActiveSheet.Range("A1").Value = Worksheets("リスト").Range("A" & cnt).Value
        ActiveSheet.Range("B1").Value = Format(Date, "yyyymmdd")
        Worksheets("ログ").Range("A" & cnt).Value = Worksheets("リスト").Range("A" & cnt).Value
        Worksheets("ログ").Range("B" & cnt).Value = Now'日付日時を表す関数
        Worksheets("ログ").Range("C" & cnt).Value = Application.UserName'使っているPCのユーザ名を取得
    Next

End Sub

変更したら実行してみましょう。

以下のように表示されていたら成功です。

ここまでできたら完成！と言いたいところですが、実はこのままだとうまくいきません。

 

追加したシートを削除して、新しいシートを追加しようとする、つまりログを取ろうとするとログが上書きされてしまいます。

試しに以下のようにリストシート変更して再度実行してみました。

すると、

ログが上書きされてしまいました！

これは、良くないですね。記録を取るためのものなのに上書きされてしまっては意味がありません。

そこでひとつ工夫をしていきます。

 

■変数を工夫しよう

このような時は、変数を工夫することで上書きされずにログを取ることができます。

テクニックとして覚えておきましょう。

 

では、コードを以下のように書き直してください。

Sub loglog()

    Dim cnt
    Dim Lrow
    Dim logLrow 'ログ最終行取得
    
    Lrow = Worksheets("リスト").Range("A" & Rows.Count).End(xlUp).Row
    logLrow = Worksheets("ログ").Range("A" & Rows.Count).End(xlUp).Row + 1 'ログシートの最終行＋１行を取得
    
    For cnt = 2 To Lrow
        Worksheets.Add after:=Worksheets(Sheets.Count)
        ActiveSheet.Name = Worksheets("リスト").Range("A" & cnt).Value
        ActiveSheet.Range("A1").Value = Worksheets("リスト").Range("A" & cnt).Value
        ActiveSheet.Range("B1").Value = Format(Date, "yyyymmdd")
        Worksheets("ログ").Range("A" & logLrow).Value = Worksheets("リスト").Range("A" & cnt).Value 'cntからlogLrowへ変更
        Worksheets("ログ").Range("B" & logLrow).Value = Now 'cntからlogLrowへ変更
        Worksheets("ログ").Range("C" & logLrow).Value = Application.UserName 'cntからlogLrowへ変更
        logLrow = logLrow + 1 '書き出す行を追加する
    Next

End Sub

 

logLrowという、ログシートの最終行を取得するための変数を用意しました。

その変数に、ログシートのA列の最終行プラス１を格納します。

なぜプラス１するかと言うと、書き出す行数は取得した行の次の行だからです。

 

 

そして、ログシートのA～C列に書き出すコードにあった変数cntをlogLrowに変更します。

さらに、ログを書き出したら変数logLrowを１ずつ増加させます（logLrow = logLrow + 1）

 

 

書き直せたら実行してください。

すると、

上書きされずに記録を残すことができました。

常に書き出し先がログシートの最終行の次の行になるので重複しないで済みますね。

 

いかがでしたか。

簡易的なものではありますが、VBAで少し工夫するだけでログを取ることもできます。

実務では複数人で同じファイルを使い、シートの更新や削除することも多いと思います。

そうなると、いつの間にファイルが削除されていたり上書きされていたりします。

そんな時に、誰がどのような処理をしたか記録を残しておくのは、後々トラブルを避けるためにも重要です。

 

管理面において使える技なので覚えておいて損はないと思います。

また、変数の工夫の仕方も応用が効くので、どんどん使ってください。

メインとなるテクニックの紹介は以上になりますが、おまけとしてもう少し続きますのでお付き合い下さい。

 

おまけ：シートを非表示にしてみよう

 

■ログは普段見るものではない

ログって普段目にしないですよね。

 

大抵の場合、分析・管理・トラブル対策などの時に使うもので、みんながみんないつでも見るものではないです。

今回もログとしてデータは取得するけど、使う人全員の目に触れないようにした方が何かと使い勝手が良さそうです。

そこで、普段はログシートを非表示にしておきましょう。

非表示にする方法は簡単で以下のようにします。

ログを取りつつ普段はログを意識しない資料が出来上がりました。

これで、今回の目的をすべて達成しました。

 

いかがでしたか。

ちなみに、再度表示させる場合は、以下のようにします。

 

 

 

まとめ

今回は、以上となります。

コードをすべて下記に載せておきます。

Sub loglog()

    Dim cnt
    Dim Lrow
    Dim logLrow 'ログ最終行取得
    
    Lrow = Worksheets("リスト").Range("A" & Rows.Count).End(xlUp).Row
    logLrow = Worksheets("ログ").Range("A" & Rows.Count).End(xlUp).Row + 1 'ログシートの最終行＋１行を取得
    
    For cnt = 2 To Lrow
        Worksheets.Add after:=Worksheets(Sheets.Count)
        ActiveSheet.Name = Worksheets("リスト").Range("A" & cnt).Value
        ActiveSheet.Range("A1").Value = Worksheets("リスト").Range("A" & cnt).Value
        ActiveSheet.Range("B1").Value = Format(Date, "yyyymmdd")
        Worksheets("ログ").Range("A" & logLrow).Value = Worksheets("リスト").Range("A" & cnt).Value 'cntからlogLrowへ変更
        Worksheets("ログ").Range("B" & logLrow).Value = Now 'cntからlogLrowへ変更
        Worksheets("ログ").Range("C" & logLrow).Value = Application.UserName 'cntからlogLrowへ変更
        logLrow = logLrow + 1 '書き出す行を追加する
    Next

End Sub

 

 

業務改善は自動化して時間短縮するだけではありません。

データをきちんと管理して、のちの分析やトラブル回避に役立てると結果として全体の改善に繋がることがあります。

今回のログもまさにそうですね。

今だけではなく、将来どのように役立つかを日々考えると仕事の効率が劇的に上がっていきます。

 

今後、仕事をするときは、今だけではなく、将来どうなるかを予測して動きましょう。

この記事が皆さんのお役に立てていれば幸いです。