郵便番号一覧から住所を求めていくREST APIを使ったVBAのサンプルコード(zipcloud版)

Sub ボタン1_Click()
    Dim objXMLHttp As Object, zipArr
    Dim yubinNo As String
    Dim splitLine() As String
   
    Dim i As Long
    i = 2 '行番号
    
    Do While Cells(i, 1).value <> ""
        '入力値からハイフンの削除
        yubinNo = Replace(Worksheets("Sheet1").Cells(i, 1).value, "-", "")
    
        Set objXMLHttp = CreateObject("MSXML2.XMLHTTP")
        objXMLHttp.Open "GET", "https://zipcloud.ibsnet.co.jp/api/search?zipcode=" & yubinNo, False
        objXMLHttp.Send
        
        Dim address As String
        Dim kana As String
        address = ""
        kana = ""
        
        'JSON-Paserなしを前提としたコード
        Dim text As Variant
        text = Split(objXMLHttp.responseText, vbLf)
        For j = 0 To UBound(text)
            Dim line As String
            line = text(j)
            '住所
            If InStr(line, "address") Then
                address = address + getFormattedValue(line)
                
                '改行
                If InStr(line, "address3") Then
                    address = address + vbLf
                End If
                
            End If
            
            'カナ
            If InStr(line, "kana") Then
                kana = kana + getFormattedValue(line)
                
                '改行
                If InStr(line, "kana3") Then
                    kana = kana + vbLf
                End If
                
            End If
            
        Next j
        
        '余計な改行を除去
        If Right(address, 1) = vbLf Then
            address = Left(address, Len(address) - 1)
        End If
        If Right(kana, 1) = vbLf Then
            kana = Left(kana, Len(kana) - 1)
        End If
        
        'セル格納
        Worksheets("Sheet1").Cells(i, 2).value = address
        Worksheets("Sheet1").Cells(i, 3).value = kana
     
        i = i + 1
    Loop
    
End Sub
Function getFormattedValue(value As String)
    Dim reg As Object
    Set reg = CreateObject("VBScript.RegExp")

    With reg
        .Pattern = "^.+:\s""|"",$"     ' "^.+:\s"|",$"     行が「"address1": "高知県",」ならば、高知県の前の～": "と後の",を削除
        .IgnoreCase = False            ' 大文字小文字区別
        .Global = True                 ' 文字列全体対象
    End With
    
    getFormattedValue = reg.Replace(value, "") ' 空文字に置換
    
End Function