r/excel • u/gk802 • 25d ago

Excel 365 Memory Issue solved

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).

6 Upvotes

100% Upvoted

33 comments sorted by

•

u/AutoModerator 25d ago

/u/gk802 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/Downtown-Economics26 162 25d ago

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.

1

u/gk802 25d ago edited 25d ago

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.

2

u/AxelMoor 12 24d ago

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.

1

u/gk802 24d ago edited 24d ago

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.

1

u/Andrassz 22d ago

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.

2

u/AxelMoor 12 22d ago

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.

1

u/Andrassz 22d ago

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.

1

u/gk802 21d ago

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.

2

u/Economy_Ad9320 24d ago

Everything is the same, only I spent 3 days before I figured out to roll back the version. The maximum memory leak for me occurred on a sheet of 20,000 records when writing to VBA in 1 cell (Cells(x,y)="string" +10Mb !!!). And after processing and exiting the macro, the memory was not cleared.

1

u/gk802 24d ago

Good to know I'm not the only one. If you can, go to the feedback hub, category Apps, sub-category Office-Excel and add your experience to my issue report - title is "Excel 365 Version 2408 Memory Leak".

1

u/Large-Cabinet5496 21d ago

Comment avez-vous fait pour revenir sur l'ancienne version Excel ? De notre côté nous rencontrons les mêmes soucis de mémoire vive / ram depuis cette mise à jour.

2

u/d_artagnan 22d ago

yes - had the same issue. big spreadsheet but previously okay. rolling back to 2407 solves it.

1

u/gk802 21d ago

Please comment on the issue on the feedback hub. So far, only one person has raised the issue.

https://aka.ms/AAs58ci

2

u/d_artagnan 17d ago

Have done

2

u/AdAdventurous9153 22d ago

Same issue here. Rolled back to 2406.

2

u/SealedSeven 21d ago

Having same issues with our reports at work. No issues running the templates until this update. Soon as there's data pulled in, memory leak occurs and bogs down excel.

1

u/gk802 21d ago

Be sure and create an issue ticket with MS or post to the feedback hub.
https://aka.ms/AAs58ci

2

u/NinjaBear95 1 21d ago

Ugh, it's so frustrating when updates break our tools instead of improving them!

1

u/gk802 21d ago

Agree, but I have learned two new Excel skills, now... how to revert to a prior version and how to turn off automatic updates.

1

u/SealedSeven 14d ago

There was an update yesterday, but nothing in the release that would appear to fix this issue. Anyone tried out the update?

1

u/gk802 14d ago

No. I had been looking in the beta/preview channel as well for any notes that appear to relate to this issue and haven't seen anything. I don't plan to enable updates again until I see something definitive.

1

u/SealedSeven 14d ago

That's a shame. Has there been enough attention brought up in those channels of this issue that they are aware / looking into it?

1

u/gk802 14d ago

Good question. I raised a case with support,. In addition, I posted it to the feedback hub, but only one person has posted a supportive comment or similar issue, so I'm not sure how aware they are of the issue or that it is affecting more than a couple of users. Category in the feedback hub is "Apps"/"Office-Excel".

1

u/gk802 14d ago

Update: I received the following email this morning (9/11/24):

Thank you for sending Microsoft feedback. Your input is valuable to us and helps improve our products.

Your feedback directly resulted in improvements to Office and we believe your issue has been addressed in the latest version of Office 365. Thank you! 

2

u/SealedSeven 14d ago

We're looking to give it a try here then. However takes awhile for some computers to see the latest update.

1

u/gk802 14d ago

Let me warn that the version I just updated is 2409 build 18025.20030, which I don't see the release notes for. I updated one win11 machine and it appears to be resolved. Will update a win10 machine next.

2

u/SealedSeven 14d ago

So latest beta build seems to be good.  That's good news that should be a fix soon officially. 

1

u/gk802 14d ago

I'm updating only in the release channel, so it must be official, but the notes are not updated yet.

1

u/SealedSeven 14d ago

I'm only seeing the version available in insiders channel, so hopefully pushed to live release soon. 

(2408 sept 10th update still current channel release shown)

1

u/gk802 14d ago

Added info. The release notes are in the "preview channel", but the updated loaded in the current channel. Notes are unspecific:

Version 2409: September 09

Version 2409 (Build 18025.20030)

  • Various bug and performance fixes.