I am trying to understand how OnlyOffice manages memory during a long macro execution process. I have a macro that is about 1400 lines long and it gets about 60% through the execution and then it throws up an error about being out of memory. Is there a way to clear the unused memory during the execution of the macro to prevent running into this scenario?
This workbook has 32 worksheets, so it is a large workbook.
Periodically throughout the macro, I am running this function so that the spreadsheet shows the values that are inputted into the cells, but am guessing that there is a better way to go about this.
//Recalculate whole workbook again with all changes function
function RecalculateWorkbook() {
Api.asc_calculate(Asc.c_oAscCalculateType.All);
}
Hello @qualm
Please clarify your PC hardware specifications.
As for the request itself, is it possible to provide the test file and the macro themselves, so we can take a closer look at them?
Great questions. The computer I am using is a Macbook Air M2 2022 with 8GB of memory running Sequoia 15.3.2.
I have attached a test file that demonstrates an example of a very similar 1500+ line macro that is working without errors, but my actual spreadsheet is still not working and I am trying to replicate my errors with this file. The only large differences are that there is a lot more formatting of cells and formulas in the actual file and instead of the 1 - 28 being displayed during the macro execution, it shows emojis for food.
While I keep on trying to determine how to replicate the memory errors with this file that represents a fictitious farm with 28 barns having inventory of feed, I thought I would provide it and see if there is a better way to write the macro code to be more efficient. LongMacroMemoryTest 3.xlsx (96.9 KB)
@Alexandre After the latest update, 9.0.3, my actual spreadsheet now completes the macro without memory issues on the Mac, but when using OnlyOffice Cloud, it completed the first time and then ran out of memory the second time, but it is still using a lot of memory. Any better ways to write this macro code so that it uses less memory or manages the memory better?
@qualm thank you for the file! It’s really awesome macro!
And sorry for the late reply.
but when using OnlyOffice Cloud, it completed the first time and then ran out of memory the second time, but it is still using a lot of memory
How exactly did you test it? I’ve uploaded the file to my DocSpace Cloud and used a web browser to work with it, and everything seems OK. If it’s possible to record a video file with your demonstration of the memory issue, it would be appreciated.
@Alexandre Thank you for your reply. I was able to get that macro working without memory errors when I eliminated the copying of the cell range every time and instead used an array of values. So, that problem is solved. Let me know if you want me to start a new thread, but now what I am seeing is an excessive amount of time saving a complex workbook like this one. On my Mac, it takes about 20 seconds after running the macro to save the file, and on OnlyOffice Cloud, it is taking about double that amount of time even though it appears to save the file after every change.
Go to the Multiple Entry worksheet either using the tabs at the bottom or the link on the top of every worksheet, click on the Multiple Entry button to run that macro. Then, after the macro has run completely, click on the save button. On my Mac, this takes 20 seconds running locally and the same process takes 40 seconds on OnlyOffice Cloud even though it is making the same 28 changes that it made in my previously uploaded workbook. What is causing the delay with saving the workbook now? I added a lot more rows to each of the 28 tabs and some more complex formatting.
@Alexandre Here is an updated version of the same file that still takes about 20 seconds to save on my Mac locally and this time it takes about 55 seconds to save on OnlyOffice Cloud. I measure this by running the macro and then after the macro has ran, I click on the Save button again. In the web browser, the text “Saving spreadsheet” appears in the lower right hand corner of the screen and remains that way for 55 seconds on OnlyOffice Cloud. Why does it take so long to save the file?