[Excel]매크로 기록 활용 예제(with 상대참조로 )

다른 매체들에서 매크로 관련 글들을 쓰면서, 자동화를 위한 VBA 초보자들이나, VBA를 당장 어려운 분들은 반복 작업을 위해 매크로 녹화를 활용할 수 있다고 많이 소개드려왔습니다. 또, 매크로 녹화를 한 후 기록된 매크로 코드를 뜯어보면서 필요한 기능의 코드를 알 수도 있습니다.

글을 쓸 때마다 예제 만들기가 어려웠는데, 오늘은 좋은 예제가 있어 같이 만들어보며 감을 잡아보려고 합니다.

 

자료는 다음과 같습니다.

엑셀 매크로 기록 활용을 위한 원본 자료

 

위의 자료 형태로 60개정도 자료가 있는데, 이 자료를 아래와 같이 바꿔서 정리하고 싶습니다.

 

우리가 원하는 최종 자료, 엑셀 매크로 만들기가 가능할까?

한눈에 생각했을 때, 줄 사이사이마다 새로운 행을 삽입해주고, 일일히 '셀병합'을 해주고, 어떤 부분은 잘라내서 다른 부분에 붙여넣기 까지 하는 등 복잡해 보입니다.

하지만 여기서 중요한 것은 이 복잡한 작업들이 '반복' 되는 '반복작업'이라는 것이 겁이 난다는 점입니다.

어떤 작업이 조금 복잡하더라도 일정한 규칙으로 반복만 된다면, 얼마든지 코드를 짜서 자동화할 수 있습니다. 코드를 못짜신다구요? 걱정하지 마세요. '매크로 기록' 기능을 쓰면 컴퓨터가 알아서 코드를 짜줍니다.

이렇게 하면 작업을 '녹화' 해놨다가 '재생' 하는 것처럼 반복시킬 수 있습니다.

 

이제 어떻게 하는지 알아봅시다.

 

1.기본동작 알려주기

반복할 기본동작을 컴퓨터에 알려줘야 합니다. 그래야 그 기본값을 컴퓨터가 적어놨다가 앞으로도 써먹을 수 있습니다.

먼저 1A Cell 을 선택합니다.  매우 중요합니다. 동작을 시작할 위치를 알려주는 작업이거든요.

두번째로, [개발도구] 에서 [상대 참조로 기록]을 클릭해줍니다.

잠깐만, 개발도구가 없다구요? 

개발도구 리본메뉴에 추가하기.gif

긴 설명할 시간 없습니다. 위 영상 보세요ㅎㅎ

상대 참조까지 되셨다면, [매크로 기록]을 눌러주세요.

매크로 기록 창, 단축키를 설정해주세요!

위와 같은 창이 나오는데, 바로 가기 키 옆에 빈칸에 단축키를 지정해주세요. 저는 k를 썼습니다. 이렇게 하면 기록이 완료된 동작을 'ctrl+k' 를 눌러 반복할 수 있습니다.

 

확인을 누르셨으면 [매크로 기록] 이라고 쓰여 있던 부분이  [기록 중지] 로 바뀌게 됩니다. 기록중이라는 뜻입니다. 이제부터 실행하는 동작들을 엑셀이 코드로 기록해줍니다.

첫번째 열에 대해서 동작을 기록해 주세요.

저는 첫번째 열에 대해서 해야 하는 일들을 다 해주었습니다. A3를 선택해서 다음열까지 선택해주고 [기록 중지] 버튼을 눌러 입력이 끝났음을 컴퓨터에 알려줍니다.

 

2.테스트 해보기

기록이 진짜 됐는지, 단축키는 제대로 작동할지 ctrl+k를 한번 입력해볼까요?

 

뿅. 기록된 매크로 동작!

위와 같이 아까 첫번째 열에서 열심히 했던 작업이 순식간에 두번째 자료에 적용된 것을 볼 수 있습니다. 동작이 완료되고 나서는 A6 번 셀이 선택되었네요. 이 상태에서 ctrl+k를 누르면 네번째 자료에 매크로가 적용됩니다. 세번째 자료를 선택하고 했으면 세번째 자료에 적용이 됐을 거라는 걸 유추할 수 있죠?

지금까지 한 번 기록한 매크로를 다른 비슷한 곳에 쓰는 법이 궁금하셨던 분들, 이것이 '상대참조로 기록'의 힘입니다.

 

연속으로 적용하기 위해서는 ctrl+k 를 입력한 뒤 화살표 윗방향 (↑)을 눌러 한 칸 올리면서 작업해야겠네요. 

씐나게 작업해보겠습니다.

 

3.씐나게 반복하기

ctrl+k+↑    X  10   !!!

녹화된 매크로 실행해보기

4. 부록. 코드 구경하기

아까 이 동작을 컴퓨터가 '기록한다' 고 말씀드렸는데, 혹시나 이 기록이 어디에 있는지 궁금한 분들은 'Alt+F11'을 눌러보세요.

Alt+F11로 코드 보기

왼쪽 창에서 Module1 이라는 걸 선택하면 '매크로2'  (한번에 성공했다면 매크로 1) 이라는 이름으로 바로가기 키도 설명이 되어있고, 복잡한 코드가 쓰여있는 게 보입니다.

우리가 ctrl+k 를 누를 때마다 저 코드들이 실행됩니다.   상대 참조를 위해 ActiveCell 이라는 걸 사용한 것이 눈에 띄는데, 코드는 지금은 몰라도 됩니다. 패스!

---

복잡한 동작을 매크로 기록을 통해 해결해 보았습니다.

주의사항을 정리해보겠습니다.

 

1. 시작점 설정을 잘 해주셔야 합니다.
2. 반복하는 곳의 시작점도 잘 설정해주셔야 합니다. 
   • 반복하는 곳의 시작점을 엉뚱한 곳 ( D10 등) 에 놓고 단축키를 누르면 에러가 나는 것을 알 수 있습니다. 매크로는 중간에 에러가 나면 ctrl+z 등의 되돌리기 기능도 제한될 때가 있고 복잡하므로 되도록 에러가 안 나게 합시다.
3. 단축키 설정시 덮어씌워지므로 평소에 자주쓰는 단축키는 피해서 씁시다. (ctrl+z, ctrl+s 등을 쓰면 구찮아져요).
   • 참고로 대부분의 ctrl 조합은 단축키가 이미 지정되어 있고 j,l,m,q 정도가 비어있습니다. 
   • 본문에서 쓴 ctrl+k (하이퍼 링크 삽입) 정도 하찮은 단축키는 괜찮다고 생각합니다. 지금까지도 안 써왔잖아요.

 

사실 상대 참조를 해도 여전히 예상치 못한 곳에서 에러가 나는 등 귀찮은 점은 많지만(중간 중간 저장 필수!), 이번 예제처럼 적당히 복잡한 반복작업에서는 빨리 작업하기 편한 기능이라고 생각합니다. 더욱 발전된 자동화를 원하시는 분은 VBA라는 언어를 배워보시는 걸 추천드립니다! 조만간 기회가 닿으면 다뤄보겠습니다.