Good morning all,
When I have a small 15KB XLSX the saving (and calculating first) is fast , done in a split sec
When I have a large 26MB XLSB the saving (and calculating first) is slow , takes couple of minutes
Both makes sense to me.
But when I have the small 15KB XLSX and large 26MB XLSB open the saving (and calculating first) of only the small XLSX still takes very long.
Is this normal or is there a setting I should tweak ?
After rebooting I am unable to replicate the problem.
So for now all seems normal.
Sometimes, some programs can cause a memory leak that can often only be fixed with a reboot. When in doubt, reboot
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I will know how to do it now
Good morning all,
That only lasted a day or so. In the mean time I need to wait five to ten minutes when saving one tiny excel file.Originally Posted by Duncan-
Issue seems to be (as described before) that when two excel files are opened , one huge and one small, it is impossible to save just one file. Excel always wants to save both files.
I tries to use the menu option SAVE as well as the FLOPPYDISK icon.
At the moment I am running:
Capture.jpg
All help will be appreciated.
Last edited by Duncan-; 05-30-2019 at 05:31 AM.
I have never seen that situation before. I have often opened a large excel file, then a smaller file that updates from the larger 1. With no changes made to the larger file (or even if I did make changes there), I could always save/close the smaller 1 without having to save the larger 1
Thanks for the reply FD. Changes are made to both files, but there is no relation / connection between the files, no interlinks, shared imaged, interlinked data or whatsoever. Also the file location is in a different directory.
Would welcome a solution very much.
Did any one else experience this, or was able to replicate the problem ?
Just checking in to see anyone else has this problem ?
In the mean time I have gotten a new laptop and have a fresh install of everything. But the issue still persists :-(
All suggestions to fix this are very welcome!
Just a small bump , perhaps someone with a solution will pass along.
Possibly it has to do with the fact that one fil is set to recalculate manually and the other set to automatically ?
Just a small bump , perhaps someone with a solution will pass along.
Hopefully this help you.
PHP Code:https://trumpexcel.com/suffering-from-slow-excel-spreadsheets/
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Hi AVK
Thanks for that page. Will read it later when I have more time.
But please be aware I am not looking for a faster spreadsheet.
I would guess that your larger file has volatile functions in it. Saving the small file causes it to recalculate before saving. That in turn causes the larger file to recalculate because of the volatile functions (note: it does not save the larger file).
Rory
Hi Rorya,
I have no idea what volatile functions are. So I looked it up:
Volatile functions are functions in which the value changes each time the cell is calculated.
That is rare in the big spread sheet. When I edit or ad information some stuff changes, but 99,99% will stay the same. I am sure it can be done way more efficient, but I am not smart enough for that :-)
Also I do not re-calculate automatically. Even after recalculation 99,99% of the stuff will not change.
My main problem is that when 2 spreadsheets are open (a huge one and a tiny one with only a handful of full cells) the closing/saving of the tiny one triggers an action on the huge one. Causing the closing of the tiny to take minutes. That seems wrong.
I was hoping I can just untick a box somewhere and I will be happy again, but that is apparently not the case :-(
At the moment I regard this as a unintentional effect created by the programmers.. . . . . but I have still hopes :-)
Last edited by Duncan-; 02-27-2023 at 11:07 AM.
I don't know where you got that definition from but it's not a very good one. Volatile functions are ones that recalculate whenever any open workbook calculates, even if none of the function's inputs have actually changed. Common examples are OFFSET, INDIRECT, TODAY, NOW.
Thanks Rorva. I use at least INDIRECT
But that does not explain my problem. Atleast I don't get it.
I click to save one small xlsb and the whole other large xlsb starts recalculating (which is set to manually).
The calculation mode applies to the application as a whole, not to individual workbooks. If the small workbook is recalculated, your INDIRECT formulas in the larger workbook will also recalculate.
Thanks Rorya.
You are the first one in 5 years who thought of that. Is amazing that no one knew this. Thanks again !
I assume there is no way around this ?
It is generally best - especially with large slow workbooks - to avoid volatile functions, so let's start with why are you using INDIRECT?
I am worried that changing the formulas after so many years will jeopardize data integrity. I can only spot check the results, not all the millions of cells.
Main data is a ten digit code.
Hypothetical example . . .
Several digits have a special meaning. For instance digit 4 and 5 is year and 6 is the color. In 2014 the B stands for Glossy Black and in 2015 it stands for Velvet Black.
I use INDERECT in combination with VLOOKUP to check to the year and ten check in a sheet (name same as the year) that has a small simple table what actual color that is.
Example:
58314B8635
Year = 14
Color = B
I look up the B in the small table in sheet named "2014" and find the color is Glossy Black
Results
58314B8635
2014
Glossy Black
IMO that would have been better as one table with an additional year column. Then no need for INDIRECT. But I know from experience it's not always simple to go back and rebuild existing files.
Thanks Rorya
This is a hobby project. So efficiency is not the most important thing. On the top of the list are data integrity and me feeling good about it :-)
FWIW, there is a change coming to 365 to only calculate the active workbook and directly dependent ones but it will be a while before it makes it into general release!
Thanks for the update. Are you testing the new version ?
Will we see this this year ? Before or after the summer ?
It's just rolllng out to Beta users. I don't have it yet and have no idea how long it may take before it is in general release.
At the moment I am using...
Microsoft® Excel® for Microsoft 365 MSO (Version 2305 Build 16.0.16501.20074) 64-bit
.. and the bug is still present.
Update . . . . .
At the moment I am using...
Microsoft® Excel® for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20070) 64-bit
.. and the bug is still present. :-(
To be clear: it is not a bug. The functions are designed to work that way.
For me it is a bug that has a huge impact on system performance for no reason, except easy programming, at all and it makes working with various sized files very cumbersome. I am happy MicroSoft finally recognized this as well and is taking action.
Did you test in in the beta version you mentioned before ?
Any idea if it is still in, or was the feature taken out ?
Last edited by Duncan-; 03-29-2024 at 06:08 AM.
It’s not for no reason. Your INDIRECT function could refer to ranges in a different workbook, so would be wrong if not recalculated every time.
I am not sure I understand. The small file has no indirect at all. As a matter of fact it could be an empty spreadsheet and it still happens.
But the large one does.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks