[VBA] How to Locate and Delete the Personal Macro Workbook (PERSONAL.XLSB)

Introduction

As you start using VBA, you will likely hear about the “Personal Macro Workbook.” This is a very convenient hidden workbook that allows you to save macros for use in any open Excel file.

However, situations may arise where you need to find and delete this file, such as when you want to reset your environment or if the file has become corrupted and needs to be recreated.

This article explains how to locate the storage folder of the Personal Macro Workbook (PERSONAL.XLSB) using VBA, and provides code to delete it.

Important Warning: Deleting the Personal Macro Workbook will erase all macros saved within it. Please proceed with caution and at your own risk. It is highly recommended to create a backup before deleting.

What is the Personal Macro Workbook?

The Personal Macro Workbook is a special workbook dedicated to macros that loads automatically when Excel starts.

• File Name: PERSONAL.XLSB
• Location: A special folder named XLSTART

Any file saved in the XLSTART folder is designed to open whenever Excel launches. Since the Personal Macro Workbook is usually hidden, you might not even notice it is open.

How to Locate the File Using VBA

You can easily get the path to the XLSTART folder, where the Personal Macro Workbook is stored, by using the Application.StartupPath property in VBA.

Running the following code will display the path in the Immediate Window.

Sample Code to Find the Location

Sub FindPersonalMacroWorkbookPath()

    ' Get the path of the XLSTART folder using Application.StartupPath
    Dim startupFolderPath As String
    startupFolderPath = Application.StartupPath
    
    ' Output to the Immediate Window
    Debug.Print "Location of Personal Macro Workbook (XLSTART folder):"
    Debug.Print startupFolderPath

    ' Display in a Message Box
    MsgBox "The Personal Macro Workbook is located in the following folder:" & vbCrLf & vbCrLf & startupFolderPath, vbInformation

End Sub

How to Run

1. Open the VBA Editor (Alt + F11).
2. Open the Immediate Window (Ctrl + G).
3. Run the code above.
4. A path like C:\Users\Username\AppData\Roaming\Microsoft\Excel\XLSTART will appear in the Immediate Window and a message box.

How to Delete the File Using VBA (Handle with Care)

Once you know the location, you can delete the file. However, as mentioned earlier, all saved macros will be lost.

If you no longer need the file, it is often safer to rename it (e.g., to PERSONAL_old.XLSB) rather than deleting it immediately. This allows you to restore it if needed.

Sample Code to Execute Deletion

The following code uses the FileSystemObject for a safer deletion process.

Sub DeletePersonalMacroWorkbook()

    '== Variable Definitions ==
    Dim objFSO As Object
    Dim personalBookPath As String
    Dim userResponse As VbMsgBoxResult

    '== Set Path ==
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    personalBookPath = Application.StartupPath & "\PERSONAL.XLSB"
    
    '== Check if File Exists ==
    If Not objFSO.FileExists(personalBookPath) Then
        MsgBox "The Personal Macro Workbook (PERSONAL.XLSB) was not found.", vbInformation
        Exit Sub
    End If
    
    '== Final Confirmation ==
    userResponse = MsgBox("This will completely delete the Personal Macro Workbook." & vbCrLf & _
                          "All macros saved in it will be lost. Are you sure?" & vbCrLf & vbCrLf & _
                          personalBookPath, _
                          vbCritical + vbOKCancel, "Final Confirmation")
                          
    '== Execute Deletion Only if OK is Clicked ==
    If userResponse = vbOK Then
        On Error Resume Next ' Handle cases where the file might be open
        objFSO.DeleteFile personalBookPath, True ' Force delete even if read-only
        On Error GoTo 0
        
        If Not objFSO.FileExists(personalBookPath) Then
            MsgBox "The Personal Macro Workbook has been deleted.", vbInformation
        Else
            MsgBox "Failed to delete the file." & vbCrLf & _
                   "Please try again after restarting Excel.", vbExclamation
        End If
    Else
        MsgBox "Operation cancelled.", vbInformation
    End If
    
    '== Release Object ==
    Set objFSO = Nothing

End Sub

How to Delete (or Rename) Manually

In many cases, it is safer to open the folder and manage the file manually without using VBA.

1. Run the “Sample Code to Find the Location” above to get the XLSTART path.
2. Copy the path.
3. Paste the path into the address bar of File Explorer and press Enter.
4. The XLSTART folder will open.
5. Delete PERSONAL.XLSB or rename it.

Summary

This article explained how to identify the location of the Personal Macro Workbook and how to delete it.

• The location can be found using Application.StartupPath.
• Deleting the file erases all macros, so backing up or renaming is recommended.
• While deletion via VBA is possible, manual operation via File Explorer is often safer.

The Personal Macro Workbook is very useful, but knowing where it resides and how it works will help you calmly handle any issues that arise. Please keep this knowledge in mind for future troubleshooting.