[vba] 버튼(단추)를 이용하여 스크립트 실행하기

버튼예제.xls

0.04MB

어떤 엑셀 페이지에 늘 사용하는 스크립트를 작성하였으나 매번 실행하기 위하여 vba 창을 눌러 F5를 누르는 것은 사실 본인이 직접 스크립트 코드를 개발하는 입장이라면 조금도 번거로울 것이 없는 것이지만 누군가에게 스크립트를 작성하여 엑셀 파일을 건내 주는 경우라면, 실제 사용하는 사람이 개발과 거리가 있는 사람이라면 더더욱 이와 같은 방법으로 스크립트를 실행시키는 것은 불편한 방법이 됩니다.

게다가 스크립트가 딱 하나가 아니고 어떤 스크립트는 정렬을 해주고, 어떤 스크립트는 불필요한 값을 숨겨주고, 또 어떤 스크립트는 데이터를 새로 읽어 들여 전체 시트를 리프레쉬 하는 등의 여러개의 기능을 개발한 상태라면 vba 창에서 실제 해당 스크립트 위치에 마우스 커서를 가져다 놓는 등의 추가 행위가 필요합니다.

이럴때 버튼을 이용하여 특정 스크립트 (엑셀에서는 매크로라고 합니다) 를 연결하게 되면 지정된 버튼을 누를때 마다 필요한 스크립트가 동작하게 됩니다. 

말하자면 간단한 툴이 만들어 지는 것이죠. 

사실 엑셀의 각 셀은 함수나 계산식을 포함할 수 있으므로 왠만한 복잡한 것들은 워크시트의 함수와 계산식으로 처리가 가능하지만 이미지를 불러온다거나 어떤 데이터의 속성을 알아내거나 약간은 복잡한 관계의 셀 값들을 이용하여 결과값을 얻는다든가 하는 등의 일은 워크시트 함수로는 조금 어려운 부분이 있습니다. 그래서 매크로 스크립트를 작성하는 것이죠. 이런 기능기능이 모여 마치 어떤 툴처럼 제작이 가능합니다.

영업사원들에게는 견적서 프로그램을 만드는 것이 가능할 것이고 세무, 회계부서에서는 각종 세금 관계 계산을 위한 처리가 가능할것입니다. 

오늘은 그렇게 만들어진 스크립트 들을 버튼을 이용하여 동작하도록 하는 방법을 소개해 드릴까 합니다.

 

먼저 제가 초등학교 저학년 학생들을 위한 간단한 덧셈 연산을 자동으로 생성하는 엑셀파일을 만든적이 있습니다. 

https://diy-dev-design.tistory.com/41?category=791049

[VBA] 엑셀로 초등학교 저학년 문제집 만들기 (자동버전)

 

해당 파일을 보면 아래와 같은 화면을 볼 수 있는데요. 우측의 버튼을 누를 때 마다 버튼의 텍스트에 해당하는 기능으로 좌측 값이 자동으로 생성이 됩니다. 위 링크된 페이지에 가면 실제 엑셀 파일을 받으실 수 있으니 해당 파일을 받으셔서 참고해보셔도 좋을 것 같습니다.

버튼을 이용하여 다양한 매크로를 동작하도록 만든 엑셀 시트

 

우선 단추(버튼)르르 이용한 동작을 확인할 간단한 스크립트를 작성해 보겠습니다.

• 첫번째 스크립트로 셀 A1~C1 번에 숫자 1, 2, 3 을 넣어 보겠습니다.
  • 만약 이미 숫자가 들어있다면 현재 들어있는 숫자에 각각 1, 2, 3을 더하도록 해보죠.
• 두번째 스크립트는 A2 ~ C2 에 A1 + A1, B1 + B1, C1 + C1 까지의 값을 넣는 동작을
• 세번째 스크립트는 A3 ~ C3 에 A1 * A2, B1 * B2, C1 * C2 의 값을 넣는 동작을
• 네번째 스크립트는 A4 ~ C4 에 C1 ~ C3 의 값을 이용하여 셀의 색상을 채워 넣어 보겠습니다.
  • 컬러를 지정하기 위한 숫자는 RGB 로 각각 0~255만 가능하므로 255 보다 크면 255로 설정 하겠습니다.
  • 그리고 255보다 값이 커진다면 첫번째 행의 값을 낮은 값으로 바꾸어 보겠습니다.

 

스크립트를 작성해 보겠습니다.

먼저 엑셀을 열고 VBA 창 (alt + F11) 을 실행한 뒤 아래와 같이 입력해 보겠습니다.

Sub function_a()
    
    'cells(rowIndex, columnIndex) 와 같은 형식으로 셀을 지정하는 방법'
    If Len(Cells(1, 1)) Then
        Cells(1, 1).Value = Cells(1, 1).Value + 1
    Else
        Cells(1, 1).Value = 1
    End If
    
    If Len(Cells(1, 2)) Then
        Cells(1, 2).Value = Cells(1, 2).Value + 2
    Else
        Cells(1, 2).Value = 2
    End If
    
    If Len(Cells(1, 3)) Then
        Cells(1, 3).Value = Cells(1, 3).Value + 3
    Else
        Cells(1, 3).Value = 3
    End If

End Sub

Sub function_b()
    
    ' Range(name) 의 형태로 셀을 지정하는 방법'
    
    Range("A2").Value = Range("A1").Value * 2
    Range("B2").Value = Range("B1").Value * 2
    Range("C2").Value = Range("C1").Value * 2
    
End Sub

Sub function_c()
    
    ' range 라는 변수를 설정하여 정의하는 방법'
    
    Dim a3, b3, c3
    
    Set a3 = Range("A3")
    Set b3 = Range("B3")
    Set c3 = Range("C3")
    
    a3.Value = a3.Offset(-2, 0).Value * a3.Offset(-1, 0).Value
    b3.Value = b3.Offset(-2, 0).Value * b3.Offset(-1, 0).Value
    c3.Value = c3.Offset(-2, 0).Value * c3.Offset(-1, 0).Value
    
End Sub

Sub function_d()

    Dim a3, b3, c3, rngAll
    
    Set a3 = Range("A3")
    Set b3 = Range("B3")
    Set c3 = Range("C3")
    
    Set rngAll = Range("A4", "C4")
    
    If a3.Value > 255 Then
        a3.Value = 255
        a3.Offset(-2, 0).Value = 1
    End If
    
    If b3.Value > 255 Then
        b3.Value = 255
        b3.Offset(-2, 0).Value = 2
    End If
    
    If c3.Value > 255 Then
        c3.Value = 255
        c3.Offset(-2, 0).Value = 3
    End If
    
    rngAll.Interior.Color = RGB(a3.Value, b3.Value, c3.Value)
    
End Sub

 

Sub ~ End Sub 까지가 하나의 매크로 스크립트가 되겠습니다. 총 4개의 스크립트가 작성이 되었는데요. 내용은 위에서 기술한 내용데로 작성하였으며 각각의 스크립트에는 셀을 지정하는 방법을 달리하여 작성해 보았습니다. 여러분들도 직접 타이핑 해보시면서 어떤방법으로 셀을 지정하는 것이 편리한지 경험해보시는 것이 도움이 되실 것 같습니다.

끝으로 rngAll 이라는 Range 개체를 만들었는요 현재 시트의 4번째 행의 A~C 열을 한꺼번에 설정해주었습니다. 그리고 한꺼번에 해당 셀의 색상을 변경을 하는 코드를 넣어 봤습니다.

 

자 스크립트를 모두 작성하였으면 이제 단추를 만들어 보아야 겠죠? 

그런데 엑셀의 기본 기능 중에는 단추가 보이지 않습니다. 리본 메뉴가 만들어 지면서 사용하지 않는 기능들은 꺼내져 있지 않기 때문인데요. 사용하기 위하여 꺼내 보겠습니다. 처음 한번만 하시면 됩니다.

파일 메뉴로 가셔서 아래와 같은 창이 나오면 제일 아래 옵션을 선택합니다.

엑셀에서 파일 메뉴를 진입한 화면

그럼 옵션창이 팝업으로 뜨게 됩니다.

팝업으로 뜬 창에서 '리본 사용자 지정' 텝으로 이동해보면 아래와 같이 아주 많은 기능이 나열된 것을 볼 수 있습니다.

위 그림에서 좌측은 엑셀이 가지고 있는 모든 기능을 보여주고 있고 우측은 현재 사용자의 리본메뉴에 추가되어 있는 기능들이라고 생각하시면 되겠습니다.

아래 1번의 드롭다운 메뉴에 리본에 없는 메뉴를 선택하신 뒤 리스트에서 2번 '단추(양식컨트롤)' 이 바로 우리가 필요한 단추를 넣는 기능입니다. 단추라고 하면 좀 이상한데요, 익숙한 용어로는 버튼입니다. 버튼.

우측은 해당 기능을 넣을 리본 메뉴라고 했는데요, 저는 삽입 이라는 탭에 넣을 계획 입니다. 이미 있는 그룹에 넣는 것은 불가능하고 새로운 그룹을 만들어서 넣어야 하기 때문에 3번 삽입 을 선택하여 메뉴를 확장 시킨 뒤 아래 '새 그룹' 이라는 버튼을 이용하여 새로운 그룹을 추가해 줍니다.

엑셀의 옵션창 버전마다 약간의 차이는 있다

새로 추가한 그룹의 이름을 바꾸어 볼 차례 입니다. 저는 제 블로그 이름인 "DIY_DEV_DESIGN" 라고 변경하였습니다. 아래 이름 바꾸기라는 버튼을 이용하여 변경하시면 됩니다.

추가한 그룹의 이름을 바꾸어준 상태

 

이제 단추를 선택한뒤 가운데에 있는 "추가>>" 를 이용하여 방금 만든 그룹에 추가를 해 보겠습니다.

새로 추가한 그룹에 단추 명령을 넣은 상태

자 이제 단추를 추가하는 기능은 완료가 되었습니다. 확인 버튼을 눌러 옵션 창을 닫고 엑셀의 리본메뉴를 살펴보면 아래와 같이 단추가 추가되어 있을 것입니다.

 

이제 해당 버튼을 누르게 되면 엑셀 시트에 단추를 만들수 있게 됩니다.

만드는 방법은 아래와 같습니다. 

1. 단추 버튼을 누른다
2. 원하는 위치에 마우스를 클릭한뒤 드래그 하여 필요한 크기가 되면 마우스 버튼을 뗀다
3. 팝업으로 나오는 매크로 선택창에서 아까 만들어둔 스크립트를 선택해 준다.
4. 이미 만들어진 단추의 크기나 위치, 레이블을 수정하기 위하여는 마우스 우클릭으로 선택해주면 됩니다.

 

첫번째 단추를 추가하였다.

총 4개의 스크립트를 연결할 것이므로 4개의 스크립트를 차례로 연결해 보겠습니다

 

이제 위의 버튼부터 차례로 눌러 볼까요?

버튼을 누를때마다 해당 스크립트가 동작한다

네 잘 동작되네요.

만약 스크립트를 수정해야 하면 해당 버튼을 마우스 우클릭한 뒤 "매크로 지정" 기능으로 이동하여 설정되어 있는 매크로를 다른 메뉴로 변경하거나 선택되어 있는 매크로에서 "편집" 버튼을 눌러주게 되면 해당 매크로 스크립트를 바로 편집할 수 있는 비주얼 베이직에디터가 나타나게 됩니다.

 

자 이렇게 해서 엑셀에 버튼을 추가하고 스크립트를 연결하는 방법을 알아 보았습니다.

설정창에서 단추 기능을 꺼내는 단계부터 처음하시는 분은 조금 복잡하다고 생각하실 수도 있겠는데요. 막상 해보시면 정말 간단한 작업이고 어렵지 않습니다.

또한 이렇게 단추로 만들어진 스크립트는 매우 실행하기 편리하고 직관적이죠. 

자 여러분도 이제 엑셀을 이용하여 툴을 만들어 보시는 겁니다.

도전해 보시지요.