[Excel VBA] How to Turn AutoSave On/Off Using VBA (.AutoSaveOn)

In Microsoft 365 Excel, “AutoSave” is enabled by default for files saved to OneDrive or SharePoint.

While this feature is very convenient for collaborative editing and preventing data loss, it can cause issues when processing large amounts of data with VBA macros. Every single change might trigger a save, leading to slower processing speeds and the creation of unnecessary version history.

This article explains a safe and recommended method to temporarily turn off AutoSave during macro execution and restore it to its original state afterwards.

What is the .AutoSaveOn Property?

.AutoSaveOn is a property of the Workbook object that indicates whether AutoSave is currently enabled (True) or disabled (False).

Note that this property is only valid for workbooks saved on OneDrive or SharePoint. Attempting to access it for a locally saved file will result in an error.

By changing the value of this property via VBA, you can programmatically control AutoSave.

Complete VBA Code (Recommended Pattern)

The safest approach respects the user’s settings: turn off AutoSave only while the macro is running, and restore it when finished.

Sub ProcessDataWithAutoSaveControl()
    
    Dim targetBook As Workbook
    Dim wasAutoSaveOn As Boolean
    
    ' Set the active workbook as the target
    Set targetBook = ActiveWorkbook
    
    ' --- 1. Check if AutoSave is available for the workbook ---
    ' (Prevent errors for local files)
    On Error Resume Next
    wasAutoSaveOn = targetBook.AutoSaveOn
    If Err.Number <> 0 Then
        ' This workbook is not subject to AutoSave, so no further action is needed
    Else
        ' --- 2. Remember the original state and temporarily turn it off ---
        If wasAutoSaveOn = True Then
            targetBook.AutoSaveOn = False
            Debug.Print "AutoSave has been temporarily turned off."
        End If
    End If
    On Error GoTo 0

    ' ======================================================
    ' === Write the time-consuming main process here ===
    ' (e.g., rewriting large amounts of data, complex calculations)
    Application.StatusBar = "Processing heavy task..."
    Application.Wait Now + TimeValue("00:00:03") ' Dummy process
    Application.StatusBar = False
    ' ======================================================
    
    ' --- 3. Restore the AutoSave state ---
    ' Turn it back on only if it was originally on before the macro ran
    If wasAutoSaveOn = True Then
        targetBook.AutoSaveOn = True
        Debug.Print "AutoSave has been restored to 'On'."
    End If

    MsgBox "Process completed."
    
End Sub

Key Points of the Code

1. Pre-check and Conditions

On Error Resume Next
wasAutoSaveOn = targetBook.AutoSaveOn
If Err.Number <> 0 Then
    ' ...
End If
On Error GoTo 0

Attempting to manipulate the .AutoSaveOn property on a locally saved file (which is not subject to AutoSave) causes an error. On Error Resume Next allows the code to ignore this error and continue. The code proceeds to the next step only if no error occurs (meaning AutoSave is available).

2. Storing State and Temporarily Disabling

wasAutoSaveOn = targetBook.AutoSaveOn
' ...
If wasAutoSaveOn = True Then
    targetBook.AutoSaveOn = False
End If

This is the most critical part. First, the variable wasAutoSaveOn stores the AutoSave status (True or False) before the macro runs. Then, if it was On, targetBook.AutoSaveOn = False temporarily switches it to Off.

3. Restoring State

If wasAutoSaveOn = True Then
    targetBook.AutoSaveOn = True
End If

After the main process is complete, the AutoSave setting is restored based on the stored wasAutoSaveOn value. AutoSave is turned back on only if wasAutoSaveOn was True (meaning it was originally on). This prevents the macro from accidentally enabling AutoSave for a user who had intentionally disabled it.

Summary

The recommended pattern for safely controlling AutoSave in VBA macros is as follows:

1. Before starting the process, store the current state of .AutoSaveOn in a variable.
2. If AutoSave is on, temporarily turn it off (.AutoSaveOn = False).
3. Execute the main process.
4. At the end of the process, restore the AutoSave state based on the stored variable.

By following these steps, you can create a user-friendly and stable tool that maintains macro performance while respecting the user’s original settings.