위의 그림을 보시면 1,2,3…으로 메뉴에 번호를 붙여 두었습니다. 도움말까지 모두 10개의 메뉴가 있으므로 bytBefore 는 10이 됩니다.

      bytRow = 2
Sheet1에서 첫번째 행은 필드명이 차지하고 두번째 행부터 데이터가 시작되므로 bytRow는 2로 지정합니다.

      Do Until IsEmpty(Sheet1.Cells(bytRow, 1))
Do Unitl~Loop문을 사용하여 True가 나올때 까지 즉, 데이터가 없을때 까지 루프를 돌리고 있습니다. IsEmpty()함수는 변수가 초기화되지 않거나, Empty로 설정되면 True를, 그렇지 않으면 False를 반환합니다. 이것은 다음과 같이 고쳐 쓸 수 있습니다
          Do Until Len(Sheet1.Cells(bytRow, 1))=0
Len( )함수는 문자열의 길이를 돌려줍니다. 따라서 문자열의 길이가 0이면 더 이상 데이터가 없는 것으로 판단해야 겠죠.

        With Sheet1
           usrMnu.mnuLvl = .Cells(bytRow, 1)
           usrMnu.mnuCaption = .Cells(bytRow, 2)
           usrMnu.mnuMacro = .Cells(bytRow, 3)
           usrMnu.mnuDivider = .Cells(bytRow, 4)
           usrMnu.mnuFaceID = .Cells(bytRow, 5)
           usrMnu.mnuState = .Cells(bytRow, 6)
           usrMnu.mnuNextLvl = .Cells(bytRow + 1, 1)
       End With

위부분은 Sheet1의 자료를 읽어와서 이미 선언한 사용자정의 변수인 usrMnu에 값을 할당하는 부분입니다. 마지막 부분(usrMnu.mnuNextLvl = .Cells(bytRow + 1, 1))을 보면 미리 다음행의 메뉴레벨을 읽어옵니다. 이게 왜 필요할까요? 위의 <MENU_LEVEL1 팝업메뉴바>의 그림을 보시면 알겠지만, 닫기(C) 같은 경우 하위메뉴가 없는 것에 비해 인쇄영역(T)의 경우 하위메뉴가 있습니다. 하위메뉴가 없다면 바로 실행매크로를 걸어 두어도 되지만 인쇄영역(T)처럼 하위메뉴가 있다면 하위메뉴를 만들고 난 뒤 실행매크로를 걸어 주어야 합니다. 따라서 다음 행의 메뉴레벨을 미리 알아둘 필요가 있습니다. 이 부분은 다음에 설명하겟습니다.

    Select Case usrMnu.mnuLvl
메뉴레벨에 따라 개체를 달리 사용해야 하므로 Select문에 usrMnu.mnuLvl을 판단하도록 했습니다.
    Case MENU_LEVEL0
워크시트메뉴바에 오튜공구함을 추가하는 부분.

<오튜공구함 메뉴를 워크시트메뉴바에 추가>

    Set cmdbarPopup =
   .Controls.Add(Type:=msoControlPopup,Before:=bytBefore)
Application.CommandBars("Worksheet Menu Bar")개체에 콘트롤을 타입과 위치를 지정하여 하나 추가한뒤 cmdbarPopup 개체변수에 할당합니다. cmdbarPopup 개체변수는 오튜공구함을 의미하는 것입니다.

      With cmdbarPopup
         .Caption = usrMnu.mnuCaption
         .BeginGroup = usrMnu.mnuDivider
         .Tag = USER_TAG
      End With
사용자메뉴를 추가하였다면 이제 이름도 붙이고 꼬리표도 붙여야겠죠. 이러한 작업을 하기 위해 앞에서 cmdbarPopup 개체변수에 추가된 콘트롤을 할당하는 것입니다.
● Caption속성에는 메뉴이름을 담고 있는 변수 usrMnu.mnuCaption을 할당합니다.
● BeginGroup 속성은 메뉴와 메뉴간에 구분자를 넣어 줄것인가 여부를 True/False로 정하는 것으로 usrMnu.mnuDivider값에 따라 구분자가 들어 가거나 말거나 하겠죠.
● Tag속성은 그자체만으로는 큰 의미는 없습니다. 다만 같은 부류의 개체에서 특별한 의미를 부여하기 위한 것입니다. 여기에서 Tag속성에 USER_TAG상수를 준 것은 나중에 이를 가지고 판단하여 이 메뉴를 삭제하기 위한 것입니다. 이부분은 Sub DeleteUserMenu( ) 프로시져에서 사용됩니다. 그때 다시 설명하죠.

   Case MENU_LEVEL1
두번째 단계의 메뉴를 구성하기 위한 부분입니다.

      If usrMnu.mnuNextLvl = MENU_LEVEL2 Then
앞에서도 이미 usrMnu.mnuNextLvl이 왜 필요한 가 설명한 바 있습니다. 위의 코드는 다음 레벨이 MENU_LEVEL2인 경우 즉 하위메뉴가 있는 경우입니다.

        Set cmdbarPup = cmdbarPopup.Controls.Add(Type:=msoControlPopup)
앞서에서 오튜공구함메뉴를 대표(대신)하는 개체는 무엇이었죠? cmdbarPopup입니다. 여기에 메뉴콘트롤을 추가해야 합니다. 따라서 cmdbarPopup.Controls컬렉션에 콘트롤을 추가합니다. 아래의 그림을 참고하세요.

<cmdbarPopup개체변수에 개체변수할당>

       With cmdbarPup
         .Caption = usrMnu.mnuCaption
         If usrMnu.mnuDivider <> 0 Then .BeginGroup = True
       End With
할당한 cmdbarPup개체변수에 이름(Caption)과 구분자를 정해줍니다. 여기서는 하위메뉴가 있기 때문에 실행매크로를 걸어 두진 않죠.

     Else
다음메뉴레벨이 마지막단계인 MENU_LEVEL2가 아니라면 더 이상의 하위메뉴가 없는 경우입니다. 따라서 개체변수를 달리 사용합니다. cmdbarPup개체변수가 아니라 cmdbarBtn개체변수에 값을 할당합니다. 따라서 바로 앞서와는 달리 실행매크로를 걸어두어야 합니다. 또 FaceID를 둘 수 있습니다. 그외 Caption이나 BeginGroup등의 속성에는 별다른 점은 없군요.

         Set cmdbarBtn = cmdbarPopup.Controls.Add(Type:=msoControlButton)
         With cmdbarBtn
            .Caption = usrMnu.mnuCaption
            .OnAction = usrMnu.mnuMacro
 
cmdbarBtn개체변수의 OnAction에는 실행매크로를 가지고 있는 usrMnu.mnuMacro변수를 할당합니다.

            If usrMnu.mnuDivider <> 0 Then .BeginGroup = True
            If usrMnu.mnuFaceID <> 0 Then .FaceId = usrMnu.mnuFaceID

위에선 FaceID속성에 값을 할당합니다. FaceID는 도구모음의 작은 아이콘그림입니다. 여러 종류가 있는데, 이를 확인해볼 수 있는 유틸리티가 있습니다. FACEIDS.XLA라는 것인데요, 제가 만든 건 아니고 JWalk & Associates(http://www.j-walk.com/ss/)에서 만든 것입니다.

           If Len(usrMnu.mnuState) <> 0 Then
             Select Case LCase(usrMnu.mnuState)
             Case "msobuttonup"
               .State = msoButtonUp
             Case "msobuttondown"
               .State = msoButtonDown
             Case "msobuttonmixed"
               .State = msoButtonMixed
             End Select
           End If
State속성은 명령 표시줄의 버튼컨트롤의 모양을 되돌리거나 설정합니다. 사용할 수 있는 MsoButtonState 상수에는 msoButtonUp, msoButtonDown, msoButtonMixed 등이 있습니다.

<State속성>

이는 토글(Toggle)명령을 걸어둘 때 사용합니다. 가령 셀구분선을 보이게 하거나 숨기거나 할 때, 행열머리글을 보이거나 숨기는 경우 유용하겠죠. 보이는 상태는 msoButtonDown으로 표시하고, 안보이는 경우엔 msoButtonUp으로 State상태를 표시합니다.

           End With
         End If
      Case MENU_LEVEL2

마지막 하위레벨의 메뉴인 경우입니다. 여기서 굳이 메뉴를 여기까지는 두는 이유는 되도록 메뉴구성을 단순하게 하는 것이 만드는 사람이나 사용하는 사람 모두 편하기 때문입니다. 명령 하나를 사용하기 위해 양파껍질 벗기듯 메뉴속에 메뉴, 다시 그속에 메뉴,메뉴,메뉴,…하면 얼마나 피곤하겠습니까?

     Set cmdbarBtn = cmdbarPup.Controls.Add(Type:=msoControlButton)
이제 다시 cmdbarBtn이라는 개체가 나왔군요. 앞서의 마찬가지이므로 긴 설명은 생략.

      With cmdbarBtn
         .Caption = usrMnu.mnuCaption
         .OnAction = usrMnu.mnuMacro
         If usrMnu.mnuDivider Then .BeginGroup = True
         If usrMnu.mnuFaceID Then .FaceId = usrMnu.mnuFaceID
         If Len(usrMnu.mnuState) <> 0 Then
           Select Case LCase(usrMnu.mnuState)
           Case "msobuttonup"
             .State = msoButtonUp
           Case "msobuttondown"
             .State = msoButtonDown
           Case "msobuttonmixed"
             .State = msoButtonMixed
           End Select
         End If
      End With
   End Select
   bytRow = bytRow + 1
이제 줄바꿔야줘!
   Loop

   End With

   Set cmdbarBtn = Nothing
   Set cmdbarPup = Nothing
   Set cmdbarPopup = Nothing
일반 변수와는 달리 개체변수는 Set구문으로 메모리를 할당했으면 다시 Nothing을 할당하여 메모리를 풀어줘야 합니다. 물론 프로시져가 종료되면 자동적으로 메모리가 해제되지만, 중간중간 더 이상 필요가 없는 개체변수는 프로시져 종료전이라도 이렇게 풀어줍니다.

End Sub
이제 메뉴를 만드는 프로시져를 종료합니다.

자 여기부터는 사용자정의 메뉴를 삭제합니다.

Sub DeleteUserMenu( )
   Dim cmdbarPopup As CommandBarPopup

   Set cmdbarPopup = Application.CommandBars("Worksheet Menu Bar") _
       .FindControl(Type:=msoControlPopup, Tag:=USER_TAG)
위의 구문에선 눈에 익은 게 보이는 군요. USER_TAG! 맞습니다. Sub CreateUserMenu()에서 사용했죠. FindControl메소드는 지정하는 조건에 맞는 CommandBarControl 개체를 되돌립니다. 여기서 지정한 조건이란 Tag를 의미합니다. CommandBars 컬렉션에 찾을 조건에 맞는 두 개 이상의 컨트롤이 있을 때 첫 번째 컨트롤을 검색 결과로 되돌립니다. 조건에 맞는 컨트롤이 없으면 Nothing을 되돌립니다.

   If Not cmdbarPopup Is Nothing Then
      cmdbarPopup.Delete
      Set cmdbarPopup = Nothing
   End If
End Sub

앞서의 설명처럼 Nothing인가 아닌가를 판단하여 우리가 찾는 개체를 확인합니다. 만약 찾았으면 Delete 메소드로 메뉴를 삭제합니다. 그리고 메모리를 풀어줍니다.
이를 다른 방법 좀 무식한 방법으로 해볼까요. Application.CommandBars("Worksheet Menu Bar")의 Controls컬렉션을 탐색하여 Tag를 확인하여 내가 찾는 것이면 개체를 삭제하는 것입니다.

Sub FindDeleteCmdBarPopup()
   Dim cmdbarPopup As CommandBarPopup

   For Each cmdbarPopup In Application.CommandBars("Worksheet Menu Bar").Controls
      If cmdbarPopup.Tag = USER_TAG Then
        cmdbarPopup.Delete
        Exit For
      End If
   Next
   Set cmdbarPopup = Nothing
End Sub

오늘은 여기까지입니다. 그럼 다음에….