集計業務で電話番号から"-"（ハイフン）を削除したり、メールアドレスからアカウント名やドメイン名を抜き出すデータの整形業務があります。本記事では、これらの処理をVBAで行うサンプルコードを詳しくご紹介します。
また記事の後半では、以下の処理を1つのユーザ定義関数にまとめた場合のサンプルコードについてもご紹介しております。
- 電話番号から"-"（ハイフン）を削除する
- メールアドレスからアカウント名を取得する
- メールアドレスからドメイン名を取得する
まずは本記事で解説する内容を、動画でサッと確認するための動画(約2分)を用意してありますので、宜しければご覧ください。
目次
サンプルファイル
本記事で使用するサンプルファイルは、3枚のシートから構成されています。3枚のシート名は、それぞれ「電話番号」、「メールアドレス」、「リスト」です。
「電話番号」シートには、ハイフン付きのダミーの電話番号が10件あります。「メールアドレス」には、メールアドレスのダミーデータが10件あります。
最後の「リスト」シートには、約10000件の電話番号とメールアドレスが記載されており、その右側には出力列があり、それら項目名として「電話番号（ハイフン無し）」、「メールアドレス（アカウント名）」、「メールアドレス（ドメイン名）」が記載されています。
VBAコード内での3つのシートの取り扱い方について
本記事では、前述した3つのワークシートを扱いますが、これらのシートは本記事でご紹介するVBAサンプルコード内では、すべてのワークシートをオブジェクト名で指定しています。
ダウンロードしたファイルのVBE画面上でもご確認頂けますが、下図の通りワークシート名とオブジェクト名が対応しておりますのでご確認ください。
- 「電話番号」シート・・・ Sheet1
- 「メールアドレス」シート・・・Sheet2
- 「リスト」シート・・・Sheet3
ダウンロード用サンプルファイル
電話番号のハイフンを削除する
下図はサンプルファイル「電話番号」シート上のハイフン付電話番号のハイフンを削除する、VBAサンプルコードの実行前後の様子です。
電話番号のハイフンを削除するVBAサンプルコード
電話番号からハイフンを削除するVBAサンプルコードになります。
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 | Sub main_1() Dim r As Integer With Sheet1 For r = 1 To .Range("A" & .Rows.Count).End(xlUp).Row .Cells(r, 1) = DelHyphen(.Cells(r, 1)) 'ユーザ定義関数に引数としてハイフン付電話番号を渡す Next End With End Sub '■電話番号のハイフンを取り除くユーザ定義関数 '[引数：電話番号（ハイフン付）、戻り値：電話番号（ハイフン無し）] Public Function DelHyphen(ByVal vString As String) As String Dim rString As String 'ハイフン無し電話番号を格納する変数 Dim i As Long rString = "" '初期化 For i = 1 To Len(vString) '文字列の長さ分を1文字ずつ繰り返す If Mid(vString, i, 1) <> "-" And Mid(vString, i, 1) <> "―" Then 'i番目の1文字がハイフン以外の場合 rString = rString & Mid(vString, i, 1) '処理対象の文字を連結していく End If Next DelHyphen = rString '最終的に格納された文字列を関数の戻り値とする End Function |
電話番号のハイフンを削除する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 を呼んでいますが、第二引数の指定が違います。つまりこの第二引数の指定の違いにより、それぞれアカウント名とドメイン名という異なる情報を取得できるようにしています。
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 | '■メールアドレスからアカウント名取得 Sub main_2() Dim r As Integer With Sheet2 For r = 1 To .Range("A" & .Rows.Count).End(xlUp).Row .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 0) '引数としてメールアドレスと0（アカウント名取得）を渡す Next End With End Sub '■メールアドレスからドメイン名取得 Sub main_3() Dim r As Integer With Sheet2 For r = 1 To .Range("A" & .Rows.Count).End(xlUp).Row .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 1) '引数としてメールアドレスと1（ドメイン名取得）を渡す Next End With End Sub '■メールアドレスからアカウント名もしくはドメイン名を取得するユーザ定義関数 '[引数：メールアドレス、抽出部指定フラグ（ユーザ名：0、ドメイン名：1)、戻り値：ユーザ名/ドメイン名] Public Function GetMAddInfo(ByVal vString As String, ByVal vFlag As Integer) As String Dim rString As String '関数の戻り値を格納する変数 Dim aPos As Integer '@の前方からの位置を格納する変数 Dim i As Long aPos = InStr(vString, "@") '@の前方位置を取得 rString = "" '戻り値を格納する変数の初期化 If vFlag = 0 Then '第二引数が0の場合（アカウント名取得時） rString = Left(vString, aPos - 1) 'アカウント名取得 ElseIf vFlag = 1 Then '第二引数が1の場合（ドメイン名取得時） rString = Mid(vString, aPos + 1, Len(vString) - aPos) 'ドメイン名取得 End If GetMAddInfo = rString '戻り値を返す End Function |
VBAサンプルコード実行結果
では紹介したVBAサンプルコードの2つのプロシージャを実行した結果をそれぞれご紹介したいと思いますが、実行する前に1点だけ注意があります。
VBAサンプルコードは、アカウント名取得用(main_2プロシージャ）とドメイン名取得用(main_3プロシージャ）のプロシージャが、それぞれ同じ対象レコード(メールアドレスシートのA列）に対して処理をします。
よって、main_2を実行した後のアカウント名が出力されたままで、main_3を実行しても既にドメイン名以下が削除されていますので、実行結果に変化が起こりません（アカウント名取得状態のまま）。
そこで、main_2の実行前に対象レコードを隣の列などにコピペして、元のメールアドレスデータを転記しておいてください。そうすれば、main_3実行時にその隣にコピペした元のメールアドレスデータを再利用できます。
main_2プロシージャ(アカウント名取得)実行結果
メールアドレスの@から前の部分をアカウント名、またはユーザ名と呼んだりします。実際の集計業務で経験したことですが、社員のユーザIDとして社内メールアドレスのアカウント名を利用していたところもありました。
そこでは、実際に社員のメールアドレスから集計のキーとなるユーザID（アカウント名）を抜き出した上で、集計資料を作成したこともあります。まさに本記事でご紹介しているアカウント名を抜き出すサンプルコードを利用していたのです。
上図はサンプルコードのmain_2プロシージャを実行した結果になります。@より前の情報が取得されていますよね。
main_3プロシージャ(ドメイン名取得)実行結果
ドメイン名は、＠より後ろの文字列を指します。main_3の実行結果は、以下のように@マークより後ろの文字列が抜き出されています。
メールアドレスからアカウント名/ドメイン名を取得する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 からなります。関数の内容は、前述の通り電話番号からハイフンを削除するのと、メールアドレスからアカウント名とドメイン名を抜き出す処理です。
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 | '■電話番号（ハイフン無し）とメールアドレスのアカウント名とドメイン名を取得する Sub main() Dim rngSelf As Range '集計対象レコード範囲 Dim varSelf As Variant '集計対象レコード格納用配列 Dim r As Long Application.ScreenUpdating = False '画面更新オフ '出力範囲をクリア With Sheet3 .Range(.Cells(2, 3), .Cells(.Range("A" & .Rows.Count).End(xlUp).Row, 5)).ClearContents End With '集計対象レコード範囲を配列に格納 With Sheet3.Range("A1").CurrentRegion Set rngSelf = .Resize(.Rows.Count - 1).Offset(1) End With varSelf = rngSelf.Value 'すべてのレコードを繰り返し処理 For r = 1 To UBound(varSelf) varSelf(r, 3) = GetTelEMailInfo(varSelf(r, 1)) '電話番号のハイフンを削除 varSelf(r, 4) = GetTelEMailInfo(varSelf(r, 2), 0) 'メルアドからアカウント名を抜き出す varSelf(r, 5) = GetTelEMailInfo(varSelf(r, 2), 1) 'メルアドからドメイン名を抜き出す Next rngSelf = varSelf '結果をセルに反映 End Sub '■電話番号のハイフンを削除、メールアドレスのアカウント名/ドメイン名を取得するユーザ定義関数 '引数：文字列（ハイフン付電話番号やメールアドレス） ' 整数値（オプションなので省略時は2でハイフン無電話番号、0はアカウント名、1はドメイン名） Private Function GetTelEMailInfo(ByVal vString As String, Optional ByVal vFlag As Integer = 2) As String Dim rString As String '戻り値格納用変数 Dim aPos As Integer '@の前方からの位置を格納する変数 Dim i As Integer 'メールアドレスに関する処理の場合実行 If vFlag = 0 Or vFlag = 1 Then aPos = InStr(vString, "@") '@の前方からの位置を取得 End If rString = "" If vFlag = 0 Then '第二引数が0の場合 rString = Left(vString, aPos - 1) 'メールアドレスのアカウント名取得 ElseIf vFlag = 1 Then '第二引数が1の場合 rString = Mid(vString, aPos + 1, Len(vString) - aPos) 'メールアドレスのドメイン名取得 ElseIf vFlag = 2 Then '第二引数が2の場合（第二引数省略時） '電話番号からハイフンを削除 For i = 1 To Len(vString) '文字列の長さ分を1文字ずつ繰り返す If Mid(vString, i, 1) <> "-" _ And Mid(vString, i, 1) <> "―" Then 'i番目の1文字がハイフン以外の場合 rString = rString & Mid(vString, i, 1) '処理対象の文字を連結していく End If Next End If GetTelEMailInfo = rString '戻り値を返す End Function |
VBAサンプルコードの実行結果
サンプルコード実行前後のSheet3を図示してみました。図のようにmainプロシージャを実行することにより、「ハイフン無し電話番号」、「アカウント名」、「ドメイン名」の3つの情報が一度に出力されます。
すべての処理を1つのユーザ定義関数にまとめたVBAサンプルコード のアルゴリズム
これよりVBAサンプルコードの解説になりますが、これまで本サイトで詳細にお伝えしきた内容については、簡単に解説しております。より詳細な内容については、文中のリンクを参照してください。
ユーザ定義関数GetTelEMailInfo
ユーザ定義関数 （Functionプロシージャ）やSubプロシージャに渡す引数には、引数の指定を省略できない引数と、引数の指定を省略できる引数があります。
引数の指定を省略できない引数は、これまで解説してきた引数のことで、呼び出し側のVBAコードでは必ず指定していました。よって、この省略できない引数を、指定し忘れると当然コンパイルエラーになります。
一方の省略できる引数は、本記事で詳しくお伝えするもう1つの引数の使い方です。この引数は呼び出し側で引数の省略が可能です。省略された場合は、ユーザ定義関数側で指定された初期値が採用されます。
では詳細を見ていきましょう。
ユーザ定義関数宣言部
VBAコード35行目記載の関数宣言文について、以下の図を用意しました。
図は本サンプルコードのユーザ定義関数GetTelMailInfoの第二引数を、省略できる引数にする場合の記載方法を示しています。
省略可能な引数の記載方法は、通常の引数の書き方に対して、前側に「Optional」を書き、後ろ側には省略された際の値を「=」に続き記述します。図の場合、呼び出し側で第二引数が省略された場合は、第二引数の値が「2」になることを意味しています。
なお、第二引数の後ろ側（本サンプルでは「= 2」）の初期値を省略すると、データ型に応じた初期値が代入されます。例えば、今回の整数型（Integer)では0、文字列型は""(ブランク）が初期値になります。
本サンプルコードでは、第二引数の値により処理の内容を変えています。下図に第二引数の値と処理内容を表にまとめましたのでご覧ください。
@の前方からの位置を取得
下の図は、第二引数がメールアドレスに関する処理の場合、メールアドレスの中にある@の前方からの位置情報を取得するためのVBAコードになります。
VBAコード41行目で、第二引数が0(アカウント名取得）か、1（ドメイン名取得）が指定された際に、変数aPosに@マークの前方からの位置を格納します。
第二引数の値により処理を分けるアルゴリズム
VBAコード46～62行目は、第二引数の値とIf文を使う事で実際の処理を分けています。
受取った第二引数の値に応じて処理が3つに分岐されています。処理内容については、これまで解説してきた内容と同じですので割愛しますが、図中のVBAコード56行目のIf文の途中にある"_"（アンダーバー）について補足します。
VBAコード途中の"_"(アンダーバー)は、VBE上で一行が長くなりすぎたVBAコードを見やすくするために使う改行文字です。見た目だけの効果ですので、VBAの実行結果には全く影響しません。
mainプロシージャ
次に、VBAコード2～30行目のmainプロシージャについての解説です。
出力範囲をクリア
VBAコード11～13行目では、出力先の3列目(C列)から5列目(E列)を消去しています。
集計対象のセル範囲を配列に格納
VBAコード16～20行目では、 CurrentRegionやResize を使いながら、すべてのデータ範囲を二次元配列に格納しています。これにより、集計を 二次元配列 で行う準備が出来たことになります。
すべてのレコードを繰り返し処理して、3つの情報を取得する
VBAコード23～27行目については、下図に抜き出してみました。先ほど解説しました共通化されたユーザ定義関数GetTelMailInfoを使い、3つの処理（ハイフン無し電話番号、アカウント名、ドメイン名のそれぞれを取得）をしています。
3つの異なる処理を同じ関数で実行できるのは、関数に渡す第二引数を変えているからです。
このように、機能・目的などが似たようなユーザ定義関数は、今回の例のようにまとめると管理しやすくなり、可読性もよくなりますのでお勧めします。
まとめ
本記事では、電話番号からハイフンを削除する処理と、メールアドレスからアカウント名、およびドメイン名を取得するVBAサンプルコードについて詳しく解説してきました。
さらに、記事の後半ではこれら3つの処理を、1つのユーザ定義関数にまとめる方法について、オプション付引数の使い方も含めて解説してきました。
集計処理内容を、適宜サブルーチン化することでVBAコードの保守性・可読性を向上させることができますが、似たような機能・目的のサブルーチンが複数あれば、引数を適宜オプション化して1つのサブルーチンにまとめることにより、さらに保守性・可読性の向上効果が期待できます。