excel-vba
Excel-VBA 최적화

소개

비고

워크 시트 업데이트 비활성화

워크 시트의 계산을 사용하지 않으면 매크로의 실행 시간이 현저하게 줄어들 수 있습니다. 또한 이벤트, 화면 업데이트 및 페이지 나누기를 비활성화하면 도움이됩니다. 다음 Sub 는이 목적으로 모든 매크로에서 사용할 수 있습니다.

Sub OptimizeVBA(isOn As Boolean)
    Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
    Application.EnableEvents = Not(isOn)
    Application.ScreenUpdating = Not(isOn)
    ActiveSheet.DisplayPageBreaks = Not(isOn)
End Sub

최적화를 위해 아래의 의사 코드를 따르십시오.

Sub MyCode()
    
    OptimizeVBA True

    'Your code goes here

    OptimizeVBA False

End Sub

실행 시간 확인

다른 절차를 통해 동일한 결과를 얻을 수 있지만 서로 다른 처리 시간을 사용하게됩니다. 어떤 것이 빠르는지 체크하기 위해 다음과 같은 코드를 사용할 수 있습니다 :

time1 = Timer

For Each iCell In MyRange
   iCell = "text"
Next iCell

time2 = Timer

For i = 1 To 30
   MyRange.Cells(i) = "text"
Next i

time3 = Timer

debug.print "Proc1 time: " & cStr(time2-time1)
debug.print "Proc2 time: " & cStr(time3-time2)

MicroTimer :

Private Declare PtrSafe Function getFrequency Lib "Kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "Kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long

Function MicroTimer() As Double
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency

    MicroTimer = 0
    If cyFrequency = 0 Then getFrequency cyFrequency        'Get frequency
    getTickCount cyTicks1                                   'Get ticks
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 'Returns Seconds
End Function

With 블록 사용하기

블록과 함께 사용하면 매크로를 실행하는 프로세스가 빨라질 수 있습니다. 범위, 차트 이름, 워크 시트 등을 쓰는 대신 아래와 같이 with-blocks을 사용할 수 있습니다.

With ActiveChart
    .Parent.Width = 400
    .Parent.Height = 145
    .Parent.Top = 77.5 + 165 * step - replacer * 15
    .Parent.Left = 5
End With 

이보다 더 빠릅니다.

ActiveChart.Parent.Width = 400
ActiveChart.Parent.Height = 145
ActiveChart.Parent.Top = 77.5 + 165 * step - replacer * 15
ActiveChart.Parent.Left = 5

노트:

• With 블록을 입력하면 개체를 변경할 수 없습니다. 결과적으로 여러 개의 다른 객체에 영향을 미치기 위해 하나의 With 문을 사용할 수 없습니다.

• With 블록 안팎으로 뛰어 들지 마십시오 . With 블록의 문이 실행되지만 With 또는 End With 문이 실행되지 않으면 프로 시저를 종료 할 때까지 개체에 대한 참조를 포함하는 임시 변수가 메모리에 남아 있습니다

• 특히 With 캐시 된 객체가 반복자로 사용되는 경우 With 문을 반복하지 마십시오.

• With 문을 다른 문 안에 넣음으로써 문을 중첩시킬 수 있습니다. 그러나 With With 블록의 멤버는 In With 블록 내에서 마스크되므로 In With 블록의 정규화 된 개체 참조를 Out With 블록의 개체 멤버에게 제공해야합니다.

중첩 예 :

이 예제에서는 With 문을 사용하여 단일 개체에 대해 일련의 문을 실행합니다.
객체와 속성은 설명을 위해 사용 된 일반적인 이름입니다.

With MyObject 
    .Height = 100               'Same as MyObject.Height = 100. 
    .Caption = "Hello World"    'Same as MyObject.Caption = "Hello World". 
    With .Font 
        .Color = Red            'Same as MyObject.Font.Color = Red. 
        .Bold = True            'Same as MyObject.Font.Bold = True. 
        MyObject.Height = 200   'Inner-most With refers to MyObject.Font (must be qualified
    End With
End With

MSDN 에 대한 자세한 정보

행 삭제 - 실적

• 셀을 루핑하고 행을 하나씩 삭제할 때 특히 행 삭제가 느립니다.

• 다른 방법은 자동 필터를 사용하여 삭제할 행을 숨기는 것입니다.

• 보이는 범위 복사 및 새로운 워크 시트에 붙여 넣기

• 초기 시트를 완전히 제거하십시오.

• 이 방법을 사용하면 삭제할 행이 많을수록 더 빨라집니다.

예:

Option Explicit

'Deleted rows: 775,153, Total Rows: 1,000,009, Duration: 1.87 sec

Public Sub DeleteRows()
    Dim oldWs As Worksheet, newWs As Worksheet, wsName As String, ur As Range

    Set oldWs = ThisWorkbook.ActiveSheet
    wsName = oldWs.Name
    Set ur = oldWs.Range("F2", oldWs.Cells(oldWs.Rows.Count, "F").End(xlUp))

    Application.ScreenUpdating = False
    Set newWs = Sheets.Add(After:=oldWs)    'Create a new WorkSheet

    With ur    'Copy visible range after Autofilter (modify Criteria1 and 2 accordingly)
        .AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>"
        oldWs.UsedRange.Copy
    End With
    'Paste all visible data into the new WorkSheet (values and formats)
    With newWs.Range(oldWs.UsedRange.Cells(1).Address)
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteAll
        newWs.Cells(1, 1).Select: newWs.Cells(1, 1).Copy
    End With

    With Application
        .CutCopyMode = False
        .DisplayAlerts = False
            oldWs.Delete
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    newWs.Name = wsName
End Sub

모든 Excel 기능 비활성화 큰 매크로를 실행하기 전에

아래 절차에 따라 WorkBook 및 WorkSheet 수준의 모든 Excel 기능이 일시적으로 비활성화됩니다.

• FastWB ()는 On 또는 Off 플래그를 허용하는 토글입니다.

• FastWS ()는 선택적 WorkSheet 객체를 허용하거나 사용하지 않습니다.

• ws 매개 변수가 누락되면 콜렉션의 모든 워크 시트에 대해 모든 기능을 켜고 끕니다

  • 사용자 정의 유형을 사용하여 모든 설정을 끄기 전에 캡처 할 수 있습니다
  • 프로세스가 끝나면 초기 설정을 복원 할 수 있습니다.

Public Sub FastWB(Optional ByVal opt As Boolean = True)
    With Application
        .Calculation = IIf(opt, xlCalculationManual, xlCalculationAutomatic)
        If .DisplayAlerts <> Not opt Then .DisplayAlerts = Not opt
        If .DisplayStatusBar <> Not opt Then .DisplayStatusBar = Not opt
        If .EnableAnimations <> Not opt Then .EnableAnimations = Not opt
        If .EnableEvents <> Not opt Then .EnableEvents = Not opt
        If .ScreenUpdating <> Not opt Then .ScreenUpdating = Not opt
    End With
    FastWS , opt
End Sub

Public Sub FastWS(Optional ByVal ws As Worksheet, Optional ByVal opt As Boolean = True)
    If ws Is Nothing Then
        For Each ws In Application.ThisWorkbook.Sheets
            OptimiseWS ws, opt
        Next
    Else
        OptimiseWS ws, opt
    End If
End Sub
Private Sub OptimiseWS(ByVal ws As Worksheet, ByVal opt As Boolean)
    With ws
        .DisplayPageBreaks = False
        .EnableCalculation = Not opt
        .EnableFormatConditionsCalculation = Not opt
        .EnablePivotTable = Not opt
    End With
End Sub

모든 Excel 설정을 기본값으로 복원

Public Sub XlResetSettings()    'default Excel settings
    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .DisplayStatusBar = True
        .EnableAnimations = False
        .EnableEvents = True
        .ScreenUpdating = True
        Dim sh As Worksheet
        For Each sh In Application.ThisWorkbook.Sheets
            With sh
                .DisplayPageBreaks = False
                .EnableCalculation = True
                .EnableFormatConditionsCalculation = True
                .EnablePivotTable = True
            End With
        Next
    End With
End Sub

확장 디버깅을 통한 오류 검색 최적화

줄 번호 사용하기 ... 오류 발생시 문서화하기 ( "Erl을 보는 것의 중요성")

어떤 라인이 에러를 발생시키는 지 탐지하는 것은 디버깅의 중요한 부분이며 원인 찾기를 줄입니다. 식별 된 오류 라인을 짧은 설명과 함께 문서화하려면 모듈 및 프로 시저의 이름과 함께 성공적인 오류 추적을 완료하십시오. 아래 예제는 이러한 데이터를 로그 파일에 저장합니다.

배경

오류 개체는 오류 번호 (Err.Number) 및 오류 설명 (Err.Description)을 반환하지만 오류를 찾을 위치에 대한 질문에 명시 적으로 응답하지 않습니다. ERL 기능을하지만, 않습니다,하지만 당신은 코드 (구 기본 번에 여러 가지 다른 양보의 BTW 일)에 * 행 번호)를 추가 조건.

오류 줄이 전혀없는 경우 Erl 함수는 0을 반환하고 번호 매기기가 완료되지 않은 경우 프로 시저의 마지막 줄 번호를 가져옵니다.

Option Explicit


Public Sub MyProc1()
Dim i As Integer
Dim j As Integer
On Error GoTo LogErr
10     j = 1 / 0    ' raises an error
okay:
Debug.Print "i=" & i
Exit Sub

LogErr:
MsgBox LogErrors("MyModule", "MyProc1", Err), vbExclamation, "Error " & Err.Number
Stop
Resume Next
End Sub

Public Function LogErrors( _
           ByVal sModule As String, _
           ByVal sProc As String, _
           Err As ErrObject) As String
' Purpose: write error number, description and Erl to log file and return error text
  Dim sLogFile As String: sLogFile = ThisWorkbook.Path & Application.PathSeparator & "LogErrors.txt"
  Dim sLogTxt  As String
  Dim lFile    As Long

' Create error text
  sLogTxt = sModule & "|" & sProc & "|Erl " & Erl & "|Err " & Err.Number & "|" & Err.Description

  On Error Resume Next
  lFile = FreeFile

  Open sLogFile For Append As lFile
  Print #lFile, Format$(Now(), "yy.mm.dd hh:mm:ss "); sLogTxt
      Print #lFile,
  Close lFile
' Return error text
  LogErrors = sLogTxt
 End Function

' 로그 파일을 보여주는 추가 코드

Sub ShowLogFile()
Dim sLogFile As String: sLogFile = ThisWorkbook.Path & Application.PathSeparator & "LogErrors.txt"

On Error GoTo LogErr
Shell "notepad.exe " & sLogFile, vbNormalFocus

okay:
On Error Resume Next
Exit Sub

LogErr:
MsgBox LogErrors("MyModule", "ShowLogFile", Err), vbExclamation, "Error No " & Err.Number
Resume okay
End Sub