VBA ThisWorkbook in Excel — Why It Is Not the Same as ActiveWorkbook

TL;DR — ThisWorkbook always refers to the one workbook that holds the running code, no matter which window the user clicks. ActiveWorkbook refers to whatever is on top right now, and that can change under your feet. A bare Range("A1") is worse still: it does not name a workbook or a sheet at all, so it lands on the active one at that instant. Anchor writes to ThisWorkbook (or a workbook variable you Set yourself) and the macro stops depending on where the mouse last clicked.

Sub WhereDoesItLand()
    ' ThisWorkbook = the file this code lives in - fixed forever
    ThisWorkbook.Worksheets("Summary").Range("A1").Value = "Report"

    ' ActiveWorkbook = whatever is on top when this line runs - fickle
    Debug.Print ThisWorkbook.Name          ' e.g. Macros.xlsm - always
    Debug.Print ActiveWorkbook.Name        ' whatever the user last clicked
End Sub

What you'll learn

• The mental model — ThisWorkbook is a fixed address, ActiveWorkbook is a moving one
• The three tools for saying which object you mean, and why an unqualified reference is dangerous
• ThisWorkbook versus ActiveWorkbook, the single most important distinction in reliable VBA
• The silent trap — why a macro that works at your desk corrupts a colleague's open file
• How to reference another open workbook by name, and how to open one and keep a handle
• The opinion — qualify every reference, and default to ThisWorkbook

The mental model: a fixed address, not a moving one

Every reference in VBA answers a question: which workbook, which sheet, which cell. ThisWorkbook answers the first one and never changes its answer. It is the workbook that contains the code you are running — the file where your modules and macros live. Open ten other workbooks, click through all of them, and ThisWorkbook still points at the same file it did when the macro started.

ActiveWorkbook answers the same question with whatever is on top at this instant. It is a moving target by design: the moment the user clicks another window, or your own code opens a new file, the active workbook changes. Both are useful — but they are not interchangeable, and treating one as the other is the root of a whole family of "my macro edited the wrong file" bugs.

Three ways to say which object you mean

ThisWorkbook is one of three tools VBA gives you for naming the object you want instead of letting the code guess. They shape whether your macro is reliable or fragile, and they are the backbone of this short cluster.

The rule that ties them together: an unqualified reference reaches for whatever is active. Write Range("A1") = 1 with no workbook and no sheet in front of it, and VBA fills in the blanks with the active workbook and the active sheet at that moment. That is convenient in the Immediate Window and catastrophic in a saved macro.

ThisWorkbook vs ActiveWorkbook: fixed vs fickle

This is the distinction to burn into memory. Picture your macro living in Macros.xlsm, and the user has also opened Sales.xlsx and is looking at it when they press your button.

Sub FixedVsFickle()
    ThisWorkbook.Worksheets(1).Range("A1").Value = "written by the macro"
    ' ^ always lands in Macros.xlsm, the file this code lives in

    ActiveWorkbook.Worksheets(1).Range("A1").Value = "written by the macro"
    ' ^ lands in Sales.xlsx - because that is what the user was looking at
End Sub

Same-looking code, two different files. ThisWorkbook is the honest choice when the target is "the file my macro belongs to" — a settings sheet, a log, an output tab that ships with the tool. ActiveWorkbook is correct only when you genuinely mean "whatever the user brought to the front." If you are ever unsure which you meant, you meant ThisWorkbook: it is the one that cannot surprise you.

A cousin worth knowing: ThisWorkbook is not always the active one, and it is not always the one that was open first. It is simply the one with the code. When a macro in Macros.xlsm opens Report.xlsx, the new file becomes ActiveWorkbook, but ThisWorkbook never budges.

The silent trap: it works at your desk and breaks on theirs

Here is how the bug actually ships. You write and test the macro with only one workbook open — your own. With a single workbook, the active workbook is your workbook, so an unqualified Range("A1") and a ThisWorkbook-qualified one behave identically. Every test passes.

Then a colleague runs it with three workbooks open. Now "active" is whatever they were looking at, the unqualified writes land in their file, and your macro quietly overwrites a cell in a report it was never meant to touch. No error is raised — the code did exactly what it said, which was "write to whatever is active." The fix is not a On Error handler; it is to stop leaving the workbook unnamed.

' FRAGILE - depends on what the user last clicked
Range("A1").Value = total

' RELIABLE - names the workbook and the sheet, every time
ThisWorkbook.Worksheets("Summary").Range("A1").Value = total

Qualify the reference and the macro behaves the same on every desk, with any number of files open. This is why the single most valuable habit in workbook-level VBA is: never let a Range or a Cells call float without a workbook and a sheet in front of it.

Referencing another open workbook by name

Sometimes you genuinely need a different workbook — a data file the user opened, or one you open yourself. Do not go through ActiveWorkbook and hope. Name it, or hold a handle to it.

Sub TalkToAnotherFile()
    Dim src As Workbook

    ' Already open? Grab it by its file name (with extension)
    Set src = Workbooks("Sales.xlsx")

    ' Not open yet? Open it and keep the handle Open returns
    Set src = Workbooks.Open("C:\Data\Sales.xlsx")

    ' Now every reference is explicit - no guessing which file
    Debug.Print src.Worksheets("Jan").Range("A1").Value
    ThisWorkbook.Worksheets("Summary").Range("A1").Value = _
        src.Worksheets("Jan").Range("A1").Value
End Sub

Two rules make this safe. First, Workbooks("Sales.xlsx") needs the file's name with its extension, and the file must already be open or it raises Subscript out of range. Second, Workbooks.Open returns the workbook it opened — capture it into a Set variable in the same line, so you never have to ask "which one is active now." A workbook variable you Set yourself is even better than ThisWorkbook: it is fixed and explicit about which file you mean.

The opinion: qualify everything, default to ThisWorkbook

Every reference in a macro should name its workbook and its sheet, and the default workbook should be ThisWorkbook. Unqualified references are not shorter code worth having — they are a bet that the right file will be active at the exact instant the line runs, and that bet loses the day someone else runs your macro with more than one file open.

The discipline is small and it pays forever: reach for ThisWorkbook when the target belongs to your tool, Set a Workbook variable when you mean a specific other file, and reserve ActiveWorkbook for the rare case where "whatever the user has in front" is genuinely the correct target. Treat a bare Range with no workbook in front of it as an unfinished line, exactly the way you would treat an undeclared variable.

When the real job is bigger than picking the right workbook

Getting the reference right is table stakes; the actual work is usually the logic on top of it — reconcile these two files, pull last month's rows, total by region into the summary. Writing that by hand means getting the workbook reference and the loop and the edge cases right, every time. ExcelMaster lets you describe the outcome in plain English — "compare Sales.xlsx to the summary tab in this file and flag rows that do not match" — and it writes and runs the code, backing up your workbook first and qualifying every reference for you. You state which files you mean; it handles the plumbing that this article is about.

Frequently asked questions

What is the difference between ThisWorkbook and ActiveWorkbook in VBA?

ThisWorkbook always refers to the workbook that contains the running code and never changes. ActiveWorkbook refers to whichever workbook is on top at the moment the line runs, which changes whenever the user clicks another window or your code opens a new file. Use ThisWorkbook when you mean the macro's own file, and ActiveWorkbook only when you genuinely mean "whatever the user has in front."

Is ThisWorkbook always the active workbook?

No. ThisWorkbook is the file that holds the code; the active workbook is whatever is on top. They match only when your macro's own file happens to be the one in front. As soon as another workbook is active — the user clicked it, or your macro opened it — the two point at different files.

How do I reference another workbook in VBA?

If it is already open, use Workbooks("Name.xlsx") with the file name and extension. If it is not open, use Set wb = Workbooks.Open("C:\path\Name.xlsx"), which returns the workbook so you can hold a handle to it. Then qualify every reference through that variable instead of relying on the active workbook.

Why does my macro write to the wrong workbook?

Almost always because a Range or Cells call has no workbook in front of it, so it lands on whatever is active when the line runs. It works while you test with one file open, then writes into a colleague's file when several are open. Qualify the reference with ThisWorkbook or a workbook variable and it lands in the right file every time.

Does ThisWorkbook work in an add-in?

Yes, and that is exactly where it earns its keep. In an add-in, ThisWorkbook refers to the add-in file itself, while the user's data is in some other workbook. Use ThisWorkbook for the add-in's own resources and a Set workbook variable (or ActiveWorkbook, deliberately) for the user's data.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-07.

Related guides: VBA ActiveWorkbook · VBA Worksheets · VBA Workbook · VBA Range · VBA Dim