VBA 매크로를 문서마다 재활용하는 방법(Personal 파일 활용)

여는글

엑셀을 활용하여 매크로를 만들다보니, 문득 공통적으로 사용하는 매크로를 어딘가에 등록해놓고 그때그때 버튼만 눌러서 활용할 수 없을까 라는 생각이었습니다. VBA에는 이러한 상황에 대비하여, 매크로를 저장하고 모든 문서에서 공통적으로 활용할 수 있도록 기능을 제공하고 있습니다.

 

여러분들께서 매크로를 기록할 때, 매크로 저장 위치로 뜨는 ‘개인용 매크로 통합 문서’가 바로 그 기능입니다. 참고로 이 개인용 매크로 통합 문서는 PC의 특정 위치에 'PERSONAL.XLSB'로 저장됩니다.

 

개인용 매크로 통합 문서 초기 접근법

 

 

개인적으로는 개인용 매크로 통합 문서를 활용하면서 엑셀의 활용성이 한 단계 업그레이드 되었습니다. 언제든 불러올 수 있는 매크로 파일 덕분에, Merge 일괄 적용, 해제처럼 자주 사용되지만 엑셀에서 기본제공하지는 않는 기술들을 개인용 매크로 통합 문서에 등록해두니 사용도 간편하고, 매크로 코드의 유지/보수도 쉽더라구요. 개인용 매크로 통합 문서을 어떻게 활용하면 되는지에 대해 여러분과 지식을 공유해보고자 합니다.

 

 

개인용 매크로 통합 문서 활용하여 재활용하기

개인용 매크로 통합 문서 생성방법

가장 간단한 방법은 매크로 기록 창에서 매크로 저장 위치를 개인용 매크로 통합 문서로 지정하여 아무 매크로나 하나 기록하는 방법입니다. 일단 한차례 매크로를 만들고나면, 개인용 매크로 통합 문서이 자동으로 특정 경로에 생성되며, 특별히 이 파일을 지우지 않는 한은 반영구적으로 존재하게 됩니다.

 

이렇게 생성된 매크로는 ｢개발도구 – 매크로｣, 혹은 ｢시트탭 우클릭 – 코드 보기｣를 통해 접근하는 VBA 창에서, 아래 스크린샷과 같이 우측 프로젝트 탐색기의 가장 윗쪽 칸에서 확인하실 수 있습니다.

 

VBA 에디터상 개인용 매크로 통합 문서의 위치

 

 

개인용 매크로 통합 문서 사용방법

매크로 등록

이렇게 생성된 개인용 매크로 통합 문서에 프로시저를 작성하려면, 다른 VBA 프로젝트와 마찬가지로 ｢프로젝트명 우크릭 – 삽입 – 모듈｣을 통해 모듈을 추가하고, 해당 모듈에 프로시저를 입력해주시면 됩니다. 저는 아래와 같이 공통적으로 활용가능한 기능과 특정 문서에서만 사용할 기능으로 각각 구분해서 등록해두었습니다.

- 공통기능 : Merge일괄적용, Merge일괄해제, 빈칸있는줄_일괄삭제, 숨겨진 줄 일괄삭제 등

- 특수기능 : 재무제표_요약, 물품구입내역_항목추출, 인건비_지급내역_요약, 폴더_내_파일_일괄합산 등

 

VBA 모듈 삽입 방법

 

 

매크로 사용

이렇게 등록해둔 매크로는 엑셀의 빠른 실행단추에 등록한 후, 매크로 실행창을 단축키(Ctrl+숫자)로 불러와서 사용하였습니다. 개별매크로에 단축키를 부여하는 기능도 있긴한데, 아무래도 단축키별 매크로 기능을 외워야하다보니 나중에 혼선이 생길것 같아서 창을 띄워서 확인하는게 낫겠더군요.

 

그런데 글을 작성하면서 보니 매크로 실행 창을 띄우는 단축키가 아예 따로 있었네요(Alt+F8). 빠른 실행 단추 등록, 개별 매크로를 단축키로 등록, Alt+F8로 창을 띄워 실행 중 본인 편하신 방법으로 매크로를 실행하시면 되겠습니다.

 

개인용 매크로 통합 문서는 PERSONAL.XLSB!'매크로명'으로 표시된다

 

 

개인용 매크로 통합 문서의 특징

장점

일반 엑셀문서에서 매크로 사용 가능

일반 엑셀 문서(xls, xlsx)에서는 본래 매크로를 저장할 수 없도록 세팅되어 있습니다. 아울러, 매크로 사용문서를 활용하더라도, 기본적으로 매크로 사용을 허용한다는 버튼을 누르는 등 몇 가지 조치를 취해야 비로소 매크로 사용이 가능합니다. 그러나 개인용 매크로 통합 문서를 활용할 경우에는 이러한 조치가 없더라도 일반문서에서 즉시 매크로 사용이 가능합니다.

 

참... 회사생활에서 매크로를 통한 업무 효율은 다른 사람과 나를 구분 짓는 중요한 요소가 되기도 하죠. 이럴때에는 개인용 매크로 통합 문서가 도움될 수 있습니다. 일반 엑셀 문서에서 매크로 실행 버튼을 만들어두고, 개인용 매크로 통합 문서의 프로시저를 불러오도록 세팅한다면 매크로 내역을 다른사람에게 공유하지 않으면서 매크로를 사용할 수 있습니다.

 

맞춤 설정 가능 및 편의성

당연한 이야기지만, 개인용 매크로 통합 문서는 업무 스타일과 필요에 따라 개인별로 맞춤 세팅할 수 있습니다. 엑셀 고유의 기능만으로는 해결할 수 없지만 자주 부딪히는 업무들을 매크로로 만들어두면 업무시간이 상당히 단축됩니다. 가령 앞서 언급한 셀 합치기 일괄적용 및 해제 같은 기능은 은근히 자주 쓰이는데, 이런 기능을 엑셀에서 고유로 제공하지는 않고, 매 엑셀파일마다 이러한 스크립트를 작성하기도 애매합니다. 하지만 개인용 매크로로 만들어두면 필요할때마다 즉시 사용이 가능합니다.

728x90

 

매크로 코드 사용과 유지보수 및 잠금 설정이 용이함

특히 가장 중요한 장점 중 하나는 사용과 유지보수와 패스워드 설정이 쉽다는 점입니다. 본래 다른 파일에서 사용하던 매크로 코드를 현재의 문서에서 사용하려면 코드를 직접입력창에 붙여넣던지, 다른 매크로 파일을 켜서 현재 파일에서 실행하는 등 다소 번거로운 절차가 필요합니다. 그러나 개인용 매크로 통합 문서는 기본적으로 엑셀이 켜지면 함께 모든 문서에서 사용 가능하도록 켜지기 때문에, 어느 문서에서든 개인용 매크로 통합 문서에 접근하여 코드 수정을 할 수 있습니다.

 

아울러 이런 코드는 개인의 지적인 자산인 만큼, 타인에게 공유되길 원치않는 분들도 계실텐데요. 개인용 매크로 통합 문서는 해당파일 단 하나에만 암호를 설정하면 되기 때문에, 암호화에도 유리합니다. 앞서 언급했듯, 매크로와 일반문서로 분리가 가능하여 매크로 사용내역을 어느정도 감출 수 있고, 타인의 사용을 차단할 수 있는 점도 있기 때문에, 회사생활시 내 노하우가 타인에게 함부로 노출되는 것을 적절히 감출 수 있습니다.

 

 

단점 및 주의사항

코드작동 수준의 문제

Sheet 단위에서 사용하는 것이 아니라, Application 수준에서 실행되기도 하기 때문에 코드를 실행할 위치를 구체적으로 언급해 주어야 의도대로 작동합니다.

 

특히, 현재 켜져있는 시트에 다른 시트나 엑셀파일의 자료들을 불러오는 코드의 경우에는 여러 시트를 넘나들게 되는데요. 작업하려는 시트가 아닌 다른 시트가 활성화 된 상태에서 시트 구분 없이 Range 등의 코드를 사용한다면, 현재 활성화 된 시트를 기준으로 코드가 작동하면서 엉뚱한 곳에서 코드가 작동하는 등 예상치 못한 문제가 생길 수 있습니다.

 

보안 문제

개인용 매크로 통합 문서의 위치는 항상 고정되어 있기 때문에 바이러스의 좋은 표적이 될 수 있다고 생각합니다. 아직까진 그런 경우를 겪어보지 못했지만, 개인용 매크로 통합 문서 파일 자체를 삭제하고 엉뚱한 파일을 놓는다던지 혹은 매크로 통합 문서의 내용 자체를 수정하여, 아예 다른 코드로 변조하는 등 바이러스의 주요 타겟이 될 가능성도 있다고 봅니다.

 

어찌 되었건 기본적으로 스크립트 내용 자체를 바꾸려면 패스워드를 입력해야 하므로, 해킹툴이 스크립트의 내용을 함부로 수정할 수 없도록 개인용 통합 매크로 파일 접근은 비밀번호로 잠궈두시기 바랍니다. 암호화 방법은 아래와 같습니다.

 

개인용 통합 매크로 문서 암호설정 방법

 

간혹 발생하는 파일 충돌 문제

가끔 엑셀이 실행되면서 개인용 매크로 통합 문서를 2번씩 여는 경우가 있습니다. 이 경우 아래와 같이 PERSONAL.XLSB 파일을 읽기전용으로 열겠냐는 아래 메시지가 뜹니다.

(참고로 여기서 말하는 PERSONAL.XLSB 파일은 개인용 매크로 통합 문서의 실제 파일명입니다.)

 

PERSONAL.XLSB의 사용 중인 파일 경고창

 

이 경우 ｢사용중인 파일｣ 경고창이 뜬 파일을 닫고 다시 실행시켜주시면 대부분의 경우에서 정상적으로 실행이 가능합니다. 아울러 개인용 매크로 통합 문서 파일을 수정할 것이 아니라면(즉, 코드를 변경할 게 아니라면), 그냥 읽기전용으로 실행시켜서 코드만 실행하셔도 문제없습니다.