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

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

안녕하세요.
지난 번에는 강의에 너무 몰두하다 보니 이런저런 얘기를 못한 것 같군요.

조만간 MS오피스XP 버전이 출시될려는 모양입니다. 여기저기서 XP얘기를 하더군요. XP의 새로운 기능에 대해 많이 들어보셨는지… 제가 궁금한 것은 여러분들이 XP로 업그레이드 하실 의향이 있는지 입니다. 물론 제가 XP파는 사람은 아니지만 여러분의 생각이 저의 비슷한지 알고 싶군요. 제생각이요? 나중에 가르쳐 드리죠. 만일 제가 긍정적으로 아니면 반대로 부정적으로 얘기하면 생각이 바뀌실 분도 있을 지도 모르니까요? 아쉬운게 있다면 한글판 오피스 XP버전은 음성지원기능이 빠져 있다더군요. 아직 MS가 필요로 하는 기술을 가진 벤더를 찾지 못해 빠져 있다던데, 이 기능이 된다면 키보드 두드리지 않고 원고를 쓸 수 있지 않을 까 싶군요.

XP와 관련하여 한 가지 얘기를 하자면 마이크로소프트(MS)오피스 XP에서는 오피스길잡이가 사라질 전망입니다.
아무 때나 불쑥 튀어나와 도움말을 주겠다고 자청하는 귀찮은 존재인 오피스 길잡이를 영어로는 '클리피'(Clippy)라고 합니다. 사실 얘가 모양이 우스워서 "이게 뭐야?"했지만 종이를 묶어 두는 클립입니다. 사실 얘 말고도 이쁜이, 돌고래, 아인슈타인박사등이 길잡이로 나섰지만 어는 것 하나 사랑받지 못하고 우리의 기억속에 귀찮아서 얼른 숨겨버리는 존재입니다(불쌍하죠?)
외국언론들은 "MS도 이러한 사정을 아는 듯 클리피를 해고한다"고 보도하는데 "해고"라는 표현이 제법 재미있습니다. 그럼 아래아 한글에서 채용시켜 줄려나?

요즘 국내건 외국이건 닷컴기업(일명 쩜컴기업)의 화두는 서비스유료화입니다. 무료에 길들여져 대머리가 되어버린 네티즌들의 지갑을 어떻게 벌릴 수 있을까요? 가장 유망한 콘텐츠는 게임과 교육분야입니다. 저 같은 사람도 온라인교육에 관심이 있거든요. 학원가기엔 너무 시간이 없고 돈이 많이 들다보니 제대로된 온라인교육에 눈길이 가더군요. 콘텐츠유료화에 대한 이런저런 얘기를 듣다보니 가장 가까운 쩜컴기업인 오피스튜터는 어떤지 모르겠군요, 묻고답하기를 유료화하면 누가 감히 물어볼까? 아니면 1년에 만원씩 회비를 받으면 어떨까? 오튜를 보면 기업이 아니라 동호회 성격이 짙은데 수수료보다는 회비라는 이름이 어울릴 것 같군요 그러면 서버도 큼직한 거 사고 직원들 월급도 더 주어 서비스를 개선할 수 있지 않을까? 1년에 만원으로는 어림도 없으려나? ^.^;

자 이제 얘기는 그만하고 강의를 하죠. 강의에 돈 안받으니까 떨지 마세요( ^^ )
엑셀을 비롯하여 다른 마이크로소프트 오피스 응용프로그램들은 메뉴와 도구모음 작성에 대해 같은 방법을 사용하고 있습니다. 즉 오피스 어느 제품에서나 CommandBars개체를 이용하여 메뉴, 도구모음, 팝업메뉴를 만들 수 있습니다. 메뉴는 지난 시간에 이미 다루었고, 팝업메뉴는 다음 시간에 다룰 것입니다. 오늘은 도구모음에 대해서만 알아보도록 하겠습니다.

본격적인 제작에 앞서 CommandBars개체를 가지고 엑셀의 메뉴체계를 탐험해보도록 하겠습니다. 사실 지난 시간에 메뉴를 작성하기 전에 이걸 먼저 해야 했는데, 마음이 급하다 보니 경황이 없었습니다.
아래의 프로시져는 CommandBars컬렉션개체를 탐색하고 그 이하의 Control개체를 보여주는 것입니다. 출력된 내용을 보시면 메뉴의 이름과 각종 도구모음의 이름 등이 보일 것입니다.

Sub NavigateCommandBars()
     Dim cmdbarNavigator As CommandBar
     Dim cmdctlControl As CommandBarControl

     For Each cmdbarNavigator In Application.CommandBars
        Debug.Print cmdbarNavigator.Name
        For Each cmdctlControl In cmdbarNavigator.Controls
           Debug.Print cmdctlControl.Caption
        Next
     Next
End Sub

여러분의 도구모음을 기존의 도구모음에 추가하려면 cmdbarNavigator.Name을 참고해야 합니다. 예를 들어 표준도구모음의 이름은 "Standard "입니다. 오튜공구함 버튼을 표준도구모음에 만들려면 다음과 같습니다.

'표준 도구모음의 컨트롤수를 돌려준다.
Private Function FindNumOfBtn() As Byte
     FindNumOfBtn = CommandBars("Standard").Controls.Count
End Function

'표준 도구모음에 "OT-Tools"를 추가한다.
Public Sub CreateUserCmdBarBtn()
     Dim cmdbtnUser As CommandBarButton

     Set cmdbtnUser = CommandBars("Standard").Controls.Add(Type:=msoControlButton, Before:=FindNumOfBtn())

     With cmdbtnUser
        .Style = msoButtonCaption
        .FaceId = 17
        .Caption = "OT-Tools"
        .OnAction = "CommandBar"
        .BeginGroup = True
     End With

     Set cmdbtnUser = Nothing
End Sub
'표준 도구모음에서 "OT-Tools"를 삭제한다.
Public Sub DeleteUserCmdBtn()
     Dim cmdbtnUser As CommandBarControl

     For Each cmdbtnUser In CommandBars("Standard").Controls
        If cmdbtnUser.Caption = "OT-Tools" Then
           cmdbtnUser.Delete
        End If
     Next cmdbtnUser
End Sub

삭제와 관련하여 좀더 세련한 기법을 보여드리죠!
다음은 보이지 않는 모든 사용자 정의 명령 표시줄을 삭제하는 예제입니다.
For Each bar In Application.CommandBars
If Not bar.BuiltIn And Not bar.Visible Then bar.Delete
Next
BuiltIn속성은 엑셀의 내장개체인가 아니면 사용자가 만든 개체인가를 True/False값으로 돌려줍니다. 또한 Visible속성은 현재 화면상에 표시가 되는 가를 확인하는 것이죠.

이제부터는 워밍-업을 끝내고 본격적인 코딩에 들어가겠습니다. 먼저 코드를 보여 드리죠.
　

모듈 : user_toolbarOption Explicit

Const USER_TAG As String = "UserToolbar"
Const CBO_TAG As String = "UserCombo"
Const TOOL_NAME As String = "오튜공구함"

Sub CreateUserToolbar()
     Dim cmdbarUser As CommandBar

     On Error GoTo ErrHandler

     Set cmdbarUser = Application.CommandBars.Add(Name:=TOOL_NAME, Position:=msoBarTop)

     CreateUserButton cmdbarUser
     CreateUserCombo cmdbarUser

     cmdbarUser.Visible = True
     Set cmdbarUser = Nothing
     Exit Sub

ErrHandler:
     DeleteUserToolbar
     Resume
End Sub

Sub DeleteUserToolbar()
     Dim cmdbarUser As CommandBar

     On Error GoTo ErrHandler

     Set cmdbarUser = Application.CommandBars(TOOL_NAME)
     cmdbarUser.Delete
     Set cmdbarUser = Nothing
     Exit Sub

ErrHandler:
     Exit Sub
End Sub

Sub CreateUserButton(cmdbarUser As CommandBar)
     Dim i As Byte
     Dim cmdctlUser As CommandBarButton
     Dim NameFaceIDMacroOfCmdBtn

     NameFaceIDMacroOfCmdBtn = Array( _
     "otHELP", 49, "sbShowHelp", False, _
     "otABOUT", 625, "sbShowAbout", True, _
     "otEXIT", 358, "sbExit", True _
     )

     For i = LBound(NameFaceIDMacroOfCmdBtn) To UBound(NameFaceIDMacroOfCmdBtn) Step 4
        Set cmdctlUser = cmdbarUser.Controls.Add(Type:=msoControlButton, before:=1)
        With cmdctlUser
           .Caption = NameFaceIDMacroOfCmdBtn(i)
           .FaceId = NameFaceIDMacroOfCmdBtn(i + 1)
           .OnAction = NameFaceIDMacroOfCmdBtn(i + 2)
           If NameFaceIDMacroOfCmdBtn(i + 3) Then .BeginGroup = True
        End With
      Next i
      Set cmdctlUser = Nothing
End Sub

Sub CreateUserCombo(cmdbarUser As CommandBar)
     Dim cmdctlUser As CommandBarComboBox

     Set cmdctlUser = cmdbarUser.Controls.Add(Type:=msoControlComboBox, Id:=1, before:=1)
     With cmdctlUser
        .Caption = "Date-Format Selector"
        .OnAction = "cmdcboUserAction"
        .BeginGroup = True
        .AddItem Text:="Select any Date-Format"
        .AddItem Text:="YYYY-MM-DD"
        .AddItem Text:="YY-MM-DD"
        .AddItem Text:="YY.MM.DD"
        .Width = 150
        .ListIndex = 1
        .Tag = CBO_TAG
     End With
     Set cmdctlUser = Nothing
End Sub

Sub cmdcboUserAction()
     Dim cmdctlUser As CommandBarComboBox

     Set cmdctlUser = Application.CommandBars(TOOL_NAME) _
        .FindControl(Type:=msoControlComboBox, Tag:=CBO_TAG)
     MsgBox "You Selected! " & vbCrLf & cmdctlUser.Text, vbInformation
End Sub

위에서 중심이 되는 프로시져는 가장 먼저 나오는 Sub CreateUserToolbar( )입니다. 여기서는 기본이 되는 도구모음을 만들고 그 도구모음위에서 버튼을 만드는 Sub CreateUserButton(cmdbarUser As CommandBar) 과 콤보박스를 만드는 Sub CreateUserCombo(cmdbarUser As CommandBar)를 호출합니다. 그게 전부입니다. 간단하죠.
　

목차 | 이전 | 다음