VBAサンプル集
ユーザー定義関数でハイパーリンクのURLを取得(Hyperlink)

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日:2020-08-26

ユーザー定義関数でハイパーリンクのURLを取得(Hyperlink)


ネットから、何らかの一覧をエクセルにコピペすると、


文字列や画像等に、リンクの設定がくっついてきます。

URLが表記されていれば良いですが、表示されていない事の方が多いでしょう。

そこで、VBAでユーザー定義関数を作成し、URLを取得できるようにします。

Excel VBA 解説

A1は、文字にURLのリンクが設定されています。

A2は、画像にURLのリンクが設定されています。

A3は、文字と画像にURLのリンクが設定されています。

A4は、文字にメールアドレスのリンクが設定されています。

B列は、以下のユーザー定義関数を設定しました。

=GetHyperlink(A1)

A2以下にも同様の関数を設定しました。


以下が、ユーザー定義関数になります。



Function GetHyperlink(セル As Range) As String
    Dim sp As Shape
    If セル.Hyperlinks.Count > 0 Then
        GetHyperlink = セル.Hyperlinks(1).Address
    End If
    For Each sp In ActiveSheet.Shapes
        If セル.Address = sp.TopLeftCell.Address Then
            GetHyperlink = GetHyperlink & vbLf & sp.Hyperlink.Address
        End If
    Next
End Function


標準モジュールに作成して下さい。


説明

Dim sp As Shape
図形オブシェクトの変数定義です。

セル.Hyperlinks.Count
セルに含まれるハイパーリンクがあるかの判定です。

For Each sp In ActiveSheet.Shapes
現在シートの図形コレクションから1つづつ取り出します。

sp.TopLeftCell
図形の左上のセルを取得します。

つまり、図形の左上が、当該セルに含まれているかを判定しています。

全体が入っているかの判定をしてしまうと、漏れが発生します。

逆に、一部が含まれているかの判定では、重複が発生します。

左上での判定が、最も見た目の間隔に近いと思います。


セル.Hyperlinks(1).Address
sp.Hyperlink.Address
URLを文字列で取得します。

複数のハイパーリンクが1つのセルに含まれる場合は、(複数図形等)

改行コードで結合しています。

セルの書式を、「折り返して全体を表示」に設定して下さい。


注意.
セルは、Hyperlinks(1)
図形は、Hyperlink
です。

この違いを理解するには、CellsがRangeオブジェクトである事を理解する必要があります。

Rangeオブジェクトは、セル範囲ですので、Hyperlinkのコレクションになります。


コレクションですから、インデックスを指定します。

この場合は、1つのセル範囲なので、(1)しか存在しません。

図形は、指定された図形1つだけですので、Hyperlinkになります。

上のRangeオブシェクトの話が理解できない場合は、

RangeとCellsの深遠 を参考にして下さい。
RangeとCells特集にします。今さら…と、あなどるなかれ、結構奥が深いのです。すでに説明した内容もありますが、知っておいた方が良い事、知らなくても困らない事(笑) これらを、まとめてみました。まずは基本 A1セルに"エクセル"と入れる場合。

ただ、結構難しい解説となっています。

できれば、ExcelマクロVBA入門 を順序良く読んで下さい。
VBAはExcelの操作を自動化するマクロ機能で使われているプログラミング言語です。「VisualBasicApplications」の略になります。マクロVBA入門シリーズでは、始めはより詳しく丁寧に解説し、少しずつ難易度を上げることで無理なく学習を進められるようにしています。


少しVBAが解る方は、

For~Next等で処理し、値のみをセルに入れた方が、使い勝手は良いと思います。

例えば以下のように。

Sub GetHyperlink2()
  Dim i As Long, iMax As Long
  Dim strUrl As String
  iMax = Cells(Rows.Count, 1).End(xlUp).Row
  For i = 1 To iMax
    strUrl = ""
    If Cells(i, 1).Hyperlinks.Count > 0 Then
      strUrl = Cells(i, 1).Hyperlinks(1).Address
    End If
    For Each sp In ActiveSheet.Shapes
      If Cells(i, 1).Address = sp.TopLeftCell.Address Then
        strUrl = strUrl & vbLf & sp.Hyperlink.Address
      End If
    Next
    If Not IsEmpty(strUrl) Then
      Cells(i, 2) = strUrl
    End If
  Next
End Sub


少し面倒ですね。


セル内に、左上が入っている図形を簡単に取得できれば良いのですが、

現在、私には解りません。(多分無理かなと思っています。)

従って、シート内の図形を全て検索し、当該セルに含まれるかの判定をしています。

注意.
前回は、Do~Loopを使用しました。
今回は、For~Nextを使用しています。
特段の理由はありません。
同じ事をするにも、いろいろなVBAコードがある事の紹介にすぎません。

ネットからコピーして、資料等を作成する場合には、使う機会があると思います。

ユーザー定義関数の実践使用例

ユーザー定義関数の作り方
マクロVBAを作成して、ボタンで一括処理…それほどではないが、関数だけではちょっと大変、そんな時は、ユーザー定義関数を使ってみましょう。作り方は簡単です。簡単な例でユーザー定義関数を作ってみましょう 上記は、引数を足し算して返すユーザー定義関数です。

ユーザー定義関数でフリガナを取得する(GetPhonetic)
ワークシート関数の、「PHONETIC」では、他のソフト等からコピペした漢字は取得できません。そこで、VBAでユーザー定義関数を作成し、読みを取得できるようにします。A列はメモ帳よりコピペしました。B列に、ユーザー定義関数を指定して、振り仮名を取得しています。

スピルに対応したXSPLITユーザー定義関数(文字区切り)
エクセルにスピルが登場して、VBAのユーザー定義関数の用途も広がってきていると思います。そこで、文字列を指定した記号・文字列で区切て、セルに出力するユーザー定義関数を作ってみましょう。VBAにはSplit関数がありますが、これをワークシート関数として使えるようにしてみましょうという事です。

スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)
2019年Office365に追加された革新的な機能としてスピルがあります。スピルとともに強力な新関数が登場しました。その中でも特に使用頻度が高いと思われるXLOOKUP関数の練習をかねて、問題を作成しました。



同じテーマ「マクロVBAサンプル集」の記事

ユーザー定義関数でフリガナを取得する(GetPhonetic)
ユーザー定義関数でハイパーリンクのURLを取得(Hyperlink)
カラーのコード取得(256RGB⇔16進変換)
時刻になったら音を鳴らして知らせる(OnTime)
指定文字、指定数式でジャンプ機能(Union)
「値の貼り付け」をショートカットに登録(OnKey)
「セルの結合」をショートカットに登録(OnKey)
半角カナのみ全角カナに変換する
計算式の元となる数値定数を消去する(Precedents)
Beep音で音楽(Beep,Sleep)
日付の検索(配列の使用)


新着記事NEW ・・・新着記事一覧を見る

VBA100本ノック 18本目:名前定義の削除|VBA練習問題100(11月6日)
VBA100本ノック 17本目:重複削除(ユニーク化)|VBA練習問題100(11月6日)
VBA100本ノック 16本目:無駄な改行を削除|VBA練習問題100(11月5日)
VBA100本ノック 15本目:シートの並べ替え|VBA練習問題100(11月4日)
VBA100本ノック 14本目:社外秘シート削除|VBA練習問題100(11月3日)
VBA100本ノック 13本目:文字列の部分フォント|VBA練習問題100(11月1日)
VBA100本ノック 12本目:セル結合を解除|VBA練習問題100(10月31日)
VBA100本ノック 11本目:セル結合の警告|VBA練習問題100(10月30日)
VBA100本ノック 10本目:行の削除|VBA練習問題100(10月29日)
VBA100本ノック 9本目:フィルターコピー|VBA練習問題100(10月28日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ