Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

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

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

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

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

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

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

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

と書く必要があります。

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

=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関数のリンク先文字列には、3つのパターンがある。
'①同じシート内。→シート名が不要で、$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
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした