거래처별 엑셀 시트 나누는 매크로를 리팩토링 한다.
Refactoring
성능면에서 최적의 성능을 구현하고 싶고, 그래야 하는 것이 간지나는 개발자가 아닐까?
엑셀 vba 매크로를 해보면서도 느낀다.
사람이 해야 했던 업무들이 자동화로 대체가 되면서 업무시간이 눈에 띄게 줄었다고,
몇 가지 추가할 수 있는지 물어보는 친구 덕분에 나도 vba 매크로 를 좀 더 해볼 수 있었다.
생각보다 성능이 별로라 의아했던 매크로를 수정하면서
역시 구린건 내 실력이었고, 초기 버전을 많이 개선할 수 있었던 경험이 생겼다.
요구사항은 항상 변하고 추가된다. 그래서 기술이 발전하는 것이 아닐까 생각한다.
발견된 문제점
문제점
- 첫 행부터 마지막 행까지 루프를 도는 비효율
- 시트를 생성한 뒤, 마지막에 한번에 저장하는 오버헤드
- 처리 시간이 지연되는 동작들
페르소나
생각해보자
엑셀에서 데이터를 복사해서, 새로운 시트를 생성하고 붙여넣기 하는 작업이 잇따.
몇 줄 안되는 데이터라고 해도 업무 처리 하는 방법이 썩 좋은 것 같지 않다.
근데 데이터가 수백 줄, 수천 줄의 데이터라면 어떻게 되겠는가?
어떻게 해결할 수 있을까?
먼저, 엑셀에는 정렬 기능이 있다.
우리는 그 기능을 사용하여 거래처명으로 정렬을 할 수 있다. 굉장히 빨리 끝난다.
그 후, 동일한 거래처명 데이터를 셀 선택을 통해서 한 번에 복사-붙여넣기 작업을 하자.
왠지 vba 성능이 확 올라갈 것 같은 느낌이 든다.
알고리즘 수정
- 수정
- 수정_진짜수정
- 수정_진짜수정_마지막수정
- 최종
- 최종_최종수정
전해들은 바에 의하면,
아래 vba 소스 코드 기반 매크로로
몇 시간이 걸리던 작업을 수초~수십초 만에 대체할 수 있었다고 한다.
추가 사항으로는 거래처가 요구한 양식대로 최종 거래처.xlsx 파일을 저장해야 하는 프로세스가 필요했다.
초기 버전보다 성능도 꽤 업그레이드 됐다고 생각한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
Dim venderTemplate
'거래처가 요구한 양식으로 시트를 저장해야 하는 경우가 생겼다.
'해당 거래처 양식으로 저장해야 하는 거래처 목록이다.
Function useVenderTemplate() As Object
'...
End Function
'자동화 과정에서 시트를 초기화 해야하는 로직이 들어있고, 항상 메인 시트를 활성화할 필요가 있었다.
Function initSheets()
'...
End Function
'거래처 양식을 불러온다.
Function getTemplateName(vender As String)
'거래처 양식이 저장되어 있는 파일
'거래처 양식은 항상 시트 1번에 있어야 하고, 우리가 복사한 시트는 2번째에 있도록 한다.
'시트가 항상 2개가 생성되어 있어야 한다.
getTemplateName = "_" & vender & "_양식.xlsx"
End Function
'거래처 양식 파일에 데이터를 복사한다.
Function createTemplate(vender As String)
templateFile = getTemplateName(vender)
templateFilePath = ThisWorkbook.Path & "\양식다른업체\" & templateFile
'복사할 데이터 row수
dataRowCount = ActiveSheet.Range("a1", Range("a1").End(xlDown)).Rows.Count
'원본 엑셀 파일의 2번째 시트에 복사할 데이터가 들어있음
ActiveSheet.Range("A1:V" & dataRowCount).Copy
'거래처 양식이 들어있는 template 파일 열기
Workbooks.Open Filename:=templateFilePath
'거래처 양식의 2번째 시트 선택
Workbooks(templateFile).Sheets(2).Select
'2번째 시트의 첫번째 칸부터 데이터 붙여넣기
ActiveSheet.Paste Destination:=ActiveSheet.Range("A1")
'2번째 시트에 데이터를 복사하면, 1번째 시트에 데이터가 자동완성되니까 이때, 시트1의 데이터를 복사
Workbooks(templateFile).Sheets(1).Select
'연결되어 있는 수식들을 값으로 대체
If venderTemplate.Item(vender) Then
With ActiveSheet.UsedRange
.Value = .Value
End With
'2번째 시트 지우기
Sheets(2).Delete
End If
End Function
'거래처 양식 파일을 닫는다.
Function closeTemplate(vender As String)
'...
End Function
'시트를 저장한다.
Function saveSheets()
'데이터 원본인 1번째 시트 제외, 나머지 시트 전부 저장
For i = 2 To Sheets.Count
ThisWorkbook.Sheets(i).Select
hasTemplate = hasVenderTemplate(ActiveSheet.Name)
vender = ActiveSheet.Name
If hasTemplate Then
createTemplate(vender)
Else
ActiveSheet.Copy
End If
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Format(Date, "yymmdd") & "_" & vender & "_결산.xlsx"
ActiveWorkbook.Close
Next
End Function
'거래처 명을 확인한다.
Function hasVenderTemplate(toBeFound As String) As Boolean
'...
End Function
'성능 높이기
Function configure(flag As Boolean)
'...
End Function
'메인 매크로
Sub Extractor()
configure (False)
initSheets
'템플릿이 필요한 거래처
Set vnederTemplate = useVenderTemplate()
'메인 시트(첫번째 시트)의 전체 행수
dataSheetRowCount = Range("a1", Range("a1").End(xlDown)).Rows.Count
'업체명 오름차순 정렬
'a열의 2번째 행부터, z열의 끝 행까지를 대상으로 정렬을 하는데, 만약 원본시트에 데이터가 z열 보다 많다면, 아래에서 z 를 수정하면 됨
Range("A2:Z" & dataSheetRowCount).Sort Key1:=Range("A1"), Order1:=xlAscending
'현재 거래처명은 타이틀행인 1행 다음행인 2행의 거래처명이 들어있는 열의 값
currentVenderName = Cells(2, columnIndexOfVender).Value
'데이터가 들어있지 않은 행까지 loop 를 돌리면서 거래처 명 변화를 체크
For i = startRowToCopy To dataSheetRowCount + 1
If currentVenderName <> Cells(i, columnIndexOfVender).Value Then
'거래처 명이 바뀔 경우, 그 이전의 거래처에 해당하는 데이터 복사를 위해 시트 생성
'메인시트 다음에 새로운 시트 추가
Worksheets.Add After:=Worksheets(1)
'...
'데이터 시트의 startRow부터 i번재까지 복사
Worksheets(1).Range(startColumnToCopy & startRowToCopy & ":" & endColumnToCopy & i - 1).Copy
'거래처 시트의 타이틀 행 다음행(a2)에 몽땅 붙여넣기
With Worksheets(currentVenderName).Range("A2")
.PasteSpecial xlPasteAll
End With
'최종 시트 저장
saveSheets
End If
Next
configure (True)
End Sub
엑셀 매크로 성능 최적화
아래 환경값을 false 로 셋팅해두면 좀 더 빠른 처리속도를 기대할 수 있다.
1
2
Application.ScreenUpdating '코드 실행 동안 화면 업데이트
Application.Displaystatusbar '상태 표시줄에 현재 작업 상태 표시
이 외에도 vba 최적화, vba 속도 높이기 방법은 많다.
생각해 볼 것
예외 처리를 어떻게 할 것인지에 대한 고민을 했다.
일반적인 개발 상황이라면, 입력값 등의 예외 처리에 대해 충분한 고려가 필요했겠으나,
이번 vba 매크로의 경우 단순한 셀 복사를 위한 자동화이기 때문에 특별한 에러처리가 필요하지 않다는 결론을 내렸다.
- 수 년간 쌓인 양식이므로 프로세스 또한 쉽게 바뀔 일은 크게 없을 것이라는 결론과 함께
필요하면 언제든 연락해라
결과
하루 반나절 이상 관련 업무를 해야 했는데, 수 초 ~ 수십 초 만에 업무가 끝났다고 한다.
vba 를 좀 더 검색해보니 크롤링이나 메일 보내는 것들도 가능하다고 한다.
해볼 수 있는 것들이 많을 것 같다.
- 첫 행부터 마지막 행까지 루프를 도는 문제점
- 시트를 생성한 뒤, 마지막에 한번에 저장하는 문제점
- 자동화 시간 동안, 일일히 화면을 보고 있어야 하는 문제점
연관 글 : [c#] 클라이언트에게 동시 메일 보내기