16_엑셀 VBA for next문 완벽히 이해하기

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

제가 생각하는 초보도 할 수 있고 또 VBA의 실무적인 활용을 위해서 최소한 알아야 할 문법이 3개가 있습니다. 첫 번째는 If 문으로 엑셀 함수의 개념과 비슷하고 이미 소개해 드렸습니다. 두 번째가 이번 시간에 소개해 드릴 For 문으로 거의 모든 프로그래밍 언어에 공통적으로 쓰이는 문법으로 프로그래밍을 할 때 절대 빠질 수가 없습니다. For 문은 공통적으로 반복되는 작업을 내가 모두 매크로 기록으로 입력하지 않아도 반복 몇 번 반복할지 반복 횟수를 정해서 반복하게 해주는 프로그래밍에 빠질 수 없는 필수 문법입니다. 예를 들어 A1셀에 B1셀을 30번 곱하는 작업을 매크로로 만든다고 하면 A1셀을 B1셀에 곱하는 걸 한번만 매크로로 기록하고 for문으로 30회 반복하라는 명령을 할 수 있습니다.

16연습파일.xlsx

16연습파일_완성본.xlsm

1. 변수 선언 Dim에 관해

   엑셀 VBA문법은 어렵지 않은데 어렵게 보이는 요인이 몇 가지 있습니다. 첫 번째는 엑셀 매크로로 만든 VBA를 보면 엑셀이 불필요한 코드를 너무 많이 집어 넣어서 어렵게 보입니다. 두 번째는 엑셀의 코드가 길어 지게 되면 필요한 변수 선언 입니다. 엑셀 VBA코드를 처음 보면 시작되는 Dim구문은 엑셀 VBA를 실행 할 때 컴퓨터의 메모리를 효율적으로 사용하게 해주기 위해 변수 타입이 정수인지, 문자 인지 등을 정해 주는 것 입니다. 만약 생년 월일 과 같은 변수를 지정한다면 엑셀의 5자리 정수 이상 사용할 필요가 없으므로 메모리를 많이 잡아 먹는 Double 타입으로 지정할 지정할 필요가 없지요. 여기서 VBA코드를 처음 배우는 사람의 난관이 시작되고 String Type, Boolean Type, Double Type등 처음 보는 용어들이 등장해서 당황하게 합니다. 원칙적으로 말하면 변수를 지정해 주는 것은 맞습니다. 그러나 VBA를 처음 배우는 입장에서 간단한 코드를 만들 때는 변수를 꼭 지정해 주지 않아도 됩니다. 변수를 지정하지 않으면 엑셀은 Variant라는 가장 메모리를 많이 먹는 변수를 자동으로 할당해서 계산하지만 일반 유저들이 간단히 작성하는 VBA코드문에는 거의 속도에 영향을 주지 않습니다.

   VBA와 매크로 편에서는 변수 선언을 생략하고 VBA의 감을 익히는데 집중을 하도록 하겠습니다.

2. 매크로 VBA작업의 순서

   매크로로 단순히 몇 가지 서식을 바꾸는 것 과는 달리 약간이라도 복잡한 작업을 한다면 내가 정한 원칙을 정하고 반드시 그 틀에 맞추어서 해야 합니다. 당장 VBA에디터부터 열어 놓고 코딩을 시작하면 분명히 중간에 생각지도 못한 실수를 발견하게 되고 처음부터 모든 일을 다시 해야 할 경우가 많습니다. 필자도 모두 겪었던 일이고 이제는 아무리 간단한 코드라도 내가 정한 원칙에 따라 한 단계씩 차근 차근 실행 합니다.

   상대 참조 인가 절대 참조 인가

   매크로로 기록한 VBA를 수정한다면 가장 먼저 고민해야 할 것이 상대 참조, 절대 참조의 결정 입니다. 내가 선택하는 셀들이 항상 같은 위치에 있는지, 아니면 선택되어 있는 셀의 위치에서 상대적인 위치에 있는지 알아야 합니다. 일반적인 경우로는 처음 셀을 선택할 때는 절대 참조이고 다음부터의 매크로는 상대 참조로 바꾸어서 기록해야 할 경우가 많습니다. 일정한 형식을 가진 시트가 많으므로 처음 매크로를 시작할 때는 나의 기준 셀로 이동하는 절대 참조로 시작 하여 거기서부터 상대적인 위치로 서식을 정해 나갑니다.

   매크로 기록을 시작하고 반복된 작업을 최소 2회에서 3회를 반복하여 코딩의 실수를 줄인다.

   for문을 사용하게 될 것 같은 경우 내가 반복하는 작업을 정확히 어느 부분에서 끊어야 할 지 알면 다행 이지만 보통 코딩을 보면 헛갈리는 경우가 매우 많습니다. 이런 경우를 대비해 내가 안다고 하더라도 같은 작업을 2회에서 3회를 반복하여 어느 부분이 반복되는지 명확히 아는 것이 중요합니다.

   VBA코드를 열어서 규칙을 발견하고 불필요한 부분을 지운다

   매크로를 종료를 하고 VBA에디터로 가서 코드가 완전히 일치하는 부분이 반복된다면 반복되는 부분은 for문으로 처리하면 되므로 제거 합니다.

   내가 필요한 논리 문 for 문 등을 추가 한다.

   코딩의 가장 기본은 if문과 for 문 입니다. 이번 시간에는 for문을 매크로로 기록된 VBA에 추가해 보겠습니다.

   코드를 한 줄씩 실행하여 디버깅 작업을 한다.

   코드를 완성했다면 바로 실행하는 것 보다 엑셀 VBA에 기본으로 있는 디버깅 모드를 통해 오류를 체크해 봅니다. 일반 숙련된 프로그래머들도 여기에서 걸리면 가장 시간을 많이 허비하게 되며 필자도 사소한 실 수 때문에 밤을 샌 적도 많습니다.

3. 매크로 기록 하기

   제가 예제에서 보여 주는 매크로 작업은 직원 이름, 건물, 부서를 Bold체로 표시하고 그 나머지의 필드는 이탤릭 체로 표시하는 매우 간단한 VBA입니다. 이 예제를 선택한 이유는 다른 서식 명령어와는 달리 엑셀 매크로가 불필요한 코드를 생성하지 않습니다. 직원의 명단과 정보가 예제와 같이 세로로 나열 되어 있고 1000명의 직원이 있다면 일단 같은 작업을 두번이나 세번을 해서 VBA코드에서 공통점을 발견하겠습니다.

    

   먼저 보기 탭의 매크로 버튼 밑의 화살표를 눌러서 상대 참조로 기록에 체크가 되어 있는지 확인 합니다. 지금 불이 안 들어와 있으므로 절대 참조 입니다.

   

    

   먼저 매크로의 반복을 시작하기 위해 우리는 항상 커서를 B1으로 이동해야 합니다. 많은 매크로가 처음 시작은 절대 참조로 시작하고 다음의 작업은 상대 참조 입니다.

    

   절대 참조인 것을 확인했다면 매크로 기록을 시작 합니다. 이름은 기본 설정으로 하겠습니다.

   

    

    

   B1셀을 한번 클릭만 하고 바로 상대 참조로 기록을 선택해 줍니다. 지금부터 하게 될 모든 작업은 B1셀에서 모두 상대적으로 위치한 셀들 이기 때문입니다.

   

    

    

   상대 참조로 기록을 시작 했다면 직원이름, 건물, 부서는 굵은 Bold체로, 그리고 나머지 필드는 이탤릭체 표기를 3회 반복해 주었습니다.

   

    

    

   상태 표시줄의 매크로 정지 버튼을 눌르서 정지를 합니다.

   

    

    

   Alt + F11을 눌러서 VBA에디터를 엽니다.

   

    

    

4. 반복되는 VBA문을 찾아라

   절대 참조가 아닌 상대 참조로 같은 작업을 두 번 이상 반복 하였습니다. VBA코드안에 반드시 같은 부분이 반복되게 되어 있습니다. 그 부분을 찾아서 삭제하고 for문으로 대체하면 됩니다.

    

   코드의 구분이 쉽도록 이렇게 엔터키를 눌러서 구분해 주면 더욱 쉽습니다.

   

    

    

   다음 4단락이 정확히 똑같이 반복됨을 확인 할 수 있습니다. 3번째와 4번째 단락을 삭제하겠습니다.

   

    

5. For, Next 구문

   For, Next 구문은 If 문 보다 오히려 논리가 쉽습니다.

   For 변수 = 1 to 30

   프로시저(Procedure) 본문

   Next 변수

   여기서 원칙적으로 변수도 타입 선언을 해 주어야 하지만 생략 하였습니다.

   변수로 일반적으로 가장 많이 사용하는 알파벳은 소문자 i(iteration) 입니다.

   변수 뒤의 1 to 30은 변수에 1부터 30을 대응하면서 프로시저(Procedure)를 반복한다 입니다. 즉 30회 반복 입니다. 2 to 31도 같은 결과이지만 코드는 사람이 알아보기 쉽게 작성해야 하겠죠

    

   우리의 반복 예제보다 넉넉한 숫자를 넣어서 for 문을 구성해 보았습니다.

   

    

   코딩이 끝났습니다. 여기서 내가 한 것은 반복 문을 찾아서 제거하고 for, next문만 추가한 것뿐입니다. VBA의 객체를 전혀 이해 하지 못해도 할 수 있는 것은 많습니다.

6. 디버깅

   프로그래머들이 가장 많은 시간을 허비하는 부분이 바로 이 디버깅이 아닐까 싶습니다. 가끔은 오류를 못 찾으면 차라리 프로그램을 처음부터 다시 짜는 것이 빠를 정도로 디버깅은 힘든 작업 입니다. 그러나 매크로로 만들어진 VBA코드에 단 몇줄의 코드를 더한 것은 거의 오류가 나지 않고 디버깅 작업도 매우 쉽습니다.

   VBA 프로시저(Procedure)를 한 줄씩 실행하기

   F8

   이것도 VBA를 사용한다면 반드시 외워야 하는 단축키 입니다. 메뉴에 있지만 외워야 합니다.

   디버깅 작업은 VBA안에서 프로시저(Procedure)의 시작 부분에 마우스를 위치하고 F8을 누르면 한 줄 씩 실행됩니다. 여기서 듀얼 모니터라면 각각의 윈도우를 다른 모니터에 배치하고 하나의 모니터라면 다음 사진과 같이 두개의 윈도우를 분리해 놓고 실시간으로 언제 오류가 나는지 감시하면서 내려 갑니다. 지금 노란색이 보이는 곳이 실행 대기 중인 코드이고 위의 select문, 즉 선택문이 이미 실행 된 상태이어서 B1부터 B1셀이 선택되어 있습니다.

   

    

    

   F8을 누르면서 한 줄씩 실행 하며 내려 갑니다. 바로 위의 코드 5개의 행을 선택하라 가 실행된 상태이고 지금 노란색, 이탤릭체가 실행되기 직전 입니다.

   

    

    

   이제 반복되는 for문에 진입 하였습니다.

   

    

    

   Next i에 가면 for문이 다시 시작되는 것을 볼 수 있습니다.

   

    

    

   I를 넉넉히 잡았으므로 우리의 목적은 달성했고 코드는 완성 되었습니다. 더 이상의 for문 반복은 의미가 없으므로 위의 정지 버튼을 눌러서 디버깅을 종료 합니다.

   

    

    

   모든 서식이 이제 매크로 한번으로 실행 되게 됩니다.

   

    

   다시 한번 강조 드리면 제가 한 것은 기존에 만들어진 VBA에 매우 쉬운 for문을 넣은 것 뿐입니다. VBA의 명령어를 몰라도 아주 기초적인 3개의 문법만 알고 있다면 할 수 있는 작업은 매우 많습니다. 또 여기서 반복 횟수를 정하는 것은 내가 알고 있다면 다행 이지만 항상 변하는 것이라면 넉넉하게 잡는 것이 좋겠지요. 그렇지만 더 좋은 방법은 다음에 소개해 드릴 Do While, Do Until문을 이용하시면 됩니다. 논리를 따라서 데이터의 끝에 가서 프로시저(Procedure)를 멈추게 할 수 있습니다.