​ExcelVBA小技教室

第二回 郵便番号からの住所呼び出しフォーム

Microsoft社は、「郵便番号辞書」のアドインをWeb上で提供しております。それをダウンロードすると、例えばAccessのテーブルでは郵便番号を入力してEnterを押せば、別のフィールドに住所が自動入力されます。リレーショナルデータベース・ソフトであるAccessでは、同様の結果がフォーム上でも実現できます。しかし
Excelの場合、アドインが公開されてはいるものの、フォームのテキストボックスでAccess並にこれを利用することは難しいようです。そこで今回は郵政公社のダウンロードデータを利用して、私が「顧客管理名簿」の中で実際に使用している住所変換マクロをご紹介します。Microsoftの郵便番号は更新が不定期(ってゆか、数か月に一度)であるのに対し、郵政公社はさすがに毎月更新しています。

郵便番号ダウンロード http://www.post.japanpost.jp/zipcode/

シートの構成は「検索」「東日本」「西日本」の三つです。全国の郵便番号を1枚のシートに持たせることは無理なので東日本と西日本に分けました。ちなみに、Excel2007以降のエクセルバージョンでは1枚に持つことが可能です。Userformを作り、その上にTextBox2個とCommandButton2個、それとLabelをひとつ配置します。

シートに配置した住所検索ボタンを押すとUserform1が表示されます。

TextBox1に郵便番号を入力し、検索ボタンを押すと。

TextBox2に、途中までの住所が表示されます。

該当する検索値がない場合。

Userform1のプロシージャ

------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()’検索ボタンを押す。

Dim 検索値 As String’変数を宣言。
Dim 検索セル As Range’変数を宣言。
Dim 検索セル2 As Range’変数を宣言。

   検索値 = TextBox1.Text’変数「検索値」にTextBox1のテキストを代入。

   Application.ScreenUpdating = False’画面を停止。
   Worksheets("東日本").Visible = True’東日本シートを再表示。
   Worksheets("西日本").Visible = True’西日本シートを再表示。
   Worksheets("東日本").Select’東日本シートを選択(省略可)。
   With Worksheets("東日本")
   Set 検索セル = .Range("A1:A65536").Find(検索値)

’Range("A1:A65536")の該当検索値をオブジェクト変数「検索セル」に代入。
      If 検索セル Is Nothing Then’検索セルに該当がない場合。
         Worksheets("西日本").Select’西日本シートを選択(省略不可)。
         With Worksheets("西日本")
         Set 検索セル2 = Worksheets("西日本").Range("A1:A65536").Find(検索値)

’Range("A1:A65536")の該当検索値をオブジェクト変数検索セル2」に代入。
         If 検索セル2 Is Nothing Then’検索セル2に該当がない場合。
            Label1.Caption = "検索値は存在しません。"’ラベルにメッセージを表示。
         Else’検索セル2に該当があったら。
            TextBox2.Text = Cells(検索セル2.Row, 2)’該当した検索セル2の2列目をTextBox2に表示。
            Label1.Caption = ""’ラベルにメッセージを表示しない。
         End If
         End With
      Else
         TextBox2.Text = Worksheets("東日本").Cells(検索セル.Row, 2)’該当した検索セルの2列目をTextBox2に表示。
         Label1.Caption = ""’ラベルにメッセージを表示しない。
      End If
   End With

   Worksheets("東日本").Visible = False’東日本シートを非表示。
   Worksheets("西日本").Visible = False’西日本シートを非表示。
   Application.ScreenUpdating = True’画面停止を解除。

End Sub’
------------------------------------------------------------------------------------ 

サンプルファイルはこちらからダウンロードすることが出来ます。

∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞

人事システム、不動産システムなら

データベースシステム開発  エクセル工房Inage