매크로를 가속화하는 10 가지 방법 - Excel 매크로가 점점 강력 해지고 복잡해지면서

Excel 매크로가 점점 강력 해지고 복잡해지면 성능이 저하 될 수 있습니다. 매크로를 논의 할 때, performance 라는 단어는 일반적으로 speed 와 동의어입니다. 속도는 VBA 절차가 원하는 작업을 얼마나 빨리 수행 하는지를 나타냅니다. 다음은 Excel 매크로를 최적의 성능 수준으로 유지하는 데 도움이되는 10 가지 방법입니다.

시트 계산 중지

스프레드 시트의 수식에 영향을주는 셀이 변경되거나 조작 될 때마다 Excel에서 전체 워크 시트를 다시 계산한다는 사실을 알고 계셨습니까? 수식이 많은 워크 시트에서는이 동작으로 인해 매크로가 크게 느려질 수 있습니다.

신청서를 사용할 수 있습니다. 수동 계산 모드로 전환하도록 Excel에 알리는 계산 속성입니다. 통합 문서가 수동 계산 모드 인 경우 F9 키를 눌러 계산을 명시 적으로 트리거 할 때까지 통합 문서가 다시 계산되지 않습니다.

Excel을 수동 계산 모드로 전환하고 코드를 실행 한 다음 자동 계산 모드로 다시 전환하십시오.

Sub Macro1 () 응용 프로그램. Calculation = xlCalculationManual '여기에 매크로 코드를 놓습니다. Calculation = xlCalculationAutomatic End Sub

계산 모드를 다시 xlCalculationAutomatic으로 설정하면 워크 시트의 재 계산이 자동으로 트리거되므로 매크로가 실행 된 후에 F9 키를 누를 필요가 없습니다.

시트 화면 업데이트 비활성화

매크로를 실행하면 화면이 상당히 깜박입니다. 이 깜박임은 워크 시트의 현재 상태를 보여주기 위해 화면을 다시 그리는 Excel입니다. 불행히도 Excel이 화면을 다시 그릴 때마다 메모리 리소스를 차지합니다.

신청서를 사용할 수 있습니다. ScreenUpdating 속성은 매크로가 완료 될 때까지 화면 업데이트를 비활성화합니다. 화면 업데이트를 비활성화하면 시간과 리소스가 절약되므로 매크로가 좀 더 빠르게 실행됩니다. 매크로 코드 실행이 끝나면 화면 업데이트를 다시 시작할 수 있습니다.

Sub Macro1 () 응용 프로그램. 계산 = xlCalculation 수동 응용 프로그램. ScreenUpdating = False '여기서 매크로 코드를 응용 프로그램에 배치하십시오. 계산 = xlCalculation 자동 응용 프로그램. ScreenUpdating = True End Sub

ScreenUpdating 속성을 다시 True로 설정하면 Excel에서 자동으로 화면 다시 그리기를 트리거합니다.

상태 표시 줄 끄기

Excel 창의 맨 아래에 나타나는 Excel 상태 표시 줄은 일반적으로 Excel에서 특정 작업의 진행률을 표시합니다.매크로가 많은 양의 데이터로 작업하는 경우 상태 표시 줄에 일부 리소스가 사용됩니다.

화면 업데이트를 끄는 것은 상태 표시 줄을 끄는 것과는 별도입니다. 화면 갱신을 사용 불가능하게하더라도 상태 표시 줄은 계속 업데이트됩니다. 응용 프로그램을 사용할 수 있습니다. DisplayStatusBar 속성을 사용하면 상태 표시 줄 업데이트를 일시적으로 비활성화하여

Sub Macro1 () 응용 프로그램 매크로의 성능을 더욱 향상시킬 수 있습니다. 계산 = xlCalculation 수동 응용 프로그램. ScreenUpdating = 거짓 어플리케이션. DisplayStatusBar = False '여기서 매크로 코드를 응용 프로그램에 배치하십시오. 계산 = xlCalculation 자동 응용 프로그램. ScreenUpdating = True 응용 프로그램. DisplayStatusBar = True End Sub

Excel에서 이벤트 무시

매크로를 이벤트 프로 시저로 구현하여 워크 시트 또는 통합 문서가 변경 될 때 특정 코드를 실행하도록 Excel에 지시 할 수 있습니다.

때로는 표준 매크로가 이벤트 프로 시저를 트리거하는 변경을하는 경우가 있습니다. 예를 들어, Sheet1의 여러 셀을 조작하는 표준 매크로가있는 경우 해당 시트의 셀이 변경 될 때마다 Worksheet_Change 이벤트가 실행되는 동안 매크로가 일시 중지되어야합니다.

EnableEvents 속성을 사용하여 매크로가 실행되는 동안 Excel에 이벤트를 무시하도록 지시하여 다른 수준의 성능 향상을 추가 할 수 있습니다.

매크로를 실행하기 전에 EnableEvents 속성을 False로 설정하십시오. 매크로 코드 실행이 끝나면 EnableEvents 속성을 다시 True로 설정할 수 있습니다.

Sub Macro1 () 응용 프로그램. 계산 = xlCalculation 수동 응용 프로그램. ScreenUpdating = 거짓 어플리케이션. DisplayStatusBar = 거짓 응용 프로그램입니다. EnableEvents = False '여기에 매크로 코드를 배치하십시오. 계산 = xlCalculation 자동 응용 프로그램. ScreenUpdating = True 응용 프로그램. DisplayStatusBar = True 응용 프로그램입니다. EnableEvents = True End Sub

페이지 나누기 숨기기

매크로가 행 수를 수정하거나 열 수를 수정하거나 워크 시트의 페이지 설정을 변경하면 Excel에서 표시된 페이지 나누기를 다시 계산해야합니다 시트에.

매크로를 시작하기 전에 페이지 나누기를 숨기기 만하면이 동작을 피할 수 있습니다.

페이지 나누기를 숨기려면 DisplayPageBreaks 시트 속성을 False로 설정합니다. 매크로를 실행 한 후에도 페이지 나누기를 계속 표시하려면 DisplayPageBreaks 시트 속성을 다시 True로 설정하십시오.

Sub Macro1 () 응용 프로그램. 계산 = xlCalculation 수동 응용 프로그램. ScreenUpdating = 거짓 어플리케이션. DisplayStatusBar = 거짓 응용 프로그램입니다. EnableEvents = 거짓 액티브 시트. DisplayPageBreaks = False '여기에 매크로 코드를 배치하십시오. 계산 = xlCalculation 자동 응용 프로그램. ScreenUpdating = True 응용 프로그램. DisplayStatusBar = True 응용 프로그램입니다. EnableEvents = True Activesheet. DisplayPageBreaks = True End Sub

피벗 테이블 업데이트 일시 중단

매크로가 큰 데이터 원본을 포함하는 피벗 테이블을 조작하는 경우 피벗 필드를 동적으로 추가하거나 이동하는 경우 성능이 저하 될 수 있습니다.

모든 피벗 필드가 변경 될 때까지 피벗 테이블의 재 계산을 일시 중단하여 매크로의 성능을 향상시킬 수 있습니다. 단순히 피벗 테이블을 설정하십시오. ManualUpdate 속성을 True로 설정하여 재 계산을 연기하고 매크로 코드를 실행 한 다음 피벗 테이블을 설정합니다. ManualUpdate 속성을 다시 False로 설정하여 다시 계산을 트리거합니다.

Sub Macro1 () 액티브 시트. 피벗 테이블 ("피벗 테이블 1"). ManualUpdate = True '여기서 매크로 코드를 ActiveSheet에 놓습니다. 피벗 테이블 ("피벗 테이블 1"). Manual Recorder = False End Sub

복사 및 붙여 넣기 조정 취소

Macro Recorder는 VBA 코드를 작성하여 시간을 절약하지만 항상 가장 효율적인 코드를 작성하지는 않습니다. 가장 좋은 예는 녹음하는 동안 매크로 레코더가 복사 및 붙여 넣기 작업을 캡처하는 방법입니다.

중개인을 잘라내어 하나의 셀에서 대상 셀로 직접 복사하여 매크로를 약간 높일 수 있습니다. 이 대체 코드는 대상 인수를 사용하여 클립 보드를 무시하고 셀 A1의 내용을 셀 B1에 직접 복사합니다.

범위 ("A1"). 복사 대상: = 범위 ("B1")

값 (서식 또는 수식 제외) 만 복사해야하는 경우 Copy 메서드를 함께 사용하지 않으면 성능을 더욱 향상시킬 수 있습니다. 대상 셀의 값을 소스 셀에서 발견 된 값과 동일하게 설정하기 만하면됩니다. 이 메서드는 복사 메서드를 사용하는 것보다 약 25 배 빠릅니다.

Range ("B1"). 값 = 범위 ("A1"). 값

하나의 셀에서 다른 셀로 값 또는 서식이 아닌 수식 만 복사해야하는 경우 대상 셀 수식을 원본 셀에 포함 된 수식으로 설정할 수 있습니다.

범위 ("B1"). 수식 = 범위 ("A1"). 수식

With 문 사용

매크로를 기록 할 때 종종 동일한 개체를 두 번 이상 조작합니다. With 문을 사용하여 한 번에 주어진 개체에 대해 여러 가지 작업을 수행하여 시간을 절약하고 성능을 향상시킬 수 있습니다.

다음 예에서 사용 된 With 문은 Excel에 모든 서식 변경 내용을 한 번에 적용하도록 지시합니다.

With Range ("A1"). 세례반. Bold = True. 기울임 꼴 = 참. Underline = xlUnderlineStyleSingle End With

With 문으로 작업 청킹 습관을 얻는 것은 매크로를 빠르게 실행시킬뿐만 아니라 매크로 코드를 더 쉽게 읽을 수있게 해줍니다.

Select 메서드 피하기

Macro Recorder는 Select 메서드를 사용하여 개체를 명시 적으로 선택하여 작업을 수행하는 것을 선호합니다. 일반적으로 객체를 선택하기 전에 객체를 선택할 필요가 없습니다. 실제로 Select 메서드를 사용하지 않으면 매크로 성능을 크게 향상시킬 수 있습니다.

매크로를 기록한 후 생성 된 코드를 변경하여 Select 메소드를 제거하는 습관을 갖도록하십시오. 이 경우 최적화 된 코드는

Sheets ("Sheet1")과 같습니다. 범위 ("A1"). Formula1C1 = "1000"시트 ("시트 2"). 범위 ("A1"). Formula1C1 = "1000"시트 ("시트 3"). 범위 ("A1"). FormulaR1C1 = "1000"

아무것도 선택되지 않습니다.이 코드는 필요한 계층 구조를 사용하여 필요한 작업을 적용하기 만합니다.

워크 시트로의 이동 제한

매크로 속도를 높이는 또 다른 방법은 코드에서 워크 시트 데이터를 참조하는 시간을 제한하는 것입니다. 워크 시트의 데이터를 메모리에서 가져 오는 것보다 항상 효율적이지 않습니다. 즉, 반복적으로 워크 시트와 상호 작용할 필요가 없으면 매크로가 훨씬 빠르게 실행됩니다.

예를 들어 다음 간단한 코드는 VBA가 지속적으로 Sheets ("Sheet1")로 돌아가도록합니다. Range ("A1"): If 문에서 비교를 수행하는 데 필요한 숫자를 가져옵니다.

For ReportMonth = 1 To 12 Range ("A1"). Value = ReportMonth Then MsgBox 1000000 / ReportMonth End If 다음 ReportMonth

훨씬 효율적인 방법은 Sheets ("Sheet1")에 값을 저장하는 것입니다. 범위 ("A1")를 MyMonth라는 변수에 지정합니다. 이렇게하면 코드가 워크 시트 대신 MyMonth 변수를 참조합니다.

Dim MyMonth as Integer MyMonth = Range ("A1"). ReportMonth의 값 = 1 ~ 12 If MyMonth = ReportMonth Then MsgBox 1000000 / ReportMonth End If 다음 ReportMonth End If 워크 시트를 직접 참조하는 대신 변수를 활용하여 메모리의 데이터를 사용하는 것이 좋습니다.