Excel 매크로 (VBA) - 015. 함수(function) (후편)

전체 목차

 - 001. 특정 행을 복사하여 삽입하기

 - 002. 선택하여 붙여 넣기 기능 정리

 - 003. 변수/Assignment/Loop/비교

 - 004. 데이터가 있는 셀의 범위 알아내기

 - 005. 데이터 시트(쉘) 순회하기

 - 006. 셀 병합 하기

 - 007. 변수에 저장되어 있는 데이터 확인하기 (디버그 기능) 

 - 008. 정렬하기 (Sort)

 - 009. 버튼 컨트롤 (Excel Form vs. ActiveX)

 - 010. 콤보 상자 컨트롤 (Excel Form vs. ActiveX)

 - 011. 확인란/옵션단추 컨트롤(Excel Form vs. ActiveX)

 - 012. 스핀단추 컨트롤(스피너, Excel Form vs. ActiveX)

 - 013. 자동필터(Auto Filter)

 - 014. 함수(function) (전편)

 

포스트 목차.

01. 함수의 파라미터가 없는 경우

02. 함수의 파라미터가 두 개인 경우

03. 함수의 파라미터가 배열인 경우

04. 함수의 파라미터가 여러 형태를 포함할 수 있는 경우

05. 함수의 반환값이 배열인 경우

 

본 포스트는 함수의 파라미터와 반환값을 다양하게 변경하며 정의하는 방법을 소개하는 것을 목표로 한다. 

 

01. 함수의 파라미터가 없는 경우

 

파라미터가 없는 함수도 정의가 가능하다.   VBA에서 제공하는 Now() 함수가 그러한 함수인데, Now함수의 경우 현재날짜와 시간을 반환해주는 함수이다. 

 

또한 예전 포스트 중에 셀의 형식이 시간일 때, 시간과 분을 추출하는 함수를 다룬적이 있는데,

https://eggdrop.tistory.com/27

Excel [지식인 해결 시리즈] 006. 근무시간에서 특정 시간 구하기 (야간 근무 인정 시간)

날짜에서, 달과 일을 분리하는 함수 또한 존재한다. Day함수는 날짜를 파라미터로 입력하면, 날짜에서 일을 분리해서 반환한다.

 

Now함수와 Day함수를 이용하여 오늘 날짜를 반환하는 dateNow()함수를 만들어보자.

 

Function dateNow() As Integer
 
   dateNow = Day(Now())
 
End Function

 

엑셀에서 dateNow()함수를 사용하면 다음과 같은 값이 나온다. 

 

오늘 날짜가 2022년 9월 6일 이므로 6이라는 숫자가 표기된다.

 

02. 함수의 파라미터가 두 개인 경우

 

함수의 파라미터는 두 개 이상일 수도 있는데, 여기서는 두 개인 경우를 가볍게 살펴보고 넘어가자.

기존에 엑셀에도 빌트인 함수로 있는데, 최대공약수를 구하는 함수를 만들어보자. 단 지금 정의하는 함수는 파라미터를 두개만 허용하는 함수이다.

 

Function MyGcd(intA As Integer, intB As Integer) As Integer
    '입력한 두 수 중에서 작은 값을 저장하기 위한 변수
    Dim min As Integer
    '임시로 계산한 공약수를 저장하기 위한 변수
    Dim tmp As Integer
    '공약수의 값을 -1로 초기화
    tmp = -1
    '입력된 수가 음수 또는 0인 경우 -1 반환
    If intA * intB <= 0 Or (intA < 0 And intB < 0) Then
        gcd = -1
    Else
        '입결된 두 수중에 작은 수를 min에 저장
        If intA < intB Then
            min = intA
        Else
            min = intB
        End If
        '1부터 1씩 증가시켜가며, 공약수를 구한다.
        For i = 1 To min
            If intA Mod i = 0 And intB Mod i = 0 Then
                tmp = i
            End If
        Next i
    End If
    '공약수 중에 가장 큰 값을 반환
    MyGcd = tmp
 
End Function

 

엑셀에서 MyGcd()함수를 사용하면 다음과 같은 값이 나온다.

32와 16의 최대공약수 16이 반환된다.

 

03. 함수의 파라미터가 배열인 경우

 

함수의 파라미터로 배열, 즉 엑셀에서는 Range 형태로 입력받는 것도 가능하다.

 

일단 함수를 정의하기전에  엑셀의 빌트인 함수인 Concatenate에 대해서 알아보자.

Concatenate 함수는 입력된 파라미터를 하나의 문자로 합쳐주는 기능을 한다.

 

 예를 들어 위의 그림처럼 여러 개의 단어를 하나로 합치는 기능을 수행하는데, 파라미터를 여러개 지정하는 것이 가능하다. 그러나 빌트인 Concatenate함수는 입력 인자로 Range 형태를 입력할 수 없다.

 

(각각의 단어에는 마지막에 공백 한칸이 들어있어서 병합 했을 때, 띄어쓰기를 하는 효과가 있다.)

Range 형태로 입력하면, 우리가 원하는 결과값이 나오지 않는다.

 

이제 Range 형태를 입력받을 수 있는 Concatenate 함수를 만들어보자

 

Function MyConcatenateRange(section As Range) As String
    For Each cell In section.Cells
        
        MyConcatenateRange = MyConcatenateRange & cell.Value
    
    Next
End Function

 

For Each ... Next 구문은 이전 포스트에서 다룬적이 있으니 더 알아보고 싶다면 다음 링크의 도구부분을 참고하자.

https://eggdrop.tistory.com/11?category=1087789 

Excel 매크로 (VBA) - 006. 셀 병합 하기

 

위의 함수에서 & 연산자는 두개의 내용을 합치는 연산자이다.

 

위의 함수를 실행한 결과는 다음과 같다.

 

04. 함수의 파라미터가 여러 형태를 포함할 수 있는 경우

 

위에서 다룬 MyConcatenateRange 함수는 반대로 Range만 받아들일 수 있다는 약점이 있다. 따라서 여러 개의 파라미터를 다 받을 수 있는 함수를 만들어보자.

 

파라미터를 정의할 때, ParamArray 형태로 지정하면 여러개의 데이터를 받는 것이 가능하다.

 

Function MyConcatenateAll(ParamArray params() As Variant) As String
    For Each param In params
        For Each cell In param
            MyConcatenateAll = MyConcatenateAll & cell
        Next cell
    Next param
End Function

위와 같이 정의하면, 파라미터 여러개를 처리할 수 있으며, 파라미터 중에 Range가 들어와도 모두 처리 가능하다.

 

실제 사용 예를 보며 위 함수의 특성을 파악해보자.

 

위의 그림처럼, 함수 중간 중간 Range와 일반 셀을 포함해서 파라미터로 입력했는데, 우리가 의도하는 결과대로 반환값이 설정되는 것을 알 수 있다. 

 

05. 함수의 반환값이 배열인 경우

 

지금까지 다양한 파라미터의 형태를 다뤘다면, 이번에는 함수가 배열 형태의 값을 반환하는 경우를 다뤄보자. 다음 함수는 Range로 입력받은 데이터를 제곱하여 배열(Range)로 반환하는 함수이다.

 

Function MeanSquare(section As Range) As Variant
    'section이라는 파라미터(Range)에 입력되어 있는 값의 제곱값을 저장할 배열 선언
    Dim Numbers() As Double
    'section에 존재하는 숫자에 접근하기 위한 인덱스
    Dim count As Integer    
    count = 0
    'section크기만큼 Numbers 배열을 재정의
    ReDim Numbers(1 To section.count)
    
    'section의 모든 셀에 대한 순회
    For Each cell In section.Cells
       'cell의 값이 숫자인 경우만 작업 수행
       If IsNumeric(cell.Value) Then
            '인덱스 값 1증가
            count = count + 1
            '배열의 인덱스에 맞는 엘리먼트에 셀의 제곱값 입력
            Numbers(count) = cell.Value * cell.Value
       End If
    Next
    '만들어진 배열을 함수의 반환값으로 지정
    MeanSquare = Numbers
    
End Function

위 함수를 실행해보자.

하나의 셀은 배열 값을 표현할 방법이 없기 때문에 가장 첫 엘리먼트의 값(13의 제곱)만 표기된다. 

 

위의 값에 Sum 함수를 이용해보자.

위의 그림처럼 SUM함수에는 배열형태로 입력되어, 모든 제곱값을 더한 값인 31067이 출력되는 것을 확인할 수 있다. 

 

지금까지 다양한 형태의 파라미터 조합과, 배열 형태의 반환값을 이용하는 사용자 정의함수를 만드는 법을 다뤘다. 이제 자신이 원하는 함수를 만들어서 엑셀을 더 재미있게 사용해보자.