엑셀, 곽승주님의 오튜공구함 제작으로 배워보는 VBA 이야기, Excel

자료다운로드 : 오튜공구함013.xls
자료다운로드 : 중복데이터_연습파일.xls

안녕하세요 오튜 가족 여러분

오늘은 중복데이터를 처리하는 방법을 다룰 차례입니다. 그런데 지난 컬럼에서 제가 3부작에 걸쳐 중복된 데이터를 복사하거나 거꾸로 복사하는 내용을 얘기한 것이 있었습니다. 그래서 그때의 모듈을 되살려 오튜공구함에 붙였습니다. 그런데 붙이면서 몇 가지 속도 향상과 안정적인 프로그램 수행을 위한 몇 가지의 코드를 추가하였습니다. 기능은 그때 그대로 입니다.

먼저 엑셀VBA에서 할 수 있는 간단한 속도향상을 위한 팁을 보여드리죠(사실 이미 여러분들이 아실테지만)

      Application.ScreenUpdating = False
     Application.EnableEvents = False
     Application.Calculation = xlCalculationManual

     주요작업 실행…

      Application.ScreenUpdating = True
     Application.EnableEvents = True
     Application.Calculation = xlCalculationAutomatic

Application개체의 속성을 다루고 있습니다. 화면에 관련한 것, 이벤트와 관련한 것, 계산과 관련된 것입니다.

Application.ScreenUpdating은 화면의 갱신을 지정하는 것입니다. 기본적으로 사용자가 어떠한 작업을 하거나 조작을 할때마다 엑셀은 물론 윈도우시스템자체도 화면을 다시 그립니다. 그속도가 매우 빨라 우리의 눈으로는 보이지 않습니다. 혹시 보인다면 여러분의 눈이 아주 좋거나 사용하시는 컴퓨터가 굼벵이 친척쯤 될 겁니다. TV프로그램에서 컴퓨터을 촬영한 것을 보면 화면이 위에서 아래로, 왼쪽에서 오른쪽으로 깜빡거리는 것을 보셨죠. 평소에는 우리의 눈이 너무 느려 보질 못하지만 우리 눈보다 빠른 카메라를 통해 보신 겁니다. (그러므로 보이는 게 진실은 아닙니다)

다시 Application.ScreenUpdating으로 돌아와서 이 속성을 FALSE로 주면 엑셀은 화면을 바꾸어 주질 않습니다. 그러므로 화면을 바꾸는 작업을 하지 않는 대신 다른 작업을 더 많이 할 수 있어 속도향상에 기여할 수 있습니다. 모든 작업이 끝나면 값을 다시 TRUE로 설정하여 엑셀이 화면갱신작업을 하도록 하여 우리가 편하게 사용할 수 있도록 해주어야 합니다. 특히 워크시트에 많은 자료를 쓰는 경우 속도향상을 체감하실 수 있을 겁니다.

Application.EnableEvents는 말 그대로 이벤트와 관련된 것입니다. 예를 들어 엑셀통합문서에 Private Sub Worksheet_Change(ByVal Target As Excel.Range)라든가, Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 같은 이벤트 프로시져를 둔 경우, 워크시트에 무언가를 쓰거나 현재 셀 위치를 바꿀때마다 이것들이 실행될 것니다. 작업에 필요한 작업이 아니라면 이러한 이벤트 프로시져가 작동하지 않는 편이 낫습니다. 그러므로 Application.EnableEvents를 FALSE로 설정하여 이벤트를 무시하도록 할 필요가 있습니다.

옵션을 변경하지 않는 한 기본적으로 엑셀은 자동계산을 합니다. 워크시트에 무언가를 기록할 때마다 엑셀이 자동계산을 하게 되면 작업시간을 낭비하게 됩니다. 그러므로 주요 작업 전에 Application.Calculation을 수동(xlCalculationManual)으로 하고 작업이 끝나면 다시 자동(xlCalculationAutomatic )으로 해줍니다.

이상으로 VBA에서 할 수 있는 속도향상코드를 소개해드렸습니다. 이젠 프로그램의 안정적인 실행을 위한 GetQueueStatus() API함수를 소개해드리겠습니다. 이것은 사실 VBA의 DoEvents와 같은 역할을 하지만 더 나은 성능을 보여줍니다.

VBA에서 시간이 상당히 걸리는 작업을 하는 경우 가령 For~Next, Do~Loop등등 그냥 루프를 돌려버리면 윈도시스템자체를 거의 독점하여 다른 작업을 할 수 없다는 점입니다. 간혹 중간에 시스템이 다운되는 경우도 있습니다. 그래서 아시는 분들은 DoEvents를 반복코드 중간에 사용하여 다른 프로세스가 CPU를 사용할 수 있도록 여유를 줍니다.

그러나 DoEvents가 너무 느려 이걸 사용하면 전체 프로그램수행을 상당히 굼뜨게 합니다. 그래서 이번에 소개하고자 하는 것이 GetQueueStatus()라는 API함수입니다. 사용에 앞서 이것이 DoEvents보다 얼마나 빠른지 결과를 보여드리죠(결과는 사용자마다 다를 수 있습니다)

With DoEvents by itself: 30.9296875 seconds
With GetQueueStatus check: 0.0390625 seconds

GetQueueStatus()함수를 사용한 것이 무지 빠르죠!

다음은 위의 결과를 보여준 프로그램소스입니다.

Option Explicit
Declare Function GetQueueStatus Lib "user32" (ByVal fuFlags As Long) As Long
Public Const QS_KEY = &H1
Public Const QS_MOUSEMOVE = &H2
Public Const QS_MOUSEBUTTON = &H4
Public Const QS_MOUSE = (QS_MOUSEMOVE Or QS_MOUSEBUTTON)
Public Const QS_INPUT = (QS_MOUSE Or QS_KEY)

Sub DoEventsTester()
     Dim i As Long
     Dim start As Long

     start = Timer
     For i = 1 To 100000
           DoEvents
     Next I
     Debug.Print "With DoEvents by itself: "; Timer - start; " seconds"

     start = Timer
     For i = 1 To 100000
          If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents
     Next I
     Debug.Print "With GetQueueStatus check: "; Timer - start; " seconds"
End Sub

상수를 보시면 알겠지만 키보드를 두드리거나 마우스를 이동하거나 마우스버튼을 클릭하는 경우를 GetQueueStatus()함수에 인수로 지정합니다.

이번 컬럼의 첨부파일에는 중복데이터 기능을 시험해보기 위한 연습용 파일을 같이 첨부합니다. 같이 다운 받으셔서 해보시고, 기능에 대해서는 앞서의 중복데이터처리 컬럼(3부작)을 참고하시길 바랍니다.

오늘은 여기까지입니다. 수고하세요.

목차 | 이전 | 다음