나눔터  
  HOME > 나눔터 > 묻고답하기 > 엑셀
엑셀
엑셀에 대한 질문과 답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
 "000 님, 도와주세요", "부탁 드립니다.", "급합니다!" 등과 같이 막연한 제목을 달지 말아주세요.
[필독] 빠르고 정확한 답변을 얻는 16가지 Tip !
[필독] 저작권법 개정에 따른 이용안내
작성자:  

 조삿갓 (choga21)

추천:  2
파일:     조회:  4843
제목:   강좌: Worksheet _change 이벤트 핸들러 설계하기
     
  워크시트의 특정 셀에 대하여
사용자의 직접적인 조작(키보드 입력, 삭제, 붙여넣기 등)으로
값이 바뀌게 되면
Worksheet_Change 이벤트가 발생합니다.

이 때, 해당 워크시트 모듈에
Worksheet_Change라는 이름의 Sub 프로시저를 코딩해 놓으면
그 이벤트가 발생할 때마다 이 프로시저가 호출됩니다.
이런 프로시저를 '이벤트 핸들러'라고 하지요.

이 때, 값이 바뀌게 된 셀 개체의 정보가
Target이라는 Range형 변수에 전달됩니다.

보통 Change 이벤트 핸들러에서는
특정 셀이 바뀔 때 어떤 기능을 수행하도록 설계하므로
그 기능을 코딩하기 전에 먼저
Target의 정보(주소)를 조사하여
값이 바뀐 셀이 '그 특정 셀'에 해당하는지 판단한 후에
기능을 수행하도록 해야 합니다.

그게 바로 종전 답변에서 알려드린 IF 구문의 조건부
Target.Address = "$A$2"
입니다.

즉, 특별히 A2 셀의 값이 바뀌었을 때만
해당 영역을 지우라는 것이지요.

그런데 Address 속성을 직접 조사하여 조건을 판단하는 것은
단일 셀에 대헤서는 가장 효율적인 방법이지만

이번에 질문하신 것처럼
어떤 일정한 범위에 속할 경우를 판단하려고 할 때에는
가장 비효율적인 방법입니다.

단일 셀이 아닌 두 개 이상의 셀 범위의 값을
한꺼번에 바꾼 경우
Target.Address의 값은
$F$6:$F$9 (연속 범위의 경우)
$F$1:$F$2,$F$6:$F$8 (Ctrl 키를 누른 상태에서
                    서로 떨어진 두 군데를 범위 지정한 경우)
와 같이 복잡해져 버립니다.
이 문자열을 분석하여 그게 F4:F8에 해당하는지 아닌지를
판단하는 것은 매우 불쌍한 노가다를 감수해야 합니다.

따라서 조금 공부를 한 중급 프로그래머 수준이라면
다른 방법을 강구하게 됩니다.

Range 개체(Cells도 Range의 일원임)는
자신의 위치를 나타내는데 Address 외에
Row, Column 속성을 통해 행, 열번호를 각각 나타내기도 합니다.
이 속성들은 엑셀의 시트 구조상
반드시 Long 형으로 다루는 것이 안전합니다.
(Interger는 32768행부터 오류 발생)

즉, 다음과 같이 생각할 수 있습니다.
----------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim c As Long
    r = Target.Row
    c = Target.Column
    If c = 6 Then   'F열의 값이 바뀐 경우에만
        If r >= 4 And r <= 8 Then  ' 4행부터 8행 사이의 값이 바뀐 경우만
            Cells(r, 7).ClearContents
                ' 같은 줄에 7열 즉, G열 셀의 값을 지움
                ' 주소로 지정하는 것보타 이와 같이 행, 열번호로 지정하는 것이 더 효율적임
                ' Cells 개체는 행번호, 열번호 순서임에 주의할 것!
        End If
    End If
End Sub
------------------------------------------------

자, 이 코드를 입력하고 테스트해 보세요. 어떤가요?


역시 보기 좋게 실패할 겁니다.
물론 단일 셀에서는 잘 동작합니다.
안성진님이 생각하신 코드보다 훨씬 간결하면서도
확장성과 활용성이 좋은 코드이지요.

그러나, 복수 개의 셀이 동시에 바뀌었을 때 적응하지 못한 것은
Row, Column 속성이 범위의 좌상단 셀의 위치 값만 알려주기 때문이지요.
이 때에는 Rows.Count, Columns.Count 라는
손자 속성을 사용하면
그 범위의 깊이(행 개수)와 폭(열 개수)을 알아내어
수학적으로 계산할 수 있긴 합니다만...

이 경우에도 만일 Ctrl 키를 사용하여
연속되지 않은 범위를 선택하여 바꾸었을 경우에는
또 문제가 발생할 수 있을 겁니다.

그러므로, 다른 방법을 생각해야 합니다.

Target이 범위로 선택된 경우에도
그 범위 속에 내가 의도하는 특정 셀이 포함되어 있는지
검사하는 방법으로
Intersect라는 함수가 있습니다.

Intersect(범위1, 범위2, 범위3, ...)

아마 고등학교 때 공부를 좀 열심히 했었다면
이 단어가 익숙할텐데요...
수학 시간에 배운 교집합(Intersection)과
벤다이어그램의 모양을 연상해 보시면 이해가 쉬울 겁니다.

인수로 지정한 범위1, 범위2, 범위3, ... 등이 집합이라고
생각할 때, 그들의 교집합 부분 즉, 공통부분에 해당하는
범위를 반환해 줍니다.

그리고, 만일 공통부분이 없어서 공집합이 되면
이 함수의 결과는 nothing 이라는 값을 반환합니다.

이제 다음 코드를 보시죠.

-------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim c As Long
    c = 6
    For r = 4 To 8
                ' 4행부터 8행까지 순차적으로 자동 조사
        If Not Intersect(Target, Cells(r, c)) Is Nothing Then _
                Cells(r, 7).ClearContents
            ' Target과 r행 c열 셀이 공통부툰이 존재하면 G열 삭제
    Next r
End Sub
------------------------------------------------

이 코드를 입력하고 테스트해 보세요.

잘 되나요?

주의할 것은 조건식에서 nothing과 비교할 때에는

Intersect(Target, Cells(r, c)) <> Nothing

또는

Not Intersect(Target, Cells(r, c)) = Nothing

과 같이 등호나 부등호를 사용하면 오류가 발생하고

꼭 위와 같이 Is 로 비교해야 합니다.


그런데...
위 코드 역시 연속된 범위 영역에 대해서는 잘 동작하지만
Ctrl 키를 이용하여 F4:F5, F7:F10 과 같이 불연속적인
영역을 선택하여 값을 변경했을 경우
G4:G5, G7:G8 영역이 지워져야 되지만
실제 실험해 보면 G8이 지워지지 않습니다.

그 이유는
ClearContents 매소드(명령)로 셀 값을 지우는 경우도
Worksheet_Change 이벤트가 발생하여
동일한 이벤트핸들러가 호출되기 때문입니다.

즉, 이벤트핸들러가 아직 수행 중인데
또 다시 자기자신이 호출되는 것입니다.
이걸 재귀호출이라고 하고
이렇게 다시 호출될 때 Target 인수는
바로 방금 전에 ClearContents 매소드(명령)로 값이 지워진
G열의 셀이 되겠지요.

이게 원래는 원래 수행 중이던 핸들러에서의 Target과는
독립적으로 작용하고
재귀호출이 끝나고 다시 수행 중인 원래의 핸들러로 돌아가면
거기서는 또 원래의 F열 셀에 대한 값을 보존하고 있어야 하는ㄷ

실험에 의하면 그 부분에서 엉킴 현상이 발생하는 것 같습니다.

쉽게 비유하자면 '기억 상실증'에 걸린 것 같다고 할까요?

이런 현상을 안전하게 방지할 방법이 있습니다.

마지막 완성 코드입니다.

---------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim c As Long
    c = 6
    Application.EnableEvents = False
' 프로시저가 끝날 때까지 이벤트의 발생을 억제시킴
    For r = 40 To 50
        If Not Intersect(Target, Cells(r, c)) Is Nothing Then _
                Cells(r, 7).ClearContents
    Next r
    Application.EnableEvents = True
' 이벤트 억제 상태 해제
End Sub
---------------------------------------------------

한 번 Worksheet_Change 이벤트가 발생하여 프로시저가 호출되면
프로시저의 기능을 끝낼 때까지는 이벤트 발생을 무시하도록 하여
ClearContents 메소드에 의해 핸들러가 재귀호출되는 것을 방지함

이제 테스트해 보세요.
잘 되지요?

강의 끝. ^^
 
[불량 게시물 신고]  
안성진와...대박....Worksheet_change 이벤트에 대해서 확실하게 알게 되었네요. 질문올리고난 후 찾아보면서 intersect를 이용해보기도했는데 그때 겪었던 문제점을 딱 찝어주셨네요ㅎㅎ.
이벤트발생 억제 기능은 생각하지도 못했기때문에 조삿갓님 아니였으면 포기했을뻔했네요. 정말 정말 정말 감사합니다!!!
 좋은 하루되세여~
03-30 (09:07)
삭제 ■신고
        
  

작성일 : 2018-03-29(22:05)
최종수정일 : 2018-03-29(22:05)
 


 ◎ 관련글

  제 목   작성자   날짜
조삿갓님 저번 Worksheet _change 답변에대한 추가질문이요!! 안성진 2018-03-29
강좌: Worksheet _change 이벤트 핸들러 설계하기 조삿갓 2018-03-29
추가 보충: For Each 반복문을 활용하는 방법 조삿갓 2018-03-30