Clear Excel Memory Cache Using VBA (3 Easy Ways)

Microsoft Excel is one of the most effective programs currently available. You can use Excel’s tools and functions to do continuous operations on a dataset. Frequently, we must clear Excel’s cache memory to improve application speed. This article describes three straightforward Excel VBA methods for clearing cache memory. Therefore, check out these three easy ways to Clear Excel Memory Cache using VBA.

How to Clear Excel Memory Cache Using VBA: 3 Suitable Ways

We’ll look at a sample dataset to show how this works. For instance, the table below contains Product, Sales Rep, and State columns. In the First Method, we will turn the dataset into a Pivot Table and use a VBA macro to clear the Pivot Cache. The second method, on the other hand, will clean up Excel Memory by assigning Zero to RecentFiles Properties. But in the third method, we will set any Object variable to the literal value of Nothing. So, it will free up the memory used by Object variables. I also used Microsoft Excel 365 to write this paper. You can choose the edition that best fits your needs.

1. Combine PivotTable and PivotCache Objects to Clear Memory Cache

We can summarize data quickly and easily with the help of a Pivot Table, a type of active visualization tool. This PivotTable object belongs to the PivotTables collection. Conversely, the Pivot Cache has generated automatically whenever a Pivot Table is created. Therefore, cleaning Pivot Table caches is also necessary to erase the Excel cache. Here are the instructions you need to complete the task.

• First, select the B4:D10 range >> Navigate to the Insert tab, followed by PivotTable.

Subsequently, the PivotTable window will appear.

• After that, write the sheet name with an Exclamation mark followed by the range in the Table box >> Check the New Worksheet option >> hit OK.

Consequently, the PivotTable Fields pane will open on the right side.

• Next, check all the dataset columns.

Finally, the Pivot Table will look like the one below.

• Presently, go to the Developer tab, followed by Visual Basic.

• Afterward, click Insert, followed by the Module symbol.

• Now, type the following code in the Module Box.

Sub CleanPivotCache()
Dim softEkoTable As PivotTable
Dim softEkoSheet As Worksheet
Dim softEkoCache As PivotCache
Application.ScreenUpdating = False
For Each softEkoSheet In ActiveWorkbook.Worksheets
For Each softEkoTable In softEkoSheet.PivotTables
softEkoTable.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next softEkoTable
Next softEkoSheet
For Each softEkoCache In ActiveWorkbook.PivotCaches
On Error Resume Next
softEkoCache.Refresh
Next softEkoCache
Application.ScreenUpdating = True
End Sub

• Hence, press  F5  or click the Run button. As a result, you will be able to clear your Excel memory cache.

2. Clean Memory Cache by Assigning Nothing Literal to Objects

A literal descriptor, Nothing can only be used with variables specified as entities or variants. If the reference value of an object property is zero, then that variable is effectively uninitialized. In this context, we will create an object called softEkoObject and set it to Nothing literal. To complete the task, please follow the steps that are listed below. To complete the job, please follow the steps that are listed below.

• To start, open the sheet you need and make it an active sheet >> Click on the Developer tab >> Visual Basic.

• Click Insert again >> the Module icon.

• Now, copy and paste the code below into the Module Box. Remember to modify the sheet name and the range as your need.

Sub nothingLiteralToAObject()
     Dim softEkoObject As Object
 Set softEkoObject = Application.Worksheets("NothingLiteral").Range("B4:D10")
         Set softEkoObject = Nothing
End Sub

• To conclude, press F5 or click the Run button. Finally, you will be able to clean the memory cache.

3. Assign Zero to RecentFiles Properties to Clear Excel Memory Cache

Excel’s RecentFiles property lists the most recently used files. This list is called the RecentFiles collection. Now, we’ll use a VBA macro to set the RecentFiles property to O. So, we free up some space in Excel. If you want to do well on the assignment, please pay close attention to these instructions.

• Firstly, navigate to the Developer tab >> Tap the Visual Basic symbol.

• After that, choose Insert, followed by the Module icon.

• Presently, type the below code in the Module Box.

Sub clearMemoryCache()
    Application.RecentFiles.Maximum = 0
End Sub

• Lastly, press F5 or click the Run button. Finally, you will be able to clear the memory cache.

Download Practice Workbook

You may download a free copy of the example workbook we mentioned during the presentation by clicking on the link below.

Conclusion

After this point, you can use the methods we discussed to clear Excel memory cache using VBA. Keep using them, and if you think of other ways to get the job done or have any new ideas, let us know. Remember to ask questions, comment, or give suggestions in the part below.

<< Go Back to Clear Cache in Excel | Excel Files | Learn Excel