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

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

안녕하세요 오튜가족 여러분!

벌써 추석이 다음 주입니다. 이번 추석은 휴일이 길어 여느 추석보다 반갑군요. 모두 좋은 추석이 되시길 미리 인사드립니다.

지난 주말에는 PC를 하나 장만하려고 서초동의 국제전자쎈터를 다녀왔습니다. 용산이나 테크노마트 등을 제쳐두고 거길 가는 이유는 사람이 적기 때문에 천천히 다리품 팔며 여유있게 구경할 수 있기 때문이죠. 그리고 거기엔 단골가게가 있습니다. PC가격이 다른 곳에 비해 싼 것인지 아닌지 모르겠지만 몇 번 PC를 주문해보니 PC가 안정적이라 믿고 맡기죠. 또 9층에는 예원서점이라는 곳이 있는데 온라인 뿐만 아니라 직접 찾아가서 사도 책값을 깍아 주기 때문에 저의 단골코스이죠. 잠시 얘기가 딴 곳으로 샜군요!

거기에서 PC카메라를 두 개 샀습니다. 하나는 제가 쓰고 나머지는 분당에 설치하여 부모님과 얼굴보며 얘기를 해볼려구요. 이게 잘 될련지 모르겠습니다. 지방에 부모님이 계신 분들은 여건이 허락된다면 이번 추석에 내려가셔서 이런 걸 한번 해보시는 것이 어떨련지…

오늘은 지난 번 엑셀의 옵션대화상자에서 조정할 수 있는 옵션중 [행&열머리글]과 [R1C1참조유형] 옵션을 프로그래밍으로 다룰 수 있는 코드를 먼저 소개하겠습니다. 사실 옵션만 다를 뿐이지 지난 번의 [셀구분선]옵션을 다루는 것과 다른 게 없어 큰 설명을 들릴 것은 없습니다.

행열머리글이란 시트의 열과 행에서 A,B,C,D,… 1,2,3,4,…등으로 표시된 곳을 말합니다. 경우에 따라서는 더 많은 셀을 보기 위해 이걸 감추거나 자신의 프로그램을 만들때 미관상 보기싫거나 프로그램상 필요한 부분을 사용자가 행이나 열머리글을 선택하고 지울지 않을까 싶어 행열머리글을 숨기기도 합니다(그래도 막을 수는 없겠지만)

ActiveWindow.DisplayHeadings
Application.ReferenceStyle

위에서 보시다시피 DisplayHeadings라는 속성은 워크시트에 속하는 속성이 아닙니다. 현재창을 가리키는 ActiveWindow개체에 속하는 것입니다. 지난 시간에 설명드린 DisplayGridlines 역시 ActiveWindow개체 소속입니다. 이러한 속성을 보면 워크시트는 추상적인 것입니다. 존재하지만 눈에 보이지 않는 것입니다. 다만 이걸을 보려면 ActiveWindow개체를 통해서 보는 것이며 표시하는 방법 역시 ActiveWindow개체의 속성에 따르는 것입니다. 그림을 통해 설명드리자면 이런거죠.

위의 그림을 보시면 오른쪽의 하나의 워크북의 "Sheet1"이라는 워크시트를 두 가지의 방법으로 보고 있습니다. 하나의 행열머리글과 셀구분선을 가지고 있으며 나머지 하나는 행열머리글과 셀구분선을 숨기고 있습니다. 동일한 자료( "1", "2", "3" )를 가지고 있지만 창의 표시방법을 달리한 것입니다. 그러나 다음에 나오는 속성인 . ReferenceStyle은 ActiveWindow 개체가 아닌 Application 개체의 것입니다. 이것은 수식계산과 관련된 것이고 수식계산의 주체는 통합문서나 워크시트가 아닌 엑셀 즉 Application 개체이기 때문입니다(저의 주관적인 생각입니다)

행열 머리글

Public Sub ShowHideHeading()
     Dim cmdBtn As CommandBarButton
     Dim cmdbarPopup As CommandBarPopup

     ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings

     Set cmdbarPopup = Application.CommandBars("Worksheet Menu Bar") _
          .FindControl(Type:=msoControlPopup, Tag:=USER_TAG)
     Set cmdbarPopup = cmdbarPopup.Controls("행과 열")

     For Each cmdBtn In cmdbarPopup.Controls
          If cmdBtn.Caption = "행열머리글" Then
               If ActiveWindow.DisplayHeadings Then
                    cmdBtn.State = msoButtonDown
               Else
                    cmdBtn.State = msoButtonUp
               End If
               Exit For
         End If
     Next
     Set cmdBtn = Nothing
     Set cmdbarPopup = Nothing
End Sub

R1C1참조유형이란 셀주소표기방식을 의미합니다. 흔히 볼 수 있는 $A$1같은 것을A1참조라고 하고 R8C3같은 것을 R1C1참조라고 합니다. 프로그래밍할때 Cell(…)을 자주 사용하는 경우 H열이 몇번째인가 일일이 세어보곤 합니다. 그래서 R1C1참조유형으로 표시하기도 합니다.

R1C1참조유형

Public Sub R1C1Reference()
     Dim cmdBtn As CommandBarButton
     Dim cmdbarPopup As CommandBarPopup

     Application.ReferenceStyle = IIf(Application.ReferenceStyle = xlR1C1,
     xlA1, xlR1C1)

     Set cmdbarPopup = Application.CommandBars("Worksheet Menu Bar") _
          .FindControl(Type:=msoControlPopup, Tag:=USER_TAG)
     Set cmdbarPopup = cmdbarPopup.Controls("행과 열")

     For Each cmdBtn In cmdbarPopup.Controls
          If cmdBtn.Caption = "R1C1참조유형" Then
               If Application.ReferenceStyle = xlR1C1 Then
                    cmdBtn.State = msoButtonDown
               Else
                    cmdBtn.State = msoButtonUp
               End If
               Exit For
          End If
     Next
     Set cmdBtn = Nothing
     Set cmdbarPopup = Nothing
End Sub

이제는 이름을 삭제하는 기능을 살펴보도록 하겠습니다. 여기서 말하는 이름이란 셀범위를 참조하기 위해 주소 대신 사용자가 지정한 이름을 말하는 것입니다. 우리가 만들려는 이름 삭제기능은 통합문서내 이름을 지우는 것입니다. 먼저 이름이 어떻게 만들어 지고 어떻게 삭제되는 가를 매크로기록기를 통해 알아보겠습니다.

매크로기록기로 만든 이름정의와 이름삭제 매크로

Sub 매크로5()
'
' 매크로5 Macro
' 승주이(가) 01-09-24에 기록한 매크로
'

' 이름정의 매크로
ActiveWorkbook.Names.Add Name:="오피스튜터도구", RefersToR1C1:="=Sheet1!R2C3"
' 이름삭제 매크로
ActiveWorkbook.Names("오피스튜터도구").Delete
End Sub

일단 위의 코드에서 보듯이 이름을 가리키는 것은 Names라는 개체가 사용된 것을 볼 수 있으며 이것은 통합문서( Workbook )의 개체입니다. Name을 추가하는 메소드는 .Add입니다. 그리고 Name을 하나 만들 때 필요한 정보가 2개 있습니다. 하나는 Name의 이름이고 나머지는 어떤 영역을 이름으로 지정할 것인가 입니다. 그래서 .Add메소드를 보면 Name 인수와 RefersToR1C1라는 인수를 지정해야 합니다. 그러나 이것은 하나 하나의 셀 영역에 이름을 각각 지정하는 경우입니다. 엑셀의 이름메뉴에서 보면 선택한 영역의 각 셀에 이름을 한번에 이름을 지정하는 [작성]메뉴명령이 있습니다. 이것을 매크로기록기로 살펴보도록 하죠.

[삽입]-[이름]-[작성]명령으로 이름을 작성하는 경우 기록한 매크로

Sub 매크로6()
'
' 매크로6 Macro
' 승주이(가) 01-09-25에 기록한 매크로
'

'
     Range("B3:C10").Select
     Selection.CreateNames Top:=False, Left:=True, Bottom:=False, Right:= _
          False
End Sub

위의 매크로는 "B3:C10"영역을 선택하고 Selection개체의 .CreateNames 메소드를 사용하고 있습니다. 선택영역의 왼쪽(B열)을 이름으로, 오른쪽(C열)을 참조영역으로 사용하는 것이죠

그러면 통합문서의 이름을 지우는 코드를 살펴보죠

통합문서의 이름을 지우는 코드

Sub DeleteNames()
     Dim nmeUser As Name

     For Each nmeUser In ActiveWorkbook.Names
          nmeUser.Delete
     Next
End Sub

생각보다 간단합니다. Naem개체변수를 만들고 Names컬렉션을 For Each~Next루프를 돌면서 .Delete 메소드를 사용하고 있습니다. 물론 작업대상은 ActiveWorkbook입니다.

오늘은 여기까지입니다. 즐거운 추석을 보내시기 바랍니다.

목차 | 이전 | 다음