ExcelVBAのHyperlink.Addで表示文字が変わらない

日はHyperlink.Addで表示文字が思ったように表示されなくてハマったので、使い方を記録として残しておく。

ハイパーリンクとは
Hyperlink.Addの使い方
今回ハマったこと
参考
まとめ

ハイパーリンクとは

ハイパーリンクとは、文字通りハイパーリンクである。（←解説を諦めた人）

ユーザーがクリックしたと同時に、所定の動作をさせることが出来る。

具体的に何が出来るのかというと

別のExcelブックを開く
任意のPDFを開く
任意のJPGを開く（要するにWindows関連付け依存で何でも開ける）
WEBサイトを開く
メール作成画面を開く
任意のセルにジャンプする
別のシートにジャンプする

などなど。

限定的ではあるがxlsx形式のままでも、動きを与える事ができるので是非とも使いこなしたい機能の一つである。

ハイパーリンクの作成方法は

URLっぽいものを入力して、オートコレクトにより自動的にハイパーリンクに変化させる
HTML文章やRTF文章をコピーして、リンク情報の書式設定ごとセルに貼り付ける
右クリックの「リンク」からハイパーリンク作成ダイアログにて作成する
HYPERLINK関数を使用する
VBAのHyperlink.Addメソッドを使用する

などが考えられる。

本記事はVBAのHyperlink.Addメソッド使用における失敗談である。

Hyperlink.Addの使い方

通常、マクロの記録によりHyperlink.Addを作成すると下記のようになる。

ActiveSheet.Hyperlinks.Add _
    Anchor:=Selection, _
    Address:="C:\hoge\fuga.pdf", _
    TextToDisplay:="ひょうじもじれつ"

しかし、上の書き方は危険なので、下のように書き換えよう。※好み

If TypeName(Selection) <> "Range" Then Exit Sub
Dim rng As Range
Set rng = Selection

rng.Worksheet.Hyperlinks.Add _
    Anchor:=rng, _
    Address:="C:\hoge\fuga.pdf", _
    TextToDisplay:="ひょうじもじれつ"

B2を選択した状態で実行するとこのようになる。

エラー処理をしたので、図形を選択している時などは処理が取り消される。

これで、関数の引数にRange型変数rngを持たせることで、ハイパーリンク生成を行う部分を括りだすこともできたりと応用が効くようになる。

今回ハマったこと

最初に原因を言っておくと、「数値型（及びシリアル値）のセルには、表示文字列TextToDisplayが使えないということが判明した。

具体的には、上記プログラムを実行する前後で下図のようになる。

後に気がついたのだが、ハイパーリンクの編集をした時にも表示文字列が編集できない状態になっている。

というわけで、解決策はセルの値を文字列に変更しておくことだ。

つまり

rng.ClearContents

や

rng.NumberFormatLocal = "@"
rng = "" & rng.Value

と言った対策が考えられる。

そもそも、今回は数値を6桁の数字に揃えるのが目的だった。

結果が数字の場合、表示文字に指定した先頭のゼロが消えてしまうため、セルの文字列と同じようにシングルクォーテーションを記載する必要があった。

TextToDisplay:="'" & Format(rng.Value, "000000")

したがって、本件における最終的なプログラムはこのようになった。

Sub Test_解決法数値の場合()
    If TypeName(Selection) <> "Range" Then Exit Sub
    Dim rng As Range
    Set rng = Selection
    
    rng.NumberFormatLocal = "@"
    rng = "" & rng.Value
    rng.Worksheet.Hyperlinks.Add _
        Anchor:=rng, _
        Address:="C:\hoge\fuga.pdf", _
        TextToDisplay:="'" & Format(rng.Value, "000000")
End Sub

参考

なぜVBAでハイパーリンクを作成するに至ったか

HYPERLINK関数では、Office365など近年のExcelではリンクが開けなくなる障害が起こるため
データブックにマクロを埋め込みたくなかったため
まだ存在しないファイルということもあるため

何を試したのか

セルの書式設定の文字列化（結果としてValueを文字列化しなければならなかった）
先頭にシングルクォーテーションを付けるか付けないか
ダブルクォーテーションで囲うか囲わないか
イコールから始まる数式にしたらどうか

まとめ

数値型のセルにハイパーリンクを設定するときは、事前に対策が必要ですよ。

実行時エラーが出ないので気が付きにくいですよ。

という話でした。

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ