엑셀 | 매크로는 Ctrl+Z가 안 먹는다

매주 같은 짓을 반복하는 파일이 있다. 내려받고, 필요 없는 열 지우고, 머리글에 색 넣고, 열 너비 맞추고, 다른 이름으로 저장. 손에 익어서 5분이면 끝나는데 1년이면 네 시간이다.

이 정도는 엑셀이 이미 할 줄 안다. 코드를 쓰는 게 아니라, 내가 하는 조작을 받아 적었다가 재생하는 기능이다.

개발 도구 탭부터 꺼낸다

기본값이 꺼져 있어서 여기서부터 막힌다. 윈도우는 파일 → 옵션 → 리본 사용자 지정에서 “개발 도구”에 체크. 맥은 엑셀 → 기본 설정 → 리본 및 도구 모음에서 같은 항목을 켠다.

탭이 생겼으면 매크로 기록을 누르고 늘 하던 작업을 평소처럼 한다. 끝나면 기록 중지. 그게 전부다.

대화상자에서 챙길 게 둘 있다. 이름에는 공백과 특수문자를 못 쓰니 밑줄로 붙이고, 바로 가기 키는 이미 쓰이는 단축키를 뺏지 않는 걸로 잡는다. Cmd + C 같은 걸 가져가 버리면 나중에 복사가 안 돼서 한참 헤맨다.

그런데 위 그림처럼 한글 이름을 넣으면 맥에서는 안 넘어간다. “The syntax of this name isn’t correct”가 뜨고 제자리다. 밑줄을 빼고 주간보고정리로 해도 마찬가지고, 같은 자리에 WeeklyReport_Clean을 넣으면 그냥 통과한다. 한글이 문제인 것 같다. 윈도우에서는 한글 이름이 되는 걸로 알고 있어서, 양쪽을 오가며 쓸 파일이면 처음부터 영문으로 짓는 편이 안전하겠다.

절대 참조와 상대 참조

잘 돌던 매크로가 어느 순간부터 엉뚱한 셀을 건드린다면 대개 이것 때문이다.

기본값인 절대 참조로 기록하면 “B2를 선택하라”가 그대로 저장된다. 커서가 어디 있든 항상 B2로 간다. 개발 도구의 “상대 참조로 기록”을 눌러 두고 기록하면 “지금 커서에서 아래로 한 칸”으로 저장된다.

항상 같은 자리의 표를 정리하거나 머리글 서식·인쇄 설정을 잡는 일은 절대 참조. 커서 위치부터 아래로 반복 처리하는 일은 상대 참조. 둘을 섞어야 하는 작업이면 기록만으로는 한계가 온다.

녹화된 코드는 대체로 군더더기가 많다

Alt + F11로 편집기를 연다. 맥이면 도구 → 매크로 → Visual Basic Editor. 왼쪽 트리에서 모듈 → Module1을 열면 방금 기록한 게 코드로 나와 있다.

셀을 하나 고르고 행 높이를 바꾸고, 한 칸 내려가서 열 너비를 바꿨다. 딱 이 네 동작만 기록해 봤다.

Sub로 시작해 End Sub로 끝나고, 그 사이 한 줄이 조작 하나다. 대상이 앞에 오고 동작이 점 뒤에 붙는 “무엇을 . 어떻게” 순서라고 보면 대부분 읽힌다.

눈에 띄는 건 .Select와 Selection.이 번갈아 나온다는 점이다. 셀을 고르고, 뭔가 하고, 또 고르고, 또 하고. 마우스로 클릭한 걸 그대로 받아 적어서 그렇다. 사람이 쓰면 이렇게 줄어든다.

Range("A2").RowHeight = 30
Range("A3").ColumnWidth = 15

네 줄이 두 줄이 됐다. 선택 단계를 빼면 화면 깜빡임이 줄고 속도도 붙는다. 새로 짜는 게 아니라 기록된 걸 다듬는 정도라, 몇 번 해 보면 문법이 절반쯤 눈에 들어오는 것 같다.

기록으로 안 되는 지점에서 필요한 네 가지

행 개수가 매번 다르거나, 조건에 따라 다르게 처리해야 할 때다. 넘어가는 데 필요한 문법은 사실상 넷뿐인 듯 싶다.

데이터 끝을 찾는다. A열 맨 아래에서 Ctrl + ↑를 누른 결과라고 보면 된다.

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

2행부터 마지막 행까지 훑는다.

For i = 2 To lastRow
    Cells(i, "C").Value = Cells(i, "A").Value * Cells(i, "B").Value
Next i

값에 따라 갈라진다.

If Cells(i, "C").Value >= 1000000 Then
    Cells(i, "D").Value = "고액"
Else
    Cells(i, "D").Value = "일반"
End If

그리고 MsgBox lastRow 한 줄. 실제로 몇 행까지 잡혔는지 바로 보인다. 디버깅의 9할이 이거다.

넷이면 시트 전체를 돌며 조건에 따라 표시하고 저장하는 수준까지 된다. 뒤집어 말하면 셀 값을 조건으로 비교하는 정도는 SUMIF·COUNTIF나 조건부 서식으로 끝나는 경우가 많다. 그걸 매크로로 만들면 유지보수만 는다.

되돌리기가 없다

매크로를 실행하면 Ctrl + Z가 안 먹는다. 매크로가 지운 열은 그냥 사라진다. 아무래도 여기서 제일 크게 데는 것 같다.

그래서 새로 만든 매크로는 반드시 사본에서 먼저 돌린다. 몇 번 돌려 보고 괜찮다 싶을 때 원본에 붙인다. 첫 줄에 백업을 만들어 두는 것도 방법이고.

ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
    "backup_" & Format(Now, "yyyymmdd_hhmm") & ".xlsx"

경로 구분자를 "\"로 박아 둔 코드를 자주 보는데 맥에서는 그러면 안 된다. Application.PathSeparator를 쓰면 양쪽에서 다 돈다.

xlsm으로 저장하지 않으면 코드가 사라진다

다 해 놓고 저장하면 매크로 제외 통합 문서로는 저장할 수 없다는 경고가 뜬다. 여기서 무심코 예를 누르면 코드만 쏙 빠진 채 저장된다. 파일은 멀쩡해 보이는데 매크로만 없다.

.xlsx는 매크로를 못 담고 .xlsm이 담는다. 매크로를 만들었으면 저장할 때 형식을 바꾸는 걸 손버릇으로 들여야 한다.

받은 사람 쪽에서 안 열린다면 대개 보안 설정이다. 회사 PC는 매크로 포함 파일을 기본 차단해 두는 경우가 많아서, 파일 속성에서 차단 해제를 하거나 신뢰할 수 있는 위치에 넣어야 한다. 남에게 배포할 물건이라면 이 안내까지 같이 줘야 덜 물어본다.

결론. 사본에서 먼저 돌리자

되돌리기가 없다는 것 하나만 몸에 배면 나머지는 해 보면서 익히면 될 것 같다. 기록 → 코드 열어 보기 → .Select 지우기, 이 순서가 제일 덜 무섭다.

On Error 처리까지 챙기는 건 아무래도 한 번 데어 보고 나서일 것 같다.