처음 시작하는 VBA 매크로

제 채널 업무의 잔머리에서 가장 많이 다루었던 영상은 VBA 매크로 에 대한 영상이었습니다.

타 채널 대비 특색있는 점은 PPT에서 돌아가는 VBA 매크로를 많이 다뤘다는 점 정도가 될것 같네요.

사실 VBA 매크로는 Window 환경에서 운영되는 MS Office 프로그램들이라면 모두 사용이 가능한 기능이고, 그 응용이 가능한 영역은 생각보다 많습니다.

그런 까닭에 한번쯤 활용해 보기를 꿈꾸지만, 지금까지 사용해 왔던 다른 업무용 S/W와는 다르게 직접 코드를 작성하는 영역이 있어서 처음 익히기가 쉽지 않은것 또한 사실입니다.

이번 글에서는 처음 VBA 매크로를 익히고자 하는 분들이 어떤 순서로 익혀 나가시면 좋은지, 그리고 참고할만한 자료로는 어떤 것이 있는지를 소개해 보고자 합니다.

---

시작은 엑셀 VBA 매크로 부터

제 채널의 시작은 파워포인트 VBA 매크로 예제에서 부터 시작하지만, 실제 VBA 매크로를 처음 익히고자 하시는 분들께는 엑셀 VBA 매크로부터 시작하시기를 강력히 추천합니다.

엑셀로 시작해야되는 이유 1 : 정확한 위치

VBA 매크로를 통하여 어떤 일을 자동화하게 되면, 자동으로 동작한 결과를 문서의 어떤 구역에 입력하고 저장하는 작업을 해야 합니다.

사람에게 일을 시킨 경우라면, ‘적당히 빈칸 보기 좋은곳에 값을 넣어.’

이와 같은 형태로 업무 지시를 할 수 있겠지만, 컴퓨터에게 일을 시킬 때는 절대 불가능한 이야기 입니다.

‘정확하게 어떤 칸, 어떤 위치에 어떻게 값을 넣어.’

라고 컴퓨터에게 지시를 해 줘야 하는데, 이 주소가 엑셀만큼 정확하게 딱 떨어지는 사무용 S/W도 없습니다.

어느 Sheet, 몇번째 칼럼의 몇번째 줄에 값을 넣어. 라는 값을 명확하게 정해줄 수 있으니까요.

엑셀로 시작해야 하는 이유 2 : 매크로 기록 기능

처음 매크로 파일을 만들려고 생각하면 빈 화면에 무엇부터 어떻게 채워 넣어야 할지 막막하기 그지 없습니다.

하지만, 엑셀에서는 구현하고자 하는 기능이 있다면, 키딩부터 시작 하는 것이 아니라, 내가 하고자 하는 일을 녹화하는 매크로 기록 기능을 활용할 수 있습니다.

메뉴의 개발도구 → 매크로 기록을 통해 간단히 실행가능한 이 기능은 내가 엑셀에서 하는 동작 하나하나를 기록하여 VBA 코드로 만들어줍니다.

자동으로 만들어진 코드를 분석하면 내가 기능에 대해 잘 모르고 있더라도 쉽게 구현이 가능하고, 어떤 방식으로 응용할 수 있는지도 확인이 가능합니다.

예를 들어 다음과 같은 KBO 사이트에서 팀 순위표를 엑셀로 복사해서 가져온 다음 원하는 형식으로 편집하는 작업을 매일 한다고 가정을 했을때,

해당 영역을 복사한 다음 엑셀에 붙여 넣으면 일단 잘 붙습니다.

여기서 몇가지 편집을 통해 일괄적으로 보고 싶은 양식이 있다고 했을 때 매번 자료를 가져온다음 그와 같은 반복 작업을 하는것은 번거로울 수 있습니다.

승률을 굵게 강조하고 순위 1~4까지를 색깔 변경을 하고, 표 테두리 색을 좀더 진하게 한다고 할때

매번 하나하나 선택하여 작업해도 되겠지만, 항상 해야하는 일이 똑같다면 매크로로 한번 그 과정을 저장하여 만들어 두면 좀더 편하게 작업할 수 있습니다.

​메뉴의 개발도구 → 매크로 기록을 클릭하고 편집작업을 시작합니다.

편집작업을 마친다음 기록 중지를 누릅니다.

이제 개발도구 → Visual Basic을 클릭하여 VBA 화면에 들어가면 모듈탭이 하나 만들어져 있고, 내가 모르는 소스 내용이 잔뜩 들어가 있습니다.

Sub 매크로1()
'
' 매크로1 매크로
'

'
    Cells.Select
    Selection.Font.Bold = False
    Range("G1:G11").Select
    Selection.Font.Bold = True
    Rows("2:5").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Range("E3").Select
End Sub

그런데, 소스 내용을 잘 살펴 보면 어떤 패턴이 반복 되는 것이 보입니다.

1) 선택하고
2) Action

결국 VBA 매크로에서 실행하는 대부분의 작업은 내가 원하는 선택, Action의 작업을 얼마나 정확히 잘 지시할 수 있느냐의 문제라고 볼 수 있습니다.

엑셀에서 기본적으로 제공하는 기능을 구현하는 것에 대한 영역만이라면, 매크로 기록 만으로 원하는 기능을 구현하는 법을 알고, 적용할 수 있기 때문에 첫 시작으로는 엑셀만한 친구가 없습니다.

---

개체와 속성을 파악하자

VBA 매크로 기록을 통해 어떤 방식으로 코딩이 작성되는지를 봤다면, 선택 → 동작이 반복되는 방식으로 작업이 이루어 진다는 것을 확인할 수 있었습니다.

결국 어떤 개체를 잘 ‘선택’하고, 그 개체에서 할 수 있는 ‘동작’을 알아서 내가 원하는 동작을 이끌어 내는것.

이것이 VBA 매크로를 만드는 핵심이라고 할 수 있겠습니다.

엑셀의 개체 구성 요소

엑셀의 경우 개체는 여러개의 구성요소(하위개체)로 구성되고 그 구성요소들은 다시 여러개의 구성요소를 가지는 방식으로 구성됩니다.

엑셀 파일 자체는 Workbook이라는 개체로 구성되어 있고, 이 Workbook에는 여러개의 Sheet가 들어갑니다. 즉, Sheet가 모여서 Wrokbook을 구성합니다.

그리고 각각의 Sheet에는 여러개의 칸 즉, Cell이 들어가 있습니다.

각각의 셀에는 그 칸에 해당하는 정보와 서식 등의 여러가지 정보를 가지고 있습니다.

Cell 이외에도 외부 파일에서 이미지를 삽입하거나 어떤 도형을 입력하였을 경우 이들 이미지/도형들 또한 Sheet 안에 존재하는 별도의 개체로 인식됩니다.

앞서 매크로를 사용할 때 Range라는 개체를 많이 사용했는데, 사실 이 Range라는 개체는 1개 또는 여러개의 Cell을 범위로 잡는 개체입니다.

좀 더 엄밀히 따진다면 “Workbook 하위에는 Sheet가, Sheet의 하위에는 Range가 존재”하는 형태라고 볼 수 있습니다.

​※ 여기서 Workbook, Sheet를 생략할 경우 이는 가장 최근에 선택하여 작업하던 Workbook, Sheet의 구성요소라는 것을 가정하고 명령을 실행합니다.

​이와 같은 개체의 상관관계를 표현하는 방법은 최 상위 개체이름 다음에 하위 구성요소에 대해서 자세히 언급할때는 “.”으로 연결하는 방식으로 취하고 있습니다.

​그리고 이 .의 맨 마지막에 붙는 값은 어떤 특정 속성 또는 명령어를 취할 수 있게 되어 있습니다.

​예를 들어서 현재 Workbook의 2번째 Sheet의 “C3” 칸을 선택하고자 한다면,

2번째 Sheet를 선택한 상태에서

Range("C3").Select

로 하여도 해당 위치가 선택되는 것을 볼 수 있습니다.​

보다 정확하게 만들어 본다면 아래와 같이 표현할 수 있습니다.

ActiveWorkbook.Sheets(2).Range("C3").Select

이와 같은 개념을 처음 공부할 때부터 가져가시는 것이 좋습니다.

소규모 VBA 매크로의 경우 같은 Sheet 내에서만 동작이 이루어지는 경우가 많아서 크게 신경쓰지 않고 Range 만으로 값을 다루더라도 크게 문제가 되지 않습니다.

하지만, 조금 더 복잡한 VBA를 다루게 되어 여러개의 파일을 열어서 동시에 편집하는 등, 여러개의 Sheet를 넘나들 때에는 이와 같은 개념을 이해하고 있어야 정확하게 어떤 일을 할 수 있습니다.

속성과 그 구조를 파악하는 방법

​각각의 개체와 하위개체, 그리고 개체에 포함되어 있는 속성들을 잘 모를 경우에는

1) 보기 → 조사식창을 통하여,
2) 매크로 기록을 통하여 원하는 동작을 실행한 다음 만들어진 소스를 통하여

알아볼 수 있습니다.

​매크로 기록을 통하여 원하는 동작을 실행한 후 소스를 통해 알아보는 방법은 바로 위의 매크로 기록의 예에서 본 내용을 참고하면 되겠습니다.

조사식창을 통하여 알아보는 방법은 다음과 같습니다.

​예를 들어 C3 칸의 세부 속성을 알고 싶다고 한다면, 매크로 소스에서 중단점을 만든 다음, 매크로를 실행하여 중단점에서 소스를 멈춥니다.

보기 → 조사식 창 → 마우스 우클릭 → 조사식 추가 → 보고자 하는 개체 입력, 확인하여 알고자 하는 개체에 대한 속성값들을 살펴볼 수 있습니다.

Activeworkbook을 선택해서 보더라도 굉장히 많은 개체와 속성값이 포함되어 있는 것을 볼 수 있는데, 아래로 내리다 보면 여러개의 Sheets가 구성요소로 들어가 있는것을 볼 수 있습니다.

VBA에서 다룰수 있는 개체와 속성은 워낙 다양한 관계로 모든 내용을 파악하기는 매우 어렵습니다.

하지만, 어떤 방식으로 구성 되고 어떤 식으로 필요한 내용을 찾아볼 수 있는지 정도만 알고 있어도 내게 필요한 상황이 생겼을때 찾아볼 수 있으므로 큰 도움이 됩니다.

---

조건문과 반복문 부터 이해하자

VBA 매크로는 분명 컴퓨터 프로그래밍 언어의 모습을 띄고 있습니다.

따라서, VBA 매크로를 이용해 우리가 얻을 수 있는 가장 큰 이득은 컴퓨터 프로그램을 통하여 얻는 이득과 동일하다고 볼 수 있습니다.

1) 무한에 가까운 반복을 수행하더라도 지치지 않고 정확히 시행하는 능력(반복)
2) 정확한 기준이 있을 경우, 실수없이 정확한 판단을 하는 능력(조건)

​사실 제 아무리 복잡한 프로그램이라 할지라도 그 근간에는 이들 반복문과 조건문을 걸려서 돌아가는 것이 대부분의 프로그램입니다.

따라서, VBA 매크로를 다룰때 우리도 조건문과 반복문에 대한 내용만 잘 이해하고 있어도, 이후 필요한 기능을 구현할때 큰 도움을 받을 수 있습니다.

​MS에서 제공하는 VBA Referance site에서 Looping through code라는 내용을 심도있게 다룬 영역이 있는데, 이 영역을 잘 살펴보면 조건문에 대한 예제와 설명을 찾아볼 수 있습니다.

Looping through code

조건문 살펴보기

1) IF … Then … ELS 문

​가장 기본적인 조건문이라고 볼 수 있겠습니다. 어떤 조건이 만족하는지 아닌지를 따져보고 해당 조건을 만족하면 Then 이하의 명령어를 시행하고 그렇지 않으면 Else 이하의 명령을 수행하는 구문입니다.

Sub FixDate() 
 myDate = #2/13/95# 
 If myDate < Now Then myDate = Now 
End Sub

위 예시로 볼때 myDate로 설정된 값이 지금 날짜보다 이른 날짜일 경우 현재 날짜를 입력하라는 명령어가 수행됩니다.

Sub AlertUser(value as Long) 
 If value = 0 Then 
   AlertLabel.ForeColor = vbRed 
   AlertLabel.Font.Bold = True 
   AlertLabel.Font.Italic = True 
 Else 
   AlertLabel.Forecolor = vbBlack 
   AlertLabel.Font.Bold = False 
   AlertLabel.Font.Italic = False 
 End If 
End Sub

위 예시에서는 Value가

0일때에는 글씨 색을 붉은색, 굵게, 기울임체 효과를 주라는 명령이고,
0이 아닐 때에는 검은색 글씨로 하면서 굵게와 기울임 효과는 해지하라는 명령입니다.

O 아니면 X의 2가지 케이스에 대해서면 명령어를 나눠서 수행할 수 있으며, 여러가지 조건을 순차적으로 따질 때에는 연속해서 작성할 수 있습니다.

Function Bonus(performance, salary) 
 If performance = 1 Then 
  Bonus = salary * 0.1 
 ElseIf performance = 2 Then 
  Bonus = salary * 0.09 
 ElseIf performance = 3 Then 
  Bonus = salary * 0.07 
 Else 
  Bonus = 0 
 End If 
End Function

Select Case 문

앞선 예제의 If… Then … Else 문의 경우 하나의 조건만을 따질 때는 무리없이 사용이 가능합니다.

하지만, 마지막에 사용된 예제처럼 같은 변수값을 3가지 또는 그 이상의 경우의 수에 따라서 다른값을 적용하고 동작해야 할 경우에는 Select Case 문이 더 용이할 수 있습니다.

Function Bonus(performance, salary) 
  Select Case performance 
    Case 1 
      Bonus = salary * 0.1 
    Case 2, 3 
      Bonus = salary * 0.09 
    Case 4 To 6 
      Bonus = salary * 0.07 
    Case Is > 8 
      Bonus = 100 
    Case Else 
      Bonus = 0 
  End Select 
End Function 

위의 예제의 경우 performance에

1이라는 값이 들어갔을 경우,
2 또는 3이 들어갔을경우,
4~6이 들어갔을 경우,
8보다 큰 값이 들어갔을경우,
그리고 그 외의 경우에 따라

값이 다르게 계산되게 Case문이 작성되었습니다.

이를 같은 내용으로 IF … Then … Else 문으로 만든다면 만들 수야 있겠지만, Case문에 비해 훨씬 보기도 안좋고 해석하기도 좋지 않았을 듯 합니다.

반복문 살펴보기

Do… loop 문

특정 조건하에서 계속 반복하게 하는 구문입니다.

반복문의 시작지점에 Do를, 반복이 종료되는 위치에 Loop를 입력합니다.

반복을 계속 할지 말지를 판단하는 기준은 Do 다음 또는 Loop 다음에 들어옵니다.

Do 바로 뒤에 조건이 있을 경우에는 반복문의 시작 시점에 조건을 확인하고,
Loop 뒤에 조건이 있을 경우에는 반복문을 한번 실행하고 난 다음에 다시 반복을 할지 말지를 판단합니다.

동일 조건이라면 1번의 반복을 더하거나 덜하는 차이가 있을 수 있습니다.

반복을 계속할지 여부에 대한 판단 기준으로 사용하는 조건은 While 또는 Until 입니다.

While의 경우 해당 조건이 만족하는 한 계속 반복하라는 의미이고,
Until은 해당 조건이 만족 될 때까지 반복하라는 의미입니다.

​While과 조합이된 Do… Loop 예시문은 다음과 같습니다.

Sub ChkFirstWhile() 
    counter = 0 
    myNum = 20 
    Do While myNum > 10 
        myNum = myNum - 1 
        counter = counter + 1 
    Loop 
    MsgBox "The loop made " & counter & " repetitions." 
End Sub 
 
Sub ChkLastWhile() 
    counter = 0 
    myNum = 9 
    Do 
        myNum = myNum - 1 
        counter = counter + 1 
    Loop While myNum > 10 
    MsgBox "The loop made " & counter & " repetitions." 
End Sub

두 가지 예시 모두 myNum 이라는 값이 주어진 조건보다 큰 값일 경우에 한해서 반복문을 계속하라는 의미가 됩니다.

첫번째 예시의 경우 초기값은 20이고, 1씩 감소하면서 몇번 감소하는지를 따지는데, myNum 값이 20에서 시작하여 한바퀴 돌 때마다 1씩 감소하는 구조입니다.

따라서 20, 19, 18, … 이런식으로 줄어서 11까지 계산을 하고, 10이 되는 순간 조건을 While 조건을 만족하지 않기 때문에 Do … Loop 문을 빠져 나가게 됩니다. counter에는 10이 기록 될 것이고요.

두번째 예시의 경우 초기값이 9로 이미 10보다 낮은 값이기 때문에 반복문을 돌 수 있는 조건은 되지 않지만, 반복문의 조건을 따지는 While 문이 반복문의 마지막인 Loop에 있기 때문에 해당 반복문은 한번은 실행하게 됩니다.

즉, 반복하는 조건으로 걸었던 10보다 큰값을 만족하지 못하는 초기값을 가졌음에도 불구하고 한번은 반복문을 실행한 것이지요. 이런부분이 반복문을 만들때 항상 주의해야 하는 부분입니다.

​Until문을 사용한 예시는 다음과 같습니다.

Sub ChkFirstUntil() 
    counter = 0 
    myNum = 20 
    Do Until myNum = 10 
        myNum = myNum - 1 
        counter = counter + 1 
    Loop 
    MsgBox "The loop made " & counter & " repetitions." 
End Sub 
 
Sub ChkLastUntil() 
    counter = 0 
    myNum = 1 
    Do 
        myNum = myNum + 1 
        counter = counter + 1 
    Loop Until myNum = 10 
    MsgBox "The loop made " & counter & " repetitions." 
End Sub

앞서 살펴봤던 While 조건을 사용한 Do … Loop 문과 크게 다르지 않습니다. 다만 이번에는 걸려있는 조건이 만족 될 때까지 반복하는 상태로 조건이 반대로 적용되었을 뿐입니다.

​그 외에 살펴볼 것은 반복문을 실행하는 조건에 있기는 하지만, 중간에 반복문을 빠져 나와야 하는 경우 사용할수 있는 반복구문 탈출 문구 입니다.

Exit Do 라는 명령어를 추가함으로써 해당 명령이 실행될 때 반복문 구문을 빠져 나오게 됩니다.

이에 대한 예시는 아래의 코드를 참고하시기 바랍니다.

Sub ExitExample() 
    counter = 0 
    myNum = 9 
    Do Until myNum = 10 
        myNum = myNum - 1 
        counter = counter + 1 
        If myNum < 10 Then Exit Do 
    Loop 
    MsgBox "The loop made " & counter & " repetitions." 
End Sub

For … Next 문

이번에 살펴볼 예시는 For Next문입니다.

For 다음에 특정 변수를 초기값과 도착값을 정하여 그 값까지 도착할 때까지 반복문을 계속하는 구문입니다.

Sub Beeps() 
    For x = 1 To 50 
        Beep 
    Next x 
End Sub

Sub TwosTotal() 
    For j = 2 To 10 Step 2 
        total = total + j 
    Next j 
    MsgBox "The total is " & total 
End Sub

Sub NewTotal() 
    For myNum = 16 To 2 Step -2 
        total = total + myNum 
    Next myNum 
    MsgBox "The total is " & total 
End Sub

원래 구문에서는 조건중에 To 다음에 Step조건을 포함하여 반복문을 돌때마다 얼마만큼씩 숫자를 증가시킬것인가를 따지는데, 생략할 경우에는 자동으로 1씩 증가한다고 생각하고 반복문을 실행합니다.

For Each … Next

For Each … Next문은 For … Next 문과 유사한 구조를 가지지만, 반복을 되풀이할 조건을 따지는 것은 배열 변수로 합니다.

그래서 정해진 배열의 모든 값에 대하여 순차적으로 작업을 완료할 때까지 명령을 반복하는 구조입니다.

※ 제가 작성한 코드에서는 가장 많이 사용하는 구문입니다.

정확히 몇번을 반복하게 할 것인지를 정하거나, 엑셀에서 몇번째 줄까지 값을 넣겠다.

이런식으로 작업을 할 때는 For … next 문을 사용하면 되겠지만, 어떤 폴더의 파일들을 순차적으로 불러온다거나, 크롤링 등의 방법을 통해 변수 배열을 가져와서 빠짐 없이 모든 변수값을 살펴보고자 할 때에는 For Each … Next 문을 사용하는 것이 더 좋습니다.

​특히, 엑셀에서 어떤 범위가 선택된 상태에서 범위내에 있는 모든 Cell을 하나하나 따지거나 하는 등의 반복문을 짜야 할 때에는 이 For Each … Next 문이 더 효과적입니다.

Sub RoundToZero() 
 For Each myObject in myCollection 
 If Abs(myObject.Value) < 0.01 Then myObject.Value = 0 
 Next 
End Sub

---

VBA 매크로 추천 참고 자료

VBA 매크로를 다루는 자료와 사이트는 굉장히 많은데, 저는 특히 아래의 한권의 책과, 하나의 사이트를 추천합니다.

그리고, 직접 나에게 필요한 문제를 풀어보기를 추천합니다.

추천 도서 : Mastering VBA for Microsoft Office

시중에 찾아보면 VBA를 다룬 책은 상당히 쉽게, 많이 찾아볼 수 있는데, 한글로 만들어진 VBA관련 책중 100이면 100 엑셀 VBA를 다루고 있습니다.

사실 VBA 소스를 만들다 보면 가장 강력한 것이 엑셀이고 응용이 가능한 부분이 제일 많은것 또한 엑셀이기에 이해는 갑니다.

저 처럼 온갖 Office 소프트웨어에 다 VBA 매크로 걸고 다니는 사파(?)의 길을 걷는 사람이 그리 많지 않은 것도 또 하나의 이유일듯 합니다.

그런 까닭에 MS Office 전반에 대한 내용을 포관적으로 다룬 괜찮은 교재는 찾기가 어렵습니다.

그나마, 영어로 된 원서중에는 간간히 쓸만한 책이 보입니다.

지금 소개해 드리는 ‘Mastering VBA for Microsoft Office’는 아마존 서점에서 Kindle 버전으로 구매가 가능한 제품입니다.

저의 경우 제 전자책에 킨들을 설치, 해당 책들 구매해 두고 막힐 때마다 찾아보고 있습니다.

​VBA의 맨처음 기초에서 부터 각 Office 프로그램별 활용의 중급 예까지 소개되고 있어서 입문서로 이만한 교재도 많지는 않은듯 합니다.

다만…. 영문 원서의 압박은… 극복 해야겠지요…

영어가 짧은 제게는 멘붕의 시작이기도 하긴 합니다. ㅠ.ㅠ

추천 사이트 : MS Office VBA 관련 공식 개발자 사이트

PowerPoint VBA reference 공식

위 링크를 클릭하면, MS사에서 개발자들을 위해 만들어놓은 개발자 사이트, 그중에서도 Power Point VBA에 대한 사이트로 연결됩니다.

물론 이 링크로 찾아들어가면 Power Point 뿐만 아니라 다른 Office 프로그램의 VBA에 대한 가이드도 받을 수 있습니다.

주요 기능별로 설명도 잘 되어 있고, 예시도 많으니 예시 위주로 보시고 필요한 내용이 나오면 역시 복/붙…. (아~~ 복/붙 좋아~~)

이 사이트 또한 영어의 압박이 심하긴 하지만, 예시 위주로 찾아보면 고생은 좀 하지만 왠만하면 답은 다 나오는듯 합니다.