Home MS 엑셀 강좌엑셀 고급 강좌 [VBA] 엑셀에서 계속 변하는 셀 값을 기록해두기

[VBA] 엑셀에서 계속 변하는 셀 값을 기록해두기

by 김형백
엑셀 VBA

어떤 분께서 스마트폰 문의로 질문을 해와서 잠깐 생각을 해봤는데, 이런 경우가 많이 있을 것도 같습니다. 특정 셀의 값이 지속적으로 변경되는데(자동/수동) 이 값을 기록해두고 싶다는 문의였습니다. 원래는 이런 경우에는 DB를 구성해서 자동화하는 것이 바람직하지만, 엑셀로도 충분히 가능합니다. 다만 일반적인 워크시트 작업으로는 안되고, VBA를 활용해야 합니다. (셀값이 변경된다는 이벤트를 잡아야하기 때문에 프로그램이 실행되어야 하는 것이죠.) 방법을 한번 알아볼까요?

원래 문의는 같은 시트의 특정 열에 계속 추가하는 것이었지만 여기서는 별도의 시트에 계속 기록을 남기는 것으로 해보겠습니다. 코드를 보시면 충분히 활용하실 수 있을 것입니다.

아래 과정을 잘 따라서 해보세요.

1. 새로운 통합 문서를 만들고 “원본”과 “기록”이라는 시트를 만듭니다. (시트 이름 변경) 그 후 다른이름으로 저장을 선택한 후 파일 형식을 “Excel 매크로 사용 통합 문서”인 xlsm 형식으로 바꿔서 저장합니다. VBA를 사용하려면 이 파일 형식을 반드시 사용해야 합니다.

2. 시트탭에서 “원본”시트를 마우스 오른쪽으로 클릭한 후 “코드 보기”를 선택합니다. 단축키인 AltF11을 눌러도 되겠죠? ^^

3. VBA 편집기가 실행되면, “원본” 시트가 선택되었는지 확인하고 아래 코드를 복사합니다.

공부를 위해서 실제 타이핑을 해서 코드를 만들어보는게 좋지만, 급한 분들은 아래 코드를 복사해서 사용하면 됩니다.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("A1")) Is Nothing Then
  With Sheets("기록")
   lr = .Cells(Rows.Count, "A").End(xlUp).Row
    If Range("A1").Value <> .Cells(lr, "A") Then
     .Cells(lr + 1, "A").Value = Range("A1").Value 
     .Cells(lr + 1, "B").Value = Now
    End If
   End With
 End If
End Sub

또하나 주의할 점은 위 코드는 수동으로 셀의 값을 입력/변경한 경우의 이벤트를 잡을 수 있는 것인데, 만약 함수나 기타 자동으로 값이 변경된다면 아래 코드를 사용해야 합니다. 두 코드를 동시에 모두 삽입하시면 안됩니다.

Private Sub Worksheet_Calculate()
 With Sheets("기록")
  lr = .Cells(Rows.count, "A").End(xlUp).Row
   If Range("A1").Value <> .Cells(lr, "A") Then
    .Cells(lr + 1, "A").Value = Range("A1").Value
    .Cells(lr + 1, "B").Value = Now
   End If
 End With
End Sub

자 이제 VBA 편집기는 저장해서 나오고 “원본”시트의 A1에 값을 계속 수정하면서 입력해보세요. “기록” 시트의 A열에는 변경된 값이 차례로 기록되고, 덤으로 B열에는 그 값이 변경된 시각이 입력됩니다. 간편하지만 매우 놀라운 기능으로 활용할 수 있겠죠?

VBA에는 간단한 구문이면서도 매우 강력한 오피스 제품과의 통합을 지원합니다. 기록되는 시트와 위치를 변경해보고, 다른 추가적인 기능들도 응용해서 활용할 수 있기를 기대합니다!

You may also like

3 댓글

김명선 2022년 07월 21일 - 2:05 오후

유용한 기능 정말 잘 감상했습니다. 더불어 활용도 잘 하고 있습니다.
한 가지 질문이 있습니다. 저는 알려주진 VBA를 통해 자동으로 특정 sheet의 Pivot table의 값이 변경 될 때 그 값을 다른 sheet에 기록하도록 해두었습니다. 헌데 Pivot table의 특정 셀 값이 매일 변하는 것이 아니라서 기록에 애를 먹고 있네요. 혹시 Pivot table을 Refresh 할 때마다 기록을 하게 만들 수 있을까요? 셀 값이 안변해도 시간 stamping과 함께 기록을 하고 있습니다. 감사합니다! 김명선드림 (이메일 회신주시면 정말 감사하겠습니다!)

답변
김형백 2022년 07월 22일 - 9:33 오전

안녕하세요? 유용하게 활용하셨다니 좋습니다.
질문이 명확치는 않은데요, 우선 피벗테이블을 수동으로 refresh 하셨을 때 이벤트를 잡고 싶으시다는거죠?
원칙적으로는 피벗테이블의 원본 소스 데이터를 감시하는 것이 맞지만, 경우에 따라서는 피벗테이블 refresh 이벤트가 필요할 것 같긴합니다.

PivotTables(“PivotTable1”).RefreshDate 이 방법으로 피벗테이블의 최신 변경 날짜를 이벤트로 잡으실 수 있습니다.
더 자세한 내용은 https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/ff834610(v=office.14)?redirectedfrom=MSDN 공식 문서를 보시면 예제와 함께 설명되어 있으니 참고가 되실겁니다.

감사합니다.

답변
김명선 2022년 07월 25일 - 2:36 오전

안녕하세요, 답변 정말 감사드립니다! 아직 보내주신 방법에 대해서 살펴보지는 못했습니다만, 제 질문이 제가 다시 읽어봐도 명확치가 않아서 ^^;; 다시 최대한 깔끔(?)하게 설명드려봅니다.

-매일 양산라인의 핵심 지수를 (총 5개) 일별로 기록을 하고 있습니다.
-5개 핵심 지수가 매일같이 변할 경우도 있지만, 개 중 몇 개는 그 전날과 비교 시 동일한 경우도 있답니다.
-알려주신 유용한 방법의 경우, 변하는 지수에 대해서는 기록을 해주지만, 그렇지 않은 것은 공란으로 남아있습니다.
-이렇게 전날 대비 지수 값이 같을 경우에도 당일 날짜를 기록함과 동시에 동일한 지수 값을 기록해두고 싶습니다.

최대한 설명을 드렸는데, 전달을 제대로 해드렸는지 잘 모르겠네요^^;;

알려주신 방법을 다시한번 숙지한 후에 시도해보고 또 질문드리도록 하겠습니다. 대단히 감사합니다!!!!

답변

댓글 남기기