上長というのは、得てして吐くほどめんどくせえアナログ作業を依頼してくる生き物である。
〜引用元：ボロボロ皇帝のBoroism
ようこそ諸君、ボロボロ皇帝だ。
我輩はめんどくせえこと大嫌いな面倒くさがりなのだが、
本日、久しぶりに上長(所長)がマジクソめんどくせえ事を、我輩に依頼してきたので、
せっかくだしその時使った何かと応用しやすい(が一歩間違ったら大惨事な)、マクロ(VBA)コードを諸君へ伝授してやるぞ。ありがたく思うのだ。
さっさと本題に行きたい諸君のために目次↓
我輩は小売系の会社で働いている(量販店のとある部署)。
小売業とは何かにつけてキャンペーンを発足するものだ(まあ本当に安くなるから別に顧客からすればいいことなのだがそこは置いといて)。
でまあ、そのキャンペーンがどれだけ当たったか、宣伝費などに対して売上は、という事を数値をもってしてしっかり分析せねばならん。
要は費用対効果と言うやつだ。
分析に必要な数値を集計するには、
・このお客様キャンペーン見てきた方ですよ(^o^)→フラグ1
・このお客様キャンペーン見てないですよ(´・ω・`)→フラグ0
という風に分かるようにしておけば、BI(ビジネスインテリジェンス)でもなんでもあとから集計をかけることはできる。要は条件の一致で調べていくのだ。
だがしかし！
お間抜けさんはそのフラグ1を立てない。
それどころか、お間抜けなキャンペーン発案者であれば、フラグ立ててね！というルール決めすらしないし、条件のことなど何一つ思いついていない。
つまり、無法地帯だ。無法地帯でキャンペーン受付は増えるが一体どれがキャンペーン受付分か、どれがそうじゃないのか、もう全てがごっちゃになって何がなんだか切り分けができないのだ。
頼れるのは、その時、顧客との約束事項が記載されている受付担当の自由入力欄。
自由入力欄はそれはそれは自由に記載されており条件など何もないので通常、集計には役立たない。
そんな自由入力欄に唯一、キャンペーン受付とそうでない顧客を識別できる方法がある。なぜならキャンペーン受付であれば受付担当者が、キャンペーン、もしくはｷｬﾝﾍﾟｰﾝ、もしくは割引という単語を入れてくれているからだ！！
今回我輩が科せられた縛りや与えられたアイテム等を整理しよう。
・全受付から条件無しでキャンペーン受付を切り分ける
・自由入力欄はBIでは一部しか出てこない
・BIから受付番号は拾える
・その受付番号があれば、受付用端末システム上から手動で検索し受付履歴を照会することができる
・受付履歴では自由入力欄を見ることができる
・受付件数は100件程
これをバカ正直に手作業でやるとなると、
いちいち手動で受付番号を叩き→出てきた受付履歴の自由入力欄をコピーし→EXCELシートに貼り付け(ペ)る ×100回
その後に→Find関数で単語検索掛ける
ということになり、ひと言でいえば集計だが、実質【イカれた作業】になるわけだ。
我輩はそんなイカれたことはしたくない。
そんなの、狂ってしまうじゃないか…。
もう、十分…狂っているというのに…。
それを、それに手を出してしまったら…。
終わりだッ…。
終わりなんだああああああ！！！！！！
というわけで、我輩はマクロという名の魔法を使うことにした。
我輩はマクロ使いの弟子なのだ(師匠はネット上にたくさんいる)。
我輩は、座標を拾うコードとクリックするコードでコピペ作業を自動化することにした。
※ちなみにこのコードの「一歩間違えたら大惨事」という部分は後ほど説明する。
これから紹介するのは、
好きなところを自動でクリックするVBA
である。
まずは、
座標を拾う必要がある。
APIについて説明が必要な諸君のために説明しよう。
APIとは：Application Programming Interfaceである
は？という感じだな。そりゃそうだ。
まあ我輩はいつも適当な覚え方をしているのであまりためにはならんだろうが、我輩の認識している概念みたいなのでよければ説明させてもらおう。不要であればすっ飛ばしてくれ。
APIとは共有できるマジックハンドのようなものだ。
マジックハンドってのは、これだ↓
例えば、
A地点にあるあのゴミ箱にゴミ入れたいけど遠くて手が届かないよーとなった時、
みんなで共有できる貸出マジックハンドがあればそこにゴミを入れることができる(コロナだから共有できないとかいう話は無しだ)。
それはB地点にあるゴミ箱にでも、C地点にあるゴミ箱にでも、条件さえ整えばそのマジックハンドを比較的広範囲で使えるのだ！！
つまり便利道具だ。
その便利道具は保管場所にあるのだが、その保管場所の名前を間違えると借りることは出来ないから一字一句間違えないようにしなくてはならん(名前間違えるようなやつには貸しませーん(^o^)とのことだ)。
(適当な)説明は一旦ここまでとして。
まず適当に標準モジュールへ以下のコードをコピペ。
ここから↓
Private Type position
x As Long
y As Long
End Type
Declare Function SetCursorPos Lib "User32" (ByVal x As Long, ByVal y As Long) As Long
Declare Sub mouse_event Lib "User32" ( _
ByVal dwflags As Long, _
Optional ByVal dx As Long = 0, _
Optional ByVal dy As Long = 0, _
Optional ByVal dwDate As Long = 0, _
Optional ByVal dwExtraInfo As Long = 0)
Declare Function GetCursorPos Lib "User32" (lpPoint As position) As Long
Sub Lclick()
mouse_event 2
mouse_event 4
End Sub
Sub Rclick()
mouse_event 8
mouse_event 16
End Sub
Sub zahyotyosa()
Dim pos As position
Call GetCursorPos(pos)
Debug.Print pos.x, pos.y
MsgBox pos.x & ", " & pos.y
End Sub
ここまで↑
【分かった気になれるかもしれない解説】
Private Type positionがUser32という保管場所(Lib＝ライブラリー)にあるAPI(マジックハンド)を借りるぞ、というコードだ。
今回ここから借りるAPIは、
・座標を調べるためのもの
・マウスクリックするためのもの
の二つである。
Sub Lclick()とSub Rclick()で先程User32借りてきたマウスクリックするためのものを使う。
わかりやすく名前をLclick(左クリック)とRclick(右クリック)にしており、その名の通り中身はクリックするだけのコードである。
マウスをクリックするには、押して離すという動作が必要なわけである。なので1つのクリックで処理を二つ書かなければならない。面倒なので処理をひとまとめにしておくのだ。
※ひとまとめにしたものをプロシージャという(Sub〜End Subまでのひとつのまとまり。Subはプロシージャの名前みたいなもの、別に覚えなくてもいい)。
そうすると後から呼び出せて楽である。
Sub zahyotyosa()も同じ考えである。座標を調べるためのものを使って必要な処理をひとまとめにしている。x軸とy軸の交わる点を教えてもらうためのコードというわけだ。
ちなみに、MsgBoxは別にMsgBoxでなくてもよい。これをDebug.Printに変更するとイミディエイトウィンドウとかいう絶対覚えきれなさそうな名前のウィンドウ(我輩は名前はいつも覚えきれていない)に実行結果を吐き出させることが可能なのでそこに表示させてもいい。
このイミディエイトウィンドウが見当たらない場合は、Ctrl+Gで出てくる。
でまあ、己の調査したい座標位置にマウスポインタを持ってきて、zahyotyosaをF5で実行させると無事座標を把握できる。
上記が完成して、
座標を調べたところでようやく好きなところをクリック
デキるようになるのである。
例えば、座標1000, 1000を右クリックしたい場合は以下のとおりだ。
Sub LRclick()
SetCursorPos 1000, 1000
Rclick
End Sub
このように、調べた座標とクリックのコードを組み合わせると任意の場所をクリックできるようになる。
こんな感じで出来るようになる、任意の座標自動クリック。
我輩の場合はコレを、
AppActivate "受付情報照会"
だとかで現在開いているシステムのアプリケーションウィンドウをアクティブにして受付番号を
SendKeys "^v"
でペーストしたり、
SendKeys "~"
でEnter打ったり、
Application.Wait [now()+"00:00:0.5"]
とかで参照待ちしたり、したあとに、
SetCursorPos 686, 711
Lclick
Lclick
こんな感じで、出てきた画面のコピペしたい部分にマウスポインタを持って行き、ダブルクリックで範囲指定して～といった応用をする。
そう、ダブルクリックも出来るのだ！！
それと、マウスイベントを応用すれば、
ドラッグ＆ドロップもできる
Sub Lclick()
mouse_event 2
mouse_event 4
End Sub
とあるが、
この、
mouse_event 2
が、左クリックを押した瞬間で、
mouse_event 4
が、左クリックを話した瞬間であるから、
Sub drag()
mouse_event 2
End Sub
Sub drop()
mouse_event 4
End Sub
例えば、x1000・y1000の位置からx200・y200の位置にドラッグしたい場合
SetCursorPos 1000, 1000
Call drag
SetCursorPos 200, 200
Call drop
とすれば、無事好きなもんを好きなところへドラッグ＆ドロップできるのだ！
ものすごく楽しい〜ぞ(^o^)
…と、こんな感じのを駆使してどうにかこうにか吐くほどめんどくさい集計を自動化させた我輩。
だが、外部アプリケーションを操作しているため、
Application.Wait
での待機時間の微調整が必要な内容である。
この待機時間を0.1秒でも早く見積もってしまうと、全部が狂う。
たとえば待機待ちの計算をミスって100ある繰り返し作業を流れのまま進めてしまったとしよう。というか進める、VBAとはそういう自動化であるからな。
そうなるとマジで無駄なモンができあがってしまうのである。
これが一歩間違えたら大惨事な点だ。
こんな感じで大惨事を引き起こさないようにするためには、微調整と、テスト行為が重要だ。
それらを経てこのコードは完成するため、完成までの調整とテストをいかに早く終わらせられるかも意識せねばならない。そうしないと作業自体が進められないからな。
もちろん同じ作業を人力で100繰り返すよりは断然早く済むだろうが、それでも更に素早さとある種の見切り(作業を速く終わらせようとしすぎず、待機時間に余裕を持つ見切り力)のつけ方が大事である。
以上、別に、任意の座標を自動クリックするだけであれば、別に大惨事になることは無いだろうが、我輩と同じような事(外部アプリケーションの操作)をしようとしている諸君がいたときのために、注意点であった。
我輩の場合は、大惨事を大分前に経験済みであるから、既に色々と学んで今回のこれは完璧に上手くいった。
VBA最高である！！！
余談だが、VBAでの集計自体は上手くいったが、受付時のヒューマンエラー的な物がいくつかあったため、結局手作業での修正も加わった。
まあほぼ自動で終わらせられたから良かったとして、これ、手作業でしてたらマジで発狂してたな、と思う。本当に恐ろしいぜ…。
