엑셀, 곽승주님의 오튜공구함 제작으로 배워보는 VBA 이야기, Excel

자료다운로드 : 오튜공구함003.xls

날이면 날마다 오는 것은 아니고 일주일마다 찾아 가는 곽승주입니다.
26일은 CIH바이러스가 활동하는 날인 거 다 아시죠? 여러분들의 PC는 모두 안녕하십니까? 예전에 저도 제 PC가 미켈란젤로 바이러스에 감염된 적이 있는데, 사실 이게 바이러스탓인지, 기계고장인지, 아니면 마구니(?) 같은 프로그램 탓인지 몰라 당황했었습니다. 얼마전에는 Loveletter라든가 Naked Wife같은 VBS스크립트바이러스가 한동안 요란했는데 인터넷을 통해 바이러스소스를 쉽게 구할 수 있어 더욱 문제죠. MS오피스를 사용하는 저나 여러분들에게는 매크로바이러스가 요주의 대상이죠. 사실 저는 그 바이러스가 끼치는 피해보다는 "대체 이걸 어떻게 만든 거야..." 궁시렁거리면서 프로그램의 원리를 먼저 궁금해합니다. 이 정도면 병이랄 수 밖에 없군요.

오늘은 단축메뉴를 만드는 방법을 선보이려 합니다. 단축메뉴는 마우스오른쪽 버튼을 누르면 나오는 메뉴를 말합니다. 오늘은 이 단축메뉴에 우리의 오튜공구함 메뉴를 삽입해보겠습니다. 옆의 그림을 보시면 우리가 자주 보던 단축메뉴입니다. 그리고 자세히 보시면 단축메뉴의 상단에 "About OT-Tools"라는 메뉴아이템을 보실 수 있을 겁니다. 우선 단축메뉴에 관련한 소스를 먼저 보시죠.

모듈:user_menu_short

Option Explicit

Const USER_SHORTCUT_TAG As String = "UserShortCut"

Sub CreateShortCutMenu()
     DeleteShortCutMenu
     With Application.CommandBars("cell").Controls _
        .Add(Type:=msoControlButton, before:=1, temporary:=True)
        .Caption = "About OT-Tools"
        .OnAction = "ShowSplash"
        .Tag = USER_SHORTCUT_TAG
     End With
End Sub

Sub DeleteShortCutMenu()

Dim cmdShortCut As CommandBarControl

     For Each cmdShortCut In Application.CommandBars("cell").Controls
        If Not cmdShortCut.BuiltIn Then cmdShortCut.Delete
     Next
     Set cmdShortCut = Nothing
End Sub

Sub ShowSplash()
     frmSplash.Show
End Sub

의외로 간단하죠. 사용자정의 단축메뉴 역시 앞서 만든 메뉴나 도구모음처럼 CommandBars 컬렉션개체를 사용하여 만듭니다.

Const USER_SHORTCUT_TAG As String = "UserShortCut"
사용자정의 단축메뉴에 대한 꼬리표입니다. 이미 앞서의 컬럼에서도 충분히 설명하였으므로 생략하도록 하겠습니다.

Sub CreateShortCutMenu()
     DeleteShortCutMenu
사용자정의 단축메뉴를 만들기 전에 삭제를 해봅니다. 혹시 단축메뉴가 이미 있는 상태에서 추가하는 불상사를 막기 위한 것입니다.
     With Application.CommandBars("cell").Controls _
        .Add(Type:=msoControlButton, before:=1, temporary:=True)

CommandBars 컬렉션개체의 "CELL" 개체에 컨트롤을 추가합니다, 타입이나 위치등은 앞서의 도구모음이나 메뉴와 경우와 다르지 않아 설명은 생략합니다.
        .Caption = "About OT-Tools"
사용자정의 단축메뉴 아이템의 이름을 정합니다. 단축메뉴에는 선택한 셀에 대한 직접적인 작업을 위한 기능을 추가하는 것이 좋습니다. 위의 단축메뉴그림을 보면 엑셀이 기본적으로 제공하는 잘라내기,복사,붙여넣기,셀서식등은 모두 셀과 관련한 기능입니다. 오튜공구함에는 현재 구현해놓은 기능이 없는 관계로 스플래시화면을 보여주는 것으로 기능만 넣었습니다. 나중에 셀과 관련한 기능이 만들어지면 단축메뉴에 추가하도록 하겠습니다.

        .OnAction = "ShowSplash"
스플래시화면을 보여주는 프로시져입니다.
        .Tag = USER_SHORTCUT_TAG
     End With
End Sub

이제 사용자정의 단축메뉴를 삭제하는 부분입니다.
Sub DeleteShortCutMenu()
Dim cmdShortCut As CommandBarControl

단축메뉴의 아이템은 역시 개체입니다. 이런 개체를 되돌려 받기 위한 개체변수를 선언하였습니다.
     For Each cmdShortCut In Application.CommandBars("cell").Controls
        If Not cmdShortCut.BuiltIn Then cmdShortCut.Delete
이번의 삭제에서는 FindControl 메소드나 Tag 속성 대신 Builtin이라는 속성을 사용하여 지웠습니다. 지난 컬럼에서도 잠깐 언급하였지만 엑셀의 내장개체인가 아니면 사용자정의 개체인가를 알려줍니다, 그러나 FindControl 메소드나 Tag 속성을 이용하여 삭제하는 것과 Builtin속성을 이용하여 삭제하는 것에는 약간의 차이가 있습니다. 전자의 경우에는 사용자가 지정한 조건에 따라 지우는 경우이다. 따라서 내가 만든 기능만 삭제할 수 있지만 후자의 경우에는 엑셀에 내장된 것인가 아닌가만 구별하므로 이를 판단하여 지우게 되면 내가 만든 개체뿐만 아니라 다른 사람이 만든 엑셀프로그램의 개체까지 지우게 된다.

     Next
     Set cmdShortCut = Nothing
End Sub

Sub ShowSplash()
     frmSplash.Show
End Sub

스플래시 화면을 보여주는 프로시져입니다

지난 컬럼의 첨부파일에 말도 없이 슬그머니 삽인한 것이 있는데 스플래시폼입니다. 스플래시폼은 별다른 기능은 없습니다. 다만 프로그램의 제목과 경우에 따라서는 엑셀처럼 시스템정보를 보여줄 수 있지만 여기서는 간단하게 구성하였습니다.

위의 모듈과 관련하여 이를 참조하는 부분도 바뀌어야 할 것입니다.

ThisWorkbook개체의 코딩

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     DeleteUserToolbar
     DeleteShortCutMenu
     DeleteUserMenu
End Sub

Private Sub Workbook_Open()
     CreateUserMenu
     CreateShortCutMenu
     CreateUserToolbar
End Sub

Sub Workbook_BeforeClose()에는 삭제하는 프로시져가, Sub Workbook_Open()에는 사용자정의 단축메뉴를 만드는 프로시져가 추가되었습니다.

위의 프로젝트와는 별개인지만 사용자정의 단축메뉴와 관련하여 사용자정의 단축메뉴가 특정 워크시트에서만 나타거나 아니면 특정한 범위에서만 나타나도록 하려면 다음과 같이 코딩합니다.

사용자정의 단축메뉴가 특정 워크시트에서만 사용되는 예제

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)

     Select Case Target.Worksheet
     Case "sheet1", "Sheet2"
        DeleteShortCutMenu
        Exit Sub
     Case Else
        CreateShortCutMenu
     End Select

End Sub

사용자정의 단축메뉴가 특정 범위에서만 사용되는 예제

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
     DeleteShortCutMenu
     If Not Application.Intersect(Target, Range("A:C")) Is Nothing Then
        CreateShortCutMenu
     End If
End Sub

이러한 기능은 Sub Workbook_SheetBeforeRightClick() 프로시져를 이용하는 것입니다. 이프로시져는 워크시트에서 마우스 오른쪽버튼을 클릭하면 발생하는 Workbook의 이벤트입니다.
자세한 설명은 여러분에게 맡겨 두겠습니다. 제가 일일이 설명하는 것보다는 여러분이 공부해보시는 것이 더욱 학습효과가 좋을 것 같군요. 저에게 해설을 보내주시면 보내주시는 분의 글을 소개해드리겠습니다.

컬럼을 진행하다보니 문득 프로그램이름이 오튜공구함인데 현재 기능(만들어 지지 않았지만)은 엑셀만을 위한 것임을 깨달았습니다. 적어도 오튜공구함이라면 워드나 파워포인트를 위한 것도 있어야 하지 않을 까 싶군요. 현재는 엑셀을 위한 것을 작성하고 있지만 나중에는 워드나 파워포인트 등등 다른 오피스제품을 위한 오튜공구함을 만들어야 할 것 같습니다.

다음 시간에는 워크시트 내 중복된 데이터를 처리하는 기능을 먼저 소개 할까 합니다. 엑셀의 필터등을 사용하지 않고 만들어 보았습니다. 그럼 다음 시간에 뵙죠!

목차 | 이전 | 다음