5 Essential Vba Macros In Excel 2013

Excel 2013 is a powerful spreadsheet software that can be further enhanced with the use of Visual Basic for Applications (VBA) macros. Macros are sets of instructions that can be recorded or written in VBA to automate repetitive tasks, making it easier to work with data in Excel. In this article, we will explore five essential VBA macros in Excel 2013 that can help you streamline your workflow and increase productivity.

Why Use VBA Macros in Excel 2013?

Before we dive into the essential VBA macros, let's discuss why you should use them in Excel 2013. VBA macros can help you:

• Automate repetitive tasks, saving you time and effort
• Perform complex calculations and data analysis
• Create custom tools and interfaces to simplify your workflow
• Enhance the functionality of Excel with custom features
• Improve data accuracy and reduce errors

Macro 1: Auto-Backup Workbook

The first essential VBA macro is an auto-backup workbook macro. This macro automatically saves a backup of your workbook at regular intervals, ensuring that your data is safe in case of a crash or accidental deletion.

How to Create an Auto-Backup Workbook Macro

To create this macro, follow these steps:

1. Open the Visual Basic Editor by pressing Alt + F11 or navigating to Developer > Visual Basic.
2. In the Editor, click Insert > Module to insert a new module.
3. Paste the following code into the module:

Sub AutoBackupWorkbook()
    Dim filePath As String
    Dim fileName As String
    Dim backupPath As String
    
    ' Set file path and name
    filePath = ThisWorkbook.Path
    fileName = ThisWorkbook.Name
    backupPath = filePath & "\Backup\" & fileName & "_Backup_" & Format(Now, "yyyy-mm-dd_hh-mm-ss") & ".xlsx"
    
    ' Save backup
    ThisWorkbook.SaveCopyAs backupPath
End Sub

4. Save the module by clicking File > Save.
5. To run the macro automatically at regular intervals, click Developer > Macro > Create > Scheduled Task.

Macro 2: Hide and Unhide Worksheets

The second essential VBA macro is a hide and unhide worksheets macro. This macro allows you to quickly hide and unhide worksheets in your workbook, making it easier to manage complex workbooks.

How to Create a Hide and Unhide Worksheets Macro

To create this macro, follow these steps:

1. Open the Visual Basic Editor by pressing Alt + F11 or navigating to Developer > Visual Basic.
2. In the Editor, click Insert > Module to insert a new module.
3. Paste the following code into the module:

Sub HideWorksheets()
    Dim ws As Worksheet
    
    ' Hide all worksheets except active sheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            ws.Visible = xlHidden
        End If
    Next ws
End Sub

Sub UnhideWorksheets()
    Dim ws As Worksheet
    
    ' Unhide all worksheets
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlVisible
    Next ws
End Sub

4. Save the module by clicking File > Save.
5. To run the macro, click Developer > Macro > Run > HideWorksheets or UnhideWorksheets.

Macro 3: Automatic Chart Creation

The third essential VBA macro is an automatic chart creation macro. This macro creates a chart based on a specified range of data, making it easier to visualize your data.

How to Create an Automatic Chart Creation Macro

To create this macro, follow these steps:

1. Open the Visual Basic Editor by pressing Alt + F11 or navigating to Developer > Visual Basic.
2. In the Editor, click Insert > Module to insert a new module.
3. Paste the following code into the module:

Sub CreateChart()
    Dim chart As Chart
    Dim dataRange As Range
    
    ' Set data range
    Set dataRange = Range("A1:B10")
    
    ' Create chart
    Set chart = Charts.Add
    chart.ChartType = xlColumnClustered
    chart.SetSourceData Source:=dataRange
End Sub

4. Save the module by clicking File > Save.
5. To run the macro, click Developer > Macro > Run > CreateChart.

Macro 4: Send Email with Attachment

The fourth essential VBA macro is a send email with attachment macro. This macro sends an email with an attachment, making it easier to share files with others.

How to Create a Send Email with Attachment Macro

To create this macro, follow these steps:

1. Open the Visual Basic Editor by pressing Alt + F11 or navigating to Developer > Visual Basic.
2. In the Editor, click Insert > Module to insert a new module.
3. Paste the following code into the module:

Sub SendEmailWithAttachment()
    Dim olApp As Object
    Dim olMail As Object
    
    ' Create email
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)
    
    ' Set email properties
    With olMail
       .To = "recipient@example.com"
       .Subject = "Test Email"
       .Body = "This is a test email."
       .Attachments.Add ThisWorkbook.FullName
       .Send
    End With
    
    ' Release objects
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

4. Save the module by clicking File > Save.
5. To run the macro, click Developer > Macro > Run > SendEmailWithAttachment.

Macro 5: Remove Duplicates

The fifth essential VBA macro is a remove duplicates macro. This macro removes duplicate rows from a specified range of data, making it easier to clean your data.

How to Create a Remove Duplicates Macro

To create this macro, follow these steps:

1. Open the Visual Basic Editor by pressing Alt + F11 or navigating to Developer > Visual Basic.
2. In the Editor, click Insert > Module to insert a new module.
3. Paste the following code into the module:

Sub RemoveDuplicates()
    Dim dataRange As Range
    
    ' Set data range
    Set dataRange = Range("A1:B10")
    
    ' Remove duplicates
    dataRange.RemoveDuplicates Columns:=Array(1, 2)
End Sub

4. Save the module by clicking File > Save.
5. To run the macro, click Developer > Macro > Run > RemoveDuplicates.

Gallery of VBA Macros

VBA Macro Examples

We hope this article has helped you understand the importance of VBA macros in Excel 2013 and how to create essential macros to streamline your workflow. Do you have any questions or feedback about this article? Please leave a comment below.