엑셀은 단순한 스프레드시트를 넘어 데이터 분석, 정리, 시각화 등 다양한 작업을 가능하게 해주는 강력한 도구이다. 이와 함께 VBA(Visual Basic for Applications)는 엑셀의 기능을 더욱 확장해 주는 프로그래밍 언어로, 반복적인 작업을 자동화하고, 사용자 지정 기능을 추가하며, 복잡한 데이터 처리도 손쉽게 할 수 있도록 도와준다. 이번 포스트에서는 엑셀 VBA 기능중에 자주사용하는 기능 몇개를 정리해보기로 한다.
변수정의 및 값설정
VB (Visual Basic)에서는 다양한 원시타입(Primitive Types)을 제공한다. 원시타입은 기본적인 데이터 표현 방식으로, 프로그래밍시 가장 기본이 되는 변수들이다. 면저 정의를 해놓고 바로 = 로 대입하면 된다.
Dim row As Integer
row = 1
VBA에서 Set 키워드는 객체를 변수에 바인딩할 때 사용된다. 여기서 객체란 Excel의 Worksheet, Range, Workbook, Application, UserForm, Collection 등과 같은 데이터 구조를 의미한다.
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
그리고 :=는 **명명된 인수(namend arguments)**를 지정하는 데 사용되는 연산자이다. 이를 사용하면 함수나 서브루틴 호출 시 인수의 이름과 값을 쌍으로 연결하여 더 명확하고 읽기 쉬운 코드를 작성할 수 있다. 기본적으로 명명된 인수를 사용하면 인수의 순서에 관계없이 원하는 값을 지정할 수 있다.
cf.Range("J2").AutoFill Destination:=cf.Range("J2:J" & row)
마지막행 위치 구하기
loop 처리를 할때, 마지막행의 위치를 구하는 식을 자주 사용하게 된다. Excel 워크시트의 데이터가 입력된 마지막 행의 위치를 구하는 방법은 여러 가지가 있지만, End 속성을 사용하는 방법이다.
Sub FindLastRowUsingEnd()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1") ' 해당 워크시트 설정
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' 1열(A열)의 마지막 행 찾기
MsgBox "마지막 행: " & lastRow
End Sub
반복문
1부터 10까지의 숫자를 루프 돌면서 누적합을 구하는 VBA 반복문 예제이다.
Sub ForLoopExample()
Dim i As Integer
Dim cumulativeSum As Integer
For i = 1 To 10
cumulativeSum = cumulativeSum + i ' 각 숫자를 누적합에 더함
Next i
' 최종 누적합 출력
MsgBox "최종 누적합: " & cumulativeSum
End Sub
조건문
Visual Basic (VB)에서 조건문은 프로그램의 흐름을 제어하는 데 사용된다. 주요 조건문은 If...Then...Else, Select Case 등이 있는데, If 문만 알아본다.
Dim score As Integer
score = 20
If score >= 90 Then
Console.WriteLine("등급: A")
ElseIf score >= 80 Then
Console.WriteLine("등급: B")
ElseIf score >= 70 Then
Console.WriteLine("등급: C")
ElseIf score >= 60 Then
Console.WriteLine("등급: D")
Else
Console.WriteLine("등급: F")
End If
Formula
엑셀에서는 다양한 수식이 있고, vba에서 바로 수식을 입력가능하다. 만약 여러줄에 동일한 서식을 넣으려면, loop 로 처리하는 거보다는 뒤에 설명할 Filldown 으로 처리하는게 더 수월하다.
Range("A1").Formula = "=IF(B1 > 100, ""크다"", ""작다"")"
Autofill
AutoFill 기능은 셀 범위의 값을 자동으로 채워주는 기능이다. 이 기능은 주로 데이터 패턴을 기반으로 하여 값을 반복하거나 연속적으로 생성하는 데 쓰인다. 보통 엑셀에서 셀선택하고 우측하단의 네모모서리를 잡고 드래그하는 행위와 동일하다.
아래의 예제 실행해보면, 1~10으로 연속된 숫자로 행이 채워지는 것을 볼 수 있을 것이다.
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' 원하는 시트로 수정
' A1 셀에 1 입력
ws.Range("A1").Value = 1
' A1 셀을 기반으로 A1:A10 범위까지 AutoFill
ws.Range("A1").AutoFill Destination:=ws.Range("A1:A10"), Type:=xlFillSeries
타입을 xlFillSeries 로 지정했기 때문인데, 타입은 여러 종류가 있기 때문에 맞춰 사용하도록 한다.
- xlFillDefault: 기본 채우기 옵션을 사용하여 이전 셀의 데이터 패턴을 기준으로 새 셀을 채웁니다.
- xlFillGray: 선택한 범위를 회색으로 채워서 시각적으로 강조합니다. 데이터 표시에는 영향을 주지 않습니다.
- xlFillWeekdays: 주말을 제외한 평일만을 기반으로 날짜를 채우며, 공휴일이나 특정 비지정 날짜는 반영하지 않습니다.
- xlFillSeries: 지정된 범위를 기반으로 수치, 날짜 또는 기타 패턴의 시퀀스를 자동으로 생성하여 채웁니다.
- xlFillMonths: 월 단위로 날짜를 채우며, 연도를 변경하지 않고 다음 월로 자동 증가합니다.
- xlFillYears: 연도 단위로 셀을 채워, 특정 종속성을 유지하면서 다음 연도로 넘어갑니다.
- xlFillDays: 날짜를 기준으로 하루씩 증가시키며, 선택된 시작 날짜부터 연속적인 날짜를 생성해 채웁니다.
Filldown
FillDown은 Excel VBA에서 특정 범위의 셀을 아래로 채우는 방법을 의미한다. 주로 데이터의 패턴을 유지하면서 선택한 셀의 값을 연속적으로 아래쪽으로 복사하는 데 사용된다. Ctrl+d 로 하위셀로 서식이나 값을 채우는 이벤트와 같다.
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' 원하는 시트로 수정
' A1 셀에 1 입력
ws.Range("A1").Value = 1
' A1 셀을 기반으로 A1:A10 범위까지 Filldown
ws.Range("A1:A10").Filldown
위의 기능들을 하나씩 알아가며 작성해봤다.
Public Sub CreateCashflow()
Dim i As Integer
Dim j As Integer
Dim lastRow As Integer
Dim row As Integer
Dim totalRow As Integer
Dim wsbd As Worksheet
Dim wscf As Worksheet
Dim bd As Range
Dim cf As Range
row = 1
Set wsbd = ThisWorkbook.Sheets("bond")
Set wscf = ThisWorkbook.Sheets("cashflow")
Set bd = wsbd.Cells
Set cf = wscf.Cells
lastRow = wsbd.Cells(wsbd.Rows.Count, 1).End(xlUp).row
For i = 2 To lastRow
totalRow = bd(i, 4).Value / bd(i, 5).Value
For j = 1 To totalRow
row = row + 1
cf(row, 1).Value = bd(i, 1).Value
cf(row, 2).Value = j
cf(row, 3).Value = bd(i, 5).Value
cf(row, 4).Value = bd(i, 2).Value * bd(i, 3).Value * bd(i, 5).Value
If j = totalRow Then
cf(row, 5).Value = bd(i, 2).Value
Else
cf(row, 5).Value = 0
End If
Next j
Next i
' 엑셀에 첫행에는 수식이 이미 들어가 있다 가정하고 filldown 처리.
cf.Range("F2:N" & row).FillDown
cf.NumberFormat = "General"
'열을 여러개 union 해서 선택할때,
Union(cf.Columns("D:F"), cf.Columns("I:J"), cf.Columns("L:M")).NumberFormat = _
"_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
End Sub