えくせるちゅんちゅん

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

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

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


ハイパーリンクとは

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

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


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

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

などなど。


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


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

などが考えられる。


本記事はVBAHyperlink.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を文字列化しなければならなかった)
  • 先頭にシングルクォーテーションを付けるか付けないか
  • ダブルクォーテーションで囲うか囲わないか
  • イコールから始まる数式にしたらどうか


関連ツイート

https://twitter.com/KotorinChunChun/status/1247769241261989894?s=20


まとめ

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

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

という話でした。

以上


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

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

プライバシーポリシー