18_엑셀 매크로 만들기 프로젝트

엑셀 중급_VBA 와 매크로 (18)

매크로는 엑셀에서의 모든 행동을 VBA로 바꾸어 주지만 그 기능을 100프로 활용하기 위해서는 이제껏 소개한 상대 참조 절대 참조, 그리고 3개의 기본 문법 if, for, do while 혹은 do until만 아시면 됩니다. VBA에 있는 수많은 객체와 매서드(Method)를 안다면 훨씬 복잡한 프로그래밍을 할 수 있지만 거기에 대한 부담감 때문에 VBA를 건드릴 생각도 안 할 수 있습니다. 이번 시간에는 엑셀 중급 매크로의 마지막 시간으로 VBA의 복잡한 매서드(Method)와 객체의 개념 없이 최소한의 문법으로 자동으로 만들어 진 매크로를 조작하는 법을 소개하고 마치도록 하겠습니다.

18연습파일.xlsm

18연습파일_완성본.xlsm

1. 문제점 파악하기

   보통 회사의 데이터 베이스 형식이나 엑셀에는 데이터가 테이블 형식으로 저장 되어 있습니다. 직원 정보에 대한 필드가 각 각 하나의 열을 차지 하고 직원 개인의 정보가 하나의 행을 차지 합니다. 그러나 웹 페이지에서 받은 정보나 혹은 다른 거래처에서 보내 준 정보가 만약 엑셀을 사용하지 않은 자료여서 형식이 다르다면 하나 하나 우리의 데이터 베이스에 바꾸어서 넣어 줘야 합니다. 이걸 엑셀 VBA코드를 모른다고 가정하고 매크로와 Do Until 문법 만으로 해결해 보도록 하겠습니다.

    

   데이터 베이스는 오른쪽의 표 형태로 저장되어야 하지만 거래처에서 받은 정보는 좌측과 같은 형태 입니다.

   

    

    

   보통 매크로의 처음 시작은 절대 참조입니다. 마우스의 위치를 정해 주어야 하기 때문이죠. 보기 탭에서 상대 참조에 불이 꺼진 것을 확인하고 Alt + F11을 눌러서 다음과 같이 윈도우를 조정해 줍니다.

   

    

   만약 듀얼 모니터 이상이라면 하나의 모니터에 각각 VBA에디터와 엑셀 창을 띄워 놓으시면 됩니다. 이렇게 작업 환경을 구성하면 내가 하는 모든 행동이 VBA로 바뀌는 것을 실시간 확인 할 수 있으며 마우스 스크롤등 불 필요한 행동이 기록되었을 때 VBA창에서 바로 바로 삭제 하실 수 있습니다.

    

2. 매크로 기록 하기

   매크로를 어떤 순서로 구성 할 지는 매크로를 기록하는 사람의 판단에 달려 있으며 정확한 정답은 없습니다. 지금 제가 보여 드리는 것은 일단 절대 참조로 처음 데이터를 행 열 변환으로 복사를 하고 상대 참조로 바꾸어서 나머지 3사람을 더 복사를 해서 패턴을 찾겠습니다. 그리고 패턴의 간편화를 위해 행 열 변환 복사를 할 때는 항상 그 사람의 이름이 위치한 행을 기준으로 같은 행에 붙여 넣겠습니다.

    

   처음 사람을 절대 참조로 같은 행에 붙여 넣은 후 상대 참조 기록으로 바꾸어 줍니다. 그리고 같은 방법으로 3사람만 더 복사를 해 봅니다.

   

    

   만약 VBA코드를 해석 할 줄 안다면 1~2명만 해도 되겠지만 모든 가정은 VBA코드의 기본 문법만 안다 입니다.

    

   자 이제 코드가 반복되는 부분을 찾습니다. 반복되는 부분은 상대 참조로 복사를 시작한 부분이며 마우스 스크롤 등을 할 때 기록된 불필요한 VBA코드는 지워 줍니다.

   

    

   이제 불필요한 코드를 모두 지우고 지난 시간에 배운 Do Until 문을 적용 하겠습니다.

    

   코드를 전혀 몰라도 Do Until을 반복되는 코드에 적용 했습니다. 우리는 이 코드를 해석하지는 못해도 행 열 변환 복사를 이름과 같은 행에 하는 코드란 것을 알고 있습니다.

   

    

    

   프로시저(Procedure) 안에서 F8을 눌러서 코드가 정상적으로 작동하는지 확인 합니다. F8을 누를 때마다 코드가 한 줄씩 이동하며 엑셀 시트에 명령어를 실행 합니다.

   

    

3. 절대 참조로 두 번째 프로시저(Procedure) 기록하기

   매크로에 경험이 많다면 한번에 끝낼 수 도 있지만 중간에 실수를 한다면 수정하기가 힘들어 질 수 있습니다. 이렇게 비교적으로 긴 매크로 작업은 한 작업씩 따로 매크로를 기록하고 VBA에서 매우 간단히 두 코드를 합칠 수 있습니다.

    

   보기 탭에서 매크로를 절대 참조로 바꾸고 기록을 시작 합니다.

   

    

    

   중간에 빈 행을 없애는 가장 빠른 방법은 정렬 입니다. F~M열을 클릭 선택 후 오름차순 정렬을 하면 중간의 빈 행은 사라집니다.

   

    

    

   이제 행을 삽입하고 필드를 복사해서 행열 변환으로 붙입니다. 그리고 F1셀을 클릭하고 (옵션임) 매크로 종료를 누릅니다.

   

    

   두 단계의 매크로로 우리가 원하는 데이터를 얻었습니다. 두 매크로를 합치기만 하면 한번의 실행이 되겠죠.

    

   앞의 매크로는 모듈 1에 뒤의 매크로는 모듈 2에 저장 되었습니다. 모듈 2에 저장된 매크로의 VBA코드를 모두 복사에 모듈 1코드 밑에 붙여 넣고 End Sub 과 Sub만 삭제하면 두 매크로는 하나로 합쳐집니다.

   

    

    

   이제 데이터를 처음 원본으로 돌려 좋고 매크로를 실행 해 보겠습니다.

   

    

    

   정상적으로 매크로가 작동 하고 데이터가 원하는 형태로 바뀌었습니다.

   

    

   우측의 표에서 좌측의 표 형태로 바꾸는 것도 물론 가능하지만 코딩에 관한 기초 지식이 있어야 함으로 VBA코딩 편에서 소개해 드리겠습니다.

    

4. 알아 두면 좋은 코드 수정

   매크로와 VBA에서는 매서드(Method)와 이벤트 등의 개념만 아는 하에서 VBA코드를 조작하는 것을 소개 했지만 알아 두면 좋은 매서드(Method)가 있습니다. 보통 VBA코드를 실행하면 간단한 코드라도 시간이 오래 걸릴 때가 있습니다. 이건 코드가 문제라기 보다 스크린이 바뀌는 것을 실시간으로 보여 주기 때문인데 엑셀이란 객체에서 스크린 업데이트라는 매서드(Method)를 꺼 주시면 됩니다. 자세히 알 필요는 없고 제가 알려 드리는 것만 코드의 앞 부분에 추가하면 VBA코드가 실행 될 때 업데이트 정보는 생략함으로 코드가 훨씬 빨리 실행 됩니다.

    

   코드의 시작 부분에 Application.ScreenUpdationg = False 를 입력해 주면 코드 실행 속도가 빨라 집니다. 마이크로 소프트에서 기본으로 제공하는 매서드(Method) 입니다.

   

    

    

   또 코드 실행은 문제가 없지만 가끔 나의 데이터에만 해당하는 참조가 보이면 이런 식으로 전체 열을 참조하도록 바꾸어 줍니다. 이런 수정은 내가 만든 매크로 VBA코드를 다른 사람과 공유하기 위해서 하는 것이고 개인용 매크로라면 굳이 하지 않으셔도 됩니다.

   

   이제 VBA를 조작하는 것에 대한 부담감을 없앴으면 합니다. 원칙적으로 VBA코딩을 효과적으로 하기 위해서는 제가 중급편 에서 생략한 변수 타입을 지정해 주어야 하고 또 메서드와 이벤트의 개념도 아셔야 하지만 그걸 몰라도 이렇게 자동으로 생성된 VBA코드 만으로도 웬만한 자동화 작업은 할 수 있습니다. 저는 VBA고급편에서 VBA코딩에 관한 것을 체계적으로 다시 소개 해 드릴 것을 약속 드리며 VBA와 매크로 편을 여기서 마치겠습니다.