サイト内検索

セル操作 基本

VBAで電話番号のハイフン削除とメールアドレスを分割取得する

集計業務で電話番号から"-"(ハイフン)を削除したり、メールアドレスからアカウント名やドメイン名を抜き出すデータの整形業務があります。本記事では、これらの処理をVBAで行うサンプルコードを詳しくご紹介します。

また記事の後半では、以下の処理を1つのユーザ定義関数にまとめた場合のサンプルコードについてもご紹介しております。

  • 電話番号から"-"(ハイフン)を削除する
  • メールアドレスからアカウント名を取得する
  • メールアドレスからドメイン名を取得する

まずは本記事で解説する内容を、動画でサッと確認するための動画(約2分)を用意してありますので、宜しければご覧ください。

目次

サンプルファイル

本記事で使用するサンプルファイルは、3枚のシートから構成されています。3枚のシート名は、それぞれ「電話番号」、「メールアドレス」、「リスト」です。

サンプルファイルの構成
サンプルファイルの構成

「電話番号」シートには、ハイフン付きのダミーの電話番号が10件あります。「メールアドレス」には、メールアドレスのダミーデータが10件あります。

最後の「リスト」シートには、約10000件の電話番号とメールアドレスが記載されており、その右側には出力列があり、それら項目名として「電話番号(ハイフン無し)」、「メールアドレス(アカウント名)」、「メールアドレス(ドメイン名)」が記載されています。

VBAコード内での3つのシートの取り扱い方について

本記事では、前述した3つのワークシートを扱いますが、これらのシートは本記事でご紹介するVBAサンプルコード内では、すべてのワークシートをオブジェクト名で指定しています。

ダウンロードしたファイルのVBE画面上でもご確認頂けますが、下図の通りワークシート名とオブジェクト名が対応しておりますのでご確認ください。

各ワークシート名に対するオブジェクト名
各ワークシート名に対するオブジェクト名
  • 「電話番号」シート・・・ Sheet1
  • 「メールアドレス」シート・・・Sheet2
  • 「リスト」シート・・・Sheet3

ダウンロード用サンプルファイル

電話番号のハイフンを削除する

下図はサンプルファイル「電話番号」シート上のハイフン付電話番号のハイフンを削除する、VBAサンプルコードの実行前後の様子です。

電話番号ハイフン削除VBA実行前後比較
電話番号ハイフン削除VBA実行前後比較

電話番号のハイフンを削除するVBAサンプルコード

電話番号からハイフンを削除するVBAサンプルコードになります。

 

電話番号のハイフンを削除するVBAのアルゴリズム

[main_1]
5-7行目: For r = 1 To .Range("A" & .Rows.Count).End(xlUp).Row ~ Next

「電話番号」シート(Sheet1)にあるすべての電話番号に対して、繰り返し処理をします。ここでは、Toのあとに集計対象レコードの最終行番号を求めるVBAコードを直接記載しています。

[main_1]
6行目: .Cells(r, 1) = DelHyphen(.Cells(r, 1))

セル.Cells(r, 1)は、電話番号になります。まずこの後にお話しするユーザ定義関数DelHyphenに渡す引数としてハイフン付電話番号(.Cells(r,1))を渡しています。そして関数DelHyphenの戻り値であるハイフンを削除した電話番号を、.Cells(r, 1)に再代入しています。

[DelHyphen]
13行目: Public Function DelHyphen(ByVal vString As String) As String

ユーザ定義関数DelHyphenのプロシージャ宣言文です。vStringは、呼び出し元から渡される引数になります。また戻り値は、ハイフンを削除した電話番号ですので文字列型を指定しています。

[DelHyphen]
18~22行目: For i = 1 To Len(vString) ~ Next

引数で受け取った文字列を、1文字ずつ文字列の長さ分繰り返し処理します。この間の処理は、Mid関数とLen関数を使って、対象の文字列から1文字ずつ取り出した文字が、ハイフンと等しいかどうか (VBAコードでは、半角と全角の両方をチェックしています) で処理を分けています。

具体的には、ハイフンを除く文字の場合のみ、戻り値となる変数rStringへの文字列結合処理を行います(VBAコード20行目)。これにより結果的に、電話番号からハイフンを削除したことになります。

メールアドレスからアカウント名とドメイン名を取得する

メールアドレスからアカウント名(@より前の文字列)、およびドメイン名(@から後ろの文字列)を取得するVBAサンプルコードをご紹介します。

メールアドレスからアカウント名とドメイン名を取得するVBAサンプルコード

次のVBAサンプルコードは、main_2がアカウント名を取得し、main_3がドメイン名を取得するプロシージャになります。

これら2つのプロシージャは、ともに ユーザ定義関数 GetMAddInfo を呼んでいますが、第二引数の指定が違います。つまりこの第二引数の指定の違いにより、それぞれアカウント名とドメイン名という異なる情報を取得できるようにしています。

 

VBAサンプルコード実行結果

では紹介したVBAサンプルコードの2つのプロシージャを実行した結果をそれぞれご紹介したいと思いますが、実行する前に1点だけ注意があります。

VBAサンプルコードは、アカウント名取得用(main_2プロシージャ)とドメイン名取得用(main_3プロシージャ)のプロシージャが、それぞれ同じ対象レコード(メールアドレスシートのA列)に対して処理をします。

よって、main_2を実行した後のアカウント名が出力されたままで、main_3を実行しても既にドメイン名以下が削除されていますので、実行結果に変化が起こりません(アカウント名取得状態のまま)。

そこで、main_2の実行前に対象レコードを隣の列などにコピペして、元のメールアドレスデータを転記しておいてください。そうすれば、main_3実行時にその隣にコピペした元のメールアドレスデータを再利用できます。

main_2プロシージャ(アカウント名取得)実行結果

メールアドレスの@から前の部分をアカウント名、またはユーザ名と呼んだりします。実際の集計業務で経験したことですが、社員のユーザIDとして社内メールアドレスのアカウント名を利用していたところもありました。

そこでは、実際に社員のメールアドレスから集計のキーとなるユーザID(アカウント名)を抜き出した上で、集計資料を作成したこともあります。まさに本記事でご紹介しているアカウント名を抜き出すサンプルコードを利用していたのです。

メールアドレスからアカウント名取得するVBA実行前後比較
メールアドレスからアカウント名を取得するVBA実行前後比較

上図はサンプルコードのmain_2プロシージャを実行した結果になります。@より前の情報が取得されていますよね。

main_3プロシージャ(ドメイン名取得)実行結果

ドメイン名は、@より後ろの文字列を指します。main_3の実行結果は、以下のように@マークより後ろの文字列が抜き出されています。

メールアドレスからドメイン名を取得するVBA実行前後比較
メールアドレスからドメイン名を取得するVBA実行前後比較

メールアドレスからアカウント名/ドメイン名を取得するVBAのアルゴリズム

アカウント名を取得するmain_2プロシージャとドメイン名を取得するmain_3プロシージャ、および共通の ユーザ定義関数 GetMAddInfoについて解説します。

なお前項でご紹介したVBAサンプルコードですが、記載の順番はmain_2、main_3、GetMAddInfoの順番ですが、ここでの解説はGetMAddInfoをはじめに解説し、その後main_2、main_3の順に解説します。

メールアドレスからアカウント名/ドメイン名を取得するGetMAddInfoユーザ定義関数のアルゴリズム

[GetMAddInfo]
25行目: Public Function GetMAddInfo(ByVal vString As String, ByVal vFlag As Integer) As String

ユーザ定義関数の宣言文になります。第一引数はメールアドレス(文字列)です。第二引数には、アカウント名を取得するのか、ドメイン名を取得するかを判断するためのフラグ(整数値)を設けています。

[GetMAddInfo]
27行目: Dim aPos As Integer 
30行目: aPos = InStr(vString, "@")

メールアドレスからアカウント名とドメイン名を分けて取得するには、メールアドレスの中に含まれる@の位置情報を知る必要があります。

そこで、上記のようにメールアドレスの中から@の前方の位置情報をInstr関数で取得し、変数aPosに代入しています。

この位置情報を得る方法については、電話番号からハイフンを取得した方法と同様に、Mid関数とLen関数を用いても位置情報を取得できますが、メールアドレスの中の@は1つしかないという特性がありますので、今回はInstr関数を使いました。

つづくVBAコード33~37行目は、第二引数の値によりアカウント名、もしくはドメイン名を取得するIf文での分岐処理になります(下図)。

アカウント名およびドメイン名取得のアルゴリズム
アカウント名およびドメイン名取得のアルゴリズム
アカウント名取得

第二引数vFlagが0の場合(VBAコード赤字部)は、VBAコード34行目が実行されます。アカウント名は、@の前の文字列(赤字)ですので、変数aPos(=4) - 1文字分を、メールアドレスの左側から取得すればよいです。つまりここでは図に示す通りLeft関数を使う事で取得できます。

ドメイン名取得

第二引数vFlagが1の場合 (VBAコード青字部) は、VBAコード36行目が実行されます。ドメイン名は、@の後ろの文字列(青字)ですので、変数aPos(=4) + 1文字目から、(メールアドレス全体の文字数 - @までの文字数)を、Mid関数の第三引数に指定することで取得できます。

main_2プロシージャ(アカウント名取得)のアルゴリズム

main_2は、メールアドレスからアカウント名を取得するプロシージャです。プロシージャ内部では、前述した ユーザ定義関数 GetMAddInfoを呼び出す際に、引数としてメールアドレスと0(アカウント名取得時)を渡しています。

[main_2]
7行目: .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 0) 

メールアドレスからアカウント名を取得するための ユーザ定義関数 GetMAddInfoを呼び出している行です。引き数には、.Cells(r, 1)のメールアドレスの他に、フラグとしてアカウント名取得処理をするために0を渡しています。

main_3プロシージャ(ドメイン名取得)のアルゴリズム

main_3は、メールアドレスからドメイン名を取得するプロシージャです。main_2同様、プロシージャ内部で ユーザ定義関数 GetMAddInfoを呼びだしています。そして引数としてメールアドレスと1(ドメイン名取得時)を渡しています。

[main_3]
18行目: .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 1)

メールアドレスからドメイン名を取得するための ユーザ定義関数 GetMAddInfoを呼び出している行です。引き数には、.Cells(r, 1)のメールアドレスの他に、フラグとしてドメイン名を取得するために1を渡しています。

すべての処理を1つのユーザ定義関数にまとめる

本記事でこれまで解説してきた処理内容は、「電話番号のハイフンを削除する」、「メルアドのアカウント名/ドメイン名を抜き出す」というデータ整形処理を、別々のユーザ定義関数で行いました。

実はこれら2つの関数は、処理対象の文字列を渡して、処理後の文字列を受取るという共通点に着目すると、1つのユーザ定義関数にまとめることができそうです。

そこで、本項では機能が似ている関数を1つにまとた上で、今度は電話番号のハイフン削除と、メールアドレスからアカウント名とドメイン名の取得を一度に処理するサンプルコードをご紹介したいと思います。

ユーザ定義関数は、いくつでも作ることが出来ますが、あまりにも似たような機能の関数をたくさん作ってしまうと管理が面倒になり可読性も悪くなります。

可能な限り似たような機能・目的を持つユーザ定義関数は、今回ご紹介するサンプルコードのようにまとめることをお勧めします。

なお、本項ではSheet3にある約10000件のデータを処理します。そこで本サンプルコードでは、処理の高速化のために二次元配列を採用しています。

すべての処理を1つのユーザ定義関数にまとめたVBAサンプルコード

下にご紹介するVBAサンプルコードは、1つのmainプロシージャと、1つの ユーザ定義関数 GetTelEMailInfo からなります。関数の内容は、前述の通り電話番号からハイフンを削除するのと、メールアドレスからアカウント名とドメイン名を抜き出す処理です。

 

VBAサンプルコードの実行結果

サンプルコード実行前後のSheet3を図示してみました。図のようにmainプロシージャを実行することにより、「ハイフン無し電話番号」、「アカウント名」、「ドメイン名」の3つの情報が一度に出力されます。

VBAサンプルコード実行結果比較
VBAサンプルコード実行結果比較

すべての処理を1つのユーザ定義関数にまとめたVBAサンプルコード のアルゴリズム

これよりVBAサンプルコードの解説になりますが、これまで本サイトで詳細にお伝えしきた内容については、簡単に解説しております。より詳細な内容については、文中のリンクを参照してください。

ユーザ定義関数GetTelEMailInfo

ユーザ定義関数 (Functionプロシージャ)やSubプロシージャに渡す引数には、引数の指定を省略できない引数と、引数の指定を省略できる引数があります。

引数の指定を省略できない引数は、これまで解説してきた引数のことで、呼び出し側のVBAコードでは必ず指定していました。よって、この省略できない引数を、指定し忘れると当然コンパイルエラーになります。

一方の省略できる引数は、本記事で詳しくお伝えするもう1つの引数の使い方です。この引数は呼び出し側で引数の省略が可能です。省略された場合は、ユーザ定義関数側で指定された初期値が採用されます。

では詳細を見ていきましょう。

ユーザ定義関数宣言部

VBAコード35行目記載の関数宣言文について、以下の図を用意しました。

図は本サンプルコードのユーザ定義関数GetTelMailInfoの第二引数を、省略できる引数にする場合の記載方法を示しています。

オプション付引数の記載方法
オプション付引数の記載方法

省略可能な引数の記載方法は、通常の引数の書き方に対して、前側に「Optional」を書き、後ろ側には省略された際の値を「=」に続き記述します。図の場合、呼び出し側で第二引数が省略された場合は、第二引数の値が「2」になることを意味しています。

なお、第二引数の後ろ側(本サンプルでは「= 2」)の初期値を省略すると、データ型に応じた初期値が代入されます。例えば、今回の整数型(Integer)では0、文字列型は""(ブランク)が初期値になります。

本サンプルコードでは、第二引数の値により処理の内容を変えています。下図に第二引数の値と処理内容を表にまとめましたのでご覧ください。

第二引数の値と関数の処理内容
第二引数の値と関数の処理内容
@の前方からの位置を取得

下の図は、第二引数がメールアドレスに関する処理の場合、メールアドレスの中にある@の前方からの位置情報を取得するためのVBAコードになります。

メールアドレスの@位置を取得するVBAコード
メールアドレスの@位置を取得するVBAコード

VBAコード41行目で、第二引数が0(アカウント名取得)か、1(ドメイン名取得)が指定された際に、変数aPosに@マークの前方からの位置を格納します。

第二引数の値により処理を分けるアルゴリズム

VBAコード46~62行目は、第二引数の値とIf文を使う事で実際の処理を分けています。

実際の処理をしているVBAコード
実際の処理をしているVBAコード

受取った第二引数の値に応じて処理が3つに分岐されています。処理内容については、これまで解説してきた内容と同じですので割愛しますが、図中のVBAコード56行目のIf文の途中にある"_"(アンダーバー)について補足します。

VBAコード途中の"_"(アンダーバー)は、VBE上で一行が長くなりすぎたVBAコードを見やすくするために使う改行文字です。見た目だけの効果ですので、VBAの実行結果には全く影響しません。

mainプロシージャ

次に、VBAコード2~30行目のmainプロシージャについての解説です。

出力範囲をクリア

VBAコード11~13行目では、出力先の3列目(C列)から5列目(E列)を消去しています。

集計対象のセル範囲を配列に格納

VBAコード16~20行目では、 CurrentRegionResize を使いながら、すべてのデータ範囲を二次元配列に格納しています。これにより、集計を 二次元配列 で行う準備が出来たことになります。

すべてのレコードを繰り返し処理して、3つの情報を取得する

VBAコード23~27行目については、下図に抜き出してみました。先ほど解説しました共通化されたユーザ定義関数GetTelMailInfoを使い、3つの処理(ハイフン無し電話番号、アカウント名、ドメイン名のそれぞれを取得)をしています。

共通化されたユーザ定義関数による処理例
共通化されたユーザ定義関数による処理例

3つの異なる処理を同じ関数で実行できるのは、関数に渡す第二引数を変えているからです。

このように、機能・目的などが似たようなユーザ定義関数は、今回の例のようにまとめると管理しやすくなり、可読性もよくなりますのでお勧めします。

まとめ

本記事では、電話番号からハイフンを削除する処理と、メールアドレスからアカウント名、およびドメイン名を取得するVBAサンプルコードについて詳しく解説してきました。

さらに、記事の後半ではこれら3つの処理を、1つのユーザ定義関数にまとめる方法について、オプション付引数の使い方も含めて解説してきました。

集計処理内容を、適宜サブルーチン化することでVBAコードの保守性・可読性を向上させることができますが、似たような機能・目的のサブルーチンが複数あれば、引数を適宜オプション化して1つのサブルーチンにまとめることにより、さらに保守性・可読性の向上効果が期待できます。

[スポンサーリンク]



サイト内検索

-セル操作, 基本
-

Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved.