VBA 워크시트함수(Match, Min함수), Split함수 사용법 익히기

 

VBA에서도 엑셀에서 사용하던 함수를 사용할 수 있다는 것을 알고 계신가요? 엑셀의 기본함수를 VBA에서 사용이 가능하기 때문에 활용면에서 엄청난 시너지를 낼 수 있는데요. 빈번히 사용되는 워크시트함수를 가지고 실전연습해 보는 시간을 갖도록 하겠습니다.

 

 

 

 

워크시트 함수 사용하기

워크시트함수의 기본문법은 application.WorksheetFunction.함수명입니다.

앞선 포스팅에서도 워크시트함수를 사용했었는데요. worksheetFunction은 생략이 가능하다는 것을 다시 한번 알고 넘어가셔야 합니다. 이유는 application.WorksheetFunction을 사용했을 경우 오류가 발생하는 경우가 존재하기 때문입니다.

 

그럼 워크시트함수를 한번 사용해 볼까요? 다음 예시를 통해 Match함수와 Min함수를 사용해 보도록 하겠습니다.

 

워크시트함수 사용예시

 

Match함수는 원하는 값의 위치가 몇번째몇 번째 행(열)에 있는지 숫자로 나타내주는 함수입니다. 엑셀에서 Index와 함께 엄청난 활용도를 보이는 함수입니다. 이렇게 강력한 워크시트함수를 VBA에서도 사용이 가능합니다. 회사명에 "B"가 포함된 회사가 몇 번째행에 위치하는지 찾아보는 프로시저를 만들어 보겠습니다.

 

 

sub test1() MsgBox Application.WorksheetFunction.Match("B*", Range("A2:A9"), 0) 'Match함수를 사용하여 "B"로 시작하는 회사의 행번호를 출력 If IsError(Application.Match("F*", Range("A2:A9"), 0)) Then 'F로 시작하는 회사가 없다면 오류 발생 → IsError를 통해 오류 여부를 확인 후      Exit sub       'If가 만족한다면 프로시져를 종료 End if 'IF에 해당하지 않는다면 If 종료 End Sub

 

Applicaion.WorksheetFunction.Match("B*", Range("A2:A9"), 0)를 이용하여 "B"로 시작하는 회사가 몇 번째 행에 위치하는지를 알아보겠습니다.  "B"를 포함한 회사는 3행, 8행, 9행에 위치하고 있습니다만, Match함수의 특성상 가장 먼저 일치하는 숫자의 행을 반환해 줍니다. 여기서는 세 번째에 위치한 "B-2"의 행인 3을 출력해 줍니다.

 

위 코드로만 진행할 경우 해당 결과를 받아서 출력해 주는 개체가 없기 때문에 오류가 발생하게 됩니다.

따라서 MsgBox를 통해 3의 값을 출력해 주어야 오류가 발생하지 않는다는 점 주의하여야 합니다.

 

만약 Match를 만족하는 회사가 없다면 어떻게 될까요? 만족하는 회사가 없다면 오류를 반환하게 될 텐데요.

"F*"로 시작하는 회사는 보기에 없기 때문에 F를 찾게 되면 오류가 발생한다는 것을 유추해 볼 수 있습니다.

 

그렇다면 Applicaion.WorksheetFunction.Match("F*", Range("A2:A9"), 0)를 돌리면 어떻게 될까요?

"F"로 시작하는 회사가 없기 때문에 당연히 오류가 발생하게 됩니다.

 

그렇다면 오류를 발생하지 않고 프러시저를 종료하는 방법은 무엇이 있을까요?

IF를 사용하면 간단하게 해결할 수 있습니다.

 

If IsError(Application.Match("F*", Range("A2:A9"), 0)) Then을 살펴보시죠.

 

IF를 통해 "F"로 시작하는 회사가 존재하는지 확인하는 과정을 거쳤습니다. F로 시작하는 회사가 존재하지 않기 때문에 IsError를 통해 오류를 걸러 내는 작업을 거쳤습니다. 결국 오류가 발생되었기 때문에 Exit sub를 통해 프로시저가 종료되게 되는 것입니다.

 

그럼 한 단계 더 나아가서 Match와 Min을 사용하여 최솟값을 채워 넣는 매크로를 만들어 보도록 하겠습니다.

아래의 예시처럼 점수가 가장 낮은 회사명을 "B10"셀에 넣어 보도록 하겠습니다.

 

Match와 Min 예시

 

sub test2() Dim rs As Range Dim r As Long set rs = Range("B2:B9") ' "B2:B9"의 범위를 rs변수에 담기 If IsError(Application.Match(Application.Min(rs), rs, 0)) = False Then ' "B2:B9"범위에서 최솟값의 행번호가 존재하는지 확인      r = Application.Match(Application.Min(rs), rs, 0)         'B2:B9" 범위에서 최솟값이 존재한다면 행번호를 r변수에 담기          Range("B10") = Cells(r + 1, "a")         ' "B10"셀에 해당값 입력하기 End if End Sub

 

set rs = Range("B2:B9")를 통해 점수가 들어있는 "B2:B9"를 rs변수에 담아두었습니다.

If IsError(Applicaion.Match(Application.Min(rs), rs, 0)) = False Then는 rs범위에서 최솟값이 위치한 행번호를 출력하는 코드입니다. 여기서도 마찬가지로 오류값이 발생하여 프러시저가 중단되는 것을 막기 위해 IF와 IsError를 사용해 주었습니다. 

 

오류가 발생하지 않는다면  r = Application.Match(Application.Min(rs), rs, 0)를 통해 최솟값이 위치한 행번호를 r변수에 담아둡니다.  해당 코드로 찾아낸 최소 점수는 1이며, r변수에는 1이 위치한 행번호인 "4"가 담기게 됩니다.

(제목행을 제외한 범위로 설정하였기 때문에 5가 아닌 4가 들어오게 됩니다.) 

 

Range("B10") = Cells(r + 1, "a")는 "B10"에 최솟값을 만족하는 회사명을 입력하기 위한 코드입니다. r에는 4가 담겨 있기 때문에 Cells(r + 1, "a")는 Cells(5,"a"), 즉 "a5"셀의 값이 "B10"셀에 입력되는 코드가 완성되는 것이죠.

완성된 코드를 돌려보면 다음과 같은 결과를 확인하실 수 있습니다.

 

결과 확인

 

---

Split 함수 사용하기

 

split함수의 기본문법은 split(문자열, 구분자, 최대문자열수, 비교유형)입니다. split은 "나누다"라는 의미를 가지고 있습니다. 말 그대로 특정 조건에 따라 데이터를 나누는 역할을 하는데요. 문자열과 구분자를 통해 나누어진 결과는 배열로 반환이 됩니다. 예시를 한번 살펴보시죠.

 

Split함수 사용하기

 

sub split1() Dim s Dim i As Long, j As Long s = split(Range("a2"), " ") ' "a2"셀의 값을 " "을 기준으로 분리 for i = 0 to Ubound(s) ' 0부터 s변수에 담긴 데이터의 갯수만큼(s변수에 4개의 데이터가 들어 있으므로 4를 의미) 반복    cells(i + 5, "a") = s(i)       ' Ubound(s)에 4개의 값이 담겨있으므로 (0+5, "a"), (1+5, "a"), (2+5, "a"), (3+5, "a"), (4+5, "a") 순으로 반복       ' s(i)는 s에 담긴 4개의 값("A", "BCD", "EFGHU", "1234")을 순차대로 반환 Next  End Sub

 

s = split(Range("a2"), " ")는 split함수를 이용하여 "a"셀에 있는 데이터를 공란(" ")을 기준으로 배열 분류하여 s변수에 담아두는 작업을 의미합니다. "a2"셀의 값을 " "을 기준으로 분리하면 "A", "BCD", "EFGHU", "1234"로 분리가 되어 s변수에 각각 담기게 된다는 것을 기억하고 진행하시면 이해하기 한결 수월할 것 같네요.

 

For문을 사용하여 s에 담긴 데이터의 숫자만큼 반복을 통해 각 행에 데이터를 입력해 주는 작업을 진행하여야 하는데요. For문의 최댓값을 지정해 주기 위하여 Ubound를 사용하였습니다. 

※ Ubound는 배열의 크기를 반환해 주는 함수로, 여러 개의 배열을 루프를 통해 동작시킬 때 사용합니다. 값의 크기가 동일하지 않을 경우 Ubound를 사용하여 최댓값을 구할 수 있습니다.

 

S변수에 몇 개의 데이터가 들어갈지 알 수 없기 때문에 2차원 배열을 사용하였으며, 해당 작업을 위해 2차원 배열 인덱스의 상한 값을 구하는 Ubound를 사용해 주었다고 이해하고 넘어가시면 좋을 것 같습니다. (추후 Ubound 포스팅 예정!)

 

결국 "A", "BCD", "EFGHU", "1234"로 분리된 4개의 값이 For문에 적용되기 때문에 For i = 0 to 4가 되는 것이죠.

 cells(i + 5, "a") = s(i)부터는 i값에 0~4를 반복하여 작동하게 됩니다.

 

그럼 완성된 결과를 한번 살펴보도록 하겠습니다.

 

Split 결과

 

 

 

 

 

 

For반목문을 사용하였다면 For Each반목문으로도 작업이 가능합니다. 당연히 For문과 결과도 동일하게 나오게 됩니다. 

For문의 원리와 크게 다르지 않기 때문에 작동 코드만 정리해 드리오니, 차근차근 뜯어보시기 바랍니다.

sub split2() Dim s Dim i As Long, j As Long s = split(Range("a2"), " ") For Each s In Split(Range("a2"), " ")    Cells(j + 5, "a") = s    j = j + 1 Next End Sub

---

마치며

지금까지 VBA에서 워크시트함수와 Split함수의 사용방법에 대해 알아보았습니다. VBA를 익숙하게 사용하기 위해서는 지속적인 반복으로 인한 습득밖에는 방법이 없는 것 같습니다. 조금 익숙해졌다고 하더라도 자꾸 보지 않으면 코드가 생각이 안 나서 과거 코드들을 뒤적거리기도 합니다. 업무자동화를 위해서는 당연히 겪어야 하는 과정들이니 힘들다고 포기하지 마시고 반복, 또 반복을 통해 업무자동화를 이루시기 바랍니다.

 

 

▼▼▼함께 읽으면 좋은 글▼▼▼

VBA 반복문 1탄(for~next 사용하기)

VBA 반복문 2탄(For each ~ Next 사용하기)

IF조건절의 4가지 형태(사용법) 알아보기

VBA로 행ㆍ열 삽입, 삭제하기(entirerow, entirecolumn, resize활용)

VBA로 셀병합 해제 후 빈셀 채우기(빈셀 채우기와 찰떡궁합!)

 

특정 기능을 내 것으로 만들기 위해서는 수백 번의 연습이 필요한 법.