지금까지 엑셀 매크로 프로그래밍을 위한 명령어, 문법, 함수, 그리고 원하는대로 소스를 개선하는 방법에 대해 알아보았습니다. 이번 편은 마지막으로 엑셀 매크로를 활용하여 전체 구성을 이해하고 업무에 적용할 수 있는 방법을 상세히 설명하겠습니다.
.png)
단순 반복작업 자동화: 엑셀 매크로 활용 방법과 팁 #8/8 (전체구성 이해 및 업무 적용)
이제 마지막 단원입니다. 여기서는 지금까지 설명하고 보여줬던 코드를 확장하여 여러분의 실제 업무에 쉽게 적용할 수 있도록 개선해 보겠습니다.
목차
1. 기존 코드의 한계
2. 개선된 코드
기존 코드의 한계
이제 마지막 단원입니다. 여기서는 지금까지 설명하고 보여줬던 코드를 확장하여 여러분의 실제 업무에 쉽게 적용할 수 있도록 개선해 보겠습니다.
지금까지 사용했던 코드는 특정된 컬럼의 값이 조건에 부합할 경우 데이터를 미리 정의된 데로 가공하고 변경해 주는 것이었습니다. 그런데 만약 조건이 계속 추가되어야 할 경우 어떻게 해야 할까요? 특정된
컬럼이 아니라 필요에 따라 컬럼을 플렉시블하게 변경해야 한다면 어떻게 해야 할까요? 조건이 추가될 때마다 If를 추가하고 조건 컬럼이 바뀔 때마다 프로그램을 수정해야 할까요?
이렇게 해도 되겠지만 상당히 비효율적이 됩니다. 나의 경우 한달에 새로 추가되는 조건이 30개가 넘는 경우도 있었습니다. 만약 조건이 추가될 때마다 프로그램을 수정한다면 수백라인 수천라인이 될지도
모릅니다.
그래서 이전편의 소스를 조금 개선해서 새로운 조건이 추가되더라도 소스를 수정하지 않고 적용할 수 있도록 하겠습니다. 또한 매크로 프로그램을 알지 못하는 사람도 충분히 쉽게 활용할 수 있도록
범용성을 개선하였습니다.
우선 아래 그림을 보겠습니다.
뺄간색으로 된 적용조건 시트가 보일 것입니다. 이 시트는 아래와 같이 구성되어 있습니다.
무엇을 하려고 하는지 느낌이 오시나요? 그렇습니다. 조건을 추가해야 할 경우 프로그램을 수정하는 것이 아니라 이 시트에 조건과 변경값을 입력하면 매크로 프로그램에서 해당 조건을 읽어 자동으로 처리할 수
있도록 프로그램을 변경하려고 하는 것입니다.
시트를 조금 더 설명하면 조건은 최대 4개까지 설정이 가능하고 2개의 컬럼까지 변경할 수 있습니다.
각 행에 입력된 내용을 설명하면 아래와 같습니다.
2행은 B열이 “매출” 이고 C열이 “용역매출” 이며 D열이 “C팀”이면, G열을 “해외서비스”로 변경하라는 의미입니다.
3행은 B열이 “매출” 이면, F열의 값에 -1을 곱하라는 의미이며,
4행은 B열이 “원가” 이고 D열이 “A팀” 이면, E열을 “A팀 원가”로 변경하라는 의미입니다.
위의 설명을 통해 이 시트를 어떻게 작성해야 하는지 충분이 이해하셨을 것으로 믿습니다.
프로그램의 전체 동작은 아래와 같은 순서로 진행됩니다.
1) 조건을 읽고 저장할 2차원 배열 변수를 정의한다.
2) 2차원 배열에 적용조건 시트의 값을 읽어 저장한다.
3) Do while문을 사용하여 마지막 데이터까지 반복 처리한다.
어떤가요? 생각보다 단순하지 않나요? 전체를 구현한 코드는 아래와 같습니다.
'회계결산을 위한 일괄처리 매크로
Sub 개선된회계결산일괄처리()
'적용조건의 최대수량 정의
Dim RepMaxCnt As Integer
RepMaxCnt = 100
'적용조건 시트의 값을 읽어오기 위한 2차원 배열 정의
Dim RepAry(100, 12) As String
'작업행 지정 변수 정의, 프로그램 시작은 2행부터
Dim WorkingRow As Integer
WorkingRow = 2
'RepAry에 '적용조건 시트의 값을 읽어와 저장하기
'조건컬럼1 조건값1 조건컬럼2 조건값2 조건컬럼3 조건값3 조건컬럼4 조건값4 변경컬럼1 변경값1 변경컬럼2 변경값2
' 1 2 3
4 5 6 7
8 9 10 11 12
For i = 1 To RepMaxCnt
'적용조건 시트의 조건을 읽어들이되 값이 없으면 For를 빠져나옴
If Sheets("적용조건").Cells(i + 1, 1).Value = "" Then
Exit For
End If
For col = 1 To 12
RepAry(i, col) =
IIf(Trim(Sheets("적용조건").Cells(i + 1, col).Value) <> "", _
Trim(Sheets("적용조건").Cells(i + 1, col).Value),
"")
Next
Next
'작업행의 A열이 공백이 아닐 때까지 작업 수행
Do While Cells(WorkingRow, "A").Value <> ""
'적용조건의 최대 갯수만큼 For를 수행하되, 적용조건이 없으면 For를 빠져나옴
For RepRow = 1 To RepMaxCnt
If RepAry(RepRow, 1) <> "" Then
'첫번째 검색조건
if1 =
Trim(Cells(WorkingRow, RepAry(RepRow, 1)).Value)
'두번째 검색조건은 공백이
있을 수 있으므로 관련 처리
If
Trim(RepAry(RepRow, 3)) <> "" Then
if2 = Trim(Cells(WorkingRow, RepAry(RepRow, 3)).Value)
Else
if2 = "#N/A"
End If
'세번째 검색조건은 공백이
있을 수 있으므로 관련 처리
If
Trim(RepAry(RepRow, 5)) <> "" Then
if3 = Trim(Cells(WorkingRow, RepAry(RepRow, 5)).Value)
Else
if3 = "#N/A"
End If
'네번째 검색조건은 공백이
있을 수 있으므로 관련 처리
If
Trim(RepAry(RepRow, 7)) <> "" Then
if4 = Trim(Cells(WorkingRow, RepAry(RepRow, 7)).Value)
Else
if4 = "#N/A"
End If
'첫번째~4번째 검색조건의
조건값
vl1 =
RepAry(RepRow, 2)
vl2 =
RepAry(RepRow, 4)
vl3 =
RepAry(RepRow, 6)
vl4 =
RepAry(RepRow, 8)
'조건이 맞을 경우 변경할
열과 변경할 값
rc1 =
RepAry(RepRow, 9)
rc2 =
RepAry(RepRow, 11)
rv1 =
RepAry(RepRow, 10)
rv2 =
RepAry(RepRow, 12)
'검색조건 1~4까지 모두
맞을 경우 변경 실행
If (InStr(if1,
vl1) <> 0) And (InStr(if2, vl2) <> 0) And (InStr(if3,
vl3) <> 0) _
And (InStr(if4,
vl4) <> 0) Then
If rc1 <> "" Then
'만약 변경할 값의 첫번째 값이 *, /, +, -, & 일 경우 그 뒤에 있는 값을 해당 연산자로 계산
'예를 들어 변경컬럼1이 F이고 변경값1이 *-1 이라면
F열 값에 -1을 곱하라는 의미임
Select Case Left(rv1, 1)
Case "*"
Cells(WorkingRow, rc1) = Cells(WorkingRow, rc1) *
Mid(rv1, 2, Len(rv1) - 1)
Case "/"
Cells(WorkingRow, rc1) = Cells(WorkingRow, rc1) /
Mid(rv1, 2, Len(rv1) - 1)
Case "+"
Cells(WorkingRow, rc1) = Cells(WorkingRow, rc1) +
Mid(rv1, 2, Len(rv1) - 1)
Case "-"
Cells(WorkingRow, rc1) = Cells(WorkingRow, rc1) -
Mid(rv1, 2, Len(rv1) - 1)
Case "&"
Cells(WorkingRow, rc1) = Cells(WorkingRow, rc1) &
Mid(rv1, 2, Len(rv1) - 1)
Case Else
Cells(WorkingRow, rc1) = rv1
End Select
End If
If rc2 <> "" Then
Select Case Left(rv2, 1)
Case "*"
Cells(WorkingRow, rc2) = Cells(WorkingRow, rc2) *
Mid(rv1, 2, Len(rv2) - 1)
Case "/"
Cells(WorkingRow, rc2) = Cells(WorkingRow, rc2) /
Mid(rv1, 2, Len(rv2) - 1)
Case "+"
Cells(WorkingRow, rc2) = Cells(WorkingRow, rc2) +
Mid(rv1, 2, Len(rv2) - 1)
Case "-"
Cells(WorkingRow, rc2) = Cells(WorkingRow, rc2) -
Mid(rv1, 2, Len(rv2) - 1)
Case "&"
Cells(WorkingRow, rc2) = Cells(WorkingRow, rc2) &
Mid(rv1, 2, Len(rv2) - 1)
Case Else
Cells(WorkingRow, rc2) = rv2
End Select
End If
End If
Else
Exit For
End If
Next
'작업 Row를 다음행으로 바꿈
WorkingRow = WorkingRow + 1
Loop
End Sub
위의 소스는 처음보면 복잡해 보이지만 계속 보고 분석해 보면 어렵지 않게 이해할 수 있을 것입니다.
개인적인 바램이 있다면 이해하는 것에서 멈추지 말고 소스를 본인에 맞게 개선하고 발전시켰으면 합니다. 그렇게 프로그래밍 능력을 향상시킨 후 좀 더 욕심이 생긴다면 파이썬까지 공부한다면 더 높은 수준의
업무 자동화도 가능할 것입니다.
부족한 글을 읽어 주셔서 감사하며 여러분의 성공을 진심으로 바라며, 질문 언제든지 환영하니 저의 블로그에 글을 남겨 주시기 바랍니다.
.png)
