ハイパーリンクでVBAマクロを実行

できるけど、ハイパーリンク本来の動作のついでに実行される

　ハイパーリンクのアドレスにVBAマクロ名を書いたら実行！とかできれば簡単なんだけどそんな機能はなくて、でも FollowHyperlink イベントによって、ハイパーリンクをクリックした際にVBAマクロを実行する事はできる。ただこれはちょっと使い勝手が悪くて、

　てことで課題は２つ。

1. クリックしても何も起きないハイパーリンクを作りたい。なるだけ簡単に。

2. マクロを実行すべきハイパーリンクか否かを判別する情報を、ハイパーリンクに仕込みたい。セル位置で判別するのも手だけど、すぐ誤動作しそうなので極力イヤ。

FollowHyperlink イベントの書き方

　解決編の前に、まずは FollowHyperlink イベントの書き方から。
　イベントのマクロは普通のマクロとは記述方法が少し違う。普通のマクロは標準モジュールを作ってそこにコードを記述するが、イベントはシートやブックのモジュールにコードを記述する。

　あるシートでだけハイパーリンクでマクロを実行したいのなら シートのモジュールに FollowHyperlink イベント を、ブックの全シートのハイパーリンクで共通のマクロを実行したいなら ブックのモジュールに SheetFollowHyperlink イベント を記述する。

　コードエディタの上にあるコンボボックスでイベントを選択するとイベントの Sub プロシージャの雛形が作られるので、あとはその中にコードを記述すれば OK。

クリックしても何も起きないハイパーリンクを作りたい

　先に答えを言ってしまうと、リンク先のアドレスに ? とだけ書くと、何も実行しないハイパーリンクになる。つまりはクエリだけの URL。

　ただどうも、クエリから始まる URL を書くとクエリ部分は勝手に捨てられてしまうようで、次回ハイパーリンクを編集する際にはアドレスは空欄になってるので、編集するたび毎度 ? と書かないといけない。もう意地はらずにアドレスが空欄のままでも OK を押させてくれよと思う。ほかにもっとマシな記述がないか試してみた結果は以下。: もいけた。

アドレス	URL的な意味	結果
空欄		NG	OK ボタンを押せない
?	クエリのみ	〇	クリックしても何も起きない 編集するたび空欄に戻る ? 以降にパラメータも書けるが空欄に戻る
:	ポート記号 のみ？	〇	クリックしても何も起きない 編集するたび空欄に戻る : 以外に何か書くと怒られるか、スキーム扱いになる
#	フラグメント のみ	NG	ブック内移動と解釈されるので、 # のみや、移動先と解釈できない事を書くと怒られる
http:	スキームのみ	NG	ブラウザなどの対応アプリが開く もしくはセキュリティ警告が出る もしくは "c:" とかだとエクスプローラーが開く
/	パス区切り	NG	カレントドライブのルートが開く
.	拡張子区切り	NG	カレントディレクトリが開く
& =	その他記号	NG	ファイル名と解釈されて怒られる

　ちなみに、ハイパーリンク先をそのセル自身にした場合も一見何も起きないハイパーリンクになる、が、行を追加したり並べ替えりしてセルが動いた際、リンク先のセルは一切追従しない ので、他のセルへのリンクになって無茶苦茶になる。本当クソ仕様。

マクロを実行すべきハイパーリンクに情報を仕込みたい

　ハイパーリンクをクリックした際の FollowHyperlink イベントは下記のような Sub プロシージャで、引数として Hyperlink オブジェクト が渡される。

'シートにイベントを実装する場合
'実装したシートでのみ呼ばれる
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    :   
End Sub

'ブックにイベントを実装する場合
'まず Sh を見て対応すべきシートなのか判断する必要がある
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    :   
End Sub

　Hyperlink オブジェクトから得られる情報のうち、下記はユーザーが設定できるので判断材料に使えそう。

プロパティ	内容
Address	リンク先アドレスのうち、# より前の部分。 ただしアドレスに ? から始まるクエリだけを書いた場合は 捨てられてしまうようで取得できない。
SubAddress	リンク先アドレスのうち、# より後の部分。
Range	ハイパーリンクのセル位置。 リンクがある行や列の情報を操作したければこれを使う。
TextToDisplay	ハイパーリンク部分の表示文字列。つまりセルの文字列。 ハイパーリンク編集ダイアログでも変更できる。
ScreenTip	ハイパーリンクにマウスオーバー時に表示されるヒント。 ハイパーリンク編集ダイアログの ヒント設定 で設定できる。
EmailSubject	リンク先を電子メールアドレスにした場合の件名。 これを使うにはまず電子メールアドレスのリンクにしないと いけないので使い物にはならない。

　Address や SubAddress は何か仕込もうとするとすぐハイパーリンク本来の動作が動いて怒られたり誤動作するので難しいし、TextToDisplay もセルに表示される文字列そのものだから暗号みたいなのを仕込むのはイヤだなぁと思ってたのだけど、

　いやもちろん、本来の用途からは完全に逸脱してるけど、そもそもデフォルトで表示されるヒントからしてなんか情報過多でウザいなと思ってたので、ここにマクロを実行するトリガーやパラメータを仕込むのアリじゃね？

　例えばハイパーリンクのヒント設定で @Macro:10 などとキーワードを設定しておけば、マクロを実行すべきリンクか否かを以下のように簡単に判別できるし、それ以後をパラメータとしていくらでも機能追加できる。そしてセルごとコピペしても問題ないし、他人と共有するにしてもこのヒントをうっかり変える人なんてまず居ない。これで全然いけそう。

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'まずはマクロを実行すべきリンクかをヒント文字列の先頭で判断
    '違うならただのハイパーリンクなので即帰る
    If Not Target.ScreenTip Like "@Macro:*" Then
        Exit Sub
    End If

    'パラメータ部分(8文字目より後)を取得
    Dim param As Long
    param = Val(Mid(Target.ScreenTip, 8))

    'あとはパラメータに応じて処理を実行
    'リンクをクリックした行や列を知りたければ Range で取得できる
    Debug.Print "マクロ実行 param:" & param & " Range:" & Target.Range.Address
End Sub

Excel ガチ勢すげえ

　てことであまりバグ技じみた手法にまで手を出す事もなく、気軽にハイパーリンクでマクロが実行できるようになってよかったよかった。

　ハイパーリンク機能は今までだいぶ迷走してきたようで、右クリックで設定するリンクと HYPERLINK 関数のリンクは似て非なる物だったり、ブック内のセルを移動するハイパーリンクは今でも並べ替えなどには対応してなかったり酷いもんだけど、世の先人の皆様のノウハウは本当ありがたい。

　今回そのなかでも、たまたま見つけた外人さんの記事がエライ所まで突き詰めてて面白かったので一応紹介。ぜひ翻訳してコメント欄まで読んでみよう！

　途中からはもう黒魔術じみてて気軽に使えるシロモノじゃないので試してもいないけど、とりあえず以下はびっくりした。