이 글의 목적은 엑셀 VBA에서 Timer 함수가 지연·멈춤·오작동하는 상황을 체계적으로 진단하고, 대체 기술(Application.OnTime, Sleep, 고해상도 타이머 등)과 안전한 코드 패턴으로 즉시 해결하도록 돕는 것이다.
왜 Timer 함수가 오작동하는가
Timer는 자정 이후 경과 초를 반환하는 함수이며 자료형은 Single이다. 해상도는 밀리초 단위보다 낮을 수 있으며 시스템 상태와 이벤트 처리 상황에 영향을 받는다. 다음 조건에서 문제를 일으키기 쉽다.
- 자정 래핑 문제: 23:59:59 이후 00:00:00에 값이 0으로 초기화되어 경과시간 계산이 음수가 되는 경우가 있다.
- 부동소수점 정밀도 문제:
Single정밀도 부족으로 경과 임계값 비교가 불안정해지는 경우가 있다. - UI 차단 문제: 긴 루프에서
DoEvents가 없거나 화면 갱신·재계산이 과도하면 타이밍 루프가 지연된다. - 절전/절전 해제 이후 시스템 타이머 드리프트로 간헐적 튐이 발생한다.
- 보안·참조 문제: 신뢰할 수 있는 위치가 아니거나 VBA 프로젝트가 중단 상태이면 타이밍 루틴이 중단된다.
주의 :
Timer는 실시간 제어용 정밀 타이머가 아니다. 사용자 입력·이벤트·재계산의 영향을 받으므로, 장시간 주기 제어나 정확한 지연에는 적합하지 않다.진단 체크리스트
| 증상 | 가능 원인 | 점검/해결 |
|---|---|---|
| 경과시간이 음수 | 자정 래핑 | 자정 래핑 보정식을 적용한다. |
| 루프가 멈춘 듯 보임 | UI 이벤트 미처리 | DoEvents를 주기적으로 호출한다. |
| 지연이 들쭉날쭉 | 재계산·화면갱신 간섭 | Application.Calculation·ScreenUpdating 제어로 간섭을 최소화한다. |
| 아예 실행 안 됨 | 중단점·보안 경고 | VBE 중단 해제, 파일 신뢰 위치 지정, 매크로 사용 설정을 확인한다. |
| 장시간 측정 오차 누적 | Single 정밀도 한계 | Double 변수로 저장·연산하고 누적오차를 주기적으로 리셋한다. |
안전한 Timer 경과시간 계산 패턴
자정 래핑과 정밀도 문제를 동시에 피하는 기본 패턴이다.
Option Explicit
' 경과시간(초)을 안정적으로 계산하는 함수
Private Function ElapsedSince(ByVal t0 As Double) As Double
' Timer는 Single 반환이지만 Double로 승격 저장하여 오차를 줄인다.
Dim t As Double
t = CDbl(Timer)
If t >= t0 Then
ElapsedSince = t - t0
Else
' 자정 래핑 보정: 하루는 86400초
ElapsedSince = (86400# - t0) + t
End If
End Function
Sub Demo_Timer_Wait_Accurate()
Dim startT As Double
Dim target As Double
startT = CDbl(Timer)
target = 2# ' 2초 대기 목표
Do While ElapsedSince(startT) < target
DoEvents ' UI 응답성 유지
Loop
MsgBox "2초 경과 완료이다.", vbInformation
End Sub
주의 : 루프 내부에서 파일 IO, 범위 수식 대량 업데이트, 셀 선택 이동을 수행하지 말아야 한다. 타이밍 지터가 커진다.
정확한 주기 실행에는 Application.OnTime을 우선 고려한다
주기 작업은 Timer 루프보다 Application.OnTime이 더 안정적이다. 엑셀의 메시지 큐와 통합되어 백그라운드 루프를 피한다.
Option Explicit
Private nextTick As Date
Private Const PERIOD_SEC As Double = 1 ' 1초 주기
Sub Start_OnTime_Tick()
ScheduleNextTick Now
End Sub
Private Sub ScheduleNextTick(ByVal baseTime As Date)
nextTick = baseTime + TimeSerial(0, 0, 0) + (PERIOD_SEC / 86400#)
Application.OnTime earliesttime:=nextTick, _
procedure:="TickProc", _
schedule:=True
End Sub
Public Sub TickProc()
' 할 일 수행
Debug.Print "Tick at "; Now
' 다음 일정 예약
ScheduleNextTick nextTick
End Sub
Sub Stop_OnTime_Tick()
On Error Resume Next
Application.OnTime earliesttime:=nextTick, _
procedure:="TickProc", _
schedule:=False
End Sub
주의 : 통합 문서가 닫히거나 오류로 프로시저가 중단되면 예약이 풀릴 수 있다. 저장·종료 이벤트에서
Stop_OnTime_Tick을 호출해 잔여 예약을 취소한다.간단 지연에는 Sleep API가 효율적이다
Sleep은 CPU 스핀 대기를 피하고 스레드를 지정 밀리초 동안 대기시킨다. 정확한 대기 동안 코드가 쉬므로 배터리와 CPU 사용을 줄인다.
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
Sub Demo_Sleep()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sleep 750 ' 0.75초 대기
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "대기 완료이다."
End Sub
주의 :
Sleep은 UI 이벤트를 처리하지 않는다. 진행 표시나 취소 버튼이 필요하면 DoEvents 기반 루프 또는 OnTime을 사용한다.고해상도 타이밍: QueryPerformanceCounter
밀리초 이하 정밀도가 필요하면 성능 카운터를 사용한다. 64비트 호환 선언이 필수이다.
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
Private Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#Else
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#End If
Private Function HiResSeconds() As Double
Dim cnt As Currency, freq As Currency
QueryPerformanceCounter cnt
QueryPerformanceFrequency freq
' Currency는 10,000 배 스케일 정수라서 64비트 정밀 정수로 쓰기 좋다.
HiResSeconds = (cnt / 10000@) / (freq / 10000@)
End Function
Sub Demo_HiResWait()
Dim t0 As Double
t0 = HiResSeconds
Do While (HiResSeconds - t0) < 0.005 ' 5ms 대기
DoEvents
Loop
Debug.Print "약 5ms 대기 완료이다."
End Sub
주의 : 고해상도 대기는 CPU 점유율이 높아질 수 있다. 실제 업무 자동화에서는
OnTime 또는 Sleep과 혼용한다.Timer 기반 진행 표시와 취소 처리 패턴
사용자 취소 가능성이 있으면 다음 패턴이 안정적이다.
Option Explicit
Sub Demo_Progress_With_Cancel()
Dim t0 As Double, elapsed As Double, limitSec As Double
Dim cancelled As Boolean
Dim i As Long
limitSec = 10
t0 = CDbl(Timer)
For i = 1 To 1000000
' 핵심 작업
If i Mod 1000 = 0 Then
elapsed = ElapsedSince(t0)
Application.StatusBar = "경과: " & Format(elapsed, "0.0") & "s"
DoEvents
If GetAsyncKeyState(vbKeyEscape) <> 0 Then
cancelled = True
Exit For
End If
If elapsed > limitSec Then Exit For
End If
Next
Application.StatusBar = False
If cancelled Then
MsgBox "사용자 취소이다."
Else
MsgBox "완료이다."
End If
End Sub
#If VBA7 Then
Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#Else
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#End If
화면 갱신·재계산 간섭 최소화 체크리스트
- 루프 진입 전
Application.ScreenUpdating=False로 깜빡임을 제거한다. - 배치 연산 전
Application.Calculation=xlCalculationManual로 자동 재계산을 잠시 끈다. - 루프 종료 후 반드시 원복한다.
Finally역할의On Error GoTo CleanUp패턴을 사용한다. - 클립보드·선택 변경을 최소화하고, Range 접근은 배열로 일괄 처리한다.
Sub SafeBatch() On Error GoTo CleanUp Dim calcPrev As XlCalculation calcPrev = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' 작업 수행
CleanUp:
Application.EnableEvents = True
Application.Calculation = calcPrev
Application.ScreenUpdating = True
End Sub
테스트용 타이밍 스위트
환경과 워크로드에 따른 지터를 수치화하면 문제 파악이 빠르다.
Sub TimingSuite() Dim i As Long, t0 As Double, dt As Double Dim worst As Double, best As Double, sum As Double
worst = 0: best = 1E+9: sum = 0
For i = 1 To 200
t0 = CDbl(Timer)
Do
DoEvents
Loop While ElapsedSince(t0) < 0.01 ' 목표 10ms
dt = ElapsedSince(t0)
If dt > worst Then worst = dt
If dt < best Then best = dt
sum = sum + dt
Next
Debug.Print "평균=" & Format(sum / 200, "0.000"); _
" 최악=" & Format(worst, "0.000"); _
" 최상=" & Format(best, "0.000")
End Sub
시나리오별 권장 선택
| 요구사항 | 권장 기술 | 이유 |
|---|---|---|
| 몇 초마다 보고서 갱신 | Application.OnTime | CPU 점유율 낮고 자정 영향이 없다. |
| <1초 미세 대기 | Sleep 또는 고해상도 루프 | Timer 해상도 한계 회피이다. |
| UI 진행 표시와 취소 | Timer+DoEvents | 사용자 반응성을 유지한다. |
| 긴 주기 작업 스케줄링 | OnTime 단독 | 안정적 예약과 코드 단순화이다. |
자주 쓰는 안전 유틸리티 모음
' 자정 래핑 보정 포함 대기 Public Sub WaitSeconds(ByVal seconds As Double) Dim t0 As Double t0 = CDbl(Timer) Do While ElapsedSince(t0) < seconds DoEvents Loop End Sub
' ms 단위 대기 (혼합형)
Public Sub WaitMilliseconds(ByVal ms As Long)
If ms >= 15 Then
' 15ms 이상은 Sleep이 효율적
Sleep ms
Else
Dim t0 As Double
t0 = HiResSeconds
Do While (HiResSeconds - t0) * 1000# < ms
DoEvents
Loop
End If
End Sub
64비트 호환과 선언 실수 방지
VBA7전처리기를 사용해 32/64비트 모두에서 컴파일되도록 한다.- 포인터 크기가 바뀌는 API는
PtrSafe와 적절한 자료형(LongPtr또는LongLong)을 사용한다. Sleep의 인자는 32비트 정수이므로Long으로 충분하다.- 성능 카운터 값은 64비트 정수이므로
Currency또는LongLong으로 받는다.
실무 적용 체크리스트
- 주기성 작업은
OnTime으로 설계하고, 작업 블록은 1초 내 완료하도록 나눈다. - 대량 범위 업데이트는 배열로 일괄 쓰기 후 화면을 한 번에 갱신한다.
- 사용자 취소 경로와 예외 종료 시 예약 취소 루틴을 반드시 넣는다.
- 자정 전후 실행 가능성이 있으면 래핑 보정 함수를 필수로 사용한다.
- 테스트 스위트로 지터를 측정해 목표 주기를 보수적으로 잡는다.
FAQ
자정 직전 시작한 타이밍 루프가 멈추는 이유는 무엇인가?
Timer가 86400에서 0으로 래핑되기 때문이다. 경과 계산 시 음수가 되어 조건이 영원히 참이 되지 않는 오류가 발생한다. 자정 래핑 보정 함수를 사용해야 한다.
밀리초 이하 정밀도를 확보하려면 어떻게 하나?
Sleep은 정확히 밀리초에 맞추기 어렵다. 고해상도 성능 카운터를 사용한 루프와 DoEvents를 결합하거나, 업무에서는 OnTime 기반 비동기 설계를 권장한다.
화면 깜빡임과 느려짐이 심하다. 타이머와 관련 있나?
타이밍 자체보다 루프 내 화면 갱신·재계산이 원인이다. ScreenUpdating=False, Calculation=Manual로 묶고 종료 시 원복한다.
VBA 64비트에서 API 선언 오류가 난다. 어떻게 수정하나?
PtrSafe를 추가하고 인자 자료형을 점검한다. 예시에서 제공한 전처리기 패턴을 그대로 사용하면 된다.
Application.Wait와 Sleep 중 어느 것이 좋은가?
Wait는 엑셀 수준 대기라 간단하지만 최소 해상도가 크다. Sleep은 CPU를 쉬게 하여 효율적이다. UI 응답성이 필요하면 Timer+DoEvents 또는 OnTime을 쓴다.