Excelでのハイパーリンク設定時の問題点
ハイパーリンク機能はとても便利な機能ですが、手動でハイパーリンクを設定する際に複数の問題があります。それらの問題を以下に挙げます。
セルが複数ある場合に設定が面倒
セルに書かれているURLやファイルパスに対してハイパーリンクを設定する場合、右クリックやリボンからハイパーリンクを選択してダイアログでアドレス入力、という流れになります。けど、そもそもこの操作が面倒です。セルが１つならまだいいですが、多数ある場合は本当に大変な作業になります。
フォントが勝手に変わる
ハイパーリンクを設定するとなぜかフォントが勝手に変わってしまいます。たとえばセルのフォントをメイリオに変更していた場合にハイパーリンクの設定をするとMS Pゴシックなどの標準フォントに変更されてしまいます。そのためハイパーリンク設定後にフォントをまた変更しなければならない作業が発生します。
とても迷惑です。なんでこんな実装をしたんでしょうね。
これはハイパーリンクのスタイルの設定でフォントの書式の初期値が標準フォントになっていることが原因です。手動でこれを解決するには以下の手順になります。
1. 挿入タブ→ハイパーリンク、もしくは、セル右クリック→ハイパーリンクで、セルの値に対してハイパーリンクを設定します。先にこれをやっておかないとスタイルに「ハイパーリンク」が表示されないための措置です。
2. ホームタブ→スタイル の一覧のハイパーリンクを右クリックします。
3. 右クリック→変更を選択する。
4. スタイルダイアログでフォントのチェックをはずします。
これで以降のハイパーリンクの設定でフォントが変わることは無くなります。
ハイパーリンクを行うセルのフォントの種類が固定であれば、スタイルのダイアログでフォントのチェックは付けたままで書式設定ボタンを押して、フォントの設定を変える方法でもいいです。
けど、面倒ですよね。
スタイルを変えると青文字の下線が無くなる
上記のハイパーリンクのスタイルの設定でフォントのチェックをはずすと、次に別のセルでハイパーリンクを設定すると青文字＋下線が表示されなくなります。なので、これもハイパーリンク設定後にフォントの設定を変更する必要があります。手間がかかりますね。
原因は、先ほどのフォントのチェックをはずした際にフォントの書式も設定しない、とみなされるため青文字＋下線の表示設定もされなくなってしまうのです。これは回避しようがありません。
ハイパーリンクを設定したあとで青文字＋下線の設定を書式設定で行うことになります。面倒ですよね。はっきり言うと、これはスタイルの設定の設計が良くありません。フォントの種類と色は相反するものではないのに、チェックボックスの有無でどちらか一方が設定される仕組みになっています。バグと言ってもいいと思います。
とは言ってもどうにかしないといけないのでコードで対応することにします。
ソースコード
このマクロは上記の問題点に対応しています。
そのため、複数セルを設定可能ですし、フォントの種類が書き換わることはありませんし、青文字＋下線も付けてハイパーリンクの設定を行います。
セルを範囲選択等で複数選択した場合、空のセルが含まれていても問題ありません。
空のセルはハイパーリンク設定対象外として処理します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
Sub SetHyperLink() On Error Resume Next Dim rCell As Range '// セル Dim sCellText '// セルの文字列 Dim iContinueVoidCount '// 連続空白セルカウント Dim IncludeNumber Dim IncludeFont Dim IncludeAlignment Dim IncludeBorder Dim IncludePatterns Dim IncludeProtection '// 現時点のハイパーリンクの設定を保持 With ActiveWorkbook.Styles("Hyperlink") '// 変更前を保持 IncludeNumber = .IncludeNumber IncludeFont = .IncludeFont IncludeAlignment = .IncludeAlignment IncludeBorder = .IncludeBorder IncludePatterns = .IncludePatterns IncludeProtection = .IncludeProtection '// 強制的に設定変更 .IncludeNumber = False .IncludeFont = False '// デフォルトでTrueのためフォントが変わるのでFalseに変更 .IncludeAlignment = False .IncludeBorder = False .IncludePatterns = False .IncludeProtection = False End With '// 選択セルを全てループ For Each rCell In Selection sCellText = rCell.Text '// 空セルが1000回連続の場合は処理を止める If (iContinueVoidCount > 1000) Then Exit For End If '// セルが未設定の場合 If (sCellText = "") Then '// 連続空白セルカウントを1つ増やす iContinueVoidCount = iContinueVoidCount + 1 GoTo CONTINUE '// セルに値が設定されている場合 Else '// 連続空白セルカウントを初期化 iContinueVoidCount = 0 End If ActiveSheet.Hyperlinks.Add Anchor:=Cells(rCell.Row, rCell.Column), Address:=sCellText, TextToDisplay:=sCellText '// フォント設定 With rCell.Font .Underline = xlUnderlineStyleSingle '// 下線 .ColorIndex = 5 '// 青文字 End With CONTINUE: Next '// ハイパーリンクの設定を元に戻す With ActiveWorkbook.Styles("Hyperlink") '// 設定を変更前に戻す .IncludeNumber = IncludeNumber .IncludeFont = IncludeFont .IncludeAlignment = IncludeAlignment .IncludeBorder = IncludeBorder .IncludePatterns = IncludePatterns .IncludeProtection = IncludeProtection End With If (iContinueVoidCount > 1000) Then Call MsgBox("空白行が1000行超えたので止めました", vbOKOnly) End If End Sub |
ソースコードの説明
2行目のエラー処理は新規ブックで未保存の場合にこのマクロを実行するとエラーになるために入れています。
7行目から12行目の各変数はハイパーリンクのスタイル設定の変数格納用です。
15行目から32行目はハイパーリンクのスタイル設定の現在設定の取得と設定なし（False）への書き換えを行っています。これを行うことでフォントの色設定と下線設定を行えるようになります。
35行目で選択セルを全てループします。
36行目で現時点ループのセルを取得します。
39行目から41行目は、空のセルが連続で1000回続いた場合に処理を抜けるようにしています。行選択してマクロを実行した場合とかで無駄な処理をしないようにするための措置です。
44行目から52行目はセルが空かどうかで分岐しています。空の場合は連続回数用のカウントを増やし、設定されていればカウントを0に戻しています。
54行目がハイパーリンク処理です。単一のセルだけのハイパーリンクでよいのであればHyperlinks.Addメソッドに必要な引数を渡すことでハイパーリンクの設定ができます。
57行目から60行目がフォントを青文字にして下線を引くようにしています。
67行目から75行目でハイパーリンクの設定の戻しを行っています。
77行目から79行目では空のセルが続いた場合のメッセージ出力を行っています。ここは削除してもいいです。
使い方
ハイパーリンクを設定したセルを選択してマクロを実行します。複数セルの選択や空白セルが含まれていても問題ありません。選択範囲が飛んでいても構いません。
1. 複数のセルにファイルパスやURLが入力されている状況とします。ちょっと雑ですけど、そこはご勘弁を。
2. ハイパーリンクを設定したいセルを選択します。
空白セルや選択範囲が飛んでいても構いません。
3. 上記ソースコードのマクロを実行します。
ハイパーリンクが設定されます。
以上です。
ソースコードが長いですけど便利なマクロですのでよかったら活用してください。