지금까지 엑셀 매크로 프로그래밍에 필수적인 명령어와 문법에 대해서 설명하였습니다. 이번 편에서는 엑셀 매크로 프로그래밍에서 가장 많이 사용되는 함수들에 대해 예제를 통해 자세히 알아보도록 하겠습니다.
단순 반복작업 자동화: 엑셀 매크로 활용 방법과 팁 #5/8 (기초 프로그래밍 문법 - 많이 쓰이는 함수들)
엑셀 매크로 프로그래밍시 많이 쓰이는 문자열 함수와, cells, range 함수의 사용법에 대해서 상세히 알아보도록 하겠습니다.
목차
1. 문자열 함수
2. Cells, Range 함수
문자열 함수
매크로 프로그래밍을 하다 보면 문자열을 처리하는 경우가 많습니다. 이 때 가장 많이 사용하는 함수가 Left, Mid, Right, Len 함수입니다.
- s = “This is a sample”
- ls = left(s, 4) ‘왼쪽 1~4번째 문자, 결과는 ls = “This”
- ms = mid(s, 6,2) ‘중간 6번째부터 2개 문자, 결과는 ms = “is”
- rs = right(s, 6) ‘오른쪽 1~6번째 문자, 결과는 rs = “sample”
- li = Len(s) ‘문자열의 전체 글자수, 결과는 li = 16 공백도 하나의 문자로 인식합니다.
Cells, Range 엑셀의 셀과 범위 함수
엑셀 매크로는 말 그대로 엑셀에서 사용하는 프로그래밍 언어이기 때문에 엑셀의 셀을 참조하는 함수가 매우 중요합니다. 특히 Cells과 Range 함수는 내가 매크로 프로그래밍을 할 때 가장 많이 쓰는
함수로 조금 상세히 설명하도록 하겠습니다.
a. Cells 함수
엑셀에서 아래 그림에 있는 “프로그래밍”의 셀주소를 지정하려면 절대주소냐 상대주소냐의 차이만 있을 뿐 “D3” 형식을 사용해야 하지만 엑셀 매크로에서는 다양한 방법으로 셀주소를 지정할 수 있습니다.
그 중에서 프로그래밍 시 사용하기 편리한 Cells함수를 사용하면 Cells(3, 4) 또는 Cells(3, “D”) 와 같이 숫자와 문자를 필요에 따라 골라서 사용할 수 있기 때문에 편리합니다.
다만, 엑셀에서는 “D3”와 같이 열이 먼저고 행이 다음이지만, Cells 함수에서는 행이 먼저고 열이 뒤이기 때문에 사용할 때 주의하여야 합니다.
위의 설명을 이해하였다면 다음 질문에 쉽게 답할 수 있을 것입니다.
Cells 함수를 사용해서 “엑셀 매크로 프로그래밍 VBA 최고” 를 Msgbox로 띄우려면 어떻게 해야 할까요?
Msgbox Cells(5,3) + Cells(7,3) + Cells(3,4) + Cells(9,”E”) + Cells(11, 2) 으로 하면 될까요?
코드와 실행 결과를 보도록 하겠습니다.
실행 결과는 아래와 같습니다.
뭔가 이상함을 느꼈다면 문제를 거의 맞혔다고 생각해도 됩니다. 그렇습니다. 띄어쓰기가 되어 있지 않습니다.
사람은 문장을 보고 바로 띄어쓰기를 구분할 수 있지만, 컴퓨터는 일일이 띄어쓰기를 하도록 알려줘야 합니다. 그래서 프로그래밍을 노가다(?)라고 하는 분들도 많습니다.
띄어쓰기가 되도록 소스를 고치면 아래와 같습니다.
수정한 소스의 실행 결과는 아래와 같습니다.
여기까지 열심히 집중해서 읽으신 분들은 위의 코드를 보고 뭔가 불편한 느낌을 받으셨을 것입니다. 아무 느낌도 받지 못하셨다면 그래도 괜찮습니다. 큰 문제는 아니니까.
앞에서 변수를 설명하면서 문자열의 더하기는 “+”를 쓰는 것이 아니라 “&”을 쓴다고 했었습니다. 그런데 위의 코드에는 문자열임에도 “+” 를 썼는데 정상적으로 동작하고 있습니다.
설명을 틀리게 한 걸까요? 아닙니다.
그렇게 설명한 이유는 문자열과 문자열의 더하기는 “+”과 “&” 모두 사용 가능합니다. 다만 문자열 + 숫자를 하면 에러가 발생하지만, 문자열 & 숫자는 정상적으로 동작을 합니다. 즉,
같은 데이터 형식은 “+”를 사용해서 더할 수 있고 데이터 형식이 다른 경우에는 “&” 를 사용해서 더 할 수 있습니다. 그래서 혹시 모를 에러가 발생하는 것을 방지하기 위해 가능한
“&”를 사용하는 것이 좋습니다.
하나만 더 얘기하고 다음으로 넘어가겠습니다.
Cells(row, column) 함수의 형식이 어디서 많이 본 것 같지 않나요?
앞에서 설명했던 배열, 그 중에서 2차원 배열을 기억하시나요? 2차원 배열을 설명할 때 사용했던Grade(4, 8)을 보면 Cells함수와 모양이 비슷하다는 걸 알 수 있을 것입니다.
엑셀시트의 구성도 기본적으로 2차원 배열 형식이기 때문에 Cells함수를 이해하면 배열을 이해하는데 도움이 됩니다.
b. Range 함수
잘 알고 있겠지만 엑셀에서 범위를 지정할 때 “A1:C10”의 형식을 사용하는데, 엑셀 매크로에서 이와 동일한 기능을 하는 것이 Range 함수입니다.
엑셀 매크로에서 사용하는 Range 함수의 사용 형식은 기본적으로 Range(“cell1:cell2”)이나, Range(“A1:C10”)또는 Range(Cells(1,1), Cells(10, 3))과
같이 Cells와 함께 사용하는 것도 가능하기 때문에 프로그래밍에는 주로 후자를 많이 사용합니다. 왜냐하면 선택 범위를 동적으로 변화를 주는 경우가 많기 때문에 Range(Cells(a,b),
Cells(c,d)) 와 같이 row, column 위치에 변수 a, b, c, d를 넣은 후 변수 값을 바꾸면 쉽게 범위를 변경시킬 수 있기 때문입니다.
사용법은 간단하니 위의 화면과 같이 “A1”에 있는 “엑셀 매크로”를 C3:G12에 채우는 코드를 작성해 보도록 하겠습니다.
소스코드는 아래와 같습니다.
너무 간단해서 허무하지 않으신가요? 이처럼 Range와 Cells 함수를 잘만 쓰면 상당히 쉽게 문제를 해결할 수 있습니다.
For나 Do While 을 사용해야 하지 않을까 생각하신 분들이 있으실 것 같아 For 로도 작성해 보았습니다. 프로그래밍에 정답은 없습니다. 해결 방법은 한가지만 있는 게 아니기 때문입니다.
위의 코드에서 i는 행을, j는 열을 가르킵니다. 즉, 지정된 3~7열을 다 채우면 다음 행으로 이동해서 지정된 열을 다 채우는 식으로 12행까지 동작한 후 프로그램이 끝나게 됩니다.
이번 편에서는 엑셀 매크로 프로그래밍시 많이 사용하는 문자열 함수와, Cells, Range 함수를 예제를 들면서 설명을 하였습니다. 실제 매크로 프로그래밍시 많이 사용하기 때문에 충분히 숙지하실 수 있도록
반복해서 읽어 보실 것을 권장합니다.
