짤막한 강좌

안녕하세요.

메일로 질문을 받았습니다.

[B48:D48] 셀 범위에 계속 바뀌는 데이터가 있는데, 8시 45분부터 10분 간격으로 데이터를 [B1:D1] 셀 범위에 순차적으로 복사해서 나타내고, 15시 45분에 끝나도록 하고 싶다는 것이었습니다.

Application.OnTime 메서드를 이용하면 스케줄러에 실행할 시간을 지정해서 실행할 매크로를 등록할 수 있습니다.

완성 파일 다운로드

완성 코드입니다.

Public Sub SRun()
    Application.OnTime TimeValue("08:45:00"), "SStart"
    Application.OnTime TimeValue("15:45:00"), "SStop"
End Sub

Private Sub SStart()
    If Range("A1") = "" Then
        Cnt = 1
    ElseIf Range("A2") = "" Then
        Cnt = 2
    Else
        Cnt = Range("A1").End(xlDown).Row + 1
    End If
    
    Range("A" & Cnt).Value = Format(Now, "hh:mm:ss")
    Range("B" & Cnt).Value = Range("B48").Value
    Range("C" & Cnt).Value = Range("C48").Value
    Range("D" & Cnt).Value = Range("D48").Value
    
    Application.OnTime TimeValue(Format(Now, "hh:mm")) + TimeValue("00:10:00"), "SStart"
End Sub

Private Sub SStop()
    On Error Resume Next
    
    Application.OnTime TimeValue(Format(Now, "hh:mm")) + TimeValue("00:10:00"), "SStart", , False
    
    On Error GoTo 0
End Sub

3개의 매크로를 만들었습니다.

SRun 매크로는 사용자에게 보여지고 동작하도록 만드는 매크로입니다.

SStart 매크로는 데이터를 복사하고, 10분 간격으로 반복하는 매크로입니다.

SStorp 매크로는 반복 실행되는 매크로를 취소해서 더 이상 실행되지 않도록 만드는 매크로입니다.

SRun 매크로는 프로시저 정의에 Public으로 설정되어 있고, SStart, SStop 매크로는 Private로 설정되어 있습니다.

Private으로 설정하면 모듈 안에서만 사용할 수 있고, 엑셀에서는 보이지 않습니다.

사용자는 SRun 매크로만 볼 수 있어야 합니다.

SStart 매크로를 직접 실행하면 무한 반복하게 되고, SStop 매크로를 직접 실행하면 스케줄러에 등록도 되지 않은 것을 삭제하려고 해서 오류가 생깁니다. 

SStart 프로시저 첫 부분에 입력된 데이터가 있는지 없는지, 있다면 몇 행까지 입력되었는지 확인하는 코드가 있습니다.

변수 Cnt에 새 내용을 출력할 행 번호를 구해 넣어 둡니다.

SStart 프로시저의 마지막 줄에 자신을 스케줄러에 추가하는 코드가 있습니다.

Application.OnTime TimeValue(Format(Now, "hh:mm")) + TimeValue("00:10:00"), "SStart"

자기 자신을 부르는 코드를 재귀(再歸)라고 합니다.

프로그래밍에서 많이 사용하는 방법입니다.

10분 간격으로 반복되도록 설정하는 코드입니다.

TimeValue 함수는 사람이 알아보기 쉬운 방법으로 시간을 지정하면 프로그램이 사용하는 시간값으로 바꾸는 역할을 합니다.

TimeValue 함수를 쓰지 않고 10분을 나타내려면 0.0069444를 써야 합니다.

Now의 결과값을 바로 쓰지 않고 Format 함수를 써서 Now의 결과 중 시간과 분만 가져왔는데, 이유는 초 단위까지 가져 오는 경우 스케줄러에 등록하거나 취소할 때 어떤 이유로든 코드가 실행될 때 초 단위로 늦어지는 경우가 있기 때문입니다.

분 단위까지만 가져오므로 최소한 60초나 늦거나 빠르지 않는 경우 오류가 생기지 않을 것이기 때문입니다.

코드를 테스트해 볼 때 1초 가량 늦게 실행되는 경우가 있어서 수정한 코드입니다.

SStop 프로시저에서 스케줄러에 등록된 항목을 제거하는 코드가 있습니다.

Application.OnTime TimeValue(Format(Now, "hh:mm")) + TimeValue("00:10:00"), "SStart", , False

OnTime 메서드의 4번째 인수를 True와 False 둘 중 하나의 값을 가지는데 기본값은 True입니다.

여기에 False를 넣으면 스케줄러에 등록된 항목을 취소합니다.

이때 주의할 점은 등록된 시간과 매크로 이름이 정확히 일치해야 하고, 만약 일치하는 항목이 없으면 오류가 납니다.

오류가 나는 경우 그냥 그대로 진행하도록 On Error Resume Next 코드를 입력해 두었고, 다시 반복해서 실행하도록 On Error GoTo 0 코드가 입력되어 있습니다.

SStart 프로시저 A열에 데이터를 나타내는 코드는 프로시저가 실행되는 시간을 시분초 형식으로 나타나도록 하는 것입니다.

Range("A" & Cnt).Value = Format(Now, "hh:mm:ss")

Format 함수를 이용했는데, 엑셀의 Text 함수를 써서 나타낼 수도 있습니다.

VBA에서 엑셀 함수를 쓰려면 Application.WorksheetFunction.Text 형식으로 사용할 엑셀의 함수 이름 앞에  Application.WorksheetFunction를 붙여 나타내야 합니다.

Range("A" & Cnt).Value = Application.WorksheetFunction.Text(Now, "hh:mm:ss")

필요하신 분께 도움이 되길 바랍니다.