Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 37 ยท Working with Excel

Excel VBA: Workbooks and Worksheets

Learn how to open, create, save and close workbooks and how to add, rename, copy, hide and loop over worksheets in Excel VBA, with safe patterns.

Excel is the application most people think of when they hear "VBA". Everything in Excel hangs off one object, Application, which contains the open Workbooks. Each workbook contains Worksheets, and each worksheet contains Range objects (cells). In this lesson you learn the top two levels: how to open, create, save and close workbooks, and how to add, find, rename, copy and loop over worksheets.

Almost every macro you write for Excel starts by deciding which workbook and which sheet it should work on. Getting this right is the difference between a macro that works on your machine and one that quietly edits the wrong file.

VBA cannot run on this site, so the VBA listings below are read-only. The runnable examples are small JavaScript stand-ins that model the same ideas (collections of workbooks and sheets), so you can experiment with the logic in your browser.

The Excel object hierarchy

flowchart TD
  A["Application"] --> B["Workbooks"]
  B --> C["Workbook"]
  C --> D["Worksheets"]
  D --> E["Worksheet"]
  E --> F["Range / Cells"]

Each level is a collection of the next one down. You reach a cell by walking the path, for example Workbooks("Sales.xlsx").Worksheets("Q1").Range("A1").

You can leave out the top levels. Inside a macro, a bare Range("A1") means "cell A1 of the active sheet of the active workbook". That is convenient but fragile, so in serious code you name the workbook and sheet explicitly.

ThisWorkbook, ActiveWorkbook and variables

  • ThisWorkbook is the workbook that contains the running code. It never changes while the macro runs.
  • ActiveWorkbook is whichever workbook currently has focus. It can change when you open or activate another file.

Prefer ThisWorkbook for things that belong to your macro file, and store any other workbook in an object variable the moment you open it.

VB
Sub OpenAndRead()
    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Data\Sales.xlsx", ReadOnly:=True)

    Debug.Print wb.Name, wb.Worksheets.Count

    wb.Close SaveChanges:=False
End Sub

Set is required for object variables. Forgetting it is the classic beginner error (run-time error 91 or 424).

Creating, saving and closing workbooks

VB
Sub CreateReportFile()
    Dim wb As Workbook
    Set wb = Workbooks.Add            ' new blank workbook
    wb.Worksheets(1).Name = "Summary"

    wb.SaveAs Filename:=ThisWorkbook.Path & "\Report.xlsx", _
              FileFormat:=xlOpenXMLWorkbook
    wb.Close SaveChanges:=False       ' already saved
End Sub

Notes you should remember:

  • FileFormat decides the type. xlOpenXMLWorkbook is .xlsx (no macros), xlOpenXMLWorkbookMacroEnabled is .xlsm.
  • ThisWorkbook.Path is the folder of the macro file, a good base for relative file locations.
  • Close SaveChanges:=True/False avoids the "Do you want to save?" prompt.
  • To suppress other prompts (such as "file already exists, overwrite?") set Application.DisplayAlerts = False and put it back to True straight after.

Check whether a file is already open before opening it a second time:

VB
Function IsWorkbookOpen(ByVal fileName As String) As Boolean
    Dim wb As Workbook
    For Each wb In Workbooks
        If StrComp(wb.Name, fileName, vbTextCompare) = 0 Then
            IsWorkbookOpen = True
            Exit Function
        End If
    Next wb
End Function

Worksheets: add, name, copy, delete

VB
Sub SheetBasics()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = "Archive"

    ws.Copy After:=ThisWorkbook.Worksheets("Archive")   ' copy of the sheet
    ws.Visible = xlSheetHidden                          ' hide it
End Sub

Sheet names are limited to 31 characters, cannot contain \ / ? * [ ] :, and must be unique inside the workbook. Deleting a sheet asks for confirmation, so wrap it in DisplayAlerts = False when you really mean it.

There are three ways to refer to a sheet:

Reference Example Risk
By name Worksheets("Q1") Breaks if a user renames the tab
By index Worksheets(1) Breaks if sheets are reordered
By code name Sheet1 Stable, set in the VBA editor Properties window

The code name is the best choice for sheets that your macro depends on: users can rename the tab freely and Sheet1.Range("A1") still works.

Looping and testing for a sheet

VB
Function SheetExists(ByVal sheetName As String, Optional wb As Workbook) As Boolean
    Dim ws As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    For Each ws In wb.Worksheets
        If StrComp(ws.Name, sheetName, vbTextCompare) = 0 Then
            SheetExists = True
            Exit Function
        End If
    Next ws
End Function

Worksheets contains only worksheets; the Sheets collection also includes chart sheets. Use Worksheets unless you need both.

Try it: a model of the workbook collection

The JavaScript below mimics a workbook with a sheet collection: adding a sheet with a unique-name check, and a sheetExists helper like the VBA one above.

JS Model of Worksheets.Add and SheetExists

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Loop over sheets and hide the helpers

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Relying on ActiveSheet when a macro activates other workbooks. Store the sheet in a variable.
  • Opening a file that is already open, which returns the existing instance or raises an error depending on the path.
  • Forgetting DisplayAlerts = True again after turning it off.
  • Saving a workbook that contains macros as .xlsx: the macros are silently dropped.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which object always refers to the workbook that holds the running macro?

    ThisWorkbook is the file containing the code. ActiveWorkbook can change when another file gets focus.
  2. 2. Why is a sheet's code name more reliable than its tab name?

    The code name is set in the VBA editor, so renaming the tab in Excel does not break code that uses it.
  3. 3. What does Workbooks.Open return when assigned to a variable?

    Open returns a Workbook object, so assign it with Set wb = Workbooks.Open(...).
  4. 4. What happens if you save a macro workbook with FileFormat xlOpenXMLWorkbook?

    The .xlsx format cannot store VBA code, so the macros are dropped. Use xlOpenXMLWorkbookMacroEnabled for .xlsm.

Summary

  • The path to a cell is Application > Workbooks > Worksheets > Range.
  • Use ThisWorkbook for your own file and store other workbooks in Set variables.
  • Workbooks.Add, .Open, .SaveAs and .Close manage files; choose FileFormat deliberately.
  • Add, copy, hide and loop over sheets with Worksheets; prefer code names for sheets your macro needs.
  • Turn DisplayAlerts off only briefly and always restore it.

Next: Excel VBA: Ranges and Cells