아이엑셀러 닷컴

완성 예

지난 강의에서 어느 테니스 클럽의 회원별 승패 현황에서 연속 승리 기록을 구하는 코딩을 해보았습니다.

가독성을 높이기 위해 시각적 효과를 가미했었는데, 그렇게까지 할 필요는 없고 연승 기록만 필요하다면 사용자 정의 함수^{User-defined Function(줄여서 UDF)}를 만들면 됩니다. 길고 복잡한 엑셀 수식(mega-formula)을 만들어 해결할 수도 있겠지만 VBA로 코딩 하는 것이 훨씬 효율적이고 편리합니다.

완성 예 1

사용자 정의 함수를 이용하면 코드가 조금 더 간단해질 수 있고 다양한 조건으로 결과값을 구할 수 있습니다. 예를 들어 6회차 이후의 승부 결과에 대해서만 연승 기록을 구하고자 한다면 함수 인수를 지정할 때 범위만 살짝 바꿔주면 됩니다.

완성 예 2

전체 코드 살펴보기

워크시트에서 ALT + F11 키를 눌러서 Visual Basic Editor 창을 엽니다. VB Editor에서 [삽입] - [모듈] 메뉴를 선택하여 모듈^Module을 삽입하고 코드를 작성합니다. 자세한 설명은 이어서 나오니까 전체 구조만 일별하고 진도를 계속 나갑니다. 그동안 주로 사용하던 Sub 프로시저가 아니라 Function 프로시저라는 것만 눈여겨 봐 두세요.

Function winRecord(rRow As Range) As Integer
  Dim rAreas As Range
  Dim rArea As Range
  Dim rA As Range
  Dim rWin As Range
  Dim rX As Range
  
  On Error Resume Next
  
  For Each rX In rRow.Cells
      If rX = "O" Then
          If rArea Is Nothing Then
              Set rArea = rX
          Else
              Set rArea = rArea.Resize(, rArea.Cells.Count + 1)
          End If
      Else
KKK:
          If rAreas Is Nothing Then
              Set rAreas = rArea
          Else
              Set rAreas = Union(rAreas, rArea)
          End If
          Set rArea = Nothing
      End If
  Next
  If Not rArea Is Nothing Then GoTo KKK
  
  For Each rA In rAreas.Areas
      If rWin Is Nothing Then
          Set rWin = rA
      Else
          If rWin.Cells.Count < rA.Cells.Count Then Set rWin = rA
      End If
  Next

  winRecord = rWin.Cells.Count
End Function

CODE

코드 해설

코드는 지난 강의와 크게 다르지 않습니다. 반복할 횟수가 줄어들므로 오히려 더 간단합니다. 사용자 정의 함수는 Function 함수이름(인수명) 반환 타입' 형태로 작성합니다. 뭔가 심오한 것 같지만 하나도 어려울 것이 없으니 계속 진도를 나가세요.

1. Function이라는 키워드로 시작합니다. 워크시트에서 함수를 사용할 때 '=함수명(인수)' 형태로 사용하죠? 따라서 여기서도 rRow라는 인수를 하나 사용했습니다. 워크시트 범위에 접근하므로 인수 타입은 Range로 지정합니다.

Function winRecord(rRow As Range) As Integer    
  Dim rAreas As Range         ''' 승리(O)한 영역에 접근할 변수
  Dim rArea As Range          ''' 승리한 영역에 개별적으로 접근할 변수
  Dim rA As Range             ''' 승리한 영역에 접근할 순환 변수
  Dim rWin As Range           ''' 최장 연승 영역에 접근할 변수
  Dim rX As Range             ''' 개별 셀에 접근할 변수

해설

2. 지난 강의에서 만들었던 succcessiveWin 프로시저는 전체 테이블에 대해 한꺼번에 처리했으므로 중첩 For Each 문을 사용했습니다만 이번에는 한 행씩 함수를 사용해 처리하므로 구조적으로 더 간단합니다.

  On Error Resume Next       ''' 실행 도중 오류가 발생하더라도 다음 명령 수행
    
  For Each rX In rRow.Cells   ''' 인수로 넘겨 받은 영역의 각 셀에 접근
      If rX = "O" Then        ''' 셀 값이 O(승리)라면...

          ''' rArea 변수에 아무 것도 할당되어 있지 않다면 rX를,
          ''' 이미 어떤 영역이 할당되어 있다면 다음 셀(오른쪽 셀)까지로 범위 확장
          If rArea Is Nothing Then
              Set rArea = rX
          Else
              Set rArea = rArea.Resize(, rArea.Cells.Count + 1)
          End If
      Else
  KKK:
          ''' rAreas 변수에 아무 것도 할당되어 있지 않다면 rArea 변수 값을,
          ''' 이미 어떤 영역이 할당되어 있다면 기존 rAreas 영역과 rArea 영역 통합
          If rAreas Is Nothing Then
              Set rAreas = rArea
          Else
              Set rAreas = Union(rAreas, rArea)
          End If
          Set rArea = Nothing                ''' rArea 변수 초기화
      End If
  Next
  If Not rArea Is Nothing Then GoTo KKK      ''' 마지막 rArea 영역을 처리하기 위해 KKK 레이블로 이동

  For Each rA In rAreas.Areas                ''' rAreas의 각 영역(Area)에 접근
      ''' rWin 변수에 아무 것도 할당되어 있지 않다면 rA 변수 값을,
      ''' 이미 어떤 영역이 할당되어 있다면 rWin과 rA 영역의 크기를 비교하여 큰 영역을 rWin 변수에 할당            
      If rWin Is Nothing Then
          Set rWin = rA
      Else
          If rWin.Cells.Count < rA.Cells.Count Then Set rWin = rA
      End If
  Next

해설

3. 마지막으로, 작업을 수행한 결과를 돌려줍니다. 어떤 작업을 한 다음 결과를 돌려준다는 것, 이것이 Sub 프로시저와 가장 크게 다른 점입니다. 사용자 정의 함수를 실행한 결과를 돌려줄 때^{리턴(Return)}에는 '함수명 = 리턴 값' 형태로 사용한다는 점에 유의하세요. Function 다음에 사용한 함수명과 리턴할 때 사용하는 함수명은 반드시 같아야 합니다(여기서는 winRecord).

  winRecord = rWin.Cells.Count     ''' 연속 승리한 영역의 셀 수를 카운팅하여 리턴
End Function

해설

함수 설명 추가하기

사용자 정의 함수도 일반 워크시트 함수와 마찬가지로 [함수 마법사] 대화상자에 간단한 설명이 나타나도록 할 수 있습니다.

워크시트 상태에서 [보기] 탭 - [매크로] 그룹 - [매크로] 명령을 선택합니다.
[매크로 이름] 항목에 해당 함수(여기서는 winRecord) 이름을 입력합니다.

함수 이름을 제대로 입력했다면 [함수 마법사] 대화상자의 [옵션] 버튼이 활성화됩니다. [옵션] 버튼을 클릭하면 [매크로 옵션] 대화상자가 나타납니다. [설명] 란에 함수에 대한 설명을 간략하게 입력합니다.

[확인] 버튼을 클릭하면 [매크로] 대화상자로 돌아갑니다. [취소] 버튼을 클릭하여 [매크로] 대화상자를 닫습니다.
[수식 입력줄] 왼쪽에 있는 [함수 입력] 아이콘을 클릭합니다. [함수 마법사] 대화상자에서 [사용자 정의] 범주에 있는 'winRecord' 함수를 선택하면 03 단계에서 입력한 설명이 나타납니다.

Sub 프로시저나 사용자 정의 함수^{Function 프로시저} 중에서 업무 특성에 맞는 것을 사용하시면 되겠습니다. 훌륭한 목수는 연장을 탓하지 않는다지만, 여러 가지 도구를 알고 있을수록 다양한 상황에 대처할 수 있음은 불문가지겠지요.

#사용자정의함수 #UDF #Function #엑셀_함수

들어가기 전에 ㅡ 계속하기를 계속하라

완성 예

전체 코드 살펴보기

코드 해설

함수 설명 추가하기