UserFormでマクロにて郵便番号から住所に

Question

いつもお世話になります。

OSは　Win10
エクセルは2016　です。

ご指導いただきたいのは、
参照図では、4つ目の　「郵便番号」　を入れると
5つ目の「住所1」に自動的にマクロで表示したい。

※可能ならばご指導いただけないでしょうか。

例
ユーザーフォームの「郵便番号」に　244-0842　と入力すると
「住所1」に　横浜市栄区飯島町　と自動的にマクロで表示

参考に
Private Sub cmdTouroku_Click()
 n = Cells(Rows.Count, 1).End(xlUp).Row + 1
                
        Cells(n, 1) = textKaisha.Text
        Cells(n, 4) = textYomi.Text
        Cells(n, 3) = textKatakana.Text
        Cells(n, 5) = textYubin.Text
        Cells(n, 6) = textJusho1.Text
        Cells(n, 8) = textJusho2.Text
        Cells(n, 9) = textTEL.Text
        Cells(n, 10) = textFAX.Text
        Cells(n, 11) = textEmail.Text
        Cells(n, 12) = textTantou.Text
        Cells(n, 15) = textBiko.Text
        Cells(n, 16) = textNyuryoku.Text
        
        textKaisha.Text = ""
        textYomi.Text = ""
        textKatakana.Text = ""
        textYubin.Text = ""
        textJusho1.Text = ""
        textJusho2.Text = ""
        textTEL.Text = ""
        textFAX.Text = ""
        textEmail.Text = ""
        textTantou.Text = ""
        textBiko.Text = ""
        textNyuryoku.Text = ""
        
        textKaisha.SetFocus
    
   　   For Each Ctrl In Me.Controls
        If Ctrl.Name Like "text*" Then
            Ctrl.Value = ""
        End If
    Next Ctrl
　　End Sub

WindFaller · Accepted Answer

こんにちは。
どうやら、原因が分かりました。

>3 ワークシートで、=ZI ぐらいまで入れるとインテリセンスが働いて、
>Zip2Addr( が出てくるはずですが
これは、ユーザー定義関数が生きているという意味で、見通しが立ちました。
現在は、Zip2Addr も、Zip2Address も、WebSerive 関数も、データが取れていないはずです。

おそらく同じだと思いますが、こちらでも、PCを別の環境にして再現性が取れました。私は、今まで下位バージョンばかり扱っていたので、気が付かなかったし、新しいセキュリティ設定には無頓着でした。

私も初めての経験で、お手数を掛けて、申し訳ありませんでした。
その状態から、解除するのにもかなり試行錯誤しました。

オプション－セキュリティセンター－プライバシーオプション

□　Office のインターネット接続を許可する

これをオン　☑　していただくこと。
もうひとつは、同じくセキュリティセンターの中の、
信頼できる場所に、そのファイルを置くか、そのファイル・フォルダーを信頼できる場所と登録していただく必要があります。

そう設定した後に、Excelを再起動すると、何やら、またVBA側からエラーが出てくることがあるかもしれません。もし、エラーが出ましたら、一旦、そのユーザー定義関数のマクロのコード部分だけを削除していただき、再登録していただいたほうが良いのではないかと思います。

もしくは、WebService 関数を利用した、Zip2Address 関数をお使いになったほうが、より確かかもしれません。これがダメでしたら、後は、ファイヤーウォールやセキュリティソフトを見なくてはなりませんが、それは、私からは難しいです。

ただ、お仕事でセキュリティの規則で許されていないと、もうひと方の方が語った、単独のスタンドアロン形式にするしかありません。その場合は、どの程度提供しなければならないかは不明です。
Vlookup という方法もないとは言いませんが、あまり反応が鈍いので、快適とは言えません。そうすると、データベースmdbファイルですが、それをこちらから提供することも可能ですが、いずれ更新などを考えると、csvファイルを辞書化するようなプログラムも提供しなくてはならないかと思います。

長い間、私自身もやったことがないことです。

郵便番号ウィザードの内部を書き換えて、UserForm に組み込む方法もないとはいいませんが、事実上、もう郵便番号ウィザードは手に入らないことが判明しましたし、それ以上に、郵便番号ウィザードの辞書がもう更新はしていないのです。おそらく、今回のWeb APIやIMEの変換があるので、こうした郵便番号ウィザードは消えていったのだろうと思います。

できれば、今回、私のユーザー定義関数が働くことを期待したいのです。

なお、まだ、これから先の問題が少しあります。私自身、VBA何年目かの頃に、こういうスタイルのUserForm を作って、他人に使ってもらうユーティリティとしては失敗した経験があるのですが、その失敗した原因に似通った部分を持っています。

WindFaller · Answer

>=Zip2Addr(E2)　　#NAME?

こうなるのは、モジュールに、ユーザー定義関数が認識していないということなのです。
用意されている内部エラーは、Error と　#Valueです。

例えば、モジュール内の、コードのFunction のところで、デバッグメニューのブレイクポイントのマークを置いて、数式をEnter すると、ユーザー定義関数が呼び出されるのが確認できます。

また、デバッグメニューのVBAProject のコンパイルというところをクリックすると、役に立っていないコードは、エラーが出るようになっています。

ワークシートで、=ZI ぐらいまで入れるとインテリセンスが働いて、Zip2Addr( が出てくるはずですが、今の状態は、数式バーのfx をクリックして、関数の分類の「ユーザー定義」の中に、くだんの関数は見つからないはずです。

ただし、この関数は、登録したブックにしか使うことができません。また、標準モジュール以外に登録しても、このユーザー定義関数はワークシート上では使えません。

もちろん、UserForm モジュールに入れて、モジュール内で呼び出す分には使えますが、UserForm モジュール自体の負担が増えてしまいますので、私はそういう方式を取りません。

WindFaller · Answer

最初に、数式をみると、以下のようになっていましたから、私の転機ミスです。
失礼しました。

=Zip2Addr("244-0842")
神奈川県横浜市栄区飯島町

=Zip2Addr("244-0841")
神奈川県横浜市栄区長沼町

>その結果　#NAME? と表示されました。

勝手な考え方で申し訳ないのですが、今までの経験で、初めにこのようなレベルでつまづいたことは、終わりまで、続くことが多いのです。そしてまた、お互いがつまらない結果に終わっているのです。こういうことにトラブった時は、不可抗力によるものでも、少なくとも、Windows 10 でしたら、どこかに問題が発見できるはずです。

なんとか、踏ん張って、ネット検索などをして、ご自身で解決してみてください。郵便番号を表示する方法としては、他の方法もあるのですが、この方法が使える限りは、一番楽でデータも少なくてすみます。ただ、もしも、ギブアップでしたら、こちらの方法はお忘れください。ただ、VLOOKUPにしろ、加工するのが、かなり面倒な気がします。

念のために、数式の画像を付けておきます。
正しく登録されていれば、=Z...  だけ入れても、関数は出てくるはずです。

WindFaller · Answer

VLOOKUPなどの組み込み関数を利用することと、まったく同じ原理なのですが。
ただ、ユーザー定義関数を、標準モジュールに入れればよいだけのことです。
オブジェクト名まで変えたり、コントロール名をループして処理しているなど、かなり凝った作りをなさってるのに、どうして、こちらの話が通じないのか、よく分かりません。

>ここまで難しくなると対応ができなくなり具体的にご指導いただくとありがたいのですがお願いできませんか。

ご自身のやり方と違うと、分からないということでは？
まったく分からない人に、今のUserFormの組み立てができるとは思えません。

まず、こちらが提示したコードの全体的な疑問を感じていらっしゃるのではないでしょうか。
もし、そうなら、私の書いたコードの中で、ユーザー定義関数が可動するかどうか、ワークシート上でチェックはしてみたらいかがですか？ファイヤーウォールなど、プロテクトが掛かっているかもしれません。

標準モジュールにユーザー定義関数を入れて、
ワークシートのセルに、この数式を貼り付ければ
=Zip2Addr("246-0824")
神奈川県横浜市栄区飯島町

と出てくるはずです。これが出てこないようなら、まったく別なシステムを考えなくてはなりません。

後は、画像をご覧ください。画像は小さいですが、右クリックして、画像のURLを取り出し、それをブラウザで閲覧しながら拡大できるかと思います。

めぐみん_ · Answer

No.2です。

＞※この方法だと神奈川県だけになります。
＞他の都道府県だとシート「〒」莫大なデーターが大変です。

全国で行なうと膨大になるので個人的にはACCESS等にインポートし、そのデータベースへ接続して得る方法はあります。
ただうちのは既に古くなったバージョンなので最新（？）の物で可能かどうか、データベースの構築とかについても心配はしちゃいます。
それにこう言った物は「VisualBasic」か「VisualC#」でやってたので、VBAではちょっと苦手意識も。。。

既に出ている回答のようにインターネットへの接続は必須ではなく、孤立したスタンドアローン（１台）でも出来ますね。
だって私ネット接続系の方が苦手ですので。

WindFaller · Answer

わかりやすく「tx郵便番号」と「tx住所」というテキストボックスにしてみました。

最初は、エンターで変換されるものを考えていましたが、

もしかしたら、入力の自動変換のほうがよいのかもしれませんね。分からないと言っている人に提供するのは、ちょっと顰蹙ものなのですが。複雑すぎて分からないというなら、最初に考えたほうをお使いください。ユーザー定義関数は、Zip2Addr を利用しました。なお、インターネット環境がで、外部にアクセスできることを条件としています。Zip2Address も、同様のインターネット環境が必要です。

'UserFormモジュールに以下のようにします。2つ必要です。
'---------------
Private Sub tx郵便番号_Change()
If Len(Replace(tx郵便番号, "-", "")) = 7 Then
　tx住所.Text = Zip2Addr(tx郵便番号.Text)
End If
End Sub

Private Sub tx郵便番号_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'数字とハイフン(-)しか入力できません。
If KeyAscii < 48 Or KeyAscii > 57 Then
　　 If KeyAscii <> 45 Then
　　　 KeyAscii = 0
　　 End If
　 End If
End Sub

'----------------------
'簡易型(上記と併用はできません。)
'この場合は、入力した後に、Enter を入れると、住所欄に変換された住所が出てきます。

Private Sub tx郵便番号_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode <> 13 Then Exit Sub
　tx住所.Text = Zip2Addr(tx郵便番号.Text)
End Sub
'-----------------

できれば、こちらは、tx郵便番号のプロパティのIMEModeは、「3 - fmIMEModeDisable」などの、全角数字が入らないようにしておくとよいです。

WindFaller · Answer

こんにちは。昨日から、VBAの郵便番号検索プログラム関数を作ってみました。ご使用になっているのは、Excel 2016だそうですから、特に問題はありません。ただ、これをご覧の方の中には、それ以下のバージョンもあると思いましたので、両方作ってみました。これは、郵便番号Wizard や郵便番号データが不要です。もし汎用性を考えるのでしたら、2番めのZip2Addr 関数のほうをお使いください。 UserFormのテキストボックスへの組み込み方は、おわかりになると思いますが、分からないようでしたら、サポートします。なお、専門的にはなりますが、出力は、JSONファイルですが、昨日から、これを素直に取り出す方法がないか、いろいろ検討してみましたが、以下の方法が一番短くて済むように思いました。これは、郵便番号データ配信サービス　zipcloud様のWeb APIを利用したプログラムです。 '//基本的には標準モジュールでお使いください。 Function Zip2Address(ByVal ZipCode As String) 　If Application.Version < 15 Then Zip2Address = "not Available": Exit Function 　''Excel 2010以下は、別のユーザー定義関数が必要です　Dim sZipCode As String 　Dim buf As String 　Dim arBuf 　Dim i As Long, j As Long 　Dim adrBuf As String, b As String 　Const strURL As String = "http://zipcloud.ibsnet.co.jp/api/search?zipcode=" 　sZipCode = Replace(ZipCode, "-", "", , , vbTextCompare) 　If Len(sZipCode) <> 7 Then Zip2Address = "length_Err": Exit Function 　buf = WorksheetFunction.WebService(strURL & sZipCode) 　arBuf = Split(Replace(buf, vbLf, ""), "address") 　If UBound(arBuf) = 0 Then Zip2Address = "Error": Exit Function 　For i = 1 To 3 　　b = arBuf(i) 　　j = InStr(1, b, ",") 　　adrBuf = adrBuf & Mid(b, 6, j - 7) 　Next i 　Zip2Address = adrBuf End Function '//Excel 全般、または、Office VBA用 Function Zip2Addr(ByVal ZipCode As String) 'グローバルバージョン Dim objHTTP As Object Dim strURL As String Dim arAdr As Variant Dim sZipCode As String Dim buf As String Dim arBuf Dim i As Long, j As Long Dim adrBuf As String, b As String Const URL As String = "http://zipcloud.ibsnet.co.jp/api/search?zipcode=" 　sZipCode = Replace(ZipCode, "-", "", , , vbTextCompare) 　If Len(sZipCode) <> 7 Then Zip2Addr = "Error w": Exit Function 　strURL = URL & sZipCode 　Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1") 　objHTTP.Open "GET", strURL, False 　objHTTP.Send 　If objHTTP.Status = 200 Then 　　buf = objHTTP.ResponseText 　Else 　　Zip2Addr = "Error i": Exit Function 　End If 　arBuf = Split(Replace(buf, vbLf, ""), "address") 　If UBound(arBuf) = 0 Then Zip2Addr = "Error": Exit Function 　For i = 1 To 3 　　b = arBuf(i) 　　j = InStr(1, b, ",") 　　adrBuf = adrBuf & Mid(b, 6, j - 7) 　Next i 　Zip2Addr = adrBuf End Function

めぐみん_ · Answer

＞VLOOKUP　で抽出します。

別シートに取り込んでおいて

http://officetanaka.net/excel/vba/speed/s9.htm

のようにWorkSheet関数もコード内で記載できますので、それを利用するとかですかね。

めぐみん_ · Answer

その郵便番号の一覧表をどのように作成されてるのでしょう？
それが不明ですと回答も難しいですよ。

もし作成からの質問であるなら

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

で、試しに１つの都道府県のCSVファイルを落として見てみて下さい。
同じ郵便番号の地区を省略して書いてあったりと、質問者さんの希望通りには難しいかもって感じるかもです。

UserFormでマクロにて郵便番号から住所に

こんにちは。

>=Zip2Addr(E2) #NAME?

最初に、数式をみると、以下のようになっていましたから、私の転機ミスです。

VLOOKUPなどの組み込み関数を利用することと、まったく同じ原理なのですが。

No.2です。

わかりやすく「tx郵便番号」と「tx住所」というテキストボックスにしてみました。

こんにちは。

＞VLOOKUP で抽出します。

その郵便番号の一覧表をどのように作成されてるのでしょう？

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

>=Zip2Addr(E2)　　#NAME?

＞VLOOKUP　で抽出します。