엑셀 VBA 실행 버튼 만들기(Active X 컨트롤 삽입)
Active X 컨트롤 삽입하기
엑셀파일의 VBA를 매번 Alt+F11 버튼으로 VBA를 직접 켜서 실행하는 것은 상당히 불편한 일이다. 액티브 X 컨트롤을 삽입해서 이런 불편함을 쉽게 해결할 수 있다. 먼저 버튼으로 쓸 Active X 컨트롤을 아래 그림 1과 같이 개발도구 -> 삽입 -> Active X 컨트롤 메뉴를 통해서 삽입해 준다.
삽입된 Active X 컨트롤을 클릭한 후 상단 개발도구-> 속성 버튼을 클릭하면 Active X 컨트롤 속성을 설정해 줄 수 있는데, 컨트롤의 이름과 Caption을(화면에 보이는 이름) 바꿔줄 수 있다. 그 외에도 Active X 컨트롤의 색깔이나 캡션의 글꼴, 글자크기 등을 아래 그림 2의 속성에서 바꿔줄 수 있다.
Active X 컨트롤과 VBA 코드 연결하기
위 그림 2의 Active X의 이름과 (Caption 과 헷갈리면 안 된다.) 동일한 이름의 서브루틴을 만들고 "_click()"으로 끝내면 된다. 여기서 주의할 점은 서브루틴을 Active X를 만든 시트에 만들어줘야 한다는 점이다. 그림 3의 예시로 설명하면, "result"라는 시트에 종목코드라는 Caption을 가진 "button1"이라는 이름의 Active X 컨트롤에 반응하는 서브루틴은 "result"텝 안에 "button1_click()"이라는 서브루틴을 통해서 구현할 수 있다.
다른 모듈의 서브루틴 가져오기
먼저 아래 코드와 같이 모듈1에 "기초종목별ETF찾기"라는 서브루틴을 만들었다. 변수는 결과시트명, 데이터시트명, 인덱스명, 데이터시트에서 코드가 있는 컬럼, 데이터시트에서 종목명이 있는 컬럼, 데이터시트에서 가격이 있는 컬럼, 데이터시트에서 기초지수가 있는 컬럼을 받는 서브루틴이다.
Sub 기조종목별ETF찾기(결과시트 As String, 데이터시트 As String, 인덱스명 As String, _
코드가있는컬럼 As Long, 종목명이있는컬럼 As Long, 가격이있는컬럼 As Long, 기초지수가있는컬럼 As Long)
데이터시트최대ROW = Worksheets(데이터시트).UsedRange.Rows.Count
결과시트최대ROW = Worksheets(결과시트).UsedRange.Rows.Count
Dim 딕셔너리 As Object
Set 딕셔너리 = CreateObject("Scripting.Dictionary")
For i = 2 To 데이터시트최대ROW
코드 = Worksheets(데이터시트).Cells(i, 코드가있는컬럼).Value
종목명 = Worksheets(데이터시트).Cells(i, 종목명이있는컬럼).Value
가격 = Worksheets(데이터시트).Cells(i, 가격이있는컬럼).Value
기초지수명 = Worksheets(데이터시트).Cells(i, 기초지수가있는컬럼).Value
If 딕셔너리.Exists(코드) Then
Else
딕셔너리.Add 코드, Array(종목명, 가격, 기초지수명)
End If
Next
Worksheets(결과시트).Range(Worksheets(결과시트).Cells(2, 1), _
Worksheets(결과시트).Cells(결과시트최대ROW, 4)).ClearContents
workrow = 2
For Each j In 딕셔너리.keys()
If 딕셔너리(j)(2) = 인덱스명 Then
Worksheets(결과시트).Cells(workrow, 1).Value = j
Worksheets(결과시트).Cells(workrow, 2).Value = 딕셔너리(j)(0)
Worksheets(결과시트).Cells(workrow, 3).Value = 딕셔너리(j)(1)
Worksheets(결과시트).Cells(workrow, 4).Value = 딕셔너리(j)(2)
workrow = workrow + 1
Else
End If
Next
End Sub
그러고 나서 "result"텝에 만든 button1_click() 서브루틴에서 변수들을 정의한 뒤에 "Call 기초종목별ETF찾기(변수들~)"를 아래 코드와 같이 만들어주면 "result"텝에서 "button1"을 클릭해서 모듈 1에 있는 기능인 기초종목별ETF찾기를 갖다 쓸 수 있게 된다.
Sub button1_click()
Dim 결과시트 As String
Dim 데이터시트 As String
Dim 인덱스명 As String
Dim 코드가있는컬럼 As Long
Dim 종목명이있는컬럼 As Long
Dim 가격이있는컬럼 As Long
Dim 기초지수가있는컬럼 As Long
결과시트 = "result"
데이터시트 = "data"
인덱스명 = Worksheets(결과시트).Cells(1, 7)
코드가있는컬럼 = Worksheets(결과시트).Cells(2, 7)
종목명이있는컬럼 = Worksheets(결과시트).Cells(3, 7)
가격이있는컬럼 = Worksheets(결과시트).Cells(4, 7)
기초지수가있는컬럼 = Worksheets(결과시트).Cells(5, 7)
Call 기조종목별ETF찾기(결과시트, 데이터시트, 인덱스명, _
코드가있는컬럼, 종목명이있는컬럼, 가격이있는컬럼, 기초지수가있는컬럼)
End Sub
최종적으로 아래 그림 4와 같은 구조가 된다.
