How to Write a Simple VBA Script in Excel

A photo of a man programming on a laptop

VBA (Visual Basic for Applications) is the programming language built into Microsoft Excel. It lets you automate tasks, control workbooks, and build tools that go beyond what formulas can do. If you’ve ever wished Excel could “just do it for me”, VBA is often the answer.

The good news is: you don’t need to be a developer to start. A simple VBA script can be just a few lines long. And even basic scripts can save a lot of time.

In this guide you will learn how to:

Turn on the Developer tab
Open the VBA Editor (VBE)
Create a module and write a simple macro
Run the script safely
Use variables and basic logic (If statements)
Loop through rows (For Each / For loops)
Work with ranges, cells, and the “last row”
Add error handling so your script doesn’t crash
Store your script in the right place so it’s easy to use

We’ll keep the language simple, but we’ll build real examples you can copy and adapt.

What is a “VBA script” in Excel?

In Excel, a “VBA script” is usually a macro: a set of instructions inside a Sub procedure, like this:

Sub MyMacro() 'Your code here End Sub

When you run it, Excel follows the instructions. The macro can:

write values into cells
format ranges
create worksheets
filter and sort data
build reports
import/export files (more advanced)

Before you start: macro safety and file types

To store VBA code, save your workbook as:

Excel Macro-Enabled Workbook (.xlsm)

If you save as .xlsx, Excel removes the code.

Also:

Only enable macros in files you trust
For your own workbooks and your own code, you’re fine

Step 1: Enable the Developer tab

Go to File > Options
Choose Customize Ribbon
Tick Developer
Click OK

Step 2: Open the VBA Editor

You can open the VBA Editor in two ways:

Developer > Visual Basic
or
Press Alt + F11

The VBA Editor (VBE) opens in a separate window.

Step 3: Create a module (where your code lives)

In the VBA Editor:

In the left panel (Project Explorer), find your workbook name.
Right-click your workbook.
Click Insert > Module

A new module appears (like Module1). This is a good place for simple macros.

Step 4: Write your first simple VBA script

Let’s start with something very simple: a message box.

In the module window, type:

Sub HelloWorld() MsgBox "Hello! This is my first VBA script." End Sub

Run it

Click anywhere inside the script.
Press F5 (or click Run).

You should see a message box pop up.

That’s your first VBA macro.

Step 5: A useful first script: format a report table

Now let’s write a macro that actually helps in Excel. This one:

Finds the “current region” of data around the active cell
Makes the top row bold
Adds filters
Autofits columns
Adds borders

Sub FormatReportTable() Dim rng As Range 'Work with the block of data around the active cell Set rng = ActiveCell.CurrentRegion 'Make header row bold rng.Rows(1).Font.Bold = True 'Add filters rng.AutoFilter 'Add borders rng.Borders.LineStyle = xlContinuous 'Autofit columns rng.Columns.AutoFit End Sub

How to use it

Click inside your dataset (any cell in your table).
Run the macro (F5 in the VBA Editor, or via Developer > Macros in Excel).

This is a nice example because it works on many different tables without hard-coded ranges.

Step 6: Understanding the key VBA objects (Excel “building blocks”)

In VBA, you mostly work with objects like:

Application (the whole Excel application)
Workbook (an Excel file)
Worksheet (a sheet)
Range (cells)

Example:

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

That means: on Sheet1, put “Hello” into cell A1.

Step 7: Referencing cells and ranges (the basics)

One cell

Range("A1").Value = 100

A range of cells

Range("A1:D10").Interior.ColorIndex = 6

A cell by row and column number

Cells(1, 1).Value = "A1"

Because row 1, column 1 is A1.

A range using Cells

Range(Cells(1, 1), Cells(10, 4)).Value = "X"

Step 8: Variables (store values so your code is clearer)

A variable is a named box that stores a value.

Dim total As Double total = 1250.5

Common variable types:

String (text)
Long (whole numbers)
Double (numbers with decimals)
Boolean (True/False)
Range (cells/ranges)

Step 9: If statements (basic decision making)

Example: if cell A1 is blank, fill it.

Sub FillIfBlank() If Range("A1").Value = "" Then Range("A1").Value = "Not provided" End If End Sub

If…Else example

Sub PassFail() Dim score As Long score = Range("B2").Value If score >= 60 Then Range("C2").Value = "Pass" Else Range("C2").Value = "Fail" End If End Sub

Step 10: Loops (repeat actions)

Loops are where VBA becomes really powerful.

Loop through a set number of times

Sub SimpleLoop() Dim i As Long For i = 1 To 10 Cells(i, 1).Value = i Next i End Sub

That fills A1 to A10 with 1 to 10.

Step 11: Find the last used row (very common)

Most spreadsheets grow and shrink. Hard-coding “go to row 500” isn’t reliable.

A common way to find the last row in a column:

Dim lastRow As Long lastRow = Cells(Rows.Count, "A").End(xlUp).Row

This finds the last used cell in column A.

Step 12: A practical script: label rows based on a value

Let’s say:

Column B contains a score
You want column C to show “Pass” or “Fail” for every row

Sub LabelPassFail() Dim lastRow As Long Dim r As Long Dim score As Double lastRow = Cells(Rows.Count, "B").End(xlUp).Row 'Assume headers in row 1, start from row 2 For r = 2 To lastRow score = Cells(r, "B").Value If score >= 60 Then Cells(r, "C").Value = "Pass" Else Cells(r, "C").Value = "Fail" End If Next r End Sub

This is a good “first real” VBA script because it uses:

variables
last row logic
a loop
an If statement
writing output to a new column

Step 13: Speed and reliability tips

Turn off screen updating (faster)

Application.ScreenUpdating = False '...your code... Application.ScreenUpdating = True

Turn off automatic calculation (sometimes faster)

Application.Calculation = xlCalculationManual '...your code... Application.Calculation = xlCalculationAutomatic

Only use calculation changes if you understand the impact. Always set it back.

Step 14: Add basic error handling (so it doesn’t crash)

A simple way to protect a script:

Sub SafeFormat() On Error GoTo ErrHandler Application.ScreenUpdating = False 'Example action ActiveSheet.Name = "Report" Application.ScreenUpdating = True MsgBox "Done." Exit Sub ErrHandler: Application.ScreenUpdating = True MsgBox "Something went wrong: " & Err.Description End Sub

This helps you recover if something unexpected happens.

Step 15: How to run your script from Excel

You have a few options:

Run from the Macros list

Developer > Macros
Select macro name
Run

Assign to a button

Developer > Insert > Button (Form Control)
Draw button
Choose macro
Rename the button text

Assign a keyboard shortcut

When recording macros you can set one, but for custom scripts it’s often better to use buttons or the macro list unless you’re confident.

Step 16: When VBA is the right tool (and when it isn’t)

VBA is great when:

you want one-click automation inside Excel
you need loops and logic
you’re building tools for a team
you need custom formatting and reporting

Other tools may be better when:

the main job is importing and cleaning data repeatedly (Power Query)
you need automation across Microsoft 365 apps and approvals (Power Automate)
you want a strong data model for dashboards (Power BI)

Many real solutions use a mix: Power Query for cleaning + VBA for “push-button” reporting.

Conclusion

Writing a simple VBA script in Excel is very achievable, even if you’re new to programming. Start by creating a module and writing small macros that do one job well. Learn how to reference cells and ranges, use variables, add If statements, and loop through rows. As you build confidence, you can make scripts faster, safer, and more flexible.

If you’d like structured, hands-on training (from recording macros through to building robust automation), these courses are a great next step:

Excel VBA Introduction – get started with the VBA editor, macros, objects, and simple scripts
Excel VBA Intermediate – loops, user input, working with multiple sheets/workbooks, and stronger automation
Excel VBA Advanced – professional VBA, error handling, optimisation, and building complete tools
Plus supporting courses if needed:

‍

View all ARTICLES

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.