엑셀 VBA function 만들어서 활용하기
함수 만들기(public function 사용)
엑셀 VBA를 구성할 때 기능별로 function을 만들고 function으로 구한 값을 활용하는 것이 가능하다. 기본적인 구조는 아래와 같다.
Public Function 함수명(변수명1 As String, 변수명2 As String)
함수 기능 및 결과
함수명 = 결과값
End Function
위의 구조로 함수에 변수명1, 변수명 2 등 함수에 다양한 변수를 넣을 수 있고, 함수의 계산 결과를 함수명에 저장할 수 있다. 여기서 함수에 들어갈 변수명을 넣을 때 "변수명1 as String"과 같이 변수명들의 형식을 정의해 주는 것이 좋다. 엑셀의 셀에 "1"이라는 값이 문자열(String)로 입력될 수도 있고, 숫자(Integer)로 입력될 수도 있기 때문에 함수에 변수를 넣어주는 단계부터 명확하게 해 주는 것이다. 만들어진 함수는 아래와 같은 구조로 활용할 수 있다.
Sub 서브루틴명()
Dim 변수1 As String
Dim 변수2 As String
변수1 = "~~~"
변수2 = "~~~"
함수값 = 함수명(변수1, 변수2)
Worksheets(작업시트).Cells(1, 1) = 함수값
End Sub
"함수값 = 함수명(변수1, 변수2)"을 통해서 만들어 둔 함수를 통해 계산한 값을 함수값이라는 변수에 넣을 수 있다. 계산된 함수값을 작업시트의 A1 셀에 넣어주는 식으로 활용하면 된다.
조건을 만족하는 펀드 찾기
펀드 데이터 입수하기
금융투자협회에서 제공하는 펀드정보 One-Click 시스템을(https://fund.kofia.or.kr/index/index.html) 사용하면 국내의 모든 펀드의 가격을 조회해 볼 수 있다.
그림1 에서 조회한 데이터를 바탕으로 아래 그림 2와 같은 데이터 목록을 만들 수 있다. 이 데이터 목록에서 특정 운용사/ 펀드유형을 갖고, 기준가격이 특정 가격 이상인 펀드를 찾아내서 작업시트에 기록하는 "조건판단"이라는 function을 만들고 function을 활용한 "데이터가져오기"라는 서브루틴을 만들 수 있다.
function을 활용한 서브루틴 구성
function을 활용하여 데이터를 찾는 엑셀파일을 만들려면 function을 참조하는 구조를 먼저 짜야한다. 이번 작업에서는 "데이터ROW"라는 데이터 최대 ROW를 구하는 function을 만들고, 그것을 이용해서 그림 2의 데이터 sheet의 데이터 ROW의 수를 구한 뒤에 function의 변수로 받은 운용사명, 최소가격, 펀드유형이 조건을 충족하면 작업시트에 해당 데이터를 기록하는 식으로 구조를 짰다.
이렇게 만든 function을 "데이터가져오기"라는 서브루틴에서 참조해서 작업을 한다. 최종적으로 작업시트(main 시트)에 있는 getdata라는 Active X 컨트롤러를 클릭하면 "데이터가져오기"라는 서브루틴이 작동하고, "데이터가져오기"는 Module2에서 구성한 function 들로 작업을 완수하는 구조가 된다.
구체적으로 Module2의 function 들은 아래와 같이 코드로 구성할 수 있다.
Public Function 조건판단(데이터시트 As String, 작업시트 As String, 운용사명 As String, _
최소가격 As Integer, 펀드유형 As String)
'데이터시트, 작업시트, 운용사명, 최소가격, 펀드유형을 변수로 받는 함수
행의수 = 데이터ROW(데이터시트, 1)
작업행 = 2
For i = 2 To 행의수
대상운용사 = Worksheets(데이터시트).Cells(i, 1)
펀드명 = Worksheets(데이터시트).Cells(i, 2)
대상가격 = Worksheets(데이터시트).Cells(i, 4)
대상펀드유형 = Worksheets(데이터시트).Cells(i, 3)
'데이터시트의 운용사명, 가격, 펀드유형을 변수로 받은 운용사명, 최소가격, 펀드유형과 비교
'조건을 만족하면 변수로 받은 작업시트에 해당 펀드 정보를 기록하고 작업행을 1 줄 추가
'(대상 펀드가 늘어나면 다음 행에 작업해야 하므로)
If 운용사명 = 대상운용사 And 대상가격 >= 최소가격 And 펀드유형 = 대상펀드유형 Then
Worksheets(작업시트).Cells(작업행, 1) = 대상운용사
Worksheets(작업시트).Cells(작업행, 2) = 펀드명
Worksheets(작업시트).Cells(작업행, 3) = 대상가격
작업행 = 작업행 + 1
End If
Next
End Function
Public Function 데이터ROW(시트 As String, 대상컬럼 As Long)
'ROW 개수를 셀 시트명, 기준이 되는 대상 컬럼을 변수로 받는 함수
For i = 1 To 1000000
'해당 ROW 및 컬럼에 데이터가 없으면 개수 세는 작업은 중지되고 그 전까지 ROW가
'(마지막 데이터가 존재하는 ROW)function의 결과값이 됨
If Worksheets(시트).Cells(i, 대상컬럼) = "" Then
Exit For
Else
데이터ROW = i
End If
Next
End Function
Module2의 function을 참조하는 Module1의 서브루틴은 아래와 같이 만들 수 있다.
Sub 데이터가져오기(운용사명 As String, 최소가격 As Integer, 펀드유형 As String)
'사용자가 찾고자 하는 운용사명, 펀드유형, 최소가격을 변수로 받은 뒤
'"조건판단" 함수를 사용해서 작업을 수행
Dim 데이터시트 As String
Dim 작업시트 As String
데이터시트 = "data"
작업시트 = "main"
작업 = 조건판단(데이터시트, 작업시트, 운용사명, 최소가격, 펀드유형)
End Sub
마지막으로 getdata라는 Active X 컨트롤러에 연결된 코드는 아래와 같이 짤 수 있다.
Sub getdata_click()
Dim 작업시트 As String
Dim 운용사명 As String
Dim 최소가격 As Integer
Dim 펀드유형 As String
'작업시트 명은 본 작업파일에서는 main 이라는 이름
작업시트 = "main"
'사용자가 찾고자 하는 운용사명, 최소가격, 펀드유형을 입력하는 셀 주소
운용사명 = Worksheets(작업시트).Cells(1, 6)
최소가격 = Worksheets(작업시트).Cells(2, 6)
펀드유형 = Worksheets(작업시트).Cells(3, 6)
'Module2에서 만든 "데이터ROW"함수를 이용해서 현재 ROW 수를 확인
열수 = 데이터ROW(작업시트, 1)
'확인된 ROW를 이용해서 범위를 설정하고 내부 데이터 삭제
Worksheets(작업시트).Range(Cells(2, 1), Cells(열수 + 1, 3)).ClearContents
'데이터가져오기 서브루틴을 Call 해서 작업 수행
Call 데이터가져오기(운용사명, 최소가격, 펀드유형)
End Sub
결과적으로 아래와 같은 작업파일을 얻을 수 있다.