Excel 엑셀 VBA 사용시 속도 향상법

1. 엑셀 VBA 최적화: 성능 향상을 위한 핵심 설정 변경 방법

1. 기본 설정 변경: 엑셀의 몇 가지 설정을 임시로 변경하여 속도를 개선할 수 있습니다. 이 설정들은 작업 후 원래대로 복원해야 합니다. 변경해야 할 설정은 다음과 같습니다:
   • Application.ScreenUpdating = False: 화면 업데이트를 중지하여 작업 중 화면이 갱신되지 않도록 합니다.
   • Application.DisplayStatusBar = False: 상태 바 표시를 중지합니다. (이 설정은 속도에 큰 영향을 주지 않을 수 있음)
   • Application.Calculation = xlCalculationManual: 셀 계산을 수동으로 설정하여 계산 지연을 줄입니다.
   • Application.EnableEvents = False: 이벤트 활성화를 중지하여 이벤트 처리에 따른 지연을 줄입니다.
   • ActiveSheet.DisplayPageBreaks = False: 페이지 나누기 기능을 비활성화합니다.
2. ScreenUpdating: 화면 업데이트 설정을 False로 할 경우, 작업 중 화면 갱신이 일어나지 않아, 작업 후 한번에 화면이 갱신되어 속도가 향상됩니다.
3. DisplayStatusBar: 상태 바 표시 여부는 속도 향상에 큰 영향을 주지 않지만, 필요에 따라 설정을 조절할 수 있습니다.
4. Calculation: 복잡한 시트나 많은 데이터를 가진 시트에서 이 설정을 False로 하면 계산 지연이 줄어들어 속도가 향상됩니다.
5. EnableEvents: 이벤트 실행 여부 설정은 이벤트가 많은 경우 False로 설정하여 성능을 개선할 수 있습니다.
6. DisplayPageBreaks: 페이지 나누기 기능은 일반적으로 자주 사용되지 않지만, 비활성화하면 속도 개선에 도움이 됩니다.

2. 엑셀 VBA에서 대규모 데이터 처리를 위한 효율적인 접근법과 속도 최적화

엑셀 VBA에서 대규모 데이터 범위를 처리할 때 성능을 개선하는 방법은, 데이터를 메모리에 한 번에 로드하여 처리한 후, 최종 결과를 엑셀 범위에 한 번에 적용하는 것입니다. 이 방법은 각 셀에 대해 개별적으로 값을 읽고 쓰는 것보다 훨씬 효율적입니다.

요약:

• 문제: 대규모 데이터를 처리할 때 각 셀에 값을 개별적으로 읽고 쓰면 속도가 느려집니다.
• 해결책: 전체 범위의 데이터를 배열 변수에 한 번에 할당하고, 이 배열에서 모든 계산을 수행한 후, 결과를 다시 엑셀 범위에 적용합니다.

예제 코드:

느린 소스:

Dim DataRange As Range
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double

Set DataRange = Range("A1:C10000")

For Irow = 1 To 10000
    For Icol = 1 To 3
        MyVar = DataRange(Irow, Icol)  ' 각 셀에서 값을 읽어올 때 속도 저하
        If MyVar > 0 Then
            MyVar = MyVar * MyVar  ' 값 변경
            DataRange(Irow, Icol) = MyVar  ' 각 셀에 값을 쓸 때 속도 저하
        End If
    Next Icol
Next Irow

빠른 소스:

Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double

DataRange = Range("A1:C10000").Value  ' 전체 범위를 메모리로 한 번에 로드

For Irow = 1 To 10000
    For Icol = 1 To 3
        MyVar = DataRange(Irow, Icol)
        If MyVar > 0 Then
            MyVar = MyVar * MyVar  ' 배열에서 값 변경
            DataRange(Irow, Icol) = MyVar
        End If
    Next Icol
Next Irow

Range("A1:C10000").Value = DataRange  ' 변경된 전체 데이터를 엑셀 범위에 한 번에 적용

3. 엑셀 VBA에서 Shape 작업 최적화: Select 메서드 대신 속성 직접 변경하기

엑셀 VBA에서 다수의 Shape(도형)에 작업을 할 때는 Select 메서드를 사용하지 말고 직접 속성을 변경하는 방법이 성능 향상에 도움이 됩니다. Select를 사용하면 각 도형을 개별적으로 선택하고 작업하는 과정에서 성능 저하가 발생할 수 있습니다.

요약:

• 문제: 많은 수의 Shape에 대해 Select를 사용하면 성능이 저하됩니다.
• 해결책: Select 대신 직접 Shape의 속성을 변경합니다.

예제 코드:

비효율적인 방법 (Select 사용):

For i = 0 To ActiveSheet.Shapes.Count - 1
    ActiveSheet.Shapes(i).Select
    Selection.Text = "Hello"  ' Select를 사용하여 텍스트 변경
Next i

효율적인 방법 (직접 속성 변경):

For i = 0 To ActiveSheet.Shapes.Count - 1
    ActiveSheet.Shapes(i).TextEffect.Text = "Hello"  ' 직접 속성을 변경하여 텍스트 변경
Next i

4. 엑셀 VBA에서 피벗 테이블의 자동 업데이트 기능을 끄고, 작업이 완료된 후에 다시 켜기

엑셀 VBA에서 피벗 테이블의 자동 업데이트 기능을 끄고, 작업이 완료된 후에 다시 켜는 것은 성능을 개선하는 데 도움이 됩니다. 이 방법은 특히 복잡한 계산이나 데이터 변경 작업을 수행할 때 유용합니다.

요약:

• 문제: 피벗 테이블이 데이터 변경에 반응하여 자동으로 업데이트되면 성능 저하가 발생할 수 있습니다.
• 해결책: 피벗 테이블의 자동 업데이트 기능을 잠시 끄고, 필요한 작업을 수행한 후 다시 켭니다.

예제 코드:

' 피벗 테이블의 자동 업데이트 기능 비활성화
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True

' 개발 코드 부분 (데이터 변경, 계산 등)

' 피벗 테이블의 자동 업데이트 기능 다시 활성화
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = False

이렇게 피벗 테이블의 자동 업데이트 기능을 제어함으로써, 복잡한 작업이나 대규모 데이터 처리 시 시간을 절약하고 성능을 향상시킬 수 있습니다. 특히 데이터 변경이 많은 작업에서 이 방법은 매우 유용합니다.

5.짜잘한 엑셀 VBA에서 코드의 성능과 가독성을 향상시키기

요약:

1. With 구문 사용: 객체의 여러 속성을 변경할 때 With 구문을 사용하여 코드를 더 간결하고 명확하게 만듭니다.
2. 공백 문자 사용: 공백 문자를 할당할 때는 "" 대신 vbNullString을 사용하여 성능을 최적화합니다.
3. 객체 메모리 해제: 사용한 객체는 Nothing으로 설정하여 메모리를 해제합니다.
4. 코드 한 줄로 작성: 가능하면 소스 코드를 한 줄로 작성하여 간결하게 만듭니다. 하지만 가독성과 성능 사이의 균형을 고려해야 합니다.

예제 코드:

With 구문 사용 전:

Range("A1").Select
Selection.Font.Bold = True
Selection.Font.Italic = True
Selection.Font.Underline = xlUnderlineStyleSingle

 

With 구문 사용 후:

With Range("A1").Font
    .Bold = True
    .Italic = True
    .Underline = xlUnderlineStyleSingle
End With

 

공백 문자 할당:

' 느린 방법
tmpString = ""

' 빠른 방법
tmpString = vbNullString

 

객체 메모리 해제:

Set tmpObject = Nothing

 

코드 한 줄로 작성:

' 두 줄로 작성
With Selection
    .WrapText = True
    .ShrinkToFit = False
End With

' 한 줄로 작성
With Selection
    .WrapText = True: .ShrinkToFit = False
End With

 

이러한 방법들은 코드의 실행 속도를 개선하고, 가독성을 높이며, 메모리 관리에도 도움이 됩니다. 그러나 코드의 간결성과 가독성 사이에서 적절한 균형을 찾는 것이 중요합니다.

6. 마지막 엑셀 VBA에서 코드의 성능과 가독성을 향상시키기 위한 팁

요약:

1. 불필요한 셀 복사 붙여 넣기 피하기: 클립보드를 사용하지 않고 직접 값을 복사하는 방법이 더 효율적입니다.
2. 엑셀 기본 함수 사용: 엑셀에서 제공하는 함수를 사용하면 직접 만든 로직보다 속도가 더 빠릅니다.
3. For Each 구문 사용: For Each 구문을 사용하여 코드의 실행 속도를 향상시킬 수 있습니다.

예제 코드:

셀 복사 붙여 넣기 - 클립보드 사용 코드:

Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial
Application.CutCopyMode = False

 

셀 복사 붙여 넣기 - 클립보드 미사용 코드:

Sheet2.Range("B1:B200").Value = Sheet1.Range("A1:A200").Value

 

셀 복사 붙여 넣기 (값만) - 클립보드 미사용 코드:

Sheet2.Range("B1:B200").Value = Sheet1.Range("A1:A200").Value

 

셀 복사 붙여 넣기 (수식만) - 클립보드 미사용 코드:

Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula

 

For Each 구문 사용 예시:

Dim cell As Range
For Each cell In Range("A1:A200")
    ' 코드 작성
Next cell

 

이러한 방법들은 코드의 성능을 개선하고, 가독성을 높이며, 메모리 사용을 최적화하는 데 도움이 됩니다. 코드 작성 시 이러한 팁을 활용하여 보다 효율적인 VBA 코드를 작성할 수 있습니다.