Excelで、同ブック内の他シートにリンク可能なハイパーリンクを数式で作る。

※すみません以前投稿した記事のデバッグ版になります。

Excelのハイパーリンク関数についてです。

=HYPERLINK(リンク先,表示名）

で数式でリンクがはれて便利なのですが、
同じBook内だと、以下のような書き方をする必要があります。

=HYPERLINK("#シート名!アドレス",表示名）

更に、シート名の頭の文字が、"（"などの記号だと、

=HYPERLINK("#'シート名'!アドレス",表示名）

と書く必要があります。

この辺りの場合分けが面倒くさいので、
リンク先が、
①同じBook内の同じシート
②同じBook内の他シート
③同じBook内の他シート（シート名の頭が記号）
の３パターン全てに対応できるような数式を組んでみたら、こうなりました。
（※シート名、アドレスは編集して下さい。）

=HYPERLINK("#" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(CELL("address",シート名!$A$1),1+IFERROR(FIND("]",CELL("address",シート名!$A$1)),1)-1,200),"]","'"),"!$","'!$"),"''","'"),シート名!$A$1)

この数式の特徴は、リンク先のアドレスを「文字列」ではなく「セル参照」として持っているので、
リンク先が他のセル・他のシートに移動（カット＆ペースト）してもリンクが切れず、追従してくれます。
またシート名に変更があっても、リンクが切れません。

用途としては、
Excelをマニュアルとして使うとき、リンク先のアドレスがコロコロ変わるような場合に
便利かと思われます。動的な目次を作れるわけです。

さらにマクロではなく数式なので、
そのままお客様に納品しても、やっぱりリンクが切れることはありません。

また、上記の数式を入力するのは大変なので、
リンク先を選択すると自動で数式を入力するマクロも作りました。

'Excelの数式Hyperlink関数で、他シートにもリンクを貼る
Sub HyperLinking()

'Hpyerlink関数のリンク先文字列には、３つのパターンがある。
'①同じシート内。→シート名が不要で、$a$1のようにアドレスのみでOK
'②他シート内。→ アドレスの前に、"#シート名!" の形でシート名が必要。
'③他シート内（シート名の頭が記号）→アドレスの前に、"#'シート名'!" の形でシート名が必要。

Dim TargetCell, LinkCell, strCell, strFileSheetCell, strSheetCell, strLink, strHyperLink

'選択中のセルを、Hyperlink関数を挿入するターゲットとして設定。
Set TargetCell = Selection(1, 1)

'リンク先のセルを選択してもらう。（キャンセルはエラーになるので、エラー回避しておく。）
'複数セルの時は、(1,1)で左上を対象とする。
On Error Resume Next
    Set LinkCell = Application.InputBox("リンク先のセルを選んで下さい。（他シートもOK）", "リンク先選択", Type:=8)(1, 1)
    If LinkCell Is Nothing Then End
On Error GoTo 0

'*****ここから、数式の文字列を作成する。*****

'「'シート名！セルアドレス'」という文字列を作成
strCell = "'" & LinkCell.Parent.Name & "'!" & LinkCell.Address

'ExcelのCELL関数で、数式上で「[ファイル名]シート名！セルアドレス」という文字列になる数式を作る
strFileSheetCell = "CELL(""address""," & strCell & ")"

'↑から[ファイル名]を除外したアドレス。「シート名！セルアドレス」になる。 ※"]"は後で"'"に変換するのでとっておく
strSheetCell = "MID(" & strFileSheetCell & ",1+iferror(FIND(""]""," & strFileSheetCell & "),1)-1,200)"

'シート名の前後に、"'"をつけて、リンク先文字列を完成する。
strLink = """#"" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" & strSheetCell & ",""]"",""'""),""!$"",""'!$""),""''"",""'"")"

'HYPERLINK関数を完成する。表示文字列も、リンク先とする。
strHyperLink = "=HYPERLINK(" & strLink & "," & strCell & ")"

'ターゲットに数式を挿入して完了。
TargetCell.Formula = strHyperLink

End Sub