VBA 코딩 패턴: 성능 향상

이 글은 새로운 블로그로 옮겼습니다. 5초후 자동으로 이동합니다.

▶ 새로운 블로그 주소: https://prodskill.com/

▶ 새로운 글 주소: https://prodskill.com/excel-vba-coding-pattern-performance-improvement/

목차

---

지난 글에 VBA Range Loop 방법에 대해 비교하고 Variant Array를 이용하는 방법이 가장 빠른 것을 확인했다.

2021.05.30 - [엑셀 & VBA/엑셀 VBA 코딩패턴] - VBA 코딩 패턴: Range Loop

VBA 코딩 패턴: Range Loop

 

이번에는 VBA 코드의 성능을 향상시킬 수 있는 방법에 대해 알아본다.

---

1. 성능 관련 VBA 전역 변수

다음은 전체 변수에 대한 요약이다.

성능 관련 VBA 전역 변수

 

1.1. Application.ScreenUpdating

화면 갱신여부를 제어하는 변수이고, 기본값은 True(화면 갱신함)이다.

• VBA 코드 실행 도중 화면 갱신(File 전환, Sheet 전환, Cell값 설정 등)을 하지 않도록 설정하여 성능향상
• 화면 갱신이 많은 경우 효과적임
• 코드 진입 부분에 False로 설정하고 코드 종료 부분에 다시 True로 설정

 

1.2. Application.DisplayAlerts

경고창을 표시여부를 제어하는 변수이고, 기본값은 True(경고창 표시함)이다.

• VBA 코드 실행 도중 경고창(예: 변경한 파일을 닫으려고 할 때 “저장하시겠습니까” 를 확인하는 창 등)을 보여지지 않도록 설정하여 코드가 중간에 멈추지 않도록 함
• 코드 진입 부분에 False로 설정하고 코드 종료 부분에 다시 True로 설정

 

1.3. Application.Calculation

수식 계산방식을 제어하는 변수이고, 기본값은 xlCalculationAutomatic(자동계산)이다.

• VBA 코드 실행 도중 수식(COUNTIF, VLOOKUP 등) 계산을 자동으로 실행하지 않도록 함
• 수식이 많은 경우 효과적임
• 코드 진입 부분에 xlCalculationManual 로 설정하고 코드 종료 부분에 다시 xlCalculationAutomatic 으로 설정

 

1.4. Application.EnableEvents

이벤트 처리여부를 제어하는 변수이고, 기본값은 True(이벤트 처리함)이다.

• Worksheet_Change 등의 이벤트가 발생할 때 처리하는 코드의 실행 방지
• 이벤트 처리하는 코드가 있는 경우에는 효과적
• 코드 진입 부분에 False로 설정하고 코드 종료 부분에 다시 True로 설정

 

2. 예시 코드

2.1. 적용이 적합한 경우

VBA 코드에 다음의 경우가 포함되어 있다면, 이 글에서 설명하는 성능향상 방법을 적용하기에 적합하다.

• 파일/시트를 새로 생성하는 경우
• 현재 시트를 다른 시트로 전환하는 경우
• 한 시트내에서 현재 선택한 cell을 계속 변경하는 경우
• 수식이 많아 계산에 시간이 오래걸리는 등 복잡한 연산을 실행할 경우

이런 경우는 VBA 코드의 실행이 잠시 중단되고, 화면을 갱신하거나 수식을 계산하는 등 엑셀이 필요한 이벤트를 처리한다. VBA 코드가 느려지는 가장 대표적인 경우가 바로 화면 갱신이다. VBA 코드 실행중에 다른 이벤트 처리는 잠시 보류하고, VBA 코드 실행이 끝난 다음 다른 이벤트를 처리하도록 하여 성능을 향상할 수 있다.

 

2.2. 예시 코드

처리할 코드에 진입하기 전 전역변수를 성능향상값으로 설정하고, 처리 코드 종료 후 원래의 값으로 변경한다. 다음 코드를 참고하기 바란다.

'코드 진입 부분
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
 
'실행할 코드가 들어 갈 부분
...
 
'코드 종료 부분
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True

 

---

이상으로 성능향상을 위한 VBA 코딩 패턴에 대하여 알아 보았다. 궁금한 점은 댓글 남겨 주기 바란다.