VBA + Excel

VBA 코드 예제

Excel Automation Tool

AutoMacro: 바로 사용할 수 있는 수백 개의 VBA 코드 예제 등이 포함된 VBA 애드인!

아래 목록에서 설명이 포함된 무료 Excel VBA 코드 예제를 확인해 보세요.
일부 예제에는 다운로드 가능한 파일도 포함되어 있습니다. Excel VBA 매크로 및 스크립트는 전문적으로 개발되어 바로 사용할 수 있습니다.

이 목록이 도움이 되기를 바랍니다!

VBA Code Examples
In Depth Tutorialsyes
PowerPoint VBA Macro Examples & Tutorial
Find and Find and Replace
Cheat Sheets – Commands & Syntax Lists
Word Macro Examples & VBA Tutorial
If, ElseIf, Else (Ultimate Guide to If Statements)
Value Paste & PasteSpecial
VBA – Cut, Copy, Paste from a Macro
VBA Save File – 20 Easy Examples
VBA Coding Conceptsyes
Commenting in VBA
Creating (Custom) User Defined Functions (UDFs)
Excel VBA – Named Ranges and Programming
How to Rename or Delete a Module or Form
Opening & Using the Visual Basic Editor (VBE) in Excel
Return a Value from a Sub Procedure
Shortcuts for the Visual Basic Editor
Call Function from a Sub
Function – Call, Return Value, & Parameters
Object Model
On Error – Error Handling Best Practices
Private vs Public Procedures (Subs & Functions)
Select Case Statement
Sub Procedure
What is a VBA Module?
What is a VBA Project? (And How to Edit)
VBA – Language References
VBA – Modify the Right-Click Menu to Call a Macro
VBA – Run a Macro from a Macro
VBA – Run a Macro Line by Line
VBA – Wait, Delay, Create a Timer While Running Code
Debug.Print and Immediate Window
VBA DoEvents
VBA Exit Sub or Function
VBA On Error Exit Sub
VBA Option Explicit
VBA: Improve Speed & Other Best Practices
VBA For Loop – Loop Through a Range of Cells
Loops - Ultimate Guide
For Each Examples (Quick Reference)
VBA Exit For
VBA Exit Loop
While Wend Loop
Ranges and Cellsyes
Ranges and Cells - Ultimate Guide
Formulas - Ultimate Guide
Union and Intersect
Resize Range
VBA – Autofill with a Macro
VBA – Change a Cell;s Background Color
VBA – ClearFormats – Remove Formatting For Each Cell In Range
VBA – Conditional Formatting – Highlight Duplicates in a Range
VBA – Delete All Named Ranges
VBA – Delete Hyperlinks
VBA – Format Painter
VBA – Get the Active Cell’s Column or Row
VBA – Resize an Object to a Range Size
VBA – Select All Cells
VBA – Sum by Color
VBA – Test if Selection is a Range or Object
Cell Value – Get, Set, or Change
ClearContents / Clear Cells
Copy Destination (Copy Range to Another Sheet)
Current Region
Dynamic Range
Highlight Cell
VBA Merge Cells & Unmerge Cells
Offset Range or Cell
Range / Cell Address
Range.End (xlDown, xlUp, xlToRight, xlToLeft)
VBA Routine to return Column Letter of Cell
VBA Select Range or Cells
VBA Union – Join Multiple Ranges
VBA Wrap Text
Activecell Offset VBA
Rows & Columnsyes
Select (and work with) Entire Rows & Columns
Delete or Insert Rows Based on Cell Value
Delete Rows that Meet Certain Criteria in VBA
Hide / Unhide Columns & Rows
Unhide All Rows / Columns
VBA – Autofit Columns
VBA – Count the rows in a Selection
VBA – Delete Blank Rows
VBA – Select Every 5th Row
Copy / Paste Rows & Columns
Delete Entire Row or Column
Group Rows & Columns
Insert Row or Column
VBA Set Column Width or Row Height
VBA Used Range – Count Number of Used Rows or Columns
Sheets – The Ultimate Guide
VBA – Hide (or Unhide) a Worksheet
VBA – How to Use Worksheet Functions (and a Full List)
VBA – Loop Through all Worksheets with For Each
VBA – Macro to List all Sheets in a Workbook
Send Worksheets by Email as Separate Workbooks
VBA – Unhide All Worksheets
Clear Entire Sheet
VBA Copy Worksheet
VBA Delete or Clear Worksheet
Get Sheet Name / Rename Sheet
VBA Protect / Unprotect Worksheets
VBA Routine to Add and Name Worksheets
VBA Select Sheet, Activate Sheet, and Get Activesheet
Worksheet Range
VBA: Set the Default Sheet When a WorkBook Opens
How to Sort Tabs / Worksheets with a VBA Macro
Create New Workbook (Workbooks.Add)
Activate Workbook
ActiveWorkbook vs. ThisWorkbook
VBA Open / Close Workbook
Workbook Name (Get, Set, without Extension)
Workbook Protection (Password Protect / Unprotect)
Working with Workbooks (The Workbook Object)
VBA – Array Examples
Dictionary Objects
Array of Objects
Variant Array
Dynamic Array (Redim & Redim Preserve)
Declare & Initilize String Array
Declare (Dim), Create, and Initialize Array Variable
Array Length / Size
Assign Range to Array
Clear Array – Erase Function
Loop Through Array / For Each Item in Array
Sort Array
Transpose Array
Search for (Find) Value in Array
Populate Array with Unique Values from Column
Remove Duplicates From Array
Function Return Array
Multi-Dimensional Array (2D Arrays)
Output (Print) Array to Range
Try Catch Errors
Object Required Error
Syntax Error VBA
Type Mismatch (Run-time Error 13)
Automation Error
Compile Error
Error 1004 – Application-Defined or Object-Defined Error
Ignore Error
On Error Resume Next or Goto 0
Out of Memory Error
Throw / Raise Error – Err.Raise – Custom Error Msg
Can’t Find Project or Library – VBA Compile Error
Compile Error in Hidden Module – VBA
Fix Error 400
Fix VBA Error 438 – Object Doesn’t Support Property or Method
Fix VBA Run Time Error 32809
Fix VBA Run Time Error 57121
VBA Array Variables
Boolean Data Type (Dim Variable)
VBA Constant Expression Required
VBA Data Types – Variables and Constants
VBA Date Variable
Double Data Type (Dim Variable)
VBA Global Variable
Int / Integer Data Type (Dim Variable)
Long Data Type (Dim Variable)
VBA Public Variable – Use a Variable in Multiple Modules or Subs
VBA Range Object Variables (Dim / Set Range)
Set Object Variables – Workbooks, Worksheets, & More
Single Data Type (Dim Variable)
VBA Static Variable
String Data Type (Dim Variable)
Variant Data Type (Dim Variable)
Get and Change User Name with VBA
VBA – Display Status Bar Message
VBA – Modify the titlebar
VBA – Prevent warning messages from a macro
VBA – Turn Automatic Calculations Off (or On)
VBA – Turn Formula Bar Off (or On)
VBA – Turn off Screen Updating
VBA Turn Scroll Bars On or Off
Text and Stringsyes
Prevent VBA Case Sensitive – Option Compare Text
VBA – Extract Number From String
VBA – Find the nth Word in a String of Text
VBA – Line Break or Line Feed in a Message Box
VBA – Loop Through a String
VBA – Remove Characters from Left or Right Side of Variable Length String
VBA Char / Chr Function
VBA Compare Strings – StrComp
VBA Comparison Operators – Not Equal to & More
VBA Concatenate Text Strings Together (& – Ampersand)
Convert Integer (Number) to String
Convert String to Date
Convert Text String to Number
VBA INSTR – Find Text in a String
VBA Like Operator
VBA Logical Operators – OR, AND, XOR, NOT, IS, & LIKE
VBA New Line / Carriage Return
Parse String
VBA Space Function – Add Spaces to a String
VBA Strings and Substrings Functions
Text to Columns
Trim, LTrim, and RTrim Functions – Remove Spaces From Text
VBA Upper, Lower, and Proper Case – Case Functions
VBA – ActiveWindow.WindowState
VBA – Display (or Exit) Fullscreen
VBA – Freeze Panes
VBA – Hide Excel (The Entire Application)
VBA – Scroll Vertically and Scroll Horizontally
VBA – Zoom in and Out of Worksheets
FileSystem Object
Move Files with VBA FileSystemObject (MoveFile)
Create Text File with CreateTextFile
VBA – Delete files from the hard drive
Read Text File into String
Read Text File Line by Line
VBA – Test if Workbook is Open by Workbook Name
Validate Filenames
Write to Text File
VBA Check if File or Folder Exists
Combine Multiple Excel Files into One Workbook
Copy a Folder with CopyFolder (FSO)
VBA Copy File / Workbook
VBA Delete File / Workbook
Delete Folder with DeleteFolder (FSO)
FileCopy Statement
Get File Name with GetFileName (FSO)
GetFolder & GetFile (Get File & Folder Properties)
VBA List Files in Folder
VBA Open File Dialog Box
Open Text File with OpenTextFile
Read Text File (Read, Parse, and Import)
VBA Rename File
Shapes, Charts, Objectsyes
VBA AutoFilter
VBA Guide For Charts and Graphs
VBA Guide to Pivot Tables
VBA Objects
Excel Add VBA Button
How to use VBA Controls in UserForms
VBA – Delete All Autoshapes
VBA – Programmatically Draw Boxes with Code
Listbox – Selected Item
VBA Tables and ListObjects
Conditional Formatting
Cell Borders
Cell Font – Change Color, Size, Style, & More
Center Text – Cell Alignment (Horizontal & Vertical)
Display Page Breaks Setting
Format Cells
Format Date
Format Time
VBA – Exit Without Save Prompt
VBA – Force a Workbook to Save Before Close
VBA – Run a Macro when Excel Closes – Auto_Close
VBA – Run a macro when Excel starts
VBA Worksheet Change Event – Run a Macro When a Cell Changes
VBA: Show or load a form when Excel starts
UserForms, MsgBoxes & Controlsyes
VBA – Yes No Message Box (Msgbox)
VBA InputBox – Get Input from a User
Creating VBA Userforms
Option Button Excel VBA
Spin button Excel VBA
VBA Checkbox
VBA ComboBox
VBA Listbox
VBA Open or Close UserForm
Conditional Formatting
Cell Borders
Cell Font – Change Color, Size, Style, & More
Center Text – Cell Alignment (Horizontal & Vertical)
Display Page Breaks Setting
Format Cells
Format Date
Format Time
Clear Clipboard
Pause (Break / Stop) & Resume a Macro
Save As (Print) to PDF
ColorIndex Codes List
Format Numbers
How to Install (or Uninstall) a VBA add-in (.xlam file) for Microsoft Excel
How to use VBA Structures
Removing Duplicate Values in Excel VBA
VBA – Add or Remove Cell Comments
All Operators Explained
Check if Office App is Running
Dialog Box
Get Today’s Date (Current Date)
Pivot Table Filter
Refresh Pivot Table / All Pivot Tables
Refresh Pivot Table when Data Changes
Using Goal Seek in VBA
Using the Analysis ToolPak in VBA
Advanced Filter
Application.CutCopyMode = False
Calculate – Now, Workbook, Worksheet, or Range
Call / Run Sub From another Subroutine
Copy to Clipboard
Find Value in Column
GoTo a Line Label
IIF Function
Input Statement
is Nothing
Is Operator
Line Input Statement
Multiple (Nested) If Statements
Project Password / Protect code
Wait & Sleep Functions – Pause / Delay Code
With / End With Statement
What is the Difference Between VB and VBA?
Personal Macro Workbook
VBA – Turn Off AutoFilter / Clear Filters
VBA Drop Down List (Data Validation)
VBA Hyperlinks
VBA Random Number
Round, RoundUp, and RoundDown
Split Function – Split String of Text into Array
Sqr Function
Sum Function (Ranges, Columns, & More)
SUMIF and SUMIFS Functions
Switch Statement
Text Function
Time Functions
Timer Function
UBound & LBound Functions
Val Function
Write Statement
Vlookup – Multiple Results with VBA
Advanced Programmingyes
Advanced VBA Programming Examples
Early Late Binding
Class Constructor
Class Module Tutorial & Examples
Matrix – Create & More
VBA – Convert Matrix to Vector
VBA Regex
VBA SendKeys
VBA Timer Function
Automate Internet Explorer (IE) Using VBA
VBA – Consume Soap Web Services
VBA – WinHttpRequest with Login and Password – Misc Bloglines
Send Emails from Excel through Gmail
Send Emails from Excel through Outlook
Access VBA Recordsets – Open, Count, Loop and More
Access VBA – Import / Export Excel – Query, Report, Table, and Forms
Access VBA – Open / Close Form
Access VBA – Open Excel File
Access VBA Database – Open, Connect, Login, & More
Access VBA Findfirst
Access VBA Me
Access VBA Query
Access VBA Recordsets – Open, Count, Loop and More
Access VBA Refresh Form
Access VBA Reports – Print, Export, Filter
Access VBA SQL Examples
Access VBA Tables – Update, Count, Delete, Create, Rename, Export
Word VBA – Macro to Open Word Document
Word VBA Macros – Find, Find & Replace
Word VBA Macros – SaveAs (PDF or New File Name)

AI Formula Generator

무료로 체험하기

Excel 매크로 예제

아래에서 일반적인 Excel 자동화 작업에 대한 기본 매크로 예제들을 확인할 수 있습니다.

다른 시트로 행 복사 및 붙여넣기

아래의 간단한 매크로는 다른 시트로 행을 복사합니다.

Sub Paste_OneRow()
'행을 복사해서 붙여넣습니다
Sheets("sheet1").Range("1:1").Copy Sheets("sheet2").Range("1:1")
Application.CutCopyMode = False
End Sub

이메일 보내기

이 유용한 매크로는 Outlook을 시작하고, 이메일을 초안 작성하고, 현재 통합문서를 파일로 첨부합니다.

Sub Send_Mail()
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .to = "test@test.com"
        .Subject = "Test Email"
        .Body = "Message Body"
        .Attachments.Add ActiveWorkbook.FullName
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

통합문서의 모든 시트를 목록으로 만들기

이 매크로는 통합 문서의 모든 시트를 목록으로 만듭니다.

Sub ListSheets()
    Dim ws As Worksheet
    Dim x As Integer
    x = 1
    For Each ws In Worksheets
        ActiveSheet.Cells(x, 1) = ws.Name
        x = x + 1
    Next ws
End Sub

모든 시트 숨기기 취소하기

이 매크로는 모든 시트의 숨기기를 취소합니다.

' 모든 시트의 숨기기 취소하기
Sub UnhideAllWoksheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End Sub

현재 활성화 시트를 제외한 모든 시트 숨기기

이 매크로는 활성 워크시트를 제외한 모든 시트를 숨깁니다.

' 활성 시트를 제외한 모든 시트 숨기기
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

모든 시트 보호 해제하기

이 매크로 예제는 통합 문서의 모든 시트를 보호 해제합니다.

' 모든 시트 보호 해제하기
Sub UnProtectAllSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Unprotect "password"
    Next ws
End Sub

모든 시트 보호하기

이 매크로는 통합 문서의 모든 시트를 보호합니다.

' 모든 시트 보호하기
Sub ProtectAllSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.protect "password"
    Next ws
End Sub

모든 도형 삭제하기

이 매크로는 시트의 모든 도형을 삭제합니다.

Sub DeleteAllShapes()
Dim GetShape As Shape
For Each GetShape In ActiveSheet.Shapes
End Sub

시트의 모든 비어있는 행 삭제하기

이 예제의 매크로는 시트의 모든 빈 행을 삭제합니다.

Sub DeleteBlankRows()
Dim x As Long
With ActiveSheet
    For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
        If WorksheetFunction.CountA(.Rows(x)) = 0 Then
        End If
End With
End Sub

선택 항목에서 중복된 셀을 색으로 채워서 강조 표시하기

아래의 간단한 매크로를 사용하여 선택 영역에서 중복된 값을 모두 강조 표시할 수 있습니다.

' 선택 영역에서 중복된 값을 강조 표시하기
Sub HighlightDuplicateValues()
    Dim myRange As Range
    Dim cell As Range
    Set myRange = Selection
    For Each cell In myRange
        If WorksheetFunction.CountIf(myRange, cell.Value) > 1 Then
            cell.Interior.ColorIndex = 36
        End If
    Next cell
End Sub

음수 값을 갖고 있는 셀을 색으로 채워서 강조 표시하기

이 매크로는 음수를 강조 표시하는 작업을 자동화합니다.

' 음수를 강조 표시하기
Sub HighlightNegativeNumbers()
    Dim myRange As Range
    Dim cell As Range
    Set myRange = Selection
    For Each cell In myRange
        If cell.Value < 0 Then
            cell.Interior.ColorIndex = 36
        End If
    Next cell
End Sub

맞춤법 틀린 셀을 색으로 채워서 강조 표시하기

이 매크로는 맞춤법이 틀린 셀을 강조표시합니다.

' 맞춤법 틀린 셀을 강조 표시하기
Sub highlightAlternateRows()
    Dim cell As Range
    Dim myRange As Range
    myRange = Selection
    For Each cell In myRange.Rows
        If cell.Row Mod 2 = 1 Then
            cell.Interior.ColorIndex = 36
        End If
    Next cell
End Sub

선택영역에서 비어있는 셀을 색으로 채워서 강조 표시하기

이 기본 매크로는 선택 영역의 빈 셀을 강조 표시합니다.

' 선택영역의 모든 비어있는 셀을 강조 표시하기
Sub HighlightBlankCells()
    Dim rng As Range
    Set rng = Selection
    rng.SpecialCells(xlCellTypeBlanks).Interior.Color = vbCyan
End Sub

Excel VBA 매크로 예제 – 무료 다운로드

무료 VBA(매크로) 코드 예제 애드인을 만들었습니다. 이 애드인에는 위의 매크로 예제를 포함하여 바로 사용할 수 있는 100개 이상의 매크로 예제가 포함되어 있습니다!

excel vba examples free download
Download Page

Excel 매크로 / VBA 자주 묻는 질문

Excel에서 VBA 코드(매크로)를 어떻게 작성하나요?

Excel에서 VBA 코드를 작성하려면 VBA 편집기(Alt + F11)를 엽니다. “Sub HelloWorld”를 입력하고 Enter 키를 누르면 매크로가 만들어집니다! 또는 이 페이지에 나열된 프로시저 중 하나를 복사하여 코드 창에 붙여넣습니다.

Excel VBA는 무엇인가요?

VBA는 Excel을 자동화하기 위한 프로그래밍 언어입니다.

VBA를 사용하여 Excel을 자동화하는 방법은 무엇입니까?

VBA로 매크로를 만들어 Excel을 자동화할 수 있습니다. 매크로는 특정 작업을 완료하는 코드 블록입니다.

VBA 배워보기

대화형 VBA 튜토리얼로 VBA를 배울 수 있습니다.

practice vba macros