VBA 매크로는 엑셀에서 어떤 작업이든 가장 효과적이고 빠르며 안전하게 수행할 수 있는 방법입니다. 이 글에서는 VBA를 활용해 엑셀에서 열 번호를 문자로 변환하는 방법을 단계별로 소개합니다.
연습용 워크북 다운로드
아래 링크에서 무료 연습용 엑셀 워크북을 다운로드하여 직접 따라 해볼 수 있습니다.
VBA로 열 번호를 문자로 변환하는 3가지 방법
이번 섹션에서는 특정 열 번호를 문자로 변환하는 방법, 사용자가 입력한 열 번호를 문자로 변환하는 방법, 그리고 UDF(사용자 정의 함수)를 만들어 열 번호를 문자로 변환하는 방법까지 총 세 가지 방식을 살펴봅니다.
1. 특정 열 번호를 문자로 변환하는 VBA 매크로
먼저 코드 안에 직접 지정된 특정 열 번호를 문자로 변환하는 기본적인 방법부터 알아보겠습니다.
순서:
- 키보드에서 Alt + F11을 누르거나 리본 메뉴의 개발 도구 → Visual Basic으로 이동하여 VBA 편집기(Visual Basic Editor)를 엽니다.
- 열리는 코드 창의 메뉴 모음에서 삽입 → 모듈(Module)을 클릭합니다.
- 아래 코드를 복사해서 코드 창에 붙여넣습니다.
Sub ColNumToLetter()
Dim ColNumber As Long
Dim ColLetter As String
'Input Column Number
ColNumber = 200
'Convert To Column Letter
ColLetter = Split(Cells(1, ColNumber).Address, "$")(1)
'Display Result
MsgBox "Column " & ColNumber & " = Column " & ColLetter
End Sub이제 코드 실행 준비가 완료되었습니다.
위 코드는 200번째 열에 해당하는 문자 주소를 구해주는 코드입니다.
- 키보드의 F5 키를 누르거나 메뉴 모음에서 실행 → Sub/UserForm 실행을 선택합니다. 서브 메뉴 모음에 있는 작은 재생 아이콘을 클릭해도 매크로를 실행할 수 있습니다.
그러면 Microsoft Excel 팝업 메시지 상자에 코드 안에서 지정한 열 번호(예: 200)에 해당하는 문자 주소(GR)가 표시됩니다.
함께 읽으면 좋은 글: 엑셀에서 열 번호 기준으로 Range 사용하기(VBA, 4가지 방법)
유사한 주제의 글
- [해결] 엑셀에서 열 머리글이 숫자로 표시될 때(2가지 해결책)
- 엑셀에서 열 문자를 번호로 변환하기(4가지 방법)
2. UDF(사용자 정의 함수)를 만들어 열 번호를 문자로 변환하기
다음은 VBA로 UDF(사용자 정의 함수)를 만들어 워크시트에서 자유롭게 호출하며 열 번호를 문자로 변환하는 방법입니다.
순서:
- 앞서와 동일한 방식으로 개발 도구 탭에서 VBA 편집기를 열고 코드 창에 모듈을 삽입합니다.
- 코드 창에 아래 코드를 복사해서 붙여넣습니다.
Public Function NumToLtr(ColNum)
NumToLtr = Split(Cells(1, ColNum).Address, "$")(1)
End Function
이 코드는 실행 버튼으로 돌리는 Sub 프로시저가 아니라, 사용자 정의 함수(UDF)를 만드는 코드입니다. 따라서 코드 작성 후에는 실행 버튼 대신 저장(Save)을 눌러야 합니다.
- 이제 원하는 워크시트로 돌아가서, 방금 만든 함수(NumToLtr)를 입력하고 괄호 안에 문자로 변환하고 싶은 셀 참조를 넣어줍니다. (예시에서는 B5 셀을 인수로 전달했습니다.)
최종 수식은 다음과 같습니다.
=NumToLtr(B5)
- Enter 키를 누릅니다.
데이터 집합에서 열 번호(1)에 해당하는 문자 주소(A)가 계산되어 표시됩니다.
- 이제 채우기 핸들(Fill Handle)을 아래로 끌어 나머지 셀에도 UDF를 적용하면 각 열 번호가 한꺼번에 문자로 변환됩니다.
GoTo 문 활용하기
위 과정은 GoTo 문을 사용해서도 구현할 수 있습니다. 이 방식은 오류 발생 시 에러 메시지를 표시하도록 처리하는 장점이 있습니다.
- GoTo 문으로 열 번호를 문자로 변환하는 VBA 코드는 다음과 같습니다.
Public Function Num_Letter(ByVal iColNum As Integer) As String
Dim SLetter As String
On Error GoTo iError
Num_Letter = Left(Cells(1, iColNum).Address(False, False), _
Len(Cells(1, iColNum).Address(False, False)) - 1)
Exit Function
iError:
Call MsgBox(Err.Number & " - " & Err.Description)
End Function
- 앞서와 마찬가지로 데이터 집합에서 UDF를 호출하고, 셀 참조를 인수로 전달한 뒤 Enter 키를 누르고 행을 아래로 끌어 적용하면 됩니다.
Do-While 루프 활용하기
Do-While 루프 역시 열 번호를 문자로 변환할 수 있는 또 하나의 방법입니다. 이 방식은 26진수 개념을 이용해 알파벳 조합을 직접 계산합니다.
- Do-While 루프로 열 번호를 문자로 변환하는 VBA 코드는 다음과 같습니다.
Function NumberToLetter(iCol As Long) As String
Dim a As Long
Dim b As Long
a = iCol
NumberToLetter = ""
Do While iCol > 0
a = Int((iCol - 1) / 26)
b = (iCol - 1) Mod 26
NumberToLetter = Chr(b + 65) & NumberToLetter
iCol = a
Loop
End Function
- 같은 방식으로 데이터 집합에서 UDF를 호출하고, 셀 참조를 인수로 전달한 후 Enter 키를 누르고 행을 끌어 적용하면 됩니다.
함께 읽으면 좋은 글: 엑셀 VBA: 행·열 번호로 범위 설정하기(3가지 예제)
3. 사용자 입력값으로 열 번호를 문자로 변환하기
마지막으로, 실행 중 사용자에게 직접 입력받은 열 번호를 문자로 변환하는 방법입니다. 매번 코드를 수정하지 않아도 되어 실무에서 유용합니다.
순서:
- 앞서와 동일하게 개발 도구 탭에서 VBA 편집기를 열고 코드 창에 모듈을 삽입합니다.
- 코드 창에 아래 코드를 복사해서 붙여넣습니다.
Sub GetNumberFromUser()
Dim ColNumber As Long
Dim ColLetter As String
'User Input: Column Number
ColNumber = InputBox("Please Enter a Column Number")
'Convert the user-entered Column Number To Column Letter
ColLetter = Split(Cells(1, ColNumber).Address, "$")(1)
'Display the Result
MsgBox "Column Number: " & ColNumber & _
" = Column Letter: " & ColLetter
End Sub이제 코드 실행 준비가 완료되었습니다.
- 매크로를 실행합니다.
- Microsoft Excel 팝업 상자가 나타나며 열 번호를 입력하라고 요청합니다. 변환하고 싶은 열 번호를 상자에 입력한 뒤 확인(OK)을 누릅니다.
입력한 열 번호(예: 200)가 해당하는 문자 주소(GR)로 변환되어 결과가 표시됩니다.
함께 읽으면 좋은 글: 엑셀에서 열 번호 기준으로 Range 사용하기(VBA, 4가지 방법)
마치며
이번 글에서는 VBA를 활용해 엑셀에서 열 번호를 문자로 변환하는 세 가지 방법을 살펴보았습니다. 고정된 값 변환부터 사용자 정의 함수, 사용자 입력 처리까지 상황에 맞는 방법을 골라 활용해 보시기 바랍니다. 이 글이 도움이 되었기를 바라며, 주제와 관련해 궁금한 점이 있다면 언제든지 질문해 주세요.
관련 글
- 엑셀에서 열 이름을 ABC에서 1 2 3으로 변경하는 방법
- 엑셀에서 열 문자를 번호로 변환하기(4가지 방법)
- 엑셀에서 행·열 번호로 셀 참조하기(4가지 방법)
- 엑셀 VBA: 여러 열 선택하기(3가지 방법)
- 엑셀에서 일치하는 값의 열 번호 반환하기(5가지 유용한 방법)