Excel 365 Memory Issue

[u/gk802]

I have a fairly large spreadsheet with associated macros. It normally occupies about 300MB in memory, about 9MB on disk. After Excel's recent update to the latest current channel (Version 2408 (Build 17928.20114)), the spreadsheet immediately runs up to 4GB+ in the initial calculation process after opening and Excel crashes. Repair of the spreadsheet finds no errors. I reverted to Version 2407 (Build 17830.20166) and the spreadsheet has no issues. Curious if anyone else is experiencing a similar issue with the latest build.

Update: Testing in progress, but issue appears resolved with Version 2409 (Build 18025.20030).

[u/Downtown-Economics26]

Does the macro create formulas in ranges? Applying formulas/formatting to large ranges / multiple sheets is the quickest way to cosmically inflate your file size.

[u/gk802 → u/Downtown-Economics26]

No formulas created by macros. No formatting. Macros to import text files shouldn't be in use in the initial calculation. There are 2 VB functions that determine remaining duration on bonds that act on about 100 cells each. All the rest is standard formulas. In calculation, the spreadsheet consumes memory at about 100MB/second until all memory is consumed. Reverting to 2407, the spreadsheet successfully calculates in about 8 seconds and consumes no more than 300MB of memory at any point in the process.

[u/AxelMoor → u/gk802]

Are there any links? Mainly external (internet), linked images, calls in the workbook, or PowerBI for external data (network, internet, etc).

Version 2408 (Build 17928.20114) changed a lot in this regard. To increase the performance of business networks, 365 now accumulates a lot of data on the local computer to avoid network traffic as if it were a kind of cache - and it is (very) large indeed.

Look in the workbook for any links or objects that currently, or in the past, gathered external data, and supposedly should be disabled.

Check if the workbook data is all of local origin, or if it was downloaded from the internet at some point.

If the workbook does not have any form of external communication, create a new workbook without any content and check the behavior of Excel in Memory and Network in the Task Manager - check the Excel settings for any network activity, unauthorized downloads, obtaining external data, sharing, services such as Azure or associated with Purview, etc.

Check the Account in Excel >> Files the status of your subscription.

It is hard to analyze without physically seeing what is happening.

[u/gk802 → u/AxelMoor]

Good helpful background. Thank you.

Agree it's difficult without seeing the issue. I can't send the spreadsheet off for analysis, either, since it contains personal financial data.

Spreadsheet has no external links, linked images, or links to anything external to the spreadsheet. All personally built, nothing from a template or external source. It contains about 20 identical sheets that are ledgers of mutual fund transactions with running basis and recognized gain/loss calculations. There's a single sheet that contains a list of individual bond holdings with calculations of accrued interest, next coupon date, maturity date, etc. The bond sheet uses a couple of custom VBA functions that calculate average portfolio duration. There are 2 sheets that summarize holdings. There are a number of macros that do one-time things like position a sheet when it's activated and import .csv files of security prices and holdings/account balances, but none of those should be active or doing anything when the sheet is first loaded.

I'm guessing it's a VBA problem. At some point, I'm going to have to allow one of my 3 machines to update Excel versions and then start cutting things out of the spreadsheet until the problem resolves.

I hope in the meantime that enough people will report the issue that they'll find it and resolve it in an upcoming release.

[u/Andrassz → u/AxelMoor]

Hi! Can you help where I find the detailed update description for excle 2408v? I find something on the internet but It is nothing just marketing bullshit. Thanks for your help.

[u/AxelMoor → u/Andrassz]

I don't know if this helps, Microsoft only publishes the "Release Notes" - which forces us to read all the changes from previous versions to get an idea of ​​the details of the current version.

Here is a list of more "serious" sources to consult:
1. Release notes for Current Channel
https://learn.microsoft.com/en-us/officeupdates/current-channel#version-2408-august-26

1. Excel performance: Performance and limit improvements
   https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-performance-and-limit-improvements

2. Office 365: A guide to the updates
   https://www.computerworld.com/article/1710697/office-365-a-guide-to-the-updates.html

3. Office 2021 and Office LTSC for Windows and Mac FAQ
   https://support.microsoft.com/en-us/office/office-2021-and-office-ltsc-for-windows-and-mac-faq-d574cf0b-3ebc-42cf-9035-a3b837e0463c

Follow some source links from the articles above and you may be able to find even more details.

Regarding the 'data accumulation that 365 does on computers to avoid network traffic and improve bandwidth usage' - I read an article a few days ago - but since I cleared the browser history I still couldn't find the article. I'm sorry about that, if and when found it, I'll post it here.

Regarding the topic proposed by the OP - now, I agree that the cause is in the VBA macro - objects that are not set to "nothing" after use, for example, to free up memory - and that in this version, for some reason, memory management only made it worse.

[u/Andrassz → u/AxelMoor]

Thank you for your response and for taking the time. Regarding the sentence you quoted, I am also looking for a solution or some information about what has changed between the 2407v and 2408v. If you find the article and attach it here, I would be very grateful.

[u/gk802 → u/AxelMoor]

This is helpful. I'm thinking the VB functions may be producing the issues, but I see discussion about changes in SUMIFS as well. My spreadsheet uses extensive conditional summation.