[Excel VBA] How to Check Workbook Status (Read-Only and Compatibility Mode)

When running VBA macros, the state in which an Excel workbook is opened can significantly affect your code. You may need to change the process or prevent errors depending on the file’s status.

Two critical questions to ask are: “Is this workbook editable?” and “Is it in an old file format?”

In this article, I will explain two basic properties to determine if a workbook is “Read-Only” or in “Compatibility Mode.”

How to Check if a Workbook is in “Compatibility Mode”

What is Compatibility Mode?

Compatibility Mode occurs when you open an older Excel file format (such as .xls) in a newer version of Excel. To maintain compatibility, some newer features are restricted. You will typically see “[Compatibility Mode]” displayed in the workbook’s title bar.

If your macro uses new functions (like XLOOKUP) in a workbook running in Compatibility Mode, it will cause an error. Therefore, it is important to check this beforehand.

Code and Explanation

You can determine if a workbook is in Compatibility Mode using the .Excel8CompatibilityMode property of the Workbook object.

Sub CheckCompatibilityMode()
    Dim currentBook As Workbook
    Set currentBook = ThisWorkbook

    ' Check if .Excel8CompatibilityMode is True
    If currentBook.Excel8CompatibilityMode = True Then
        MsgBox "This workbook [" & currentBook.Name & "] is in Compatibility Mode." & vbCrLf & _
               "Some new features may not be available.", vbInformation
    Else
        MsgBox "This workbook [" & currentBook.Name & "] is in Standard Mode.", vbInformation
    End If
End Sub

• .Excel8CompatibilityMode: This property returns True if the workbook is in Compatibility Mode, and False otherwise. You can use this value to branch your macro’s logic.

How to Check if a Workbook is “Read-Only”

What is Read-Only?

If a workbook is Read-Only, you can change the content in memory, but you cannot save changes over the same filename. You should always check this status before your macro attempts to modify cell values or save the file.

Code and Explanation

You can easily check if a workbook is Read-Only using the .ReadOnly property of the Workbook object.

Sub CheckIfReadOnly()
    Dim currentBook As Workbook
    Set currentBook = ActiveWorkbook

    ' Check if .ReadOnly is True
    If currentBook.ReadOnly = True Then
        MsgBox "This workbook is Read-Only. Editing process aborted.", vbCritical, "Process Aborted"
        ' Stop the macro here
        Exit Sub
    End If
    
    ' If not Read-Only, the process continues
    MsgBox "This workbook is editable. Continuing process."
    
    ' ... (Write your editing or saving code here) ...
End Sub

• .ReadOnly: This property returns True if the workbook is Read-Only, and False if it is editable.
• Exit Sub: As shown in the sample, using Exit Sub to immediately stop the macro if the file is Read-Only is a very effective technique to prevent errors (often called a “Guard Clause”).

Summary

To improve the stability of your macros, checking the workbook’s status before execution is essential.

• Compatibility Mode: Use this when you want to prevent macros with new features from running on old file formats.
  • If myBook.Excel8CompatibilityMode Then ...
• Read-Only: This is a mandatory check at the beginning of any macro that writes data or saves files.
  • If myBook.ReadOnly Then Exit Sub

By incorporating these simple checks, you can significantly reduce unexpected errors and create robust, user-friendly tools.