以下は「設定シート」を用意し、出力列・対象シート名・リトライ回数・待機時間などを外部設定で管理するハイブリッド版コード例です。
ポイント：処理条件を「設定シート」に外出ししておくと、コードを修正せずに運用条件を変更できます。
設定シート例（シート名：Config）
| 項目名 | 値 | 説明 |
|---|---|---|
| TargetSheet | Data | 処理対象シート名 |
| OutputColPref | 2 | 都道府県の出力列番号 |
| OutputColCity | 3 | 市区町村の出力列番号 |
| OutputColTown | 4 | 町域の出力列番号 |
| RetryCount | 3 | APIリトライ回数 |
| WaitSeconds | 2 | リトライ間隔（秒） |
| DBsheet | ZipDB | 郵便番号DBシート名 |
VBAコード例（設定シート連携版）
Option Explicit
' 設定値を取得する関数
Function GetConfig(key As String) As String
Dim ws As Worksheet, f As Range
Set ws = ThisWorkbook.Sheets("Config")
Set f = ws.Range("A:A").Find(key, LookAt:=xlWhole)
If Not f Is Nothing Then
GetConfig = ws.Cells(f.Row, 2).Value
Else
GetConfig = ""
End If
End Function
' メイン処理
Sub ProcessZipHybrid_Config()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim zip As String, pref As String, city As String, town As String
Dim found As Boolean
' 設定値読み込み
Dim targetSheet As String: targetSheet = GetConfig("TargetSheet")
Dim colPref As Long: colPref = CLng(GetConfig("OutputColPref"))
Dim colCity As Long: colCity = CLng(GetConfig("OutputColCity"))
Dim colTown As Long: colTown = CLng(GetConfig("OutputColTown"))
Set ws = ThisWorkbook.Sheets(targetSheet)
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To lastRow
zip = NormalizeZip(ws.Cells(i, 1).Value)
If zip = "" Then GoTo CONTINUE_NEXT
' DB検索
found = SearchLocalDB_Config(zip, pref, city, town)
' API検索
If Not found Then
found = SearchAPIWithRetry_Config(zip, pref, city, town)
If found Then AppendToLocalDB_Config zip, pref, city, town
End If
' 出力
If found Then
ws.Cells(i, colPref).Value = pref
ws.Cells(i, colCity).Value = city
ws.Cells(i, colTown).Value = town
Else
ws.Cells(i, colPref).Value = "取得失敗"
End If
CONTINUE_NEXT:
Next i
Application.ScreenUpdating = True
MsgBox "処理完了", vbInformation
End Sub
' 郵便番号整形
Function NormalizeZip(raw As String) As String
Dim z As String
z = Replace(Replace(Trim(raw), "-", ""), "ー", "")
If Len(z) = 7 And IsNumeric(z) Then
NormalizeZip = z
Else
NormalizeZip = ""
End If
End Function
' DB検索
Function SearchLocalDB_Config(zip As String, ByRef pref As String, ByRef city As String, ByRef town As String) As Boolean
Dim ws As Worksheet, f As Range
Set ws = ThisWorkbook.Sheets(GetConfig("DBsheet"))
Set f = ws.Range("A:A").Find(zip, LookAt:=xlWhole)
If Not f Is Nothing Then
pref = ws.Cells(f.Row, 2).Value
city = ws.Cells(f.Row, 3).Value
town = ws.Cells(f.Row, 4).Value
SearchLocalDB_Config = True
End If
End Function
' API検索（リトライ付き）
Function SearchAPIWithRetry_Config(zip As String, ByRef pref As String, ByRef city As String, ByRef town As String) As Boolean
Dim http As Object, JSON As Object, result As Object
Dim url As String, i As Integer
Dim retryCount As Long: retryCount = CLng(GetConfig("RetryCount"))
Dim waitSec As Double: waitSec = CDbl(GetConfig("WaitSeconds"))
url = "https://zipcloud.ibsnet.co.jp/api/search?zipcode=" & zip
For i = 1 To retryCount
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.Send
If http.Status = 200 Then
Set JSON = JsonConverter.ParseJson(http.responseText)
If JSON("status") = 200 And JSON("results").Count > 0 Then
Set result = JSON("results")(1)
pref = NzString(result("address1"))
city = NzString(result("address2"))
town = NzString(result("address3"))
SearchAPIWithRetry_Config = True
Exit Function
End If
End If
Application.Wait (Now + TimeValue("0:00:" & waitSec))
Next i
End Function
' DB追記
Sub AppendToLocalDB_Config(zip As String, pref As String, city As String, town As String)
Dim ws As Worksheet, r As Long
Set ws = ThisWorkbook.Sheets(GetConfig("DBsheet"))
r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
ws.Cells(r, 1).Value = zip
ws.Cells(r, 2).Value = pref
ws.Cells(r, 3).Value = city
ws.Cells(r, 4).Value = town
End Sub
' Null対策
Function NzString(v) As String
If IsNull(v) Or IsEmpty(v) Then
NzString = ""
Else
NzString = CStr(v)
End If
End Function
✅ この方式のメリット
- コード修正不要で運用条件を変更可能
- シート名や列番号を外部管理できるため、環境ごとに柔軟対応
- リトライ回数や待機時間も調整可能で、API制限やネットワーク環境に合わせられる
👉 この「設定シート連携版」を使えば、運用現場での調整が格段に楽になります。
さらに「ログ出力先ファイル名」や「API URL」も設定シートに外出しすれば、完全にノーコードで運用条件を切り替えられます。
