엑셀 모든 버전에서 사용 가능! 구글 무제한 번역 | GoogleTranslate 함수 만들기

이제 엑셀에서 구글 번역 기능을 무제한으로 사용해보세요! 함수를 만드는 모든 과정과 추가기능을 배포하는 방법까지 단계별로 정리했습니다!🔥

# 함수및공식 # VBA

작성자 :
오빠두엑셀
최종 수정일 : 2023. 09. 08. 02:33
URL 복사
메모 남기기 : (31)

엑셀 모든 버전에서 사용 가능! 구글 무제한 번역 | GoogleTranslate 함수 만들기

엑셀 구글 번역 GoogleTranslate 함수 목차 바로가기
영상 강의

큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [VBA프로젝트] 엑셀 무제한 구글 번역! GoogleTranslate 함수 만들기
    예제파일
  • [VBA프로젝트] 엑셀 무제한 구글 번역! GoogleTranslate 함수 만들기
    완성파일

.

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.


완성파일 실행 시 주의사항

이번 강의에서 제공하는 완성파일은 'VBA 매크로'가 포함된 "매크로 통합문서(*.xlsm)" 입니다. 따라서 사용하고 계신 엑셀 버전과 설정에 따라 매크로 차단을 해제한 후 파일을 실행해야 합니다.

  1. 다운로드 받은 완성파일을 우클릭 - [속성]으로 이동합니다. 만약 속성창 하단에 '차단 해제' 버튼이 보일 경우, '차단 해제' 옵션을 체크 → [적용] 버튼을 클릭하여 매크로 차단을 해제한 후 파일을 실행합니다.

    엑셀 매크로 차단 해제
    다운로드 받은 완성파일을 우클릭 - 속성에서 '차단해제'가 있을 경우 차단해제 옵션을 체크합니다.
  2. 파일 실행 후, 엑셀 화면 상단에 표시되는 [편집 사용]과 [콘텐츠 사용] 버튼을 각각 클릭하여 매크로를 활성화하면 완성파일에서 GoogleTranslate 함수를 사용할 수 있습니다.
    엑셀 콘텐츠 사용
    엑셀 파일을 실행한 후, '편집 사용'과 '콘텐츠 사용' 버튼을 클릭해서 매크로를 활성화합니다.
    오빠두Tip : VBA를 이용한 엑셀 GoogleTranslate 함수는 윈도우 환경에서만 작동합니다. Mac 엑셀에서는 지원되지 않는 점을 유의해주세요!

강의에서 사용한 VBA 마스터 코드

아래 실습에 사용한 VBA 마스터 코드를 복사해서 매크로 편집기에 붙여넣기 후 실습을 진행하세요!

'--------------------------------------
'● 이번 강의는 입문자 대상으로, 변수 설정이나 오류처리 없이 최대한 간단하게 준비했습니다.
' 오류처리를 모두 포함한 GoogleTranslate 함수 제작방법은 아래 프리미엄 클래스를 확인하세요!
' https://www.oppadu.com/엑셀-live-72강/
'---------------------------------------
 
'① GoogleTranslate 함수 작성하기
' Function GoogleTranslate(originaltext, sFrom, sTo)
' Function GoogleTranslate(OriginalText, Optional sFrom = "auto", Optional sTo = "ko")
'
'② 구글번역을 요청할 URL 만들기
' strURL = "https://translate.google.com/m?sl=출발언어&tl=도착언어&q=번역할문장"
'
'③ EncodeURL 함수로 유니코드언어 오류 처리하기
' OriginalText = EncodeURL(OriginalText)
'
'④ GetHTTP 함수로 URL 결과 받아오기
' strResult = GetHTTP(strURL)
'
'⑤ Splitter 함수로 <div class="result-container"> ~~~ <div> 사이 단어 반환하기
' strResult = splitter(strResult,"<div class=""result-container"">", "</div>")
'
'⑥ GoogleTraslate 함수 결과 반환 후 종료
' GoogleTranslate = strResult
'
'⑦ (선택) 결과값에 Error 413 코드 있을 경우, 최대 번역글자수 5000자 초과이므로, 오류 반환 후 종료
' If InStr(1, strResult, "Error 413") > 0 Then GoogleTranslate = "#Request Too Large!": Exit Function
 
Function GetHttp(URL, Optional formText As String, _
Optional isWinHttp As Boolean = False, _
Optional RequestHeader As Variant, _
Optional includeMeta As Boolean = False, _
Optional RequestType As String = "GET", _
Optional returnInnerHTML As Boolean = True)
 
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ GetHttp 함수
'▶ 웹에서 데이터를 받아옵니다.
'▶ 인수 설명
'_____________URL : 데이터를 스크랩할 웹 페이지 주소입니다.
'_____________formText : Encoding 된 FormText 형식으로 보내야 할 경우, Send String에 쿼리문을 추가합니다.
'_____________isWinHttp : WinHTTP 로 요청할지 여부입니다. Redirect가 필요할 경우 True로 입력하여 WinHttp 요청을 전송합니다.
'_____________RequestHeader : RequestHeader를 배열로 입력합니다. 반드시 짝수(한 쌍씩 이루어진) 개수로 입력되어야 합니다.
'_____________includeMeta : TRUE 일 경우 HTML 문서위로 ResponseText를 강제 입력합니다. Meta값이 포함되어 HTML이 작성되며 innerText를 사용할 수 없습니다. 기본값은 False 입니다.
'_____________RequestType : 요청방식입니다. 기본값은 "GET"입니다.
'_____________ReturnInnerHTML : TRUE 일 경우 InnerHTML을 기본으로 반환합니다. 기본값은 TRUE 입니다.
 
'▶ 사용 예제
'Dim HtmlResult As Object
'Set htmlResult = GetHttp("https://www.naver.com")
'msgbox htmlResult.body.innerHTML
'###############################################################
 
Dim oHTMLDoc As Object: Dim objHTTP As Object
Dim HTMLDoc As Object
Dim i As Long: Dim blnAgent As Boolean: blnAgent = False
Dim sUserAgent As String: sUserAgent = "Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.183 Mobile Safari/537.36"
 
Application.DisplayAlerts = False
 
If Left(URL, 4) <> "http" Then URL = "http://" & URL
 
Set oHTMLDoc = CreateObject("HtmlFile")
Set HTMLDoc = CreateObject("HtmlFile")
 
' 2023-02-22 | 수정 | 윈도우 인증 접속 문제 발생 시 (일부 버전) ServerXMLHTTP -> XMLHTTP 요청으로 변경
' XMLHTTP 요청 시, TimeOut 세팅 불가 (기본값 설정)
' https://stackoverflow.com/questions/11605613/differences-between-xmlhttp-and-serverxmlhttp
On Error GoTo SendError:
'------------------------------
If isWinHttp = False Then
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Else
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
End If
 
objHTTP.setTimeouts 1200000, 1200000, 1200000, 1200000 '응답 대기시간 120초
 
' 2023-02-22 | 수정 | 윈도우 인증 접속 문제 발생 시 (일부 버전) ServerXMLHTTP -> XMLHTTP 요청으로 변경
SendRestart:
'------------------------------
objHTTP.Open RequestType, URL, False
If Not IsMissing(RequestHeader) Then
Dim vRequestHeader As Variant
For Each vRequestHeader In RequestHeader
Dim uHeader As Long: Dim Lheader As Long: Dim steps As Long
uHeader = UBound(vRequestHeader): Lheader = LBound(vRequestHeader)
If (uHeader - Lheader) Mod 2 = 0 Then GetHttp = CVErr(xlValue): Exit Function
For i = Lheader To uHeader Step 2
If vRequestHeader(i) = "User-Agent" Then blnAgent = True
objHTTP.setRequestHeader vRequestHeader(i), vRequestHeader(i + 1)
Next
Next
End If
If blnAgent = False Then objHTTP.setRequestHeader "User-Agent", sUserAgent
 
objHTTP.send formText
 
If includeMeta = False Then
With oHTMLDoc
.Open
.Write objHTTP.responseText
.Close
End With
Else
oHTMLDoc.body.innerhtml = objHTTP.responseText
End If
 
If returnInnerHTML = True Then
GetHttp = oHTMLDoc.body.innerhtml
Else
Set GetHttp = oHTMLDoc
End If
Set oHTMLDoc = Nothing
Set objHTTP = Nothing
 
Application.DisplayAlerts = True
 
' 2023-02-22 | 수정 | 윈도우 인증 접속 문제 발생 시 (일부 버전) ServerXMLHTTP -> XMLHTTP 요청으로 변경
Exit Function
 
SendError:
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
On Error GoTo 0
Resume SendRestart:
'------------------------------
 
End Function
 
Function ENCODEURL(varText As Variant, Optional blnEncode = True)
 
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ EncodeURL 함수
'▶ 한글/영문, 특수기호가 포함된 문자열을 웹 URL 표준 주소로 변환합니다.
'▶ 인수 설명
'_____________varTest : 표준 URL 주소로 변환할 문자열입니다.
'_____________blnEncode : TRUE 일 경우 결과값을 출력합니다.
'▶ 사용 예제
's = "http://www.google.com/search=사과"
's = ENCODEURL(s)
'MsgBox s
'###############################################################
 
Static objHtmlfile As Object
 
If objHtmlfile Is Nothing Then
Set objHtmlfile = CreateObject("htmlfile")
With objHtmlfile.parentWindow
.execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
End With
End If
 
If blnEncode Then
ENCODEURL = objHtmlfile.parentWindow.encode(varText)
End If
 
End Function
 
Function Splitter(v As Variant, Cutter As String, Optional Trimmer As String)
 
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ Splitter 함수
'▶ Cutter ~ Timmer 사이의 문자를 추출합니다. (Timmer가 빈칸일 경우 Cutter 이후 문자열을 추출합니다.)
'▶ 인수 설명
'_____________v : 문자열입니다.
'_________Cutter : 문자열 절삭을 시작할 텍스트입니다.
'_________Trimmer : 문자열 절삭을 종료할 텍스트입니다. (선택인수)
'▶ 사용 예제
'Dim s As String
's = "{sa;b132@drama#weekend;aabbcc"
's = Splitter(s, "@", "#")
'msgbox s '--> "drama"를 반환합니다.
'###############################################################
 
Dim vaArr As Variant
 
On Error GoTo EH:
 
vaArr = Split(v, Cutter)(1)
If Not IsMissing(Trimmer) Then vaArr = Split(vaArr, Trimmer)(0)
 
Splitter = vaArr
 
Exit Function
 
EH:
Splitter = ""
 
End Function

구글 번역 웹 API 주소 살펴보기

요즘 대부분의 대형 서비스 플랫폼은 사용자가 편하게 서비스를 이용할 수 있는 다양한 API를 제공합니다. API에는 여러 종류가 있지만, 그 중에서 가장 많이 사용되는 RestAPI(REST : Representational State Transfer)는 물음표(?)와 앰퍼샌드(&) 기호 만 숙지하면 누구나 API를 쉽게 사용할 수 있습니다. 왕초보를 위한 API 기초 사용 예제는 아래 10분 기초 총정리 영상을 확인하세요!

  1. 아래 링크를 클릭하거나, 인터넷 브라우저에 주소를 복사/붙여넣기하여 구글 웹번역 페이지로 이동합니다.
    https://translate.google.com/m

    구글 번역 api
    링크를 클릭하여 구글 웹 번역 페이지로 이동합니다.
  2. 번역할 단어로 "안녕하세요"를 작성한 후, '번역' 버튼을 클릭하면 아래 그림과 같이 URL주소가 변경되면서 변역결과 페이지로 이동합니다.
    https://translate.google.com/m?sl=ko&tl=en&hl=en&q=안녕하세요

    구글 번역 api 예제
    한국어 → 영어로 "안녕하세요"를 번역하면 URL주소가 바뀌면서 번역 결과가 출력됩니다.
  3. 이전 API 기초 특강에서 알아본 것 처럼, 물음표(?)와 앰퍼샌드(&) 기호를 기준으로 구분하면, 아래와 같이 API 주소를 분석할 수 있습니다.
    · Root URL(기본주소) : https://translate.google.com/m
    · sl(시작언어) : ko
    · tl(도착언어) : en
    · q(번역문장) : 안녕하세요
  4. 따라서 아래와 같이 시작언어, 도착언어, 번역문장이 바뀌도록 URL 주소를 작성하면, 엑셀에서 구글 번역 API를 사용할 수 있습니다.
    https://translate.google.com/m?sl=시작언어&tl=도착언어&q=번역문장

엑셀 GoogleTranslate 함수 만들기

홈페이지에 올려드린 VBA 마스터 코드를 복사/붙여넣기 한 후, 아래 순서에 따라 5단계만 따라하면 누구나 엑셀에서 GoogleTranslate 함수를 바로 사용할 수 있습니다!🔥

  1. 홈페이지에서 다운로드받은 예제파일을 실행하거나 비어있는 통합문서를 실행합니다. 이후 엑셀 메뉴에서 [개발도구] 탭 - [Visual Basic] 버튼을 클릭하거나 단축키 Alt + F11 를 눌러 매크로 편집기를 실행합니다.
    엑셀 매크로 편집기 실행
    예제파일 또는 빈 통합문서를 실행한 후, [개발도구] - [Visual Basic] 버튼을 클릭해서 매크로 편집기를 실행합니다.
    오빠두Tip : 만약 엑셀 메뉴 목록에 '개발도구' 탭이 보이지 않을 경우, 리본메뉴를 우클릭 - '리본메뉴 사용자 지정'에서 '개발도구'탭을 활성화합니다.
  2. 매크로 편집기가 실행되면, [삽입]탭 - [모듈]을 클릭하여 새로운 모듈을 추가합니다.
    엑셀 vba 모듈 추가
    매크로 편집기에서 [삽입] - [모듈]을 클릭하여 새 모듈을 추가합니다.
  3. 홈페이지에 올려드린 VBA 마스터 코드를 복사한 후, 모듈 안에 붙여넣기합니다.

    엑셀 vba 마스터 코드 붙여넣기
    홈페이지에 올려드린 VBA 마스터 코드를 복사한 후, 모듈 안에 붙여넣기합니다.
  4. 아래 명령문을 복사한 후, Function GetHTTP(...) 가 작성된 줄 위에 붙여넣기합니다.
    Function GoogleTranslate(OriginalText, sFrom, sTo)
     
    OriginalText = ENCODEURL(OriginalText)
    strURL = "https://translate.google.com/m?sl=" & sFrom & "&tl=" & sTo & "&q=" & OriginalText
    oResult = GetHttp(strURL)
     
    ' 2023년 9월, HTML 반환 오류로 인한 코드 수정
    sResult = Splitter(oResult, "<div class=""result-container"">", "</div>")
    If Len(sResult) = 0 Then
    sResult = Splitter(oResult, "result-container>", "</DIV>")
    End If
     
    GoogleTranslate = sResult
     
    End Function
    엑셀 googletranslate 함수 만들기
    미리 작성한 함수 명령문을 복사한 후, GetHttp 함수가 작성된 줄 위에 붙여넣기합니다.
    오빠두Tip : GoogleTranslate 함수의 자세한 동작원리는 영상강의를 참고하세요!
  5. 이제 매크로 편집기를 종료한 후, 엑셀 시트에서 "=go"를 작성하면 함수 자동완성 목록에 "=GoogleTranslate" 함수가 표시됩니다.

    엑셀 googletranslate 함수
    엑셀 시트에서 "=go"를 작성하면 GoogleTranslate 함수가 자동완성 목록에 표시됩니다.
  6. 아래와 같이 =GoogleTranslate 함수를 작성하면, 문장의 구글 번역 결과가 실시간으로 출력됩니다.
    =GoogleTranslate("번역문장","시작언어","도착언어")
    =GoogleTrsnalste("안녕하세요.","ko","en")
    → "hello"
    오빠두Tip : 이번 강의에서 다룬 VBA 함수와 다양한 엑셀 기능을 활용하여 구글 번역 서식을 완성하는 방법은 위캔두 프리미엄 멤버쉽 강의를 참고하세요!

GoogleTranslate 함수 추가기능 배포하기

작성한 매크로 통합문서를 추가기능 형태로 저장하면 GoogleTranslate 함수를 다른 엑셀파일에서 활용할 수 있을 뿐만 아니라, 여러 사람과 추가기능을 공유하여 더욱 편리하게 작업할 수 있습니다.

  1. 추가기능으로 파일을 등록하면, 해당 파일은 엑셀을 실행할 때마다 항상 함께 실행됩니다. 따라서, 효율적인 동작을 위해 파일을 최적화하여 가볍게 만드는 것이 중요합니다. 이전 단계에서 완성한 엑셀 파일의 시트 목록에서 [+] 버튼을 클릭하여 비어있는 시트를 추가한 후, 나머지 다른 시트는 모두 삭제합니다.
    엑셀 추가기능 시트 지우기
    비어있는 시트를 제외한 나머지 시트는 모두 삭제합니다.
    오빠두Tip : 추가기능으로 공유할 기능은 VBA로 작성한 '=GoogleTranslate' 함수이므로, 시트는 모두 안전하게 삭제해도 무방합니다.
  2. [파일] 탭 - [다른 이름으로 저장] 으로 이동 후, 파일 형식으로 "Excel 추가기능(*.xlam)"을 선택하고 파일 이름은 "GoogleTranslate 함수 추가기능"으로 변경합니다.

    엑셀 추가기능 파일 만들기
    다른 이름으로 저장에서 파일 형식으로 'Excle 추가기능'을 선택합니다.
  3. 파일을 저장할 위치는 '바탕화면' (또는 접근하기 편한 위치)로 선택 후, 파일을 저장하면 GoogleTranslate 함수 추가기능 파일이 생성됩니다.

    엑셀 추가기능 생성
    사용하기 편한 경로에 추가기능 파일을 저장합니다.
  4. 이제 기존의 엑셀파일을 종료합니다. 파일을 종료할 때에는 반드시 '저장 안함'으로 종료하는 것을 주의합니다.
    엑셀 파일 저장 안함
    추가기능을 생성하기 위해 변경한 파일은 '저장 안함'으로 종료합니다.
    오빠두Tip : 만약 '저장' 버튼을 클릭할 경우, 기존에 작성했던 시트는 모두 삭제되고 비어있는 시트만 남겨진 파일이 저장됩니다.
  5. 이제 임의의 엑셀파일 또는 빈 통합문서를 실행한 후, [파일] - [옵션] - [추가기능] - [Excel 추가기능] - [이동] 버튼을 클릭하여 '추가기능' 창을 실행한 후, [찾아보기] 버튼을 클릭하여 이전 단계에서 생성한 추가기능 파일을 선택합니다.

    엑셀 추가기능 찾아보기
    파일 - 옵션 - 추가기능 - Excel 추가기능 - 찾아보기에서 생성한 추가기능을 등록합니다.
  6. 파일을 선택하면 아래 그림과 같이 GoogleTranslate 함수 추가기능이 등록됩니다. 이제 [확인] 버튼을 클릭하여 추가기능 등록을 마친 후, 엑셀에서 =GoogleTranslate 함수를 입력하면 모든 엑셀 파일에서 GoogleTranslate 함수를 사용할 수 있습니다.
    엑셀 googletranslate 함수 추가기능
    추가기능을 선택 후, [확인] 버튼을 클릭하면 추가기능 등록이 완료됩니다.
    오빠두Tip : 단, 작성한 추가기능 파일을 다른 PC에서 등록하거나 다른 사람과 공유할 경우, 이전 단계에서 정리했던 '매크로 차단' 설정을 해제한 후 추가기능을 등록하는 것을 주의하세요!
4.8 17 투표
게시글평점
31 댓글
Inline Feedbacks
모든 댓글 보기
31
0
여러분의 생각을 댓글로 남겨주세요.x