Excel(エクセル)で、URLをリンクテキストに変換する関数(HYPERLINK関数)は知っていたのですが、リンクテキストからURLを抽出する関数が調べてもみつかりませんでした。
関数がないならVBAで作ってみれば?と助言されたので、試しに作ってみました。備忘録として記録します。
Webページからリンクテキストの一覧をコピーして、そのままエクセルに張り付けると、リンクテキストとして保存されます。ここまでは良いのですが、それぞれのURLを一覧で取得したいのです。
開発タブから「Visual Basic」をクリックして「コードウィンドウ」を開きます。
「GetHyperlink」という関数名で作りたいので、下記のコードを入力して保存します。
Dim target As Range
Function GetHyperlink(target) As String
If target.Hyperlinks.Count > 0 Then
GetHyperlink = target.Hyperlinks(1).Address
End If
End Function
URLを表示させたい場所(セルB2)に「 =GetHyperlink(A2) 」を入力。
リンクテキストからURLを抽出できました。
フィルハンドルを「ドラッグ」もしくは「ダブルクリック」すれば、URL一覧が完成です。
完了です。
VBAのプログラムもネットで検索すれば、サンプルコードがたくさん見つかるので、独自の関数もマクロの作成も何とかできたりするものです。
ネットで調べて、意味を深く理解せずにコピペでプログラムを動かすこともできますが、やはり色々応用するためにはそれぞれのコードの意味や役割を理解する必要があります。
まず、変数「target」を定義します。
VBAでの変数の作成ルールは「Dim 変数名 As オブジェクト名(or データ型)」です。
変数名は任意の名前でOKです。今回は「target」と命名します。
変数「target」に対してオブジェクトを指定する必要があるのですが、特定の「セル」からURLを取得する関数をつくるため、関数の引数は「セル」になります。セルは「Rangeオブジェクト」なので「As Range」とします。「As」は「~として」という意味です。
「Dim target As Range」を日本語に翻訳すると「Rangeオブジェクトとして変数targetを定義」となります。
※オブジェクト、プロパティ、コレクションなどの用語は本ページの最下部にて解説しています。
関数を作成する場合は、「Function 関数名(引数) As データ型」と「End Function」の間に関数の内容を記述します。
関数名は任意の名前でOKです。今回は「GetHyperlink」と命名します。
引数は「セル」にしたいので、先ほどRangeオブジェクトとして作成した変数「target」を入力。
関数を利用した後の戻り値は文字列にしたいので、文字列のデータ型「String」を設定。
「Function GetHyperlink(target) As String ~ End Function」を日本語に翻訳すると「セルを引数としたGetHyperlink関数を定義。関数の戻り値のデータは文字列とする」となります。
今回のやりたいことは、
もし指定したセル(target)にハイパーリンクが存在していたら、関数(GetHyperlink)を入力したセルにURLを文字列として表示することです。
If target.Hyperlinks.Count > 0 Then
~
End If
If文(条件式)は、「If 条件式 Then」から始めて、「End If」で終わりを示します。これはVBAでのルールです。
target.Hyperlinks.Count > 0
「Hyperlinks」:ハイパーリンクのコレクション
「Count」:「Hyperlinks」のプロパティ。オブジェクトの数を返します。
上記のコードを日本語に翻訳すると「指定したセルにリンクが存在していたら」となります。
GetHyperlink = target.Hyperlinks(1).Address
「Hyperlinks(1)」:Hyperlinksはコレクション(オブジェクト群)なので単体のオブジェクトにするため「(1)」を指定。
「Address」:Hyperlinkオブジェクトのプロパティ。ハイパーリンクのアドレス(URL)を文字列で返します。※Rangeオブジェクトの場合は、参照範囲(セル範囲)を返すプロパティとなります。
上記のコードを日本語に翻訳すると「指定したセル内のアドレス(URL)を取得して、GetHyperlinkに返す」となります。
解説は以上となります。
今回紹介した変数「target」はFunctionプロシージャの外に設定してあります。そのため、他のプロシージャでも利用できる変数となっています。
もし、 変数のスコープ(適用範囲)をFunctionプロシージャの中だけに限定したい場合は、下記のような書き方となります。
Function GetHyperlink(target As Range) As String
If target.Hyperlinks.Count > 0 Then
GetHyperlink = target.Hyperlinks(1).Address
End If
End Function
関数の引数の中で、Rangeオブジェクトの変数「target」を定義する形になります。この方がVBAのコード量が多くなった時に、予期せぬ影響を受けにくいので、上記のように変数のスコープは限定した方が良い気がします。
プログラムに限らず何かの技術を習得するためには、用語を正確に理解する必要があると思っています。それを怠るとドキュメントも理解できないし、わからないことがあっても質問ができないからです。
用語 | 説明 |
---|---|
VBA | Visual Basic for Applications Microsoft Office 向けの自動処理などを行うためのプログラム言語。 |
マクロ | 複数の処理を自動化する機能のこと。VBAというプログラム言語を使ってマクロ機能を作成します。 |
変数 | プログラムにおけるデータを入れておく箱のこと。定数の反対。 |
オブジェクト | プログラムで操作したい対象のこと。セルやワークシートなど。 |
プロパティ | オブジェクトの属性や性質のこと。セルがオブジェクトで、そのセルに入る値や大きさ、色などがプロパティに該当。 |
メソッド | オブジェクトの動作、処理内容。 |
コレクション | 同じ種類のオブジェクトの集合体 |
メンバー | コレクションに所属している各オブジェクト |
データ型 | データの種類のこと。整数、日付、文字列、オブジェクトなど |
リテラル | データの値のこと。 |
プロシージャ | プログラムの一連の命令文を一つにまとめたもの。本記事で紹介した戻り値のある関数は「Functionプロシージャ」。戻り値がなくボタンなどで参照できる命令文は「Subプロシージャ」。 |
他人の助言でVBAをはじめて触ってみて、簡単な関数を見よう見まねで作成しました。そのついでに、各コードの意味を調べてブログに書きましたが、VBAは簡単な言語と聞かされていたもののプログラム言語には変わりはなく、やはり難しかったです。
NorthDetailは約8割はエンジニアなのですが、改めてエンジニアってすごいなと感じました。せっかくVBAを触ったので、もう少し奥まで進んでみようかと思いました。