[엑셀 VBA] 매크로 속도 향상 방법_심화편(코드 작성법)

엑셀 VBA 매크로 속도 향상에 도움이 되는 주요 코드 작성법을 소개합니다.

 

---

    <본문 이동 링크>

• Activate, Select 사용 자제하기
• Copy, Paste 사용 자제하기
• For문에 Each 구문 사용하기
• 변수 사전에 선언하기
• With 구문 사용하기
• Arrary로 데이터 수정하기

---

Activate, Select 사용 자제하기

Activate와 Select는 불필요한 화면 이동과 동작을 발생시켜 속도 저하가 발생함

불필요한 코드를 아래와 같이 수정하면 약 90% 이상 속도 향상됨

※ 실행코드에 따라 효과는 달라질 수 있음

 

(기존코드) _ 소요시간 0.017초

Sub Activate_Select_Before()
    
    Sheets(2).Activate
    Range("A1").Select
    ActiveCell.Value = "test1"
    Range("B1").Select
    ActiveCell.Value = "test2"
    Range("C1").Select
    ActiveCell.Value = "test3"
    Range("D1").Select
    ActiveCell.Value = "test4"
    
End Sub

 

(변경코드) _ 소요시간 0.001초

Sub Activate_Select_After()
    
    Sheets(2).Range("A1").Value = "test1"
    Sheets(2).Range("B1").Value = "test2"
    Sheets(2).Range("C1").Value = "test3"
    Sheets(2).Range("D1").Value = "test4"
    
End Sub

 

Copy, Paste 사용 자제하기

Copy, Paste 대신 바로 값을 대입하면 약 70% 이상 속도 향상 가능

※ 실행코드에 따라 효과는 달라질 수 있음

(기존코드) _ 소요시간 0.021초

Sub Copy_Paste_Before()
    
    Range("A1:A1000").Copy Range("B1:B1000")
    
End Sub

 

(변경코드) _ 소요시간 0.005초

Sub Copy_Paste_After()
    
    Range("A1:A1000").Value = Range("A1:A1000").Value
    
End Sub

 

For문에 Each 구문 사용하기

For문 사용시 Each 명령어 사용해서 약 10% 이상 속도 향상 가능

※ 실행코드에 따라 효과는 달라질 수 있음

(기존코드) _ 소요시간 0.218초

Sub For_Each_Before()
    
    Dim r As Integer
    
    For r = 1 To 10000
        Cells(r, 1).Value = 1
    Next r
    
End Sub

 

(변경코드) _ 소요시간 0.186초

Sub For_Each_After()
    
    Dim EachCell As Range
    
    For Each EachCell In Range("A1:A10000")
        EachCell.Value = 1
    Next EachCell
    
End Sub

 

변수 사전에 선언하기

변수 선언을 사전에 선언하도록 설정해두고 코드를 짜면 코드가 길어질수록 실행 시간이 단축됩니다.

Option Explicit

▼▼▼변수 선언 요구 관련글▼▼▼

 

[엑셀/엑셀 VBA] - [엑셀 VBA] 편집창(화면) 초기세팅

[엑셀 VBA] 편집창(화면) 초기세팅

 

With 구문 사용하기

반복되는 오브젝트(Workbooks, Worksheets, Ranges) 등을 with구문으로 묶어주면 코드 보기가 용이하고 약간의 속도가 빨라짐

(기존코드)

Sub With_Before()
    
    Sheets(2).Range("A1").Value = "test1"
    Sheets(2).Range("B1").Value = "test2"
    Sheets(2).Range("C1").Value = "test3"
    Sheets(2).Range("D1").Value = "test4"
    
End Sub

 

(변경코드)

Sub With_After()

    With Sheets(2)
        .Range("A1").Value = "test1"
        .Range("B1").Value = "test2"
        .Range("C1").Value = "test3"
        .Range("D1").Value = "test4"
    End With
    
End Sub

 

Arrary로 데이터 수정하기

넓은 범위의 셀을 계산할 때는 셀을 순화시켜 값을 조정하기보다는 배열에 값을 저장 후 운용하는게 속도 향상에 도움이 됨

(기존코드) _ 소요시간 0.026초

Sub Array_Before()
    
    Dim Cell As Range
    
    For Each Cell In Range("A1:A1000")
        Cell.Value = Cell.Value + 100
    Next Cell
    
End Sub

 

(변경코드) _ 소요시간 0.006초

Sub Array_After()

    Dim Arr_table() As Variant
    Dim Value_Arr As Variant
    
    Arr_table = Range("A1:A1000").Value
    
    For Each Value_Arr In Arr_table
        Value_Arr = Value_Arr + 100
    Next Value_Arr
    
    Range("A1:A1000") = Arr_table
    
End Sub