엑셀 자동화 VBA 유용한 코드

 

엑셀 자동화 업무에 가장 많이 사용하고 유용한 VBA 코드 10가지

엑셀 VBA(Visual Basic for Applications)는 반복적인 엑셀 작업을 자동화하고 복잡한 데이터 처리를 수행하는 데 매우 유용한 도구입니다. 다음은 엑셀 자동화 업무에서 가장 많이 사용되고 유용한 VBA 코드 10가지와 각 코드에 대한 자세한 설명 및 활용 예시입니다.

1. 데이터 필터링 및 추출

VBA

 

Sub FilterAndExtractData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long

    Set ws = ThisWorkbook.Sheets("Sheet1") ' 시트 이름 변경 필요
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    j = 2 ' 추출된 데이터를 넣을 행 시작 번호
    For i = 2 To lastRow ' 데이터 시작 행부터 마지막 행까지 반복
        If ws.Cells(i, "B").Value > 10 Then ' 필터링 조건 변경 필요
            ws.Range(ws.Cells(i, "A"), ws.Cells(i, "C")).Copy ws.Cells(j, "E") ' 추출할 열 범위 변경 필요
            j = j + 1
        End If
    Next i
End Sub

• 설명: 특정 조건에 맞는 데이터를 필터링하고 추출하여 다른 위치에 복사합니다.
• 활용 예시: 특정 값 이상의 데이터를 추출하여 별도의 보고서를 생성하거나, 특정 조건을 만족하는 데이터만 필터링하여 분석합니다.

2. 데이터 정렬

VBA

 

Sub SortData()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1") ' 시트 이름 변경 필요
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ws.Range("A1:C" & lastRow).Sort Key1:=ws.Range("B1"), Order1:=xlAscending, Header:=xlYes ' 정렬 기준 및 범위 변경 필요
End Sub

• 설명: 특정 열을 기준으로 데이터를 오름차순 또는 내림차순으로 정렬합니다.
• 활용 예시: 날짜별, 금액별, 이름별 등 다양한 기준으로 데이터를 정렬하여 분석하거나 보고서를 생성합니다.

3. 중복 데이터 제거

VBA

 

Sub RemoveDuplicates()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1") ' 시트 이름 변경 필요
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ws.Range("A1:C" & lastRow).RemoveDuplicates Columns:=Array(1), Header:=xlYes ' 중복 제거할 열 범위 변경 필요
End Sub

• 설명: 특정 열의 중복 데이터를 제거합니다.
• 활용 예시: 고객 목록, 상품 목록 등에서 중복된 데이터를 제거하여 데이터의 정확성을 높입니다.

4. 여러 시트 통합

VBA

 

Sub ConsolidateSheets()
    Dim wsConsolidated As Worksheet
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long

    Set wsConsolidated = ThisWorkbook.Sheets("Consolidated") ' 통합 시트 이름 변경 필요
    j = 2 ' 통합된 데이터를 넣을 행 시작 번호

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Consolidated" Then ' 통합 시트 제외
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastRow
                ws.Range(ws.Cells(i, "A"), ws.Cells(i, "C")).Copy wsConsolidated.Cells(j, "A") ' 통합할 열 범위 변경 필요
                j = j + 1
            Next i
        End If
    Next ws
End Sub

• 설명: 여러 워크시트의 데이터를 하나의 워크시트로 통합합니다.
• 활용 예시: 여러 지점의 매출 데이터를 하나의 시트로 통합하여 전체 매출 현황을 분석하거나, 여러 부서의 작업 결과를 하나의 시트로 통합하여 전체 작업 현황을 파악합니다.

5. 외부 파일 데이터 가져오기

VBA

 

Sub ImportDataFromExternalFile()
    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long

    Set wb = Workbooks.Open("C:\ExternalData.xlsx") ' 외부 파일 경로 변경 필요
    Set wsSource = wb.Sheets("Sheet1") ' 외부 파일 시트 이름 변경 필요
    Set wsTarget = ThisWorkbook.Sheets("Sheet1") ' 현재 파일 시트 이름 변경 필요

    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    wsSource.Range("A1:C" & lastRow).Copy wsTarget.Range("A1") ' 가져올 열 범위 변경 필요

    wb.Close False
End Sub

• 설명: 외부 엑셀 파일의 데이터를 현재 엑셀 파일로 가져옵니다.
• 활용 예시: 외부 시스템에서 생성된 데이터를 엑셀로 가져와 분석하거나, 다른 부서에서 제공받은 데이터를 현재 파일에 통합합니다.

6. 셀 서식 자동 설정

VBA

 

Sub FormatCells()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1") ' 시트 이름 변경 필요
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        If ws.Cells(i, "B").Value > 100 Then ' 조건 변경 필요
            ws.Cells(i, "C").Font.Bold = True ' 서식 변경 필요
            ws.Cells(i, "C").Interior.Color = RGB(255, 255, 0)
        End If
    Next i
End Sub

• 설명: 특정 조건에 맞는 셀의 서식을 자동으로 설정합니다.
• 활용 예시: 특정 값 이상의 매출 데이터를 강조하거나, 특정 조건을 만족하는 셀의 배경색을 변경하여 시각적으로 구분합니다.

7. PDF 파일 생성

VBA

 

Sub ExportToPDF()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1") ' 시트 이름 변경 필요
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Report.pdf", Quality:=xlQualityStandard, OpenAfterPublish:=False ' 파일 경로 및 이름 변경 필요
End Sub

• 설명: 특정 워크시트를 PDF 파일로 저장합니다.
• 활용 예시: 엑셀 보고서를 PDF 파일로 변환하여 이메일로 전송하거나, 웹에 게시합니다.

8. 이메일 자동 발송

VBA

 

Sub SendEmail()
    Dim outlookApp As Object
    Dim outlookMail As Object

    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)

    With outlookMail
        .To = "recipient@example.com" ' 수신인 이메일 주소 변경 필요
        .Subject = "엑셀 자동화 보고서" ' 이메일 제목 변경 필요
        .Body = "첨부된 엑셀 보고서를 확인해주세요." ' 이메일 내용 변경 필요
        .Attachments.Add ThisWorkbook.FullName
        .Send
    End With

    Set outlookMail = Nothing
    Set outlookApp = Nothing
End Sub

• 설명: 엑셀 파일을 첨부하여 이메일을 자동으로 발송합니다.
• 활용 예시: 매일/매주/매월 정기적으로 보고서를 이메일로 발송하거나, 특정 이벤트 발생 시 알림 이메일을 발송합니다.

9. 사용자 정의 함수

VBA

 

Function CalculateDiscountedPrice(price As Double, discountRate As Double) As Double
    CalculateDiscountedPrice = price * (1 - discountRate)
End Function

• 설명: 사용자 정의 함수를 만들어 엑셀 워크시트에서 사용할 수 있습니다.
• 활용 예시: 복잡한 계산식을 사용자 정의 함수로 만들어 워크시트에서 간편하게 사용하거나, 특정 비즈니스 로직을 함수로 구현하여 재사용성을 높입니다.

10. 오류 처리

VBA

 

Sub ErrorHandling()
    On Error GoTo ErrorHandler

    ' 오류 발생 가능성이 있는 코드
    ' ...

    Exit Sub
ErrorHandler:
    MsgBox "오류 발생: " & Err.Description
End Sub

• 설명: 오류 처리 코드를 추가하여 매크로