[엑셀/VBA] 특정 시간 동안 작업을 일시중지 하는 방법

엑셀에서 VBA 를 이용해 xml 형식의 오픈 API 데이터를 수집하는 작업을 하다보니, 특정 시간 동안 요청할 수 있는 횟수가 정해져 있고 그걸 초과하면 접속한 IP 를 일시적으로 요청하지 못 하도록 차단하는 정책이 있더라구요. 아마 디도스 공격 같은걸 방지하기 위함인 것 같습니만..

어쨌든 룰은 룰이니까 거기에 걸리지 않으려면 VBA 에서 For 문 이용해서 막 요청하던 것을 매 요청마다 0.2~0.3초 정도 딜레이를 주고 요청 할 수 있도록 해야 차단 당하지 않고 계속 요청 할 수 있더라구요.

그래서! VBA 에서 코드를 실행할 때 작업을 잠깐동안 일시중지 하는 방법을 찾아 보았습니다.

kernal32

첫번째 방법은 kernal32 라이브러리의 sleep 함수를 이용하는 방법입니다.

먼저 kernal32 는 VBA 외부에 있으니 내가 사용하겠다라고 선언을 해야 사용할 수 있습니다. 엑셀 VBA 창에서 워크북에 모듈을 하나 추가하고 그 모듈에 아래 코드와 같이 sleep 함수를 사용하겠다고 선언합니다.

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If

위 코드에서 VBA7 은 엑셀2010 또는 그 이후 버전일 때 sleep 함수를 사용할 수 있도록 선언하는 부분이고, VBA7이 아닐때는 엑셀 2007 또는 그 이전 버전에서 sleep 함수를 사용할 수 있도록 선언하는 부분입니다.

그 다음 실제로 sleep 함수를 이용해 원하는 곳에서 작업을 잠깐 동안 멈춰주면 됩니다. 워크시트에서 코드 작업을 하면서 원하는 부분에 아래와 같이 sleep 함수를 사용해주면 됩니다.

For i = 1 to 1000
    (실행할 코드) ....
    Sleep 1000
    (실행할 코드) ....
Next i

sleep 함수의 사용방법은 아주 간단합니다. sleep 함수 뒤에 지연시킬 만큼의 시간을 1/1000 초 단위로 적으면 되죠. 다시 말해서 Sleep 1000 이라고 되어 있으면 1초 동안 잠시 작동을 멈추라는 뜻입니다.

Application.Wait

두 번째 방법은 엑셀 자체에 내장 되어 있는 wait 기능을 이용하는 것입니다. 이 방법은 kernal32 라이브러리를 이용한 방법과 다르게 외부의 도움을 받지 않아도 되므로 오히려 사용히 간편할 수 있습니다.

For i = 1 to 1000
    (실행할 코드) ....
    Application.Wait DateAdd("s", 1, Now)
    (실행할 코드) ....
Next i

사실 wait 는 특정 시간 만큼을 지연시키는게 아니라, 특정 “시각”이 될 때까지 작동을 멈출때 사용하는 방법입니다. 그런데 그 특정 “시각”을 DateAdd 함수를 이용해 현재 시점에서 1초뒤로 설정하면 작동을 1초동안 일시정지 시키는 효과가 나오는거죠.

참고로 DateAdd 의 두번째 인수(위 코드에서 “1”이라고 적힌 부분)는 형식이 Double 로 지정되어 있어서 소수점으로 넣어도 잘 작동합니다.

다만 리서치를 좀 해본 결과 위 방법들은 완벽하게, 아주 정확하고 엄밀하게 지정한 시간만큼 작업을 지연시키지는 못하는 것 같습니다(특히 wait). 또한 자정이 넘어 갈 때에도 영원히 wait 되어 버리는 결과가 나오는 케이스가 있다고 하니 본인의 사용 방식에 적합한지 잘 확인한 다음 사용해야 할 것 같아요.

끝.

참고자료

• Sleep function (synchapi.h), Microsoft Docs
• Application.Wait method (Excel), Microsoft Docs