How to Use Visual Basic in Excel

Tired of manually formatting the same report every single week? You can stop that tedious work by teaching Excel how to do it for you using Visual Basic for Applications, or VBA. This article will show you how to get started with VBA, from enabling the right tools inside Excel to writing your first pieces of code that automate repetitive tasks.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is Visual Basic for Applications (VBA)?

Think of VBA as Excel’s own programming language. It's built right into Excel (and other Microsoft Office applications) and allows you to write instructions that automate just about any action you can perform with your mouse and keyboard. Instead of clicking through a dozen steps to transform raw data into a clean report, you can run a single "macro" - a set of VBA instructions - that does it all in an instant.

It sounds technical, but you don't need to be a professional developer to use it. At a basic level, VBA lets you:

• Apply consistent formatting to reports automatically.
• Copy and paste data from one sheet to another based on specific rules.
• Generate summary tables from large datasets.
• Create custom pop-up messages or input boxes to guide users.
• Clean up messy data exports by deleting empty rows or correcting text formats.

Ultimately, learning a little VBA can save you hours of mundane, repetitive work, freeing you up to focus on the insights in your data, not the chore of preparing it.

First Steps: Enabling the Developer Tab

Before you can write any code, you need to reveal a hidden tab in Excel's ribbon: the Developer tab. This is your command center for all things VBA. The process is slightly different for Windows and Mac.

For Excel on Windows:

1. Right-click anywhere on the Ribbon (the top menu with Home, Insert, etc.) and select Customize the Ribbon...
2. In the dialog box that appears, find the list on the right side labeled "Main Tabs."
3. Check the box next to Developer.
4. Click OK. The Developer tab will now appear in your Excel ribbon, usually after the View tab.

For Excel on Mac:

1. Click on Excel in the top menu bar (next to the Apple icon) and select Preferences... (or Settings... on newer versions).
2. Under the "Authoring" section, click on Ribbon & Toolbar.
3. In the "Main Tabs" list, find and check the box next to Developer.
4. Click Save and close the settings window. The Developer tab is now visible.

Once you see the Developer tab, you’re ready to enter the world of VBA. All the tools you need will live here.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Exploring the Visual Basic Editor (VBE)

The place where you’ll write, edit, and manage your VBA code is called the Visual Basic Editor, or VBE for short. To open it, click the Visual Basic button on the far left of the Developer tab (or use the keyboard shortcut Alt + F11 on Windows or Opt + F11 on Mac).

The VBE can look a little intimidating at first. Here’s a quick overview of the key windows you’ll see:

• Project Explorer: This is on the left side and acts like a file manager for your VBA project. It shows all the open workbooks and the objects within them, such as worksheets and modules (where your code is stored).
• Properties Window: Typically located below the Project Explorer, this window displays settings and properties for any item you've selected, like a worksheet or a form. You won't use it much as a beginner.
• Code Window: This is the large, main area where you will write and view your VBA code. This is where the magic happens.

If you don't see one of these windows, you can enable it from the View menu at the top of the VBE.

The Easy Way In: Recording Your First Macro

The best way to learn VBA is to let Excel write the code for you first. The Macro Recorder watches every action you take - every click, every format change, every text entry - and translates it into VBA code automatically. It’s an invaluable learning tool.

Let's record a macro that formats a header row. Say you always want your headers in Row 1 to have a yellow background, bold text, and be horizontally centered.

1. Click on the Record Macro button in the Developer tab.
2. A dialog box will pop up. Give your macro a descriptive name, like FormatReportHeader. Note: Macro names cannot contain spaces.
3. Click OK. A small "Stop Recording" button will appear, from this moment on, Excel is recording your every move.
4. Now, perform the actions in your worksheet:
5. Once you're done, go back to the Developer tab and click Stop Recording.

You've just created your first macro! To see the code Excel wrote for you, open the VBA Editor (Alt + F11), and in the Project Explorer, you should see a new folder called Modules. Double-click on Module1 to open the Code Window and see the VBA code for your formatting steps.

Writing Your First Lines of VBA Code

After getting comfortable with the recorder, the next step is to write a simple macro from scratch. VBA code is organized into procedures, most commonly a Sub procedure (short for Subroutine), which is just a block of code designed to perform a specific action.

Let's create the classic "Hello, World!" example.

1. Open the VBA Editor.
2. If you don't already have one, insert a new module by going to Insert > Module. A module is a blank canvas for your code.
3. In the blank Code Window, type the following code:

Sub SayHello()
    MsgBox "Automation is awesome!"
End Sub

Let's break that down:

• Sub SayHello(): This declares the beginning of a subroutine named "SayHello."
• MsgBox "Automation is awesome!": This is the command. MsgBox is a built-in function that creates a simple popup message box with the text you provide in quotes.
• End Sub: This signals the end of the subroutine.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Run Your Code

You can run this macro in two ways:

1. From the VBE: Simply click anywhere inside the Sub SayHello() text and press the green "Play" button in the VBE toolbar (or press F5).
2. From Excel: Go to the Developer tab, click the Macros button, select "SayHello" from the list, and click Run.

A message box should pop right up on your screen. You’re officially a programmer!

Working with Cells and Ranges

The real power of VBA comes from manipulating worksheet data. To do this, you need to tell your code which cells to work on. This is usually done with the Range object.

Putting Values into Cells

To place a value in a cell, you specify the range and set its .Value property.

Sub AddData()
    Range("A1").Value = "Product Name"
    Range("B1").Value = "Sales"
    Range("A2").Value = "Gadget Pro"
    Range("B2").Value = 1500
End Sub

Run this, and the code will instantly populate cells A1 through B2 with the specified data.

Changing Cell Formatting

You can also access formatting properties like color and font style.

Sub StyleCells()
    ' Make cell A1 font bold
    Range("A1").Font.Bold = True

    ' Change the background color of cell B1 to yellow
    Range("B1").Interior.Color = vbYellow
End Sub

vbYellow is a built-in VBA constant for the color yellow. You can find others like vbRed, vbGreen, and vbBlue.

Example: Using Logic with an If-Then Statement

Let’s create a slightly more advanced macro that makes a decision. This code will look at a value in cell A1, and if it's greater than 100, it will highlight the cell in red.

Sub CheckValue()

    If Range("A1").Value > 100 Then
        Range("A1").Interior.Color = vbRed
    End If

End Sub

This If...End If block lets your macro perform actions conditionally, making it much more powerful and dynamic.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Crucial Rule: Save Your Work Correctly!

This is one of the most common mistakes beginners make. If you save a workbook containing VBA macros as a standard Excel Workbook (.xlsx), all of your code will be deleted without warning!

You must save your file as an Excel Macro-Enabled Workbook, which has the .xlsm file extension. To do this, go to File > Save As, and in the "Save as type" dropdown menu, choose "Excel Macro-Enabled Workbook (*.xlsm)."

Always double-check that you're saving in the .xlsm format if your workbook has macros you want to keep.

Final Thoughts

Stepping into VBA for the first time can feel like learning a new language, but the rewards are well worth it. By starting with the Macro Recorder and experimenting with simple subroutines to manipulate cells, you can quickly build the skills to automate your most time-consuming Excel workflows and get hours of your week back.

This process of automating data prep has been a manual headache for a long time. For marketing and sales data, we know the all-too-common routine is downloading CSVs from platforms like Google Analytics, Facebook Ads, or Salesforce, then manually cleaning and visualizing everything in Excel. We created Graphed to cut out that middle step entirely. Instead of scripting or manually building reports, you can connect your data sources directly and just ask for the chart or dashboard you need in plain English, getting a real-time view of your performance in seconds, not hours.