VBA Win32API GetAsyncKeyStateを使ってマクロ実行時に特定キーが押されているかを検知する。

今回はWin32APIのGetAsyncKeyStateを使ってマクロ実行時に特定キーが押されているかを検知するコード。
この手の情報は既に沢山出ているが、検索したサイトはいずれも情報が完全ではなかったので少し苦労した。

では、早速完成コードを紹介する。
今回はシフトキーが押されているかどうかを検知した。

コード

#If Win64 Then
    Declare PtrSafe Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As LongLong) As Integer
#Else
    #If VBA6 Or VBA5 Then
        Declare Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Integer
    #Else
        Declare PtrSafe Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Integer
    #End If
#End If
'GetAsyncKeyStateは、-32768, 1, -32767, 0 のうちいずれかの16ビット整数を返す。
'これは二進数に直したときのビットに意味がある。
'-32768: 1000 0000 0000 0000  最上位ビットが1なら、現在そのキーが押されていることを示す。
'1         : 0000 0000 0000 0001 最下位ビットが1なら、最後のGetAsyncKeyState呼び出しの後にそのキーが押されたことを示す。
'-32767: 1000 0000 0000 0001 従って、これは両方に該当することを示す。
'0         :0000 0000 0000 0000 これは、どちらでもないことを示す。
'つまり現在キーが押されているかどうかを知るには、GetAsyncKeyStateの結果を-32768のAndマスクに掛け、
'-32768になればOKということ。

Function IsShiftKeyPressed() As Boolean
    Const KEY_PRESSED = -32768      '1000 0000 0000 0000 最上位ビットが1であることを示す。
    IsShiftKeyPressed = (GetAsyncKeyState(vbKeyShift) And KEY_PRESSED) = KEY_PRESSED
End Function

Sub hoge()
    Debug.Print IsShiftKeyPressed
End Sub

用途

私が公開しているフローチャート作成ツール BreadChart に機能搭載するのが目的。
Connectorモードのとき、条件分岐するシチュエーションにおいて、新しいコネクタから開始するのにわざわざモードをOff・OnしないといけないのをShift+Clickで出来るようにした。
thom.hateblo.jp

というワケで、昨日1.2を出したところだけど、1.3をしれっと公開済み。

参考サイトと苦労したポイント

最初に検索にヒットしたのがこちら。
officetanaka.net
ただ中身をみてみるとコマンドボタン限定なので今回の用途にはマッチしなかった。

次にこちら。
excel-excel.com
キーコード付きでサンプルが掲載されていてとてもわかりやすい。

そしてWin32APIを使う以上、64bit対応も考慮したかったので、こちらも参考にした。
ameblo.jp

それで単純なサンプルを書いてみた。

#If Win64 Then
    Declare PtrSafe Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As LongLong) As Long
#Else
    #If VBA6 Or VBA5 Then
        Declare Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Long
    #Else
        Declare PtrSafe Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Long
    #End If
#End If

Sub hoge()
    Debug.Print IIf(GetAsyncKeyState(vbKeyControl), "Pressed", "Not Pressed")
End Sub

しかし、なぜかCtrlを押してない時までPressedになることがあり、次のコードで値を見てみた。

Sub hoge()
    Debug.Print GetAsyncKeyState(vbKeyControl)
End Sub

結果は、Ctrlが押されているときが32768か-32767、押されていない時が1か0になる。
なんじゃこれと思って調べてみると、以下のサイトに解説があった。
tokovalue.jp

この関数は、最上位ビットと最下位ビットに意味があるのだが、そもそもこれ、戻り値はInteger(16ビット整数)だそうだ。
最上位ビットは符号を表すので、IntegerとLongでは解釈が異なってしまう。

ということで修正。

#If Win64 Then
    Declare PtrSafe Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As LongLong) As Integer
#Else
    #If VBA6 Or VBA5 Then
        Declare Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Integer
    #Else
        Declare PtrSafe Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Integer
    #End If
#End If

これで戻り値はCtrlが押されているときが-32768か-32767、押されていない時が1か0になった。

Ctrlが押されているとき、

• 32768は1000 0000 0000 0000
• 32767は1000 0000 0000 0001

Ctrlが押されていないとき、
1は0000 0000 0000 0001
0は0000 0000 0000 0000

つまり、最上位ビットが1かどうかを知りたければ、戻り値と-32768をAndしてやれば良い。
それで完成したのが冒頭のコードだ。

なんでShiftに変えたかというと、BreadChartに組み込む際に、Ctrl+クリックではマクロが発動する以前にオートシェイプの選択として機能してしまい、使えなかったので。

以上。

VBA フローチャート作成ツールBread Chartに挿入機能検討

先ほど新版を公開したところだけど、以前から悩んでいた機能がなんとか実現できそうな感じがしてきたので、取り急ぎコードだけここに書いておく。
thom.hateblo.jp

実現したい機能

たとえば下図のようなことをやりたいとする。

しかし、単純に列ごと切り取って挿入すると、コネクターが切れてぐちゃぐちゃに。

また、単純に行挿入したけれど、プレースホルダーが無くてプロセスを配置できない。

現状のバージョン1.2では、ひな形から作り直すしかない。

今回はこれを何とかするコードをとりあえず書いてみた。
とりあえずなので、ユーザーインターフェースはなく、F8で実行する仕様。
もちろん、中途半端なのでGitHubには反映させていない。

コード

標準モジュールを挿入してUtilとでもしておくと良い。

Sub FixConnectorBug()
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        Select Case sh.AutoShapeType
        Case _
            msoShapeFlowchartProcess, _
            msoShapeFlowchartDecision

            '切れたように見えて論理的につながっているコネクタの表示を直すには、
            'コネクタがつながっているシェイプを少し移動すれば良いことに気付いた。
            '移動してから戻すというコードを考えたけれど、
            'マクロで対応するときは移動しなくても位置を再設定さえすれば直った。
            '以下の一行は一見無意味に見えて、表示バグを修正しているコード。
            sh.Left = sh.Left
        End Select
    Next
End Sub

Sub InsertPlaceholderToSelection()
    Const ProcessWidth As Double = 100
    Const ProcessHeight As Double = 40
    Dim r As Range
    For Each r In Selection
        Dim sh As Shape
        Set sh = ChartSheet.Shapes.AddShape( _
            Type:=msoShapeFlowchartProcess, _
            Left:=r.Left + (r.Width - ProcessWidth) / 2, _
            Top:=r.Top + (r.Height - ProcessHeight) / 2, _
            Width:=ProcessWidth, _
            Height:=ProcessHeight)
        Call DeactivateProcess(sh)
        sh.OnAction = "Click"
    Next
End Sub

FixConnectorBugについて

列ごと入れ替えるとコネクターが切れると書いたけど、実は切れているわけではない。
コネクターを選択すると接続されていることを示す緑の端点が表示される。
実は下図でいう赤丸同士は、論理的にはつながっていて、表示がおかしいだけなのだ。

右クリックで接続されているプロセスシェイプのどちらかを選択し(左だとマクロが発動するので)、その状態で左クリックでドラッグすると接続が元に戻ることを発見した。

FixConnectorBugを実行するとすべてのシェイプを探索し、プロセスか判断だった場合(つまりコネクタ以外)はLeftプロパティで位置を再設定する。これでコネクターの表示が正常に戻る。

InsertPlaceholderToSelection

これは、プロセスのプレースホルダーが無いセルを選択して実行するとそこにプレースホルダーを作成するマクロ。
ひな型作成コードの一部を取り出しただけなので、なにも苦労は無かった。さっさと作っておけばよかった。

課題

今のところ手作業で行列の挿入・セルの挿入等を行った後にマクロで補完する方式なので、機能としてマクロに搭載するならまるっと自動でやりたい。でも欲張るとなかなか公開できないのが悩みどころ。

先行で使いたい方いたら今回の記事からコピペしていただくと良いかと。

あと、セルの挿入機能を使う以上、書式ズレの問題が避けられないのも課題。これもきちんとやるのは面倒くさそうだなぁ。

いずれにしても、光明が見えたので良かった。従来はプロセスを一つずつズラす方式を頭の中でシミュレーションして破綻してたので、セル挿入機能をベースにする方針を思いついたのは大きな収穫。むしろなぜ気付かなかったのか謎だ。

VBA フローチャート作成ツール(BreadChart)の更新 Version 1.2

GitHubで公開しているフローチャート作成ツールをアップデートしたので変更点のご案内。

入手場所

こちらからアクセスしていただき、
github.com

以下のDownloadボタンで入手できます。

更新内容

Configシートでシェイプの色を指定できるように変更

Process・Judge・Connectorの色をそれぞれ指定できます。
ただしそんなに頻繁に変えるものではないので、GUIは実装してません。気が向いたら実装します。

色は0～16777215までのLong値で指定します。
色から値を調べる方法はいくつかあります。
たとえばセルに色を塗り、そのセルを選択した状態でイミディエイトウインドウで「?selection.interior.color」を実行すると良いでしょう。
色名(XlRgbColor定数名)が分かっていれば、イミディエイトウインドウですぐに値を調べられます。例「?rgbWhiteSmoke」

コマンドボタンをリボンUIに変更

実はver 1.1としてしれっと変更してましたがTwitterでしか言ってなかったので改めて紹介。

Before

After

「チャート完成」を「チャート発行」に変更し、別ファイル出力に変更。

実はver 1.1としてしれっと変更してましたがTwitterでしか言ってなかったので改めて紹介。
といっても内容は見出しの通りなので特に書くことなし。

以前の更新

前回の更新

thom.hateblo.jp

初版

thom.hateblo.jp

忙しすぎる人に確実に読んでもらう為のメールの送り方について考察

今回は忙しすぎる人に確実にメールを読んでもらうためのテクニックについて自分の考察を紹介する。

ここでいう忙しすぎる人とは、「忙しすぎて諦めの境地にいる人」のことだ。諦めの境地なので、基本的に人の頼み事なんて知らんぷり。電話しても日中に繋がることは稀なので、基本コミュニケーションはメールになる。

にもかかわらず、メールはメールで返事が来ないのだ。

しつこく電話をかけ続けてやっと捕まえると、だいたい以下のような反応。

「あれ？そんなメール来てたかな？てへぺろ(・ω<)」
「あれ？何かしないといけないんでしたっけ？てへぺろ(・ω<)」
「あ、私関係ないと思ってました。てへぺろ(・ω<)」

彼らは、「本当に重要なことは、伝える側に責任がある」と考えている。だから、基本的によく読まないと分からないような案件は完全無視を決め込む。さもなくば自分が過労で倒れてしまうからだ。

大事なことなのでもういちどいう。
さもなくば自分が過労で倒れてしまうからだ。

。。。とはいえ、絶対に無視してもらっては困る情報もあるわけで。

今回はなぜ重要なメールが無視されてしまうのかを明らかにし、無視できなくなるメールの送り方について考察してみたい。

忙しすぎる人の情報フィルタリングフロー

メールに限らず、忙しすぎる人は情報を瞬時にフィルタリングする。なんのためかって？なるべく多くの情報を無視するためだ。

送り手は、自分が送った情報がこのように処理されることを想定している。

しかし、私の経験上、忙しすぎる人のフローは以下のようになっている。

どうしても読んでもらう必要がある場合

どうしても読んでもらわないと困るメールの場合、忙しすぎる人の情報フィルタリングフローを踏まえて、特に強調すべきメッセージが4つある。

それは、
「アクションが必要です！」
「あなたが対象者です！」
「デッドラインは〇日〇時です！」
「無視すると後で面倒なことになります！」
ということ。

この4点を示唆する文は、冒頭の数行で瞬時に把握できるようにしたい。

簡単なことなんだけど、送り手はつい「やってほしいこと」を強調してしまう。
極端な話、「重要さ」さえ伝われば、後は強調するまでもなく隅々まで読んでもらえるので、そんなところは強調しなくて良い。

アクションが必要であることを示す方法

私の場合、アクションが必要であることは、件名のタグで明示するケースが多い。
たとえば、【要返信】・【要対応】など。

多くの人が、「で、結局何かするのこれ？」っていう、ぼやっとしてよく分からないメールを受け取った経験があると思う。
件名に要対応とあれば、「とりあえず、何かアクションを求めているな」ということが分かるので、メールを読む優先度が上がる。

あなたが対象者ですと示す方法

相手が少数なら宛名で十分だけど、複数名に送る場合、「このメールは〇〇で××な方宛にお送りしています。」と冒頭に一文入れ、そのあと続けて「宛先各位」という宛名にしてることが多い。

なぜ自分に送られたのか分からないメールは、関係ないと判断される可能性が高いからだ。

デッドラインを示す方法

普通に日付を書く場合もあるけれど、基本的に社内ではHTMLメールなので、全体を赤字かつ数字をサイズアップして強調ことが多い。
2019年3月6日 (水) 17:00まで

このとき気を付けたいのが、日付と曜日のアンマッチあるあるだ。
特に、日付固定で何かをお願いする場合、絶対に外してはならない。真面目に確認してくれる人ばかりならまだいいんだけど、忙しい人は「やらない言い訳」を渇望しているので、誤解や勘違いを装った確信犯テクで依頼をかわされる危険がある。

また、時刻は基本的に入れる。なぜなら、時刻を入れることで、「何か根拠があってその時間になっているのでは？→バッファなしの本物のデッドライン」感が出るから。（ぶっちゃけてしまうと、もちろんバッファは積んでるので17時をデッドラインに設定する根拠はどこにもなかったりする。あー、言っちゃった。）

定時とかにせずに中途半端な時間にしておくと、より一層、何か事情がある感が増す。

無視すると後で面倒である旨を示す方法

これはケースバイケースだけど、基本的に面倒くささを想起させる文節を単体で強調する。

こんな感じ。
「もし、今回の期日までにご対応いただけない場合、後程ご自身で〇〇していただく必要がございます。」

まとめ

今回は忙しすぎる人を対象にどうすれば読んでもらえるのかを考察した。
でもこれは相手がそれほど忙しくなくても使えるテクニックだと思う。基本的に、だらだら書かれたメールをきちんと読むのって面倒くさい。「無視して良いか」を速やかに判定できるメールは誰にとっても好ましいことである。

私のやり方が参考になるかどうかは分からないけど、各自工夫されると良いと思う。

VBA 狙った範囲だけをAutoFitするマクロ

以下のような表を作ったとき、普通にオートフィットをかけるとA1セルに入力されたタイトルの幅もカウントされてしまい、幅がおかしくなる。

↓AutoFit後

今回はこれを何とかするマクロ。

元ネタはこちら。

こういった表、よく作るんですが、列幅のオートフィットをさせたい時、タイトル部分が邪魔をしてA列だけびよ～～～～ん！ってなります。

・対策はセルの結合
・使わない（ページ設定のヘッダを使う）
くらいしか思いつかないんですが、他に良いアイディアありますか？ pic.twitter.com/xpatLj7Szi

— ことりちゅん@えくせるしゅきしゅきVBAしゅみぐらま (@KotorinChunChun) 2019年3月1日

では、早速コードを紹介しよう。

ただし、注意点として、このマクロはシート上のデータをすべて変数に退避させて一旦消す処理を含むので、マクロがコケた場合は対象データ全消失もありえる。試す場合はくれぐれもファイルを保存してからどうぞ。
念のため言ってるだけなので、実際そんなことにはならないと思うけど。

Sub SmartFit()
    Const MAX_COLUMN_WIDTH = 80
    If TypeName(ActiveSheet) <> "Worksheet" Then
        MsgBox "このマクロはワークシート上で実行してください。", vbExclamation
        Exit Sub
    End If
    
    Dim sh As Worksheet: Set sh = ActiveSheet
    
    Dim targetArea As Range: Set targetArea = Selection.CurrentRegion
    targetArea.Select
    If vbYes <> MsgBox("選択エリアに対してSmartFitを適用しますか？", vbQuestion + vbYesNo, "確認") Then
        MsgBox "キャンセルしました。", vbInformation
        Exit Sub
    End If
    
    
    With sh
        Dim wholeArea As Range: Set wholeArea = .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell))
        Dim wholeBackup: wholeBackup = wholeArea.Formula
        Dim targetBackup: targetBackup = targetArea.Formula
        
        wholeArea.ClearContents
        targetArea.Formula = targetBackup
        
        Dim zoomLevel As Long
        zoomLevel = ActiveWindow.Zoom
        ActiveWindow.Zoom = 100
        With targetArea
            .EntireColumn.ColumnWidth = MAX_COLUMN_WIDTH
            .EntireRow.AutoFit
            .EntireColumn.AutoFit
        End With
        ActiveWindow.Zoom = zoomLevel
        
        wholeArea.Formula = wholeBackup
    End With
    
    MsgBox "実行しました。", vbInformation
End Sub

使い方

対象の表内の特定セルを選択した状態で実行する。

※シート内ならどこでも良いわけではなく、実際にオートフィットさせたい表内の任意セルを選ぶこと。例えばタイトルを書いたA1セルを選んで実行すると上手く行かない。CurrentRegionで範囲を決定しているため、表とそれ以外の要素は空行・空列で区切られていなければならない。

実行すると表全体が選択され、この範囲で実行して良いかの確認メッセージが表示される。

「はい」を選ぶと対象範囲のみを基準にAutoFitされる。

解説

※画像のサイズが不ぞろいなのはご愛敬。。
以下の画像を見ていただけると、大体何をやってるか分かると思う。

以上

VBA そのマクロ集約、ちょっとまった！

たとえば、とあるデータを次のようなステップで加工しているとする。

それぞれのマクロはボタンを押すだけで完了する。
だったら、わざわざ3つもボタンを押させる意味はあるのか。
普通はそう考える。

そしてボタンを3つ押す作業を一つのボタンに集約する。

これは本当に良いことなんだろうか？

もちろん、ケースバイケースだとは思うが、私はマクロを作るときに、最悪マクロが動かなくなっても、データの変換が人手でも行えることが重要だと考えている。
中間データをあえて見せることで、加工の過程が明らかになるのであれば、その方が良い。
ボタンを集約してしまうと内部の加工ステップがブラックボックス化し、入力データから出力データへの変換をどうやるのかという手順が想像しづらくなる。

この仕事は何ですか？→ボタンを押すことです。こうなったらおしまいだ。
人がきちんと仕事の意味を考えて仕事を遂行できるようにしておくこと。マクロはあくまで繰り返し行われる単調で面倒くさい作業の緩和や、人が行う作業のミス防止などのサポートに徹するべきだと思う。

マクロを集約したくなったら、ブラックボックス化しないか、作業者がきちんと何をしているか意識して作業できるかを考慮して慎重に行うようにしたい

参考(手前味噌)

thom.hateblo.jp

thom.hateblo.jp

マクロ作成者が覚えておきたい重要な概念「冪等性(べきとうせい)」について

冪等性(べきとうせい)とは、ある操作を1回行っても複数回行っても結果が同じであることをいう概念である。
言葉の響きは難しいが、これはプログラマーなら何度も遭遇するシチュエーションなので、是非とも覚えておきたい。

たとえばExcelマクロにおいて、以下のコードは冪等ではない。
実行するたびに、オートフィルターのON / OFFが切り替わってしまうからだ。

Sub NotIdempotent()
    Selection.AutoFilter
End Sub

先日業務であるツールを使用する様子を見たが、そのツールで使われていたオートフィルターを設定するコードは冪等ではなかった。
すると、加工するブックは予めオートフィルターを外しておかなければならない。

冪等性に注意を払ってこのコードを作り直すと、以下のようになる。

Sub Idempotent()
    If Not Selection.Parent.AutoFilterMode Then
        Selection.AutoFilter
    End If
End Sub

選択範囲の親(ワークシート)を確認し、フィルターがかかっていたらそれ以上は実行しないという処理である。
ただこの処理でもまだ詰めが甘い。なぜなら、全く意図しない場所にフィルターが掛かっていたとしても、処理済と見做してしまうからだ。

改良したのがこちらのコード。まず狙った位置にオートフィルターをかけ、もし掛かっていなければ外れたと判断してやり直す。

Sub IdempotentFixed()
    Do
        Selection.AutoFilter
    Loop Until Selection.Parent.AutoFilterMode
End Sub

あるいは、最初からフィルターを解除してしまい、それからフィルターを掛ける。

Sub IdempotentFixed2()
    Selection.Parent.AutoFilterMode = False
    Selection.AutoFilter
End Sub

前者はバグにより無限ループを作らないとも限らないので、最初からフィルターを外す後者の方が良いかもしれない。

いずれのコードも冪等性は確保されている。
この冪等性という概念を知っていると、自分のマクロが冪等性を担保しているかという批判的な眼を養うことができ、より安定したコード作成ができるようになると思う。

注意：今回は簡単に紹介する目的でSelectionを使用していますが、実際に組み込むマクロではRangeやWorksheetを特定して使っています。