[Excel] 엑셀에서도 코딩을 할 수 있다고요? 사용자 지정 함수(UDF, VBA)

 
- 엑셀에서 기존 함수가 아닌 사용자가 직접 제작해서 사용하는 방법 
 
업무 메일을 열었을 때 당혹감을 감추지 못한 적이 있는가? 첨부된 엑셀 파일을 열어보니 파일에는 수천, 수만 개의 데이터가 빼곡히 채워져 있고, 이런 말을 들었다면...

- ㅇㅇ씨 이거 오늘 오후까지 이러한 형식으로 변환해서 보내주세요.
- 네,,..? 네... 넵...!

 
 
직접 하나하나 편집 하는 건 현실적으로 불가능하다. 엑셀의 함수를 활용해보려고 구글링도 해보고 챗쥐피티에 물어보지만, 딱 맞는 해결책은 나오지 않는다. 혹은 본인이 이 글을 쓰게 된 이유이기도 한데 너무 방대한 양의 데이터라 기존의 엑셀 함수로 변환하는 것이 안 돼서 50건씩, 100건씩 나눠서 변환 해야 한다. 그러나 우리가 변환해야 할 셀의 갯수가 몇 만건, 몇 십만건이라면?
 
우리의 칼퇴는 중요하기에, 그럴 때 쓸 수 있는 엑셀의 기가막힌 기능이 있다. 
 
그건 바로 ...

사용자 지정 함수(UDF: User Defined Function)

 
엑셀에서 제공하는 기본함수만으로 해결이 어려운 경우, 직접 만들어 사용할 수 있다. 

~Visual Basic Application(VBA)~

이 녀석을 활용하면 반복되는 복잡한 작업을 단 한 줄의 함수 호출로 끝!!
 
 

사용자 지정 함수(UDF) 만드는 방법

 
 엑셀 파일을 열어

• Alt + F11 로 VBA 편집기를 연다. 

 

 

• 삽입 > 모듈

VBA 언어를 입력할 수 있는 모듈창이 뜸
 

 
이런식으로 Visual Basic 기반의 VBA 언어를 사용해서 작성하면 된다.
당연히 나는 비주얼 베이직 언어를 배운 적이 없다. 하지만 우리에겐 영원한 동반자 우리의 친구 챗쮜피티가 있지 않은가?
 
여기서 잠깐!
 
내가 제작하려고 하는 함수는 ... 엑셀 파일의 각 셀 값을 가져와 작은 따옴표(')로 감싸고, 쉼표(,)로 연결하여 한 줄로 반환하는 함수이다. 아래와 같다.
 

'000', '001', '010', '011' ...

 
그리고 chatGPT에는 이렇게 쓰면 된다. "챗쮜피티야 엑셀에서 사용할 변수를 VBA를 이용해서 만들려고 하는데, A1:A10와 같은 매개변수를 받아 위와 같은 형식으로 출력할 수 있게 해줘." 그럼 아래와 같은 코드를 알려준다. 
 
 

Function JoinCellsWithQuotes(rng As Range) As String
    Dim cell As Range
    Dim result As String
    
    For Each cell In rng
        If Not IsEmpty(cell.Value) And Not IsError(cell.Value) Then
            result = result & "'" & cell.Value & "', "
        End If
    Next cell
    
    JoinCellsWithQuotes = Trim(result)
End Function

 
얏호~~! 21세기 만만쉐이~~!!
 
사실 이 경우 엑셀이 아닌 Notepad++로 더 빠르고 쉬운 변환을 할 수 있으니 다음 편을 참고하면 좋겠다. 오늘은 Excel에서도 코딩을 통해 함수 제작이 가능하다는 것을 보여주기 위함이니까 말이다. 

- 다음편
[Notepad++] 줄바꿈 제거 및 문자열 대체 - https://choittolbok.tistory.com/m/328

[Notepad++] 줄바꿈 제거 및 문자열 대체

 

 
입력 하고 저장을 하면 이런 메세지 창이 뜰 거다. 
[저장]
 

 
만든 함수는 이렇게 사용한다
=함수명(변환할 값)
=JoinCellsWithQuotes(B1:B17)
 

 
입력 후 엔터를 치면? 
끄읕~~~~!
 
마지막의 쉼표는 삭제해서 사용해도 되고, 이 기능을 반복작업에서 사용해야 할 경우 위의 VBA 코드에서 마지막에 해당하는 셀이라면 ,를 넣지 않는다는 조건을 추가하면 되겠다.
 

Function 변환함수(범위 As Range) As String
    Dim 셀 As Range
    Dim 결과 As String
    결과 = ""
    
    For Each 셀 In 범위
        결과 = 결과 & "'" & 셀.Value & "', "
    Next 셀
    
    If Len(결과) > 2 Then
        변환함수 = Left(결과, Len(결과) - 2)
    Else
        변환함수 = 결과
    End If
End Function

 
이런식으로 
참 쉽죠?
 

 
나의 경우 쿼리 작성시 IN() 안에서 사용하기 위해 필요했음
 
 

---

 
 VBA 언어를 몰라도 우리는 개발자이기에 어떠한 방식으로 코드가 돌아가는지 해석해 볼 필요가 있다. 
- 일단 VBA 언어의 주석은 '(작은 따옴표)이다. 
 
※ 전체 코드

Function JoinCellsWithQuotes(rng As Range) As String
    Dim cell As Range
    Dim result As String
    
    For Each cell In rng
        If Not IsEmpty(cell.Value) And Not IsError(cell.Value) Then
            result = result & "'" & cell.Value & "', "
        End If
    Next cell
    
    JoinCellsWithQuotes = Trim(result)
End Function

 
 

Function JoinCellsWithQuotes(rng As Range) As String

 
함수 시작을 알림  /  함수명 (매개변수) As 반환타입_문자열
 

    Dim cell As Range
    Dim result As String

 
Dim 변수 선언을 할거라는 키워드 
변수명
As Range 타입 지정 방법 
 

    For Each cell In rng
        If Not IsEmpty(cell.Value) And Not IsError(cell.Value) Then
            result = result & "'" & cell.Value & "', "
        End If
    Next cell

 
 
VBA의 for문 형식. 완전 처음 보는 형식이라 신기방기
For Each cell In rng - 각 셀에 대해 반복할 거라는 시작의 의미
IF문 줄 - 값이 비어있지 않고, 에러가 없을 때 
result에 다음 값을 넣어준다. 
 
또 신기한 점 If문이 끝났다는 것을 End If로 알려준다. 아마 VBA 언어의 경우 {} 블록 개념이 없어서 End If로 종료한 것 같다. 한 줄 짜리의 경우 여느때와 마찬가지로  End If가 필요 없음. 
 
여기서 가장 중요한 줄이 바로 Next Cell이다. 다른 언어와 다르게 엑셀에서 사용하는 VBA 언어의 경우 '셀'이라는 개념이 있기에, 다음 셀을 처리 하라는 명령어가 있다. ㅋㅋ 재밌동~~~
 

JoinCellsWithQuotes = Trim(result)
// JS로 따지면 return result.trim(); 과 같다.

 
해석을 하다보니 발견한 것은 ;(세미콜론)도 없다. 그래서 문장이 되게 깔끔해 보이는게 매력적인 것 같음.
 
그러나 배울 생각?
 

 
없다.