Create a simple VBA Macro in Excel

Back to basics with a step-by-step guide

Recently I showed a colleague how does a VBA macro in Excel work, and did a quick demo to him live.

To not waste the effort put in, I have documented here, in case you are curious and have never coded before. Having a VBA Macro in Excel to automate some tasks and processes is a great way to kick start your journey.

A step-by-step guide to create a simple VBA macro in Excel

1. Enable Developer Tab: First, you need to make sure the Developer tab is enabled in Excel. Go to File > Options > Customize Ribbon, then check the box for Developer in the right-hand column.

2. Open Visual Basic for Applications (VBA) Editor: Click on the Developer tab, then click on Visual Basic. Alternatively, you can press Alt + F11.

3. Insert a Module: In the VBA editor, click on Insert in the menu bar, then select Module. This will insert a new module where you can write your VBA code.

4. Write your VBA code: In the module window, you can start writing your VBA code. Here's an example of a simple VBA macro that adds the numbers in two cells and displays the result in a message box:

Sub AddNumbers()
    Dim num1 As Double
    Dim num2 As Double
    Dim result As Double
    
    'Assign values from cells to variables
    num1 = Range("A1").Value
    num2 = Range("B1").Value
    
    'Calculate the sum
    result = num1 + num2
    
    'Display the result in a message box
    MsgBox "The sum is: " & result
End Sub

5. Run the Macro: Close the VBA editor and return to your Excel workbook. To run the macro, go back to the Developer tab, click on Macros, select the macro you just created (in this case, "AddNumbers"), and click Run.

   

   

6. Assign a Shortcut (Optional): If you want to assign a keyboard shortcut to your macro, you can do so by going to the Developer tab, clicking on Macros, selecting your macro, and clicking on Options. Here you can specify a shortcut key combination.

That's it! A simple VBA macro in Excel was created. You can expand upon this by exploring more VBA functions and commands to perform various tasks and automate your workflows in Excel.

Happy exploring!