엑셀 매크로(VBA)를 이용하여 vlookup 함수 구현하기

어쩌다 갑자기 매크로를 하게 됐나

인턴 시절 배운 vlookup 함수 하나로 모든 잡무를 퉁쳐오고 있던 나...

그런 나에게 친구에게 한 가지 요청이 들어왔다.

 

"주문번호에 맞는 상품명을 다른 시트에서 자동으로 끌어오고 싶어!!"

 

다행히도 요청건은 간신히 해내는 vlookup 함수만으로 구현할 수 있었다.

그러나 내 친구는 내가 한 번 만들어준 함수를 복사 붙여넣기해서 평생을 쓸 생각을 하고 있었다..(...)

그렇다면 새로운 데이터가 로우에 추가될 때마다 친구는 범위를 재지정해야하는데...

좀 더 쉽게 자동으로 할 수 없을까? 라고 생각하다가..

 

 

엑셀 매크로에 도전해보기로 맘먹었다.

 

 

 

어떻게 풀었는지?

주어진 문제는 이렇다.

Sheet1에 있는 주문명과 일치하는 sheet2에 있는 제품명을 가져와서 맨 오른쪽과 같은 결과물을 내는 것이다.

 

 

vlookup함수로 표현하면 다음과 같다.

vlookup(look_value, table_array, table_array_col)

 

 

그리고 이 vlookup 함수를 매크로로 작성하면 다음과 같다. 

 

1. Option Explicit 

 : Option Explicit은 명시적 선언을 통해 변수를 선언할 때 사용하는 구문이다.

명시적 선언이란 변수를 이용하기 전에 반드시 변수를 Dim을 통해서 선언해야 한다는 의미다.

Option Explicit이 있다면 명시적 선언을, Option Explicit이 없다면 암시적 선언을 사용하게 된다.

명시적 선언을 하게 되면 런타임이 아니라 컴파일 타임에 형식 유추를 강제로 수행할 수 있어 성능 향상에 도움이 된다. 뿐만 아니라 암시적 선언을 할 시 이름 충돌 오류와 맞춤법 오류가 발생할 확률이 커지는데 명시적 선언은 이를 방지할 수 있어 선호되는 방식이다.

 

2. Dim oldTime As Single

MsgBox "총" & Format(Timer - oldTime, "#0.00") & " : 초가 소요되었습니다."

 : 특정 매크로 작업의 소요시간을 알아볼 때 사용되는 코드이다.

Single은 숫자형식의 데이터 타입이며, 해당 코드에서는 코드 시작 시점의 시간을 할당하기 위해 사용되었다.

타이머를 작업에 들어가기 전 한 번, 작업이 종료될 때 한 번 재서 각 시간의 차이를 구한다.

 

3. lookFor = Range([D2], Cells(Rows.Count, "D").end(3))

 : Range를 통해 찾아야할 값의 범위를 지정한다. 사각형의 왼쪽맨위에 꼭짓점, 오른쪽맨아래 꼭짓점을 연결하여 사각형 범위를 지정하는 것이라고 생각하면 된다. 즉 D2부터 Cells(Rows.Count, "D).end(3)로 이루어지는 사각형의 범위를 선택한다는 뜻이다.

 Cells(Rows.Count, "D).end(3) 의 코드는 천천히 뜯어보자. 우선 Cells는 말 그대로 엑셀의 셀을 가르켜는 속성이다.  Cells(1,1)을 하게 되면 첫번째 행의 첫번째 열, 즉 A1 셀을 가르켜는 것이다. 그렇다면 Cells(Rows.Count, "D")에 적용해보자. Rows.Count는 말 그대로 행의 수 이다. 사용하고 있는 엑셀 기준으로는 1048576행까지이다. 즉 D열의 1048576행까지를 의미한다. 그런데 우리는 값이 있는 데이터 행은 1048576이 아니다. 이때 사용하는 게 end(3)이다. end는 cntrl+방향키의 기능과 같다. end(3)을 쓰면 위로 올라가기 때문에 값이 있는 마지막 행에 위치하게 된다.

 

<end 옵션>

end(1) = cntrl + ← = end(xlToLeft)

end(2) = cntrl + → = end(xlToRight)

end(3) = cntrl + ↑ = end(xlUp)

end(4) = cntrl + ↓ = end(xlDown)

 

 

4. table_array = Worksheets("물건 값").Range("C2:E65535")

 : 다른 시트에 있는 테이블에서 값을 가져올 시 WorkSheets로 지정해주면 된다. 그리고 혹시 모를 추가 데이터를 위해 엑셀 가장 마지막 행까지 범위를 지정해줬다. 이렇게 하면 나중에 따로 범위를 재수정할 필요가 없다. 

 

5. table_array_col = 2

 : 테이블에서 어느 컬럼을 가져올지를 정한다. 

 

6. lookFor_col = 2

 : 어느 열에 적재할지 정하는 코드다. 이전에 vlookup함수는 해당 셀에 직접 작성하므로 지정해줄 필요가 없었는데, 매크로에서는 별도로 지정해줘야 한다. 유의할 점이 있다면, 0,1,2,3,4 순서로 지정한다는 것이다. 순서가 1부터 시작하는 다른 속성과 달리 0부터 시작한다. 추가로 조심해야되는 점은 lookFor 밸류를 기준으로 0부터 시작하는 것이다. lookForValue가 A열에 있다면 상관없지만, E에 있는 경우는 E부터 0으로 시작한다. (처음에 이걸 몰라서 개판이 됐었다..)

 

7.lookFor.Offset(0, lookFor_col) = varResult

 : Offset을 이용하면 특정 셀이나 범위를 기준으로 몇 행, 몇 열 떨어져 있는 위치의 셀이나 범위를 참조할 수 있다. 

ex) Range("A1").Offset(1) -> [A1] 셀에서 1행 떨어져 있는 셀 = [A2]

즉 lookFor 셀에서 0행, lookFor_col 만큼 떨어져있는 셀을 의미하는 것이다. Vlookup 함수로 찾아낸 값을 우리가 원하던 열에 채워넣는 과정이다. 

 

 

느낀 점

1. 새로운 지식을 항상 탐구하려고 하다 보면 배우는 속도가 점점 빨라지는 것 같다. 학부 시절 SPARK 수업을 들었을 땐 아무리 공부를 해도 따라가지 못했었는데…. 하고…. 하고…. 하고 또 하면서 이런 과정을 많이 거치다 보니 습득하는 속도가 빨라졌다.

2. 가끔은 이론을 배우고 준비가 됐을 때 실험하는 것보다 무작정 실전에 뛰어드는 게 더 빨리 배우는 것 같다. 성격상 자꾸 공부에 충실히 하려고 하는 데 이런 점을 개선해야겠다.

3. 엑셀도 생각보다 재밌다.