僕の世界観を変えてみる

文系男子が趣味でプログラミングを勉強していくブログです。他にも日常で起きたどうでもいいことや愚痴を書いていきたいです。座右の銘は和を以て貴しとなすです。仲良くやろうよ。

MENU
トップ > 学び > 特定の時刻にマクロを実行させるためのマクロを作ってみた

特定の時刻にマクロを実行させるためのマクロを作ってみた

学び Excel プログラミング

f:id:htmllifehack:20170615235902j:plain

タイトルややこしいな。

職場のUSBメモリの在庫の数が合わなくなるのでVBAでカウンタを作り

使用したらボタンを押すようにしました。

その時の記事はこちら
www.htmllifehack.xyz

これで一件落着かと思いきや、まだまだ問題が発生します。

日報で使用したUSBメモリの数を入力する欄があるんですが

その日のうちに入力しない人がいるんです。

そうすると次の日に昨日何本使ったんだよってわからなくなるんですね。

毎日やれよって話なんですが当たり前のことができない人もいるんです。

そこでsheet2に集計表を作り、なおかつ自動でその日のカウントを集計してくれるマクロを作りました。

参考までに記事にしておきます。



作ったもの

f:id:htmllifehack:20170616001235j:plain

とりあえずひと月分の集計をするためにカレンダーを作りました。

D6はプルダウンで1～12までの数字を選択できるようになってます。

B6～B36はDATE関数で日付を表示。

C6はTEXT関数で曜日を表。

集計ボタンを押すとD6～E36にsheet1でカウントした数値が入るようになっています。

そしてこの集計ボタンを押し忘れたとしても集計予約ボタンを押せば18時に集計されるようにしました。

ではざっと書いていきます。

プルダウンリストを作る

プルダウンリストというのかは不明ですが、今回ので言えばD3をクリックすると

プルダウンメニューが表示され1～12の数字がでるようになってます。

まずあらかじめK3～K14に1～12の数字を入力しておきます。

次にデータ→データの入力規則→入力値の種類→リストをクリックしK3～K14を選択しOKです。
f:id:htmllifehack:20170616002214j:plain

これで1月～12月までを選択できるようになりました。

DATE関数で日付を表示する

B6～B36には30日までなのか31日までなのか自動で表示されるようにします。

'DATE(年,月,日)
=DATE($B$3,$D$3,1)

日が１なのは6月1日を表示させたいから１になります。
なに言ってんだろな

次に6月2日を表示させたいのでB7には

=B6+1

と入力します。

あとはB36までオートフィルでやると31日まで表示されます。

曜日を表示させる

曜日と言えばWeekday関数ですがなんかめんどくせぇので今回はTEXT関数でやりました。

'=TEXT(数値,表示形式)
=TEXT(B6,"aaa")

aaaにすると火、aaaaにすると火曜日と表示されます。

表示形式はたくさんあるので他はググってください。

C6をコピーしてC36まで貼り付けでOKです。

集計するマクロ

sheet1にはその日のうちに使ったUSBメモリの数を数えるカウンタがあります。

こんな感じです。
f:id:htmllifehack:20170616004455j:plain

sheet2の日付の横にこのカウント数の数値を引っ張ってくる必要があります。

最初に失敗したところなんですけど

Sub agg()
Range("d5").Offset(Range("l3"), 0) = Worksheets("カウント").Range("b10")
End Sub

まずl3に=today()で今日の日付を表示させておきます。このとき表示形式はdにしてあります。

なのでD5からl3ぶん下に移動し０右に移動します。

現在のセルにカウントというシートにあるB10の値を表示させます。って意味です。

しかしこれだとB42901くらいのところにカウントした１という数値が表示されてしまいます。

日付は1900年1月1日を1とし1日経つごとに+１されていきます。

1900年1月2日は2となり、2017月6月25日は42901と表示されます。

これを表示形式を変え年月日で表示させているのでoffsetでl3を選択すると42901下に移動してしまうんです。


DAY関数を使う

そこで試しにVBAでDAY関数を使ってみました。

Sub agg()
Range("l3") = Day(Date)
Range("d5").Offset(Range("l3"), 0) = Worksheets("カウント").Range("b10")
End Sub

なんとL3に今日の日付の15という数値が入るじゃないですか！

これで自動的に15日にカウント数が入力されます。

Day(today)かなって思ったんですけどとりあえずDateでうまくいってるのでこのままいきます。


一定の時刻にマクロを実行させる

この集計ボタンを押し忘れても大丈夫なようにマクロ予約ボタンを作ります。

営業時間終了の18時に自動で集計されるようのマクロを作ります、めんどくさがらずに。

Sub AutoAgg()
Application.OnTime earliesttime:=TimeValue("18:00:00"), procedure:="agg"
Range("g5") = "予約しました"
End Sub

あ、AggってのはAggregateです。

18時にaggっていうマクロを起動させますよって意味です。

OnTimeってのが関数みたいなもんですかね。

earliesttimeは実行時刻、TimeValueで時刻を指定し、Procedure:=でマクロ名を指定します。

予約したかどうかわかるように予約したらG5に予約しましたと表示させるようにしました。

あとは就業時間までに予約ボタンを押すのを忘れないようにするだけです。

ここまでするかってほどの手間がかかってます。


上書き保存のコードを追加する

あとあれだ、バカみたいに強制終了するやつもいるので集計されたら上書き保存するようにもします。

Sub agg()
Range("l3") = Day(Date)
Range("d5").Offset(Range("l3"), 0) = Worksheets("カウント").Range("b10")
MsgBox "集計しました"
Range("g5:i8").ClearContents
Workbooks("Book1").Save
End Sub

Workbooks("Book1").Saveでbook1を上書き保存できます。

そしてこのままだと次の日Book1を開いたとき予約しましたと表示されたままになってしまいます。

これだとバカみたいに今日は予約したんだなって勘違いして予約しないなんてことが起こらなくもないです。

そうならないように集計が終わったらG5に表示されている予約しましたの文字をclearで消します。

はい、これで終了となります。


まとめ

朝出勤したら予約ボタンを押す→予約しましたと表示される→18時になると自動で集計され保存される

と、こんな流れでできるようになりました。

ここまでやればきっと大丈夫でしょう。

最後のところでMsgBoxを使ってますがこれもG10あたりに表示させるほうがいいかな？

やっぱりこれをこうしたいって考えてるときは楽しいですね。

試行錯誤して思い通りに行ったときの達成感がすごいです。

しょぼいかもしれないですけど。

まだ10代に君、早めにプログラミングを始めたほうがいいですよ。

将来そういう仕事につかなかったとしてもきっと役に立ちますから。