Mastering VBA in Excel 365 – How to Create Macros Easily

Understanding how Excel VBA 365 automates tasks through macro creation and editing is essential for increasing productivity, especially for users who frequently handle large data or repetitive tasks. Macro record activities on reexecutable worksheets, saving time and effort.

With Visual Basic for Applications (VBA), Excel becomes a platform for creating automated solutions to complex problems. Macro can automate data processing, generate instant reports, and integrate Excel with other applications.

This automation is especially beneficial in jobs that require high efficiency, such as data analysis and accounting. In addition to reducing human error, macro allows users to focus on more strategic aspects of work. Therefore, learning how to create and edit macro in Excel VBA 365 is valuable for professionals in various fields.

How to Create Macros in Excel VBA 365

1. Activating the Developer Environment

The first step to creating a macro is to make sure the Developer tab is already active in Excel. This tab provides a variety of tools for creating and managing macro.

  • To turn on the Developer tab, click File at the top, then select Options.
  • In the Excel Options window, select Customize Ribbon on the left side.
  • Check the Developer option in the right pane, then click OK. The Developer tab will now appear in the Excel ribbon.

Once enabled, you can start creating macro in a variety of ways, including recording macro and accessing Visual Basic Editor (VBE).

2. Record Macros

One of the easiest ways to create a macro is to use the recording feature. This feature allows you to record a series of actions performed in Excel.

  • To get started, click on the Record Macro button in the Developer tab.
  • A dialog window will appear, asking you to give the macro to be created. For example, you could name it ‘Test_Macro’. Make sure the name follows macro naming rules.
  • Select a macro storage location: in the current workbook or Personal Macro Workbook (so that it can be accessed across all workbooks).

3. Macro Naming

When giving a name to macro, there are a few important rules to keep in mind:

  • The name macro must begin with an underscore (‘_’) letter or sign.
  • Names must not contain spaces between characters.
  • The macro name must not be the same as other names that already exist in the same workbook.

4. Changing Macros

Once you’ve created your macro, you can change it by going to Visual Basic Editor (VBE). Here, you can view macro in code and make changes as needed.

  • Access Visual Basic Editor by clicking the Visual Basic button in the Developer tab.
  • In the VBE window, select the macro you just created. The code macro is written as a VB (Visual Basic) procedure that starts with the keyword ‘Sub’ and ends with ‘End Sub’.
  • You can add or change the VBA code to improve macro functionality.
Excel VBA 365

Other Interesting Articles

Creating Macros from Scratch

Here’s an example of creating a macro from scratch that records simple steps in a worksheet, edits them by adding cell formatting, and displays the results of salary calculations using Message Box.

1. Recording Macros

At this stage, we will record the steps to enter the numbers and calculate the salary automatically:

  • Enter the wage number in cell A1.
  • Enter the number of hours in cell A2.
  • Add the results in cell A3 to calculate the salary.

2. Edit Macros

After recording those basic steps, you can open Visual Basic Editor (VBE) and add some code to:

  • Format a range of cells with font and interior properties (for example, make the text bold and give it a background color).
  • Added a Message Box to display the payroll calculation results.

Simple Macro Example: Calculating Salary and Formatting Cells.

Sub Calculate_Salary()
    Dim wage As Single
    Dim hours As Single
    Dim salary As Single

    wage = Range(“A1”).Value
    hours = Range(“A2”).Value

    salary = wage * hours

    MsgBox “Your salary is “ & salary, vbInformation, “Salary Calculation”

    Range(“A3”).Value = salary

    With Range(“A1:A3”)
        .Font.Bold = True
        .Interior.Color = RGB(144, 238, 144) ' Latar belakang hijau muda
    End With
End Sub

Code Explanation:

  • Dim wage As Single: This is a wage variable declaration (wage) with data type Single.
  • Dim hours As Single: This is a variable declaration hours (working hours) with a Single data type.
  • salary =  wage * hours: Here, the total salary is calculated by multiplying the wages and hours worked.
  • MsgBox: The salary calculation results will be displayed through the message box (Message Box).
  • Range(“A3”).Value = salary: The results of the salary calculation are stored in cell A3.
  • With Range(“A1:A3”): Cells A1 through A3 will be formatted with bold text and a light green background (RGB(1444, 238, 144)).

Expected Results:

  • You will enter the value of wages in A1 and hours worked in A2.
  • When the macro is run, the salary will be automatically calculated and displayed in the A3 cell.
  • The salary results will also appear in the Message Box.
  • Cells A1 through A3 will be formatted with bold text and a light green background color.

Latest Articles

Related Articles