VBA강좌

갑신년이 가고... 2005년 을유(乙酉)년 새해가 얼마남지 않았습니다. 한해 마무리 잘들 하시고, 다가오는 새해에는 바라는 모든 일들을 이루시기를 기원합니다.

새해 복 많이 받으세요!! ^^

VBA 입문강좌 : 중요한 오브젝트 몇 가지

가. 워크북 오브젝트(Workbook Object)

나. 워크시트 오브젝트(Worksheet Object)

다. 레인지 오브젝트(Range Object)

레인지 오브젝트에 대한 마지막 시간입니다. 앞서 설명드린 것 이외에도 레인지 오브젝트에 접근하는 방법은 더 있습니다만, 이번 시간에 소개해 드리는 것까지해서 10여 가지 남짓 되는 속성이나 메서드만 잘 이해하시면 거의 이해를 하셨다고 생각해도 좋을 것입니다. 자~~ 눈에 힘을 주고 시작해 볼까요?

역시나... 이번 강좌도 Exceller의 책 <VBA로 엑셀에 날개달기>에서 인용한 것입니다.

SpecialCells 메서드

셀(레인지 오브젝트)에는 여러 종류의 정보가 들어갈 수 있습니다. 상수^{변하지 않는 수}가 들어있는 셀, 내용이 아예 들어있지 않은 셀, 수식이 입력되어 있는 셀 등과 같이 말이지요. 이 중에서 특정한 타입의 영역을 선택해야 할 경우에 SpecialCells 메서드를 사용합니다. 이것은 워크시트 상태에서 '편집-이동' 메뉴를 선택한 다음, '옵션' 버튼을 클릭하면 나타나는 '이동 옵션' 대화상자를 통해 조작한 것과 같은 기능을 수행합니다.

Tip - '이동 옵션' 대화상자를 호출하는 단축키

워크시트 상태에서 <F5> 키나 <Ctrl + G> 키를 눌러도 '이동 옵션' 대화상자가 나타납니다.

다시 말해서 SpecialCells 메서드로 특정한 조건을 충족하는 셀을 지정하는 것은 '이동 옵션' 대화상자에서 특정한 조건을 지정하는 동작에 해당합니다. SpecialCells 메서드의 기본적인 사용법은 다음과 같습니다.

작업 대상 영역.SpecialCells (Type, Value)

<Type 인수>

상 수	셀 타입
xlCelltypeAllFormatConditions	표시 형식이 설정되어 있는 셀
xlCelltypeValidations	유효성 조건이 설정된 셀
xlCelltypeBlanks	빈 문자열이 들어있는 셀
xlCelltypeComments	메모가 들어있는 셀
xlCelltypeConstants	상수가 포함되어 있는 셀
xlCelltypeFormulas	수식이 들어있는 셀
xlCelltypeLastCell	사용된 범위 내의 마지막 셀
xlCelltypeSameFormatConditions	같은 서식을 가진 셀
xlCelltypeSameValidation	같은 유효성 조건을 가진 셀
xlCelltypeVisible	화면에 보이는(나타나 있는) 모든 셀

<Value 인수>

상 수	셀 타입
xlErrors	에러값
xlLogical	논리값
xlNumbers	숫자값
xlTextValues	문자열값

예를 들어 현재 워크시트 내에서 데이터가 입력되어 있는 마지막 셀을 선택하려면 다음과 같이 표현합니다.

Sub SpecialCells_Method()

    ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
    MsgBox "마지막 데이터 셀 주소는 " & Selection.Address & "이고" & vbCr & _
        "입력된 값은 " & Selection.Value & " 입니다"

End Sub

이 코드를 실행하면 그 결과는 아래와 같이 될 것입니다.

기본적인 사용법을 알았으니 이번에는 보다 실용적인 예제를 만들어 볼까요? 시트 내에서 수식이 입력된 셀의 배경색을 노란색으로 칠해 보도록 하지요. 다음 그림처럼 말입니다.

어떻게 하면 위와 같이 할 수 있을까요? 늘 그러하듯, 정상적인 예습/복습을 철저히 하고 정규 교과 과정을 제대로 이수하신 분이라면 능히 해결하실 수 있으리라 생각됩니다. ^^; 농담이고... 앞서 설명드린 SpecialCells_Method 프로시저를 이해하신 분이라면 쉽게 적용하실 수 있어야 합니다.

Sub Specialcells_Method_2()

Dim rngMyRange As Range
Set rngMyRange = _

        Worksheets("Sheet7").Cells.SpecialCells(xlCellTypeFormulas)

    rngMyRange.Interior.ColorIndex = 6
    MsgBox "수식이 입력된 모든 셀을 노란색으로 표시하였습니다!", , _

"www.iExceller.com"

End Sub

End 속성

End 속성이라고 하니까 뭔지 정확히는 알 수 없지만 '마지막'과 관련된 것이 아닐까 하는 짐작은 어렴풋이 드시지요? 바로 그렇습니다. End 속성은 '영역의 마지막 셀'을 구해주는 속성입니다. 마지막 셀은 알겠는데... 영역의 마지막 셀이란 무엇을 의미하는 것일까요? 짐작하시겠지만, 현재 셀을 기준으로 상/하/좌/우 방향의 맨 끝에 있는 셀을 의미합니다. 말로 하니까 이해가 될듯 말듯 하시지요? 그러실 줄 알고 그림을 준비했습니다. 다음 그림을 보세요.

이 시점에서... 수수께끼 하나 내 드릴까요?
'아니, VBA 공부하다 말고 집중력 떨어지게 왠 수수께끼?' 하시겠지요? 다 이유가 있어 그런 것이니까 한번 맞춰 보세요.

집집마다 4개씩 가지고 있는데 동네를 다 털어도 4개밖에 없고, 온 나라를 다 뒤져도 4개밖에 없는 것은 무엇일까~요?

그것은 바로... '동서남북' 이랍니다. 영역의 마지막 셀도 이와 비슷합니다. 위 그림에서 D8 셀을 기준으로 해서 위쪽으로 마지막 셀은 D4 셀, 아래쪽으로 마지막 셀은 D13 셀입니다. 또한 왼쪽으로 마지막 셀은 B8 셀, 오른쪽으로 마지막 셀은 F8 셀입니다. 이것을 컴퓨터(엑셀)가 알아들을 수 있게 VBA 코드로 표현하면,

Range("D8").End(xlUp).Select

Range("D8").End(xlDown).Select

Range("D8").End(xlToLeft).Select

Range("D8").End(xlToRight).Select

이렇게 됩니다. 조금 응용을 해 봅니다. 다음 코드를 실행해서 직접 확인해 보시면, 아마도 End 속성에 대해서는 더 이상의 설명이 필요 없을 것입니다(물론 코드를 실행하기 전에 Sheet1 시트에 앞의 그림과 비슷한 형태의 데이터는 미리 만들어 두셔야 하겠지요?).

Sub End_Property()

    Dim rngStart As Range
    Dim strAddress As String
    Set rngStart = Sheets("Sheet1").Range("D8")

    With rngStart.End(xlUp).Select
        strAddress = Selection.Address(rowabsolute:=False, _

columnabsolute:=False)
MsgBox "D8 셀의 위쪽 끝 셀인 [" & strAddress & "]셀을 선택하였습니다", , _

            "www.iExceller.com"

        .End(xlDown).Select
        strAddress = Selection.Address(rowabsolute:=False, _

columnabsolute:=False)
MsgBox "D8 셀의 아래 끝 셀인 [" & strAddress & "]셀을 선택하였습니다", , _

            "www.iExceller.com"

        .End(xlToLeft).Select
        strAddress = Selection.Address(rowabsolute:=False, _

columnabsolute:=False)
MsgBox "D8 셀의 왼쪽 끝 셀인 [" & strAddress & "]셀을 선택하였습니다", , _

            "www.iExceller.com"

        .End(xlToRight).Select
        strAddress = Selection.Address(rowabsolute:=False, _

columnabsolute:=False)
MsgBox "D8 셀의 오른쪽 끝 셀인 [" & strAddress & "]셀을 선택하였습니다"

End With

End Sub

<End 속성의 인수 및 단축키 정리>

이동 방향	인 수	단축 키
위쪽 끝	End(xlUp)	<Ctrl> + ↑
아래쪽 끝	End(xlDown)	<Ctrl> + ↓
왼쪽 끝	End(xlToLeft)	<Ctrl> + ←
오른쪽 끝	End(xlToRight)	<Ctrl> + →

End 속성은 실무적으로 어떤 경우에 사용하면 좋을까요? 여러 가지 경우에 적용할 수 있겠지만 다음과 같이 데이터를 추가적으로 입력해 나갈 때 마지막으로 입력된 셀이 어디인지를 파악하는 경우에 흔히 사용됩니다.

Sub End_Property_2()
Range("A65536").End(xlUp).Offset(1, 0).Select
MsgBox "데이터가 새로 입력될 위치는 " & Selection.Address & "입니다"
End Sub

뿐만 아니라 특정한 영역을 지정하여 색상을 지정하고자 할 경우에도 사용할 수가 있겠습니다. 아래와 같이 말입니다.

Sub End_Property_3()

    Range(Range("E2"), Range("E2").End(xlDown)).Select
    Selection.Interior.ColorIndex = 3
    Selection.Font.ColorIndex = 2
    MsgBox "급여 영역의 데이터에 대한 서식 변경을 완료하였습니다!", , _

"www.iExceller.com"

End Sub

이로서 올해 강좌는 모두 마칩니다. 새해에는 더욱 알찬 내용을 가지고 다시 찾아뵙도록 하겠습니다. 모쪼록 2005년에는 웃을 일이 많이 생기기를 기원해 봅니다.

아듀 2004년, 아듀 여러분~~!!
　