Microsoft has been moving to its new and more polished Excel 365 platform since 2021. This iteration offers various features to enhance productivity and create an ecosystem with its other software. 

One of the most powerful yet often underutilized features is the macro. Macros can automate repetitive tasks, saving time and reducing the potential for errors. This comprehensive guide will walk you through the basics of creating and using macros in Excel 365, helping you or your team leverage its full potential.

What are Macros?

Macros are essentially a series of commands and actions that you record and run as a single command. They are written in Visual Basic for Applications (VBA), a programming language for Excel and other Office applications. By automating repetitive tasks, macros can significantly boost your productivity.

Why Use Macros?

1. Time Savings: Automate routine tasks like formatting cells, creating charts, or processing data.
2. Consistency: Ensure tasks are performed the same way every time, reducing errors.
3. Efficiency: Perform complex tasks quickly and easily without manual intervention.

Getting Started with Macros in Excel 365

Enabling the Developer Tab

Before you can create or use macros, you need to enable the Developer tab in Excel 365:

1. Open Excel.
2. Go to File > Options.
3. In the Excel Options dialog box, select Customize Ribbon.
4. Check the Developer option in the Main Tabs section.
5. Click OK.

Recording a Macro

Excel 365 makes it easy to record a macro without needing to write any VBA code:

1. Go to the Developer tab.
2. Click on Record Macro.
3. Name your macro in the Record Macro dialog box and optionally assign a shortcut key.
4. Choose where to store the macro:

• This Workbook: Available only in the current workbook.
• New Workbook: Available only in a new workbook.
• Personal Macro Workbook: Available in all workbooks.

1. Click OK to start recording.
2. Perform the actions you want to automate.
3. Click Stop Recording on the Developer tab when done.

Running a Macro

To run a recorded macro:

1. Go to the Developer tab.
2. Click on Macros.
3. In the Macro dialog box, select the macro you want to run.
4. Click Run.

Editing a Macro

To edit a macro, you need to use the VBA editor:

1. Go to the Developer tab.
2. Click on Visual Basic.
3. In the VBA editor, locate your macro in the Project Explorer.
4. Double-click on the module containing your macro to open it.
5. Edit the VBA code as needed.

Deleting a Macro

To delete a macro:

1. Go to the Developer tab.
2. Click on Macros.
3. Select the macro you want to delete.
4. Click Delete.

Practical Examples of Macros

Example 1: Formatting Data

Imagine you frequently receive a report that needs consistent formatting. You can record a macro to automate this:

1. Start recording a macro and name it "FormatReport".
2. Perform the desired formatting actions (e.g., adjust column widths, apply bold text to headers, add borders).
3. Stop recording.
4. Run the "FormatReport" macro on new reports to apply the same formatting instantly.

Example 2: Creating a Chart

If you regularly create a specific type of chart, you can automate this process:

1. Start recording a macro and name it "CreateSalesChart".
2. Select the data range and insert the desired chart type.
3. Format the chart as needed.
4. Stop recording.
5. Run the "CreateSalesChart" macro to quickly generate the chart.

Example 3: Data Cleaning

Cleaning data can be tedious. Automate this with a macro:

1. Start recording a macro and name it "CleanData".
2. Perform data cleaning tasks (e.g., remove duplicates, trim spaces, convert text to proper case).
3. Stop recording.
4. Run the "CleanData" macro on new datasets to clean them efficiently.

Advanced Macro Techniques

Using VBA for More Complex Tasks

While recording macros is straightforward, you might need more complex automation that requires writing VBA code. Here are some advanced techniques:

1. Loops: Automate repetitive actions across a range of cells or worksheets.

Sub LoopExample()

    Dim i As Integer

    For i = 1 To 10

        Cells(i, 1).Value = "Row " & i

    Next i

End Sub

2. Conditional Statements: Execute actions based on conditions.

Sub ConditionalExample()

    If Range("A1").Value > 100 Then

        MsgBox "Value is greater than 100"

    Else

        MsgBox "Value is 100 or less"

    End If

End Sub

3. User-Defined Functions: Create custom functions for specific calculations.

Function AddNumbers(a As Double, b As Double) As Double

    AddNumbers = a + b

End Function

Performance Tips: Writing Efficient VBA Code to Improve Macro Performance

Optimizing the performance of your macros is crucial, especially when working with large datasets or complex operations. Efficient VBA code ensures that your macros run faster and more reliably, minimizing delays and improving overall productivity. Here are some key tips and techniques to help you write more efficient Excel 365 macros or VBA code.

1. Avoid Selecting and Activating Objects

One of the most common mistakes in VBA programming is using .Select and .Activate methods, which can significantly slow down your macros. Instead, work directly with objects. 

For example, instead of:

Sub InefficientCode()

    Sheets("Sheet1").Select

    Range("A1").Select

    ActiveCell.Value = "Hello"

End Sub

You should try:

Sub EfficientCode()

    Sheets("Sheet1").Range("A1").Value = "Hello"

End Sub

2. Use Variables to Store Worksheet and Range References

Repeatedly referencing worksheets and ranges can be slow. Instead, use variables to store these references:

Sub UseVariables()

    Dim ws As Worksheet

    Dim rng As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Set rng = ws.Range("A1")

    rng.Value = "Hello"

End Sub

3. Minimize Interaction with the Worksheet

Interaction with the worksheet is slow. Minimize this by reading data into arrays, processing it, and then writing it back:

Sub ProcessWithArray()

    Dim ws As Worksheet

    Dim data As Variant

    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    data = ws.Range("A1:A1000").Value

    For i = 1 To UBound(data, 1)

        data(i, 1) = data(i, 1) * 2 ' Example processing

    Next i

    ws.Range("A1:A1000").Value = data

End Sub

4. Disable Screen Updating and Automatic Calculations

Disabling screen updating and automatic calculations while your macro runs can greatly improve performance:

Sub OptimizePerformance()

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    ' Your macro code here

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

End Sub

5. Use With Statements for Object References

Using With statements can streamline your code and improve readability and performance:

Sub UseWithStatement()

    With ThisWorkbook.Sheets("Sheet1")

        .Range("A1").Value = "Hello"

        .Range("A2").Value = "World"

    End With

End Sub

6. Limit the Use of Loops

Loops can be slow, especially when interacting with cells. Where possible, use Excel's built-in functions and array processing instead:

Sub AvoidLoops()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Using a single operation instead of a loop

    ws.Range("A1:A1000").Formula = "=ROW()"

End Sub

7. Optimize Loop Performance

If you must use loops, optimize their performance by minimizing interactions with the worksheet and using efficient loop constructs:

Sub OptimizedLoop()

    Dim ws As Worksheet

    Dim i As Long

    Dim values As Variant

    Set ws = ThisWorkbook.Sheets("Sheet1")

    values = ws.Range("A1:A1000").Value

    For i = 1 To UBound(values, 1)

        values(i, 1) = values(i, 1) * 2

    Next i

    ws.Range("A1:A1000").Value = values

End Sub

8. Use Efficient Data Types

Choosing the right data types for your variables can impact performance. For example, use Long instead of Integer for numeric variables to avoid overflow errors:

Sub UseEfficientDataTypes()

    Dim i As Long

    For i = 1 To 1000000

        ' Perform operations

    Next i

End Sub

9. Avoid Unnecessary Calculations

Reduce unnecessary calculations by using variables to store intermediate results:

Sub AvoidUnnecessaryCalculations()

    Dim ws As Worksheet

    Dim lastRow As Long

    Dim i As Long

    Dim total As Double

    Set ws = ThisWorkbook.Sheets("Sheet1")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    total = 0

    For i = 1 To lastRow

        total = total + ws.Cells(i, "A").Value

    Next i

    ws.Range("B1").Value = total

End Sub

10. Compile VBA Code

Ensure that your VBA code is compiled before running your macros. This can be done by going to the VBA editor and selecting Debug > Compile VBAProject. This helps identify and fix syntax errors, leading to better performance.

Debugging Macros

Debugging is crucial for ensuring your macros run smoothly:

1. Step Through Code: Use F8 in the VBA editor to step through your code line by line.
2. Breakpoints: Set breakpoints by clicking on the left margin in the VBA editor to pause execution and inspect variables.
3. Immediate Window: Use the Immediate Window to run code snippets and inspect variables.

Security Considerations

Macros can pose security risks, as they can execute potentially harmful code. Always ensure macros come from trusted sources. Adjust macro security settings in Excel 365:

1. Go to File > Options > Trust Center > Trust Center Settings.
2. Select Macro Settings and choose an appropriate level of security.

Best Practices for Using Macros

1. Keep It Simple: Start with simple tasks and gradually move to more complex automation.
2. Comment Your Code: Add comments to your VBA code to explain its purpose and logic.
3. Test Thoroughly: Test your macros on sample data to ensure they work correctly before using them on critical data.
4. Backup Your Work: Regularly backup your workbooks to avoid data loss due to macro errors.
5. Stay Updated: Keep your Excel and VBA skills up-to-date by exploring new features and learning resources.

Conclusion

Macros in Excel 365 offer a powerful way to automate repetitive tasks, saving time and improving efficiency. By recording macros for simple tasks or writing VBA code for more complex automation, you can significantly enhance your productivity. Remember to follow best practices and consider security implications when working with macros. With practice and exploration, you'll find that macros can transform the way you work with Excel. Happy automating!

Enhance Your Excel Skills with Intellezy

Automating tasks with macros in Excel 365 is a powerful way to save time and boost productivity, but mastering VBA and creating efficient macros can be challenging. Whether you're a beginner looking to learn the basics or an advanced user aiming to refine your skills, Intellezy offers a comprehensive range of training videos to help you excel in Excel.

Visit our training video library to explore our extensive collection of Excel courses. Whether you need to automate repetitive tasks, enhance data integration, or improve macro performance, our videos provide the knowledge and skills you need to succeed.

Join Intellezy today and take the first step towards becoming an Excel expert. Request a free trial today, and we’ll help you identify what your team really needs.