はじめに
この記事ではExcel VBA で起きたあんなエラーや
こんな不具合、ポイントについて書いています。
動作環境
Windows10 Pro 64bit
Excel2016 32bit
Excel2019 64bit
O365なし
問題発生
障害や問題はある日突然起きる。
表題にもあるとおりExcel VBAで
ハイパーリンクを取得するマクロを
動かしていた時の話
100個のハイパーリンクが一つの
ハイパーリンクを削除しただけで消える
という問題が発生
ざっくり何が問題かと言えば
目に見えるハイパーリンクの数と
実際のハイパーリンクの数が違う
ということ
なんでこんなことが...
どうやらExcel先生はハイパーリンクの入ったセルを
複数選択でコピーして別のセルに貼り付けると
貼付先が複数セルでも
一つのハイパーリンクがセルに貼り付けられるそうだ。
要するに
セルの数だけハイパーリンクを貼り付けない
という仕様
ちょっと検証してみよう ~ 検証手順 ~
- マクロ有効ブックを新規作成
- 適当なセルにリンクの入りそうなURLを入力
- URLの入ったセルをコピー
- 複数行にセルをコピー
- ハイパーリンクを調べるをマクロ実行
マクロは以下のコードを使用した
Sub Sample2()
Dim i As Long, msg As String
'1回目 ハイパーリンクの数:2
msg = "ハイパーリンクの数:" & ActiveSheet.Hyperlinks.Count & vbCrLf
MsgBox msg
For i = 1 To ActiveSheet.Hyperlinks.Count
msg = msg & ActiveSheet.Hyperlinks(i).Range.Address & vbCrLf
Next i
Range("A1").Hyperlinks(1).Delete
'2回目 ハイパーリンクの数:1
msg = "ハイパーリンクの数:" & ActiveSheet.Hyperlinks.Count & vbCrLf
For i = 1 To ActiveSheet.Hyperlinks.Count
msg = msg & ActiveSheet.Hyperlinks(i).Range.Address & vbCrLf
Next i
MsgBox msg
Range("B2").Hyperlinks(1).Delete
msg = "ハイパーリンクの数:" & ActiveSheet.Hyperlinks.Count & vbCrLf
For i = 1 To ActiveSheet.Hyperlinks.Count
msg = msg & ActiveSheet.Hyperlinks(i).Range.Address & vbCrLf
Next i
'3回目 ハイパーリンクの数:3 ?
MsgBox msg
End Sub
トレース
簡単なプロシージャの動きを順に追っていこう
前提:
セルA1にリンクを入力
コピーしてB2からB5までペースト
1回目 このときのCountプロパティは
ハイパーリンクの数を2と返します。
1つはRange("A1")、もう一つはRange("B2:B5")
2回目 このときのCountプロパティは
ハイパーリンクの数を1と返します。
Range("A1")のハイパーリンクは削除
Range("B2:B5")はハイパーリンクの数を
1としています。
3つ貼り付けているように見えますが
実際には1つです。
それでは
3回目 このときのCountプロパティはいくつを返すか
答えは0です。
Range("B2")のハイパーリンクを削除したので
Range("B3,B4,B5")が残るように見えますが
Range("B3,B4,B5")はRange("B2")を参照しているので
Range("B2")のハイパーリンクを削除すると
つながるRange("B3,B4,B5")の
ハイパーリンクも削除してしまうのです。
厳密にいえばRange("B3,B4,B5")は
Range("B2")のハイパーリンクを共有している
対策
- 選択コピペはしない
- リンクの編集は右クリックでやる
- リンクの数が多い場合はマクロでリンクを貼る
- そもそもExcelにリンクを貼らない
まとめ
長々と書きましたが、いかがだったでしょうか。
それではまとめです。
Excelのセルにハイパーリンクを入れるときは
コピー&ペーストで貼り付けず
セルに直接、入力する形をとりましょう。