엑셀 매크로 작업을 조금 더 빠르게..

엑셀의 셀 내용이 변경되면, 자동으로 연결된 수식을 계산합니다. 또한, 변경 내용을 디스플레이에 표시하기 위한 여러가지 그래픽 작업들이 실행됩니다. 따라서, 셀 데이터를 하나씩 변경하게 되면 이러한 작업이 반복되기 때문에 작업 시간이 기하급수적으로 증가하게 됩니다.

엑셀에서는 셀 내용이 변경되더라도 일시적으로 자동계산을 멈추고, 화면 표시를 업데이트하지 않도록 설정할 수 있습니다. 매크로를 이용해 반복작업을 할 때, 작업이 완료될 때까지 업데이트를 멈추도록 하고, 매크로가 완료되면 다시 자동 업데이트되도록 하면 속도가 빨라집니다. 

그럼에도 불구하고, 여전히 셀 데이터를 하나씩 읽고, 하나씩 쓰는 것보다는 배열을 이용해 입출력 작업하는 것이 빠른 것은 마찬가지입니다.

Function CalcModeOn(Optional iMode As Boolean = True)
  Select Case iMode
    Case True
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
    Case False
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic
  End Select
End Function

위의 예제는 Function 이나 Sub 프로시저에 삽입하여 일시적으로 자동계산을 끄고 켜는 함수입니다. 엑셀의 참조셀이 많아 자동계산 수행량이 많은 작업이나 그래프 등에 대해 반복 작업을 하는 경우, 속도를 크게 향상시킬 수 있습니다.

사용방법은..

프로시저 초입부에

CalcModeOn True

를 삽입하여 자동 계산을 중지시키고, 끝나기 전에

CalcModeOn False

를 삽입하여 자동 계산을 실행시키는 것입니다.

Sub Test1()
  Dim i As Long

  For i = 1 To 50000

    ActiveSheet.Cells(i, 1).Value = i

    ActiveSheet.Cells(i, 1).Select

  Next

End Sub

Sub Test2()

  Dim i As Long

  CalcModeOn True

  For i = 1 To 50000

    ActiveSheet.Cells(i, 2).Value = i

    ActiveSheet.Cells(i, 2).Select

  Next

  CalcModeOn False

End Sub

위의 두 프로시저를 비교해보시면, Test1의 경우, 1번 열의 각 셀에 값을 입력한 후, 그 셀을 선택하고, 다시 다음 셀로 넘어가는 것입니다. 당연히 내용을 모두 업데이트 한 후 다음셀로 넘어가려니 시간이 오래걸립니다.

Test2는 잠시 스크린업데이트와 자동계산을 끄고 실행합니다. 각 셀을 선택하는 명령이 있지만, 스크린업데이트가 꺼져있기 때문에 Test1에 비해 2~3배 이상 빠릅니다. 그럼에도 불구하고, 고작 5만개 숫자를 넣는 작업을 하는데, 시간이 오래 걸립니다. 물론, 이 프로시저들은 일부로 각 셀에 대해 Select 한 후 넘어가도록 했기 때문에 느린 것입니다. Test1과 Test2를 비교하기 위해서 말이지요.

Sub Test3()
  Dim i As Long
  CalcModeOn True
  For i = 1 To 100000
    ActiveSheet.Cells(i, 3).Value = i
  Next
  CalcModeOn False
End Sub

Sub Test4()
  Dim i As Long, tVec()
  CalcModeOn True
  ReDim tVec(1 To 100000)
  For i = 1 To 100000
    tVec(i) = i
  Next
  WriteVec(tVec, ActiveSheet, Cells(1, 4), True).Select
  CalcModeOn False
End Sub

이번엔 Select를 빼는 대신 10만개의 데이터로 비교해보도록 하겠습니다. Test3는 각 셀마다 반복해서 값을 입력하는 명령입니다. 이때엔 자동업데이트를 중지시키더라도 속도가 매우 느립니다. 반면 Test4의 경우, 배열에 미리 값을 다 할당해둔 후 시트에 한꺼번에 입력하는 방법 이며 작업속도가 크게 차이나는 것을 확인하실 수 있습니다. 

(데이터 읽기/쓰기 참조 https://jhjungx.blogspot.com/2021/04/3.html)