영어 주소지를 한글로 변환해주는 VBA

1. 서론 : 왜 필요할까?

 

회사에서 입출고, 재고 관리, 배송 준비 업무를 위해 플레이오토라는 EMP서비스를 이용하고 있다.
여러 온라인 채널에 들어오는 온라인 주문 발주를 사이트의 API를 통해 수집해서 한번에 상품명을 변경하거나 송장을 등록하는 등 없어서는 안될 소중한 툴이다. 

 

그런데 알리 익스프레스라는 채널이 내게 고통을 줬다.
알리는 최근 테무와 더불어 우리나라 온라인 시장을 열심히 잡아먹고 있는 이커머스 판매 채널인데,
외국기업이여서 그런지 발주서가 영어로 나온다!!

 

고정적으로 사용하고 크게 바뀌지 않는 상품명은 EMP의 치환 서비스로 규칙을 설정해주면 되지만,
수백 수천개의 주소를 그런식으로 바꿔줄 수는 없는 노릇. 

 

영어 주소지 주문들

 

그래서 플레이오토에 이 영어 주소지들을 한글로 바꿔주십사 문의를 드렸다. 
하지만 돌아온 답변은 API연동이라 임의로 가공할 수가 없다는 것...

 

플레이오토 답변

 

한동안 수기나 컨트롤H를 이용하여 주소지를 바꾸는 노가다를 하면서 대한민국 시군구 영어표현들을 많이 배웠다..
그런데 이런 식으로는 시간이 너무나도 오래 걸리고, 비효율적이고, 휴먼에러도 걱정되는 상황이다. 그래서 생각했다.

 

언제까지 수작업을 계속해야 해!!! VBA로 해결해보자!

 

 

 

2. 과정 : 어떻게 만들어야 할까?

 

내가 원하는 건 아주 간단하다. 영어 주소지를 한글 주소지로 바꿔주는 것.
몇가지 해결 방법이 떠올랐다.

첫번째로는 단순하게 영어=>한국어 언어변경이라는 것에 초점을 뒀다.
(1) 번역 API를 연동한다. (방법은 모르지만) 
 => (장점) 어떤 새로운 주소지가 와도 대응할 수 있다. 
 => (한계점) 주소지는 고유명사이기 때문에 번역했을 때 맞지 않는 경우가 있을 수 있다.
그리고, 그 외에도 다른 쉬운 길이 있을 것 같다.

두번째로는 기존 수기작업방식 (컨트롤 H로 변경하기) 에서 아이디어를 따왔다.
(2) 영문 주소지에 대응하는 한글 주소지 데이터를 미리 작성해두고, Substitute와 같은 방식으로 치환해준다.
=> (장점) 구현하기가 상대적으로 쉬워보인다. 외부 API를 사용하는 등 많은 연산작업을 필요로 하지 않는다. 
=> (한계점) 미리 변환 데이터를 작성해둬야 한다는 번거로움이 있고,
변환 데이터가 틀렸을 경우 큰 실수로 이어질 수 있다는 위험성이 있다. 

 

장점이 명확한 두번째 방법을 채택하기로 했다. 
정확히는 두번째 방법을 더 쉽고 세련되게 실행할 수 있는 VBA를 연구해보기로 했다.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2, "키워드1", "대체값1"), "키워드2", "대체값2"), "키워드3", "대체값3")
이런식으로 바꾸고자 하는 키워드와 대체값을 한번만 지정해두면 여러 셀에서 사용할 수 있다. 
그러나 바꾸고자 하는 키워드의 수와 종류가 다양하다면 컨트롤 H로 바꾸는 것 보다도 못하다. 

중요한건!! 키워드와 대체값을 주루룩 입력하고 그걸 주루룩 읽어서 가져가야 한다. 
즉, 변환 데이터를 key와 value가 있는 객체데이터로 작성해두고, 그걸 이용해서 치환해주는 vba를 작성하면 해결이다. 

 

 

 

3. 결과 : 이렇게 만들었다!

 

(1) "알리 치환데이터"라는 이름의 워크시트를 만들고, A열에 영어주소 / B열에 한글주소를 작성한다. (변환데이터)

(2) "알리 원본"이라는 이름의 워크시트를 만든다. 앞으로 이 시트의 G열에 바꾸고 싶은 영주소를 넣는다. (원본데이터)

 Dim wsOriginal As Worksheet
 Dim wsReplacement As Worksheet
 
 Set wsOriginal = ThisWorkbook.Sheets("알리 원본")
 Set wsReplacement = ThisWorkbook.Sheets("알리 치환데이터")

두개의 워크시트를 쓸 예정이니 자료형을 미리 선언해주고 변수에 해당 워크시트를 할당해준다. 

 

(3) wsOriginal과 wsReplacement의 길이는 주문량과 누적되는 치환데이터의 양으로 인해 매일 달라질 것이므로, 
VBA를 실행할 때 마다 각 워크시트의 마지막 행을 찾고 범위를 재설정하는 과정이 필요하다. 

    Dim lastRow As Long
    Dim lastRowReplacement As Long
    
    lastRow = wsOriginal.Range("G" & wsOriginal.Rows.Count).End(xlUp).Row
    lastRowReplacement = wsReplacement.Range("A" & wsReplacement.Rows.Count).End(xlUp).Row

 

(4) wsReplacement 워크시트의 데이터를 통해 객체를 생성하고, 치환 데이터를 저장해주는 부분이 필요하다. 

    Dim dict As Object
    Dim i As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 2 To lastRowReplacement
        dict(wsReplacement.Range("A" & i).Value) = wsReplacement.Range("B" & i).Value
    Next i

변수 dict를 Object 자료형으로 선언한다.
CreateObject 함수로 Scripting.Dictionary 객체를 생성해주고 dict에 해당 객체를 할당한다.
scripting.Dictionary는 Key-Value가 한 쌍이 되는 구조로, 추가/수정/삭제할 때 쓰기 좋다. 

i가 2에서 lastRowReplacement가 될 때 까지 반복하는 for 반복문이다.
여기서 반복하고 있는 것은 wsReplacementdml Ai (key)와 Bi (value)를 매칭해서 dict에 저장하는 것이다. 

 

(5) 원본 데이터와 치환데이터의 key가 같다면, 원본데이터를 치환데이터의 value로 치환해준다.

    Dim cell As Range
    Dim key As Variant
    Dim originalText As String
    
    For Each cell In wsOriginal.Range("G2:G" & lastRow)
        originalText = cell.Value
        For Each key In dict.Keys
            If InStr(originalText, key) > 0 Then
                originalText = Replace(originalText, key, dict(key))
            End If
        Next key
        cell.Value = originalText
    Next cell

For Each로 반복하는 범위를 wsOriginal의 G열의 G2부터 lastRow (=마지막 셀)까지로 설정한다.
이때 cell은 반복중인 셀을 참조하고, 값을 originalText라는 변수에 할당한다. 

두번째 For Each는 dict객체의 key를 반복하는데
만약 InStr(originalText, key)가 0보다 크다면 즉, originalText안에 key가 포함되어 있다면 
=> originalText의 key를 dict의 key로 Replace한다. 

수정된 cell.value (G2:G & lastRow)의 값을 originalText에 업데이트한다. 

 

 

 

4. 결과

 

결과는 성공! 반복 노가다 작업에서 벗어났다.
매크로 시작 버튼을 누르기만 하면 자동으로 영주소가 한글주소로 바뀌는 감동적인 환경이 만들어졌다.
간혹 치환데이터 객체에 없는 영주소가 나오면 해당 워크시트 하단에 추가해주기만 하면 되니 유지보수라고 할 것도 없다. 

만약 나처럼 플레이오토 알리 영어 주소로 고통받고 있는 사람이 있다면..
함께 VBA를 공부해보면 좋을 것 같아서 열심히 포스팅 했다. 

끝!! 

 

 

 

전체 코드

Sub ReplacePartialValues()
    Dim wsOriginal As Worksheet
    Dim wsReplacement As Worksheet
    Dim lastRow As Long
    Dim lastRowReplacement As Long
    Dim dict As Object
    Dim i As Long
    Dim cell As Range
    Dim key As Variant
    Dim originalText As String
    
    ' 시트에 대한 참조 설정
    Set wsOriginal = ThisWorkbook.Sheets("알리 원본")
    Set wsReplacement = ThisWorkbook.Sheets("알리 치환데이터")
    
    ' 마지막 행 찾기
    lastRow = wsOriginal.Range("G" & wsOriginal.Rows.Count).End(xlUp).Row
    lastRowReplacement = wsReplacement.Range("A" & wsReplacement.Rows.Count).End(xlUp).Row
    
    ' Dictionary 객체 생성 및 치환 데이터 저장
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 2 To lastRowReplacement
        dict(wsReplacement.Range("A" & i).Value) = wsReplacement.Range("B" & i).Value
    Next i
    
    ' 원본 데이터에서 치환 데이터로 값 업데이트
    For Each cell In wsOriginal.Range("G2:G" & lastRow)
        originalText = cell.Value
        For Each key In dict.Keys
            If InStr(originalText, key) > 0 Then
                originalText = Replace(originalText, key, dict(key))
            End If
        Next key
        cell.Value = originalText
    Next cell
    
    MsgBox "치환이 완료되었습니다.", vbInformation
End Sub