How To Increase Undo Levels

Have you ever encountered a situation where Excel cannot complete your task with available resources in your computer? 

The above warning will be displayed when Excel estimates that the requested user task cannot be completed with the available RAM memory. This is quite common with any Office 32-bit version, which cannot use more than 2 GB of RAM. 

If you are running any 64-bit Windows version (Vista, 7, 8, 10 or Server 2008/R2), you can choose whether to install the 64-bit version of Office 2010 or later, which offers the advantage of using all memory installed in your PC. Eventually, even Excel 64-bit can run out of memory during large operations. 

To learn more about the compatibility between the 32-bit & 64-bit versions of Office, please click here.

Similar to Word, Excel allows users to 'undo' previous actions and return the workbook to the condition before the previous action was taken. You can use the Ctrl-Z shortcut or the Undo drop down in the Quick Access Toolbar (QAT).

Undo provides a history of up-to 100 levels (read Excel's 2007 specifications & limits) for a lot (but not all) of actions users perform in Excel. For example, running a macro or deleting a sheet resets the undo stack. 

Undo steps must be executed in the reverse order they were first done. You can't, for example, jump through the list picking which steps you would like to undo and leave the rest. All the work between the last step and the step you want to undo, gets undone as well.

If large cell ranges are modified, Excel may not be able to maintain the undo stack due to memory shortage. 

However, if the Undo history is disabled, no memory will be allocated for it. Excel can use the maximum memory available in your computer, when Undo levels are set to zero.

The Undo setting cannot be changed from Excel options. The Microsoft article 211922 describes how to modify the Excel's undo levels using the registry editor. Modifying the registry manually may cause serious problems that may require Windows to be re-installed in extreme cases.

Our free Excel Addin lets users change or disable Undo levels without the risk of registry corruption.

---

---

Undo & Excel Data Options

The memory required for undo-ing a PivotTable refresh or an Excel 2013 Data Model operation can be significant. Please ensure that Undo is disabled for large data operation, as shown below in Excel 2010 & 2013 Advanced Options. None of the options shown below is available in Excel 2007.

Advanced Options in Excel 2010

---

Advanced Options in Excel 2013

---

The Data settings shown in Excel 2013 Advanced Options are stored in the following registry keys:

• DontSupportUndoForLargePivotTables
  
• PivotTableUndoRowThousandCount 
• PreferExcelDataModel
• DontSupportUndoForLargeDataModels
  
• PivotTableUndoDataModelSize

---

How The 'Undo Levels' Control Is Made

The VBA code used to create the Excel Options Addin requires the Ribbon Commander framework 

Ten Undo level buttons are shown inside a Ribbon menu control, followed by a separator (which contains text) and finally, by a 'reset default' button. Please note that no button is shown for the current Undo setting. All Undo level buttons are created dynamically based on your PC's current Undo setting.

The 10 level icons were created in Excel using a bar chart without bar gaps. The charts were exported to images (.jpg) with the Chart.Export method and then imported to the customUI area of the OpenXML workbook.

Private Sub CreateUndoGroup()

    Dim I As Long
    Dim lUndo As Long
    
    lUndo = GetCurrentUndoValue
    
    With mainTab
        With .groups.Add(New rxGroup)
            .ID = "undo_group"
            .image = "undo_green"
            .Label = "Recover Undo Memory"
    
            With .Menus.Add(New rxMenu)
                .image = "undo_green"
                .Size = rxsLarge
    
                If lUndo = -1 Then
                    .Label = "Default Undo Levels"
                Else
                    .Label = lUndo & " Undo Levels"
                End If
    
                .menuSeparators.Add(New rxMenuSeparator).Title = "Select a new Undo level"
    
                .supertip = "Modify Excel's Undo levels (0-100)" & vbCrLf & vbCrLf & _
                            "    0 = Undo levels disabled" & vbCrLf & _
                            "100 = Max levels recommended" & vbCrLf & vbCrLf & _
                            "The lower the Undo level value, " & _
                            "the less memory is allocated by Excel for the undo history." & vbCrLf & vbCrLf & _
                            "No memory is allocated, if Undo = 0."
    
                For I = 0 To 100 Step 10
    
                    Select Case I
                    Case 0
                        If lUndo <> 0 Then
                            With .Buttons.Add(New rxButtonRegular)
                                .image = "item_disable"
                                .Label = " Disable Undo"
                                .OnAction = CustomUI.make_delegate("UndoLevel_click")
                            End With
                            .menuSeparators.Add New rxMenuSeparator
                        End If
    
                    Case 100
                        If lUndo <> 100 Then
                            With .Buttons.Add(New rxButtonRegular)
                                .image = "L10"
                                .Label = "100 Max levels"
                                .OnAction = CustomUI.make_delegate("UndoLevel_click")
                            End With
                        End If
    
                    Case Else
                        If lUndo <> I Then
                            With .Buttons.Add(New rxButtonRegular)
                                .image = "L0" & I / 10
                                .Label = " " & I & " Undo levels"
                                .OnAction = CustomUI.make_delegate("UndoLevel_click")
                            End With
                        End If
                    End Select
                Next I
    
                With .menuSeparators.Add(New rxMenuSeparator)
                    Select Case lUndo
                    Case 0: .Title = "Undo is currently Disabled"
                    Case -1: .Title = "Current Undo: " & "Excel's Default Value"
                    Case Else: .Title = "Current Undo: " & lUndo & " levels"
                    End Select
                End With
    
                With .Buttons.Add(New rxButtonRegular)
                    .image = "undo-default"
                    If lUndo = -1 Then .Enabled = rxFalse
                    .Label = "Reset default Undo"
                    .OnAction = CustomUI.make_delegate("ResetUndo_click")
                End With
            End With  'Menu control
            
            .SEPARATORS.Add New rxSeparator
            
            With .galleries.Add(New rxGallery)
                .Label = "Pivot Table Undo Levels"
                .image = "data_table_analysis"
                .Size = rxsLarge
                .itemWidth = 1050 * 0.8
                .itemHeight = 300 * 0.8
                
                With .items.Add(New rxItem)
                    .image = "pivot-undo"
                    .ScreenTip = "PivotTable Undo Levels" & Space(55)
                    .supertip = "Reduce the memory used to hold PivotTable undo data by decreasing data source rows (Excel 2010 or later) and Data Model MBs (Excel 2013 or later) from:" & vbCrLf & vbCrLf & _
                                "Excel Options > Advanced > Data settings"
                End With
            End With 'gallery
            
        End With  'Group
    End With

End Sub

Download

VBA Add-in powered by the Ribbon Commander Framework

---

comments powered by Disqus