VBA 초보자를 위한 24가지 유용한 Excel 매크로 예제(사용 준비 완료)

Excel 매크로를 사용하면 작업 속도를 높이고 많은 시간을 절약할 수 있습니다.

VBA 코드를 가져오는 한 가지 방법은 매크로를 기록하고 매크로가 생성하는 코드를 가져오는 것입니다.그러나 매크로 레코더의 코드는 종종 실제로 필요하지 않은 코드로 가득 차 있습니다.매크로 레코더에도 몇 가지 제한 사항이 있습니다.

따라서 뒷주머니에 보관하고 필요할 때 사용할 수 있는 유용한 VBA 매크로 세트를 가지고 있는 것이 좋습니다.

처음에 Excel VBA 매크로를 코딩하는 데 시간이 걸릴 수 있지만 완료되면 참조로 사용하고 다음에 필요할 때 사용할 수 있습니다.

이 방대한 기사에서는 자주 필요하고 개인 금고에 보관해야 하는 몇 가지 유용한 Excel 매크로 예제를 나열합니다.

더 많은 매크로 예제로 이 튜토리얼을 계속 업데이트하겠습니다.목록에 있어야 할 항목이 있으면 댓글을 남겨주세요.

나중에 참조할 수 있도록 이 페이지를 북마크에 추가할 수 있습니다.

이제 매크로 예제를 시작하고 VBA 코드를 제공하기 전에 먼저 예제 코드를 사용하는 방법을 보여드리겠습니다.

Excel 매크로 예제의 코드 사용

다음은 예제의 코드를 사용하기 위해 따라야 할 단계입니다.

• 매크로를 사용할 통합 문서를 엽니다.
• Alt 키를 누른 상태에서 F11 키를 누릅니다.이 열릴 것입니다VB 편집기.
• 프로젝트 탐색기에서 개체를 마우스 오른쪽 버튼으로 클릭합니다.
• 삽입 -> 모듈로 이동합니다.
• 모듈 코드 창에 코드를 복사하여 붙여넣습니다.

예제에 워크시트 코드 창에 코드를 붙여넣어야 한다고 나와 있으면 워크시트 개체를 두 번 클릭하고 코드를 복사하여 코드 창에 붙여넣습니다.

통합 문서에 코드를 삽입한 후 .XLSM 또는 .XLS 확장자로 저장해야 합니다.

매크로를 실행하는 방법

VB 편집기에서 코드를 복사한 후 매크로를 실행하는 단계는 다음과 같습니다.

• 개발자 탭으로 이동합니다.
• 매크로를 클릭합니다.

• 매크로 대화 상자에서 실행할 매크로를 선택합니다.
• 실행 버튼을 클릭합니다.

리본에서 개발자 탭을 찾을 수 없는 경우 이 자습서를 읽고 얻는 방법을 알아보세요.

워크시트 코드 창에 코드를 붙여넣으면 코드 실행에 대해 걱정할 필요가 없습니다.지정된 동작이 발생하면 자동으로 실행됩니다.

이제 작업을 자동화하고 시간을 절약하는 데 도움이 되는 유용한 매크로 예제를 살펴보겠습니다.

참고: 많은 아포스트로피(') 다음에 한 두 줄이 오는 것을 볼 수 있습니다.이들은 코드를 실행할 때 무시되고 자체/독자 주석으로 배치되는 주석입니다.

기사나 코드에서 오류를 발견하면 알려주십시오.

엑셀 매크로 예제

이 문서에서는 다음 매크로 예제에 대해 설명합니다.

한 번에 모든 워크시트 숨기기 해제

여러 개의 숨겨진 시트가 있는 통합 문서에서 작업하는 경우 이러한 시트를 하나씩 숨김을 해제해야 합니다.숨겨진 시트가 많은 경우 시간이 걸릴 수 있습니다.

다음은 통합 문서의 모든 시트 숨기기를 해제하는 코드입니다.

'이 코드는 통합 문서의 모든 시트를 숨김 해제합니다. Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Next ws End Sub

위의 코드는 VBA 루프(For Each)를 사용하여 통합 문서의 각 워크시트를 반복합니다.그런 다음 시트의 visible 속성을 visible로 변경합니다.

다음은 다양한 방법을 사용하여 Excel에서 워크시트 숨기기를 해제하는 방법에 대한 자세한 자습서입니다.

활성 워크시트를 제외한 모든 워크시트 숨기기

보고서 또는 대시보드에서 작업 중이고 보고서/대시보드가 ​​있는 시트를 제외한 모든 시트를 숨기려는 경우 이 매크로 코드를 사용할 수 있습니다.

'이 매크로는 활성 시트를 제외한 모든 워크시트를 숨깁니다. Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name then ws.Visible = xlSheetHidden Next ws End Sub

VBA를 사용하여 알파벳순으로 워크시트 정렬

이 매크로 코드는 시트가 많은 통합 문서가 있고 해당 시트를 알파벳순으로 정렬하려는 경우에 유용할 수 있습니다.시트 이름을 연도나 직원 이름 또는 제품 이름으로 사용하는 경우 이런 일이 발생할 수 있습니다.

'이 코드는 워크시트를 알파벳순으로 정렬합니다. Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

한 번에 모든 워크시트 보호

통합 문서에 많은 시트가 있고 모든 시트를 보호하려는 경우 이 매크로 코드를 사용할 수 있습니다.

코드에서 암호를 지정할 수 있습니다.시트 보호를 해제하려면 이 암호가 필요합니다.

'이 코드는 한 번에 모든 시트를 보호합니다. Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'Test123을 원하는 암호로 교체합니다. For Each ws In Worksheets ws.Protect password:=password Next ws 엔드 서브

한 번에 모든 워크시트 보호 해제

워크시트의 일부 또는 전체를 보호한 경우 워크시트를 보호하는 데 사용되는 코드를 약간만 수정하면 보호를 해제할 수 있습니다.

'이 코드는 한 번에 모든 시트를 보호합니다. Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'Test123을 원하는 암호로 교체합니다. For Each ws In Worksheets ws.Unprotect password:=password Next ws 엔드 서브

암호는 시트를 잠그는 데 사용한 것과 동일한 암호여야 합니다.그렇지 않은 경우 오류가 표시됩니다.

모든 행과 열 숨기기 해제

이 매크로 코드는 숨겨진 모든 행과 열을 숨김 해제합니다.

이것은 다른 사람에게서 파일을 가져오고 숨겨진 행/열이 없는지 확인하려는 경우에 유용할 수 있습니다.

'이 코드는 Worksheet Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub의 모든 행과 열을 숨김 해제합니다.

병합된 모든 셀 병합 해제

셀을 하나로 병합하는 것은 일반적인 관행입니다.작동하는 동안에는 셀이 병합될 때 데이터를 정렬할 수 없습니다.

병합된 셀이 있는 워크시트를 사용하는 경우 아래 코드를 사용하여 병합된 모든 셀을 한 번에 병합 해제합니다.

'이 코드는 병합된 모든 셀을 병합 해제합니다. Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

"병합 및 가운데 맞춤" 옵션 대신 "선택 영역 중심" 옵션을 사용하는 것이 좋습니다.

이름에 타임스탬프가 포함된 통합 문서 저장

작업 버전을 만들어야 하는 경우가 많습니다.시간이 지남에 따라 파일을 처리하는 장기 프로젝트에 적합합니다.

타임스탬프와 함께 파일을 저장하는 것이 좋습니다.

타임스탬프를 사용하면 파일로 돌아가 어떤 변경 사항이 있었는지 또는 어떤 데이터가 사용되었는지 확인할 수 있습니다.

다음은 통합 문서를 지정된 폴더에 자동으로 저장하고 저장할 때 타임스탬프를 추가하는 코드입니다.

'이 코드는 이름에 타임스탬프가 있는 파일을 저장합니다. Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" 및 타임스탬프 End Sub

폴더 위치와 파일 이름을 지정해야 합니다.

위의 코드에서 "C:UsersUsernameDesktop은 내가 사용하는 폴더 위치입니다.파일을 저장할 폴더 위치를 지정해야 합니다.또한 파일 이름 접두사로 일반 이름 "WorkbookName"을 사용했습니다.프로젝트 또는 회사와 관련된 콘텐츠를 지정할 수 있습니다.

각 워크시트를 별도의 PDF로 저장

다른 연도, 부서 또는 제품의 데이터를 사용하는 경우 다른 워크시트를 PDF 파일로 저장해야 할 수 있습니다.

수동으로 수행하는 경우 시간이 많이 소요되는 프로세스일 수 있지만 VBA는 작업 속도를 높입니다.

다음은 각 워크시트를 별도의 PDF로 저장하는 VBA 코드입니다.

'이 코드는 각 워크시트를 별도의 PDF 하위로 저장합니다. SaveWorkshetAsPDF() Dim ws As Worksheet As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub

위의 코드에서 PDF를 저장할 폴더 위치의 주소를 지정했습니다.또한 각 PDF는 시트와 동일한 이름을 갖게 됩니다.이 폴더 위치를 수정해야 합니다(이름이 Sumit이고 데스크탑의 테스트 폴더에 저장하지 않은 경우).

이 코드는 워크시트에만 적용됩니다(차트 시트에는 적용되지 않음).

각 워크시트를 별도의 PDF로 저장

다음은 전체 통합 문서를 지정된 폴더에 PDF로 저장하는 코드입니다.

'이 코드는 전체 통합 문서를 PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub로 저장합니다.

이 코드를 사용하려면 폴더 위치를 변경해야 합니다.

모든 수식을 값으로 변환

수식이 많은 워크시트가 있고 해당 수식을 값으로 변환하려는 경우 이 코드를 사용합니다.

'이 코드는 모든 수식을 값으로 변환합니다.Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

이 코드는 사용된 셀을 자동으로 인식하고 값으로 변환합니다.

수식으로 셀 보호/잠금

계산이 많고 실수로 삭제하거나 변경하고 싶지 않은 경우 수식으로 셀을 잠글 수 있습니다.

다음은 수식으로 모든 셀을 잠그고 다른 모든 셀은 잠금 해제된 상태로 두는 코드입니다.

'이 매크로 코드는 수식이 있는 모든 셀을 잠급니다. Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

관련 자습서: Excel에서 셀을 잠그는 방법.

통합 문서의 모든 시트 보호

다음 코드를 사용하여 통합 문서의 모든 시트를 한 번에 보호합니다.

'이 코드는 통합 문서의 모든 시트를 보호합니다. Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws. Protect Next ws End Sub

이 코드는 모든 시트를 하나씩 살펴보고 보호합니다.

모든 시트의 보호를 해제하려면 코드에서 ws.Protect 대신 ws.Unprotect를 사용하십시오.

선택 항목의 각 행 뒤에 행 삽입

선택한 범위의 모든 행 뒤에 빈 행을 삽입하려는 경우 이 코드를 사용합니다.

'이 코드는 선택 Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow에 행을 삽입합니다. ActiveCell.Offset(2, 0)을 삽입합니다. 다음 선택 i End Sub

다시 말하지만 이 코드를 수정하여 선택한 범위의 각 열 뒤에 빈 열을 삽입할 수 있습니다.

인접 셀에 날짜 및 타임스탬프 자동 삽입

타임스탬프는 활동을 추적할 때 사용하는 것입니다.

예를 들어, 언제 특정 비용이 발생했는지, 언제 판매 송장이 생성되었는지, 언제 셀에 데이터를 입력했는지, 보고서를 마지막으로 업데이트했는지 등과 같은 활동을 추적할 수 있습니다.

이 코드를 사용하여 기존 콘텐츠를 입력하거나 편집할 때 인접한 셀에 날짜 및 시간 스탬프를 삽입합니다.

'이 코드는 인접 셀에 타임스탬프를 삽입합니다. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value <> "" then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End If 핸들러: End Sub

이 코드를 워크시트 코드 창에 삽입해야 합니다(지금까지 다른 Excel 매크로 예제에서 수행한 모듈 코드 창에는 없음).이렇게 하려면 VB 편집기에서 이 기능을 원하는 시트 이름을 두 번 클릭합니다.그런 다음 이 코드를 복사하여 해당 시트의 코드 창에 붙여넣습니다.

또한 이 코드는 데이터 입력이 열 A에서 수행될 때 작동합니다(코드에 Target.Column = 1 줄이 있음).그에 따라 변경할 수 있습니다.

선택 항목에서 대체 행 강조 표시

번갈아 가며 강조 표시하면 데이터의 가독성이 크게 향상될 수 있습니다.인쇄하여 데이터를 검색해야 할 때 유용할 수 있습니다.

다음은 선택 항목의 대체 행을 즉시 강조 표시하는 코드입니다.

'이 코드는 선택 항목의 대체 행을 강조 표시합니다. Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

코드에서 색상을 vbCyan으로 지정했습니다.다른 색상(예: vbRed, vbGreen, vbBlue)을 지정할 수도 있습니다.

철자가 틀린 셀 강조 표시

Excel에는 Word 또는 PowerPoint에서 맞춤법 검사가 없습니다.F7 키를 눌러 맞춤법 검사를 실행할 수 있지만 맞춤법 오류에 대한 시각적 신호는 없습니다.

이 코드를 사용하여 철자가 틀린 모든 셀을 즉시 강조 표시하십시오.

'이 코드는 철자가 틀린 단어가 있는 셀을 강조 표시합니다. Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) then cl.Interior.Color = vbRed End If Next cl 엔드 서브

강조 표시된 셀은 Excel에서 철자가 잘못되었다고 생각하는 텍스트가 있는 셀입니다.대부분의 경우 이해하지 못하는 이름이나 브랜드 용어도 강조 표시됩니다.

통합 문서의 모든 피벗 테이블 새로 고침

통합 문서에 여러 피벗 테이블이 있는 경우 이 코드를 사용하여 모든 피벗 테이블을 한 번에 새로 고칠 수 있습니다.

'이 코드는 통합 문서 하위의 모든 피벗 테이블을 새로 고칩니다. RefreshAllPivotTables() Dim PT As PivotTable As PivotTable In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

여기에서 피벗 테이블 새로 고침에 대해 자세히 알아볼 수 있습니다.

선택한 셀의 대소문자를 대문자로 변경

Excel에는 텍스트 문자의 대소문자를 변경하는 수식이 있지만 다른 셀 집합에서 이를 수행할 수 있습니다.

이 코드를 사용하여 선택한 텍스트에서 텍스트의 대소문자를 즉시 ​​변경합니다.

'이 코드는 선택을 대문자로 변경합니다. ChangeCase() Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

이 경우 UCase를 사용하여 텍스트 대/소문자를 대문자로 설정합니다.소문자로 LCase를 사용할 수 있습니다.

주석이 있는 모든 셀 강조 표시

다음 코드를 사용하여 주석이 있는 모든 셀을 강조 표시합니다.

'이 코드는 주석이 있는 셀을 강조 표시합니다' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

이 경우 vbBlue를 사용하여 셀에 파란색을 지정합니다.필요한 경우 다른 색상으로 변경할 수 있습니다.

VBA로 빈 셀 강조 표시

조건부 서식을 사용하거나 선택 항목으로 이동 대화 상자를 사용하여 빈 셀을 강조 표시할 수 있지만 자주 수행해야 하는 경우 매크로를 사용하는 것이 가장 좋습니다.

생성된 매크로는 빠른 실행 도구 모음에 있거나 개인 매크로 통합 문서에 저장할 수 있습니다.

다음은 VBA 매크로 코드입니다.

'이 코드는 데이터 세트의 모든 빈 셀을 강조 표시합니다. Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

이 코드에서는 빨간색으로 강조 표시된 빈 셀을 지정했습니다.파란색, 노란색, 청록색 등과 같은 다른 색상을 선택할 수 있습니다.

단일 열로 데이터를 정렬하는 방법

다음 코드를 사용하여 지정된 열을 기준으로 데이터를 정렬할 수 있습니다.

하위 SortDataHeader() 
Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, 헤더:=xlYes 
최종 하위

"DataRange"라는 명명된 범위를 만들고 셀 참조 대신 사용했습니다.

여기에 사용되는 세 가지 주요 매개변수도 있습니다.

• Key1 – 데이터 세트를 정렬할 기준이 되는 키입니다.위의 샘플 코드에서 데이터는 A열의 값을 기준으로 정렬됩니다.
• Order1 – 여기에서 데이터를 오름차순 또는 내림차순으로 정렬할지 여부를 지정해야 합니다.
• 헤더 – 여기에서 데이터에 헤더가 있는지 여부를 지정해야 합니다.

VBA를 사용하여 Excel에서 데이터를 정렬하는 방법에 대해 자세히 알아보세요.

여러 열로 데이터를 정렬하는 방법

다음과 같은 데이터세트가 있다고 가정합니다.

다음은 여러 열을 기반으로 데이터를 정렬하는 코드입니다.

하위 SortMultipleColumns() 포함 ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlYes .Apply End With End Sub

여기에서 먼저 A열에서 정렬한 다음 B열에서 정렬하도록 지정했습니다.

출력은 다음과 같습니다.

Excel에서 문자열에서 숫자 부분만 가져오는 방법

문자열에서 숫자 부분이나 텍스트 부분만 추출하려는 경우 VBA에서 사용자 지정 함수를 만들 수 있습니다.

그런 다음 워크시트에서 이 VBA 함수를 사용할 수 있으며(일반 Excel 함수처럼) 문자열에서 숫자 또는 텍스트 부분만 추출합니다.

아래 그림과 같이:

다음은 문자열에서 숫자 부분을 추출하는 함수를 만드는 VBA 코드입니다.

'이 VBA 코드는 문자열에서 숫자 부분을 가져오는 함수를 만듭니다. Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) 그런 다음 결과 = 결과 및 Mid(CellRef, i, 1) 다음 i GetNumeric = 결과 종료 함수

모듈에 코드를 넣은 다음 워크시트에서 =GetNumeric 함수를 사용할 수 있습니다.

이 함수는 숫자 부분을 가져올 셀의 셀 참조인 하나의 매개변수만 사용합니다.

마찬가지로 아래 함수는 Excel의 문자열에서 텍스트 부분만 가져옵니다.

'이 VBA 코드는 문자열에서 텍스트 부분을 가져오는 함수를 생성합니다. Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function

따라서 일상 업무에서 작업을 자동화하고 생산성을 높이는 데 사용할 수 있는 유용한 Excel 매크로 코드입니다.