Excel and Access VBA: What It Is and How to Use It

VBA & macros · Updated November 22, 2026

Excel and Access VBA (Visual Basic for Applications) is a programming language built into Microsoft Office that lets you automate repetitive tasks, create custom functions, and build interactive tools in Excel and Access. In short, VBA is the engine that turns a static spreadsheet or database into a dynamic, self-running application.

What Is VBA and Where Did It Come From?

VBA is a subset of the Visual Basic programming language, introduced by Microsoft in the early 1990s to replace the older macro languages in Office applications. It is embedded in Excel, Access, Word, and other Office programs, so you don’t need to install anything extra to use it. In Excel, VBA is used to manipulate worksheets, ranges, charts, and pivot tables. In Access, VBA powers forms, reports, queries, and event-driven actions like clicking a button or updating a record. Because both applications share the same language, you can even write VBA code in Excel to open an Access database and move data between them.

How VBA Works in Excel and Access

VBA code is stored in modules, which are containers for procedures (subroutines and functions). You open the Visual Basic Editor in Excel by pressing Alt+F11 (on Mac: Fn+Option+F11). In Access, the editor opens the same way. The editor has a Project Explorer on the left, a code window in the middle, and an Immediate window for testing. A simple Excel macro might look like this:

Sub HighlightDuplicates()
    Dim rng As Range
    Set rng = Range("A1:A100")
    rng.FormatConditions.Add Type:=xlDuplicate, Operator:=xlEqual
    rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
    rng.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
End Sub

In Access, a common VBA task is opening a form and filtering records based on a value. For example, to open a form showing only orders for a specific customer, you might write:

Private Sub cmdShowOrders_Click()
    Dim strFilter As String
    strFilter = "CustomerID = " & Me.txtCustomerID
    DoCmd.OpenForm "frmOrders", WhereCondition:=strFilter
End Sub

Both examples show the core idea: VBA responds to events (like a button click) and manipulates objects (ranges, forms, recordsets). Macros are recorded in Excel via View > Macros > Record Macro, which generates VBA code you can then edit. Access doesn’t have a macro recorder for VBA, but it does have a macro builder that can convert macros to VBA.

Excel VBA vs. Access VBA: Key Differences

While the language is identical, the object models differ. Excel’s object model centers on workbooks, worksheets, ranges, and cells. Access’s object model centers on databases, tables, queries, forms, reports, and recordsets. In Excel, you often work with the Range object to read or write cell values. In Access, you work with Recordset objects to iterate through table rows. For example, to loop through rows in an Excel sheet, you might use:

Dim i As Long
For i = 1 To 100
    If Cells(i, 1).Value > 100 Then
        Cells(i, 2).Value = "High"
    End If
Next i

In Access, the equivalent might be:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Orders")
Do While Not rs.EOF
    If rs!Total > 100 Then
        rs.Edit
        rs!Status = "High"
        rs.Update
    End If
    rs.MoveNext
Loop

Notice the different objects: Cells vs. Recordset. Also, Access uses the DAO (Data Access Objects) library by default, while Excel uses the Excel object model. You can reference one from the other—for instance, Excel can use ADO (ActiveX Data Objects) to connect to an Access database.

When to Use Excel VBA vs. Access VBA

Choosing between Excel and Access for a VBA project depends on the nature of your data and what you need to do. Consider these factors:

  • Data volume: Excel handles up to about 1,048,576 rows per worksheet. Access can handle millions of rows across multiple tables, but performance degrades if you try to use Excel as a database.
  • Data relationships: If your data has many related tables (customers, orders, products), Access’s relational structure is better. Excel is fine for flat lists.
  • Multi-user access: Access supports multiple concurrent users with record-level locking. Excel files are generally single-user unless you use co-authoring in Microsoft 365, which has limitations.
  • Reporting and forms: Access has a robust form and report designer. Excel has charts and pivot tables, but forms are less flexible.
  • Complex calculations: Excel’s grid and built-in functions are superior for heavy number crunching, statistical analysis, and what-if scenarios.

In practice, many businesses use Excel for analysis and Access for data entry and storage. VBA can bridge the two: you can write an Excel macro that imports data from an Access database, or an Access form that exports data to Excel for further analysis.

Common Problems and How to Avoid Them

VBA is powerful but has a steep learning curve. Here are frequent pitfalls and their solutions:

  • Macros disabled: Excel and Access block VBA by default for security. You’ll see a yellow bar with “Enable Content.” To run your code, you must enable macros. For distribution, sign your code with a digital certificate or use a trusted location.
  • Undo doesn’t work: VBA changes cannot be undone with Ctrl+Z. Always save a copy of your workbook or database before running a new macro. A simple practice: ThisWorkbook.SaveCopyAs "backup.xlsm" at the start of a macro.
  • Runtime errors: Common errors include “Object required” (you forgot Set), “Type mismatch” (you assigned a text to a numeric variable), and “Out of memory” (you looped infinitely). Use On Error GoTo to handle unexpected errors gracefully.
  • Slow performance: If your macro processes thousands of cells, turn off screen updating and automatic calculation at the start: Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual, then turn them back on at the end.
  • Access-specific issues: When using DAO, always close recordsets and set them to Nothing to release memory. Also, be careful with reserved words like Name or Date as field names—they can cause errors.

Alternatives to VBA

VBA is not the only way to automate Office. Depending on your needs, you might consider:

  • Power Query (Excel): For data import and transformation, Power Query is a no-code solution that handles most ETL tasks without VBA.
  • Power Automate: For workflow automation across Office 365 and other apps, Power Automate can trigger flows based on file changes or emails.
  • Office Scripts (Excel for the web): A TypeScript-based alternative for Excel online, but it doesn’t work in Access.
  • Access Macros: Access has a built-in macro builder that can perform many actions without VBA, though it’s less flexible for complex logic.
  • Python or R: For advanced data analysis, many users export data and process it with Python libraries like pandas. This is not integrated with Office but is a powerful alternative.

VBA remains the most direct way to automate Excel and Access because it’s built in and requires no additional software. However, for new projects, consider whether a modern tool like Power Automate might be easier to maintain.

Costs and Safety Considerations

VBA itself is free—it’s included with Microsoft Office. The cost is the time and expertise required to write and debug code. If you hire a developer, rates vary widely, but a simple macro might cost a few hundred dollars, while a complex Access application can run into thousands. Safety is critical: VBA can be used to write malicious code, so never enable macros from untrusted sources. Always keep your Office software updated, and consider using digital signatures to verify the source of macros. When writing your own code, test it on a copy of your data first. For Access, back up the database file (.accdb) regularly, as VBA errors can corrupt the database if you’re not careful.

Frequently Asked Questions

Can I use Excel VBA to control Access?

Yes. In Excel, you can reference the Access object library (Tools > References in the VBA editor) and then create an Access Application object to open a database, run queries, or pull data. For example, you can write a macro that opens an Access database and imports a table into Excel using DoCmd.TransferSpreadsheet from within Access, or use ADO from Excel.

Is VBA the same as Visual Basic .NET?

No. VBA is an older, interpreted language tied to Office applications. VB.NET is a full object-oriented programming language used to build standalone Windows applications. VBA is not compiled into an executable; it runs inside the host application. VB.NET is compiled and runs on the .NET Framework.

Why does my macro work in Excel but not in Access?

Because the object models differ. A macro that uses Range will fail in Access because Access doesn’t have a Range object. You need to translate the logic to Access objects like Recordset, Form, or DoCmd. Even the same function name might behave differently—for example, MsgBox works in both, but ActiveCell is Excel-only.

How do I learn VBA for Excel and Access?

Start with the macro recorder in Excel to see generated code, then modify it. Microsoft’s official documentation on learn.microsoft.com is a reliable reference. For Access, study the built-in form and report events. Practice by automating simple tasks like formatting a report or importing data. Many online tutorials are available, but always test code on a copy of your data.

Can VBA macros be reversed or undone?

No. VBA changes to data or structure cannot be undone with Ctrl+Z. That’s why it’s essential to save a backup before running any macro. You can also write your macro to confirm before making changes, or use a versioning system like saving timestamped copies.

Is VBA still supported in Microsoft 365?

Yes. VBA is fully supported in Excel and Access in Microsoft 365 on Windows and Mac. Microsoft continues to update Office applications, but VBA is a stable, legacy feature. However, Microsoft has introduced Office Scripts for Excel on the web, which is a newer, cloud-based alternative, but it does not work in Access.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.