先日『エクセルマクロ』でとある依頼を受けました。

なんでも、某勤怠システムを導入して、

毎月月初に先月分の勤怠表をエクセルで出力して、
プリントアウトして、
ハンコを押して提出という流れだそうで。

総務の方で、
勤怠があっているかの確認が必要だけれど、
出力されるエクセルの項目が多く、
色がついてないのでとにかく見づらいと。

100人以上分のエクセルがあるので目視確認がとても大変だそうで。

要件としては、

• 土日祝 はセルの色をグレーにしてほしい
• 出勤、退勤どちらかが空白で、備考に何も記載がなければメッセージを表示してほしい

という内容でした。

打ち合わせ中、
ハンコが不要なら100人でも200人でもクリック一つで一括チェックできますよと、

かるく提案はしたものの、しばらくハンコは必須なようで。

社内マクロの打ち合わせにて、
やっぱりハンコで引っかかる。

ハンコがなけりゃ、
100人分でも200人分でもクリック一つでまとめてチェックできるのに、
ハンコがあるだけで、1枚1枚のチェックにすり替わる。

なんて無駄なシステムなんでしょ、と思いながらコードを書く水曜日の昼。

— 世界のアオキプログラミング×生涯学習×熊本 (@aoki_monpro) October 7, 2020

社内マクロの打ち合わせにて、やっぱりハンコで引っかかる。
ハンコがなけりゃ、100人分でも200人分でもクリック一つでまとめてチェックできるのに、
ハンコがあるだけで、1枚1枚のチェックにすり替わる。
なんて無駄なシステムなんでしょ、と思いながらコードを書く水曜日の昼。

圧倒的なハンコブレーキがかかっている状態の中、

要件だけでも叶えようとエクセルマクロを組み、
約2時間で作成したので、
その時の考え方、使った知識などを書き留めておこうと思います。

エクセルマクロで勤怠表の自動チェックをする 要件の棚卸し

操作としては、

勤怠システムからエクセルファイルが出力されるので、

マクロで、そのエクセルファイルを選択して、
色やらメッセージ表示やらを実施すればOKではあります。

まずはファイル選択回りから。

エクセルマクロでファイルを選択する

ボタンを押してファイルを選ぶ、という操作ではあるけれど、

いろいろと考えないといけない事があります。

1. エクセルファイルが選ばれているか。
   →拡張子で .xls や .xlsx だけを表示。

2. エクセルファイルを選ばずにキャンセルした場合の動き。
   → 他のマクロを動かさず処理を止める必要がある。

3. 正しい勤務表ファイルかどうかの確認。
   →勤務表ファイルの複数のセルを確認して、
   同じ内容になっているかどうかの判定をかける。

4. 不要なシートの削除
   →すでにシートがあると重複してしまうので、事前に削除しておく。

5. シートのコピー
   →勤務表ファイルのシートを、マクロ登録しているファイルにコピーして閉じる。

などの対応が必要になります。

コードとしては、

1.ファイル選択
Application.GetOpenFilenameでエクセルファイル指定

2.エクセルファイルを選ばずにキャンセル
→Application.GetOpenFilenameがfalseになった場合に、
フラグを持たせてNGなら処理停止(if文)

3.正しい勤務表ファイルかどうか確認
→if文で複数セルを比較

4.不要シート削除
→for each ws in worksheets で各シートチェック

5.シートのコピー
→workbook.worksheets().copy after:=workbook.worksheet()

いずれも『エクセルマクロ』の動きがわかっている必要がありますが、

大まかな動きや流れがわかっていれば、
細かいコードのスペルなどはググりつつ構築していく場合でも、

1時間もかからずコーディングできるのではと思います。

アオキ

このあたりのコードや考え方は、『Excel講座』にまるっとまとめています。

もんプロ～問題発見と解決のためのプログラミング〜

【ExcelVBA】初心者向け講座をリリースしました。【Udemy】

https://coinbaby8.com/udemy_excelvba.html

こちらの記事から約1か月。ようやく、『Udemy』向け第4弾動画を全て撮影し終わりました。こちらからご覧いただけます。最新クーポン情報はこちら定価5,400円ですが、クーポン経由で大幅割引になります。動画約100本で約9時間。当初思っていたよりボリュームある内容になりました。ExcelVBA初心者向け動画を撮影したきっかけオフィスワーカー、デスクワーカーの方ならほとんどの方が使っているであろう『Excel(エクセル)』。その『Excel(エクセル)』でできる事を自動化することができるのが、『ExcelVBA』という専用プログラミング言語...

エクセルマクロで色づけや判定

続いて、勤務表のテーブル範囲の判定。

endColumn = Cells(titleRow, Columns.Count).End(xlToLeft).Column
endRow = Cells(titleRow, 1).End(xlDown).Row

などのコードで、必要な行・列を取得して。

最初の行(1日)から最後の行(28日～31日)までを、

for文で1行ずつ確認しつつ、

運よく土日祝日には『休日』というような文字が書かれていたので、
この文字が書かれている行はグレーに設定としました。

for i startRow to LastRow
If Cells(i, 2).Value = "休日" Then
    Range(Cells(i, 1), Cells(i, endColumn)).Interior.Color = RGB(155, 155, 155)

というかたち。

休日以外で、出勤・退勤いずれかが空白で、
備考に何も記載がなければメッセージ表示というのを、
if文をいくつか組み合わせてつくっていきました。

最後にマクロが途中で止まらないよう、

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

などで挟んで完成。

頂いていたテスト用ファイルでテストしつつ、一部コード修正しつつ、

トータル約２時間で作成して総務にご連絡。

総務の方も「ぽかーん」としていましたが、
ルールさえ決まればすぐに作れるという事を
実感してもらえたかなと思います。

アオキ

このあたりのコードや考え方は、『Excel講座』にまるっとまとめています。

もんプロ～問題発見と解決のためのプログラミング〜

【ExcelVBA】初心者向け講座をリリースしました。【Udemy】

https://coinbaby8.com/udemy_excelvba.html

こちらの記事から約1か月。ようやく、『Udemy』向け第4弾動画を全て撮影し終わりました。こちらからご覧いただけます。最新クーポン情報はこちら定価5,400円ですが、クーポン経由で大幅割引になります。動画約100本で約9時間。当初思っていたよりボリュームある内容になりました。ExcelVBA初心者向け動画を撮影したきっかけオフィスワーカー、デスクワーカーの方ならほとんどの方が使っているであろう『Excel(エクセル)』。その『Excel(エクセル)』でできる事を自動化することができるのが、『ExcelVBA』という専用プログラミング言語...

エクセルマクロで勤務表の自動チェックを作成して

エクセルの機能がわかっていれば、さくっとつくれはするものの、

個人的には、
やはりハンコブレーキがどうにも効率化を妨げているとしか思えないので、

ウィズコロナでデジタル庁な昨今だからこそ、
なるべく早めに不要なハンコ文化はなくなってほしいなと思ったのでした。

アオキ

個人的にはエクセルでできる事はRPAではなくマクロの方が早いと思っております。

Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術

created by Rinker

SBクリエイティブ

• Amazon
• 楽天市場
• Yahooショッピング

『エクセルVBA・分析・統計』関係ではこんな記事も読まれています。