Excel 2016: xlsm files slow to save

**jkotsonas** · 08-21-2019, 06:01 PM

Hello,
Hopefully I'm posting this in the correct sub forum.

Have a User here at the office I support who all of the sudden is having issues saving
.xlsm files.

Regular excel files are having no issues, but any with the xlsm extension either crash
the program, or can take 30 + seconds to save (during which excel is unusable).

The user has a brand new laptop (Lenovo T480s) which is freshly imaged.
No other users are having this problem.
Problem just started a day ago.
User is on Office 2016 - Office 365 Pro Plus to be specific.
The files are located on a share drive via our server, and are not local
on the laptop.

I've had other users experience issues with these xlsm files, and from another forum found
that having their default printer set to one of our Fiery (EFI) printers was the problem - something
involving the postcript driver on the printer. Changing the default printer to a non-Fiery/EFI printer
resolved their issues, but not this time.

I tried updating the macro settings in excel to "enable all macros", but that didn't resolve
the issue.

Not terribly familiar with xlsm files, so not sure what else to try, other than running a repair
on Office, so hoping maybe someone here has a better idea.

If any additional info is needed, please let me know.

Thanks!!

Jim

**TMS** · 08-21-2019, 06:08 PM

Do NOT update the macro settings in excel to "enable all macros".

Do other users access the same macro enabled workbooks? Or are they specific to this user? If others use the same files, do they experience the same problems?

**jkotsonas** · 08-22-2019, 01:08 PM

Hi Trevor,
Thanks much for your reply.

To answer your question, plus some additional info...

The specific doc I've been troubleshooting is used by multiple users here, but it is not set up to share (the user is
experiencing this issue with other xlsm docs as well).

Other Users have not yet reported as having this issue, but I accessed the doc from my computer, updated
it and saved, and it too hung for 30 seconds or so before the save completed.

I copied the doc from the share location to my desktop, made an update and then saved, and the same thing - it took
a good 20-30 seconds before it completed the save.

The user who reported this also made a copy to his local desktop, and when he goes to save an update, it takes
the 20-30 seconds to complete.

If we save the doc as an xlsx file, make an update and then save, it works correctly. We've done this on the user's pc,
my pc and on the share drive.

The issue occurs if the Trust Center > Macro setting is set to "disable all macros with notification", or "enable all macros..."

The specific doc we've been testing with is used as a template. The dept. uses an app called Argus, and data is copied
from Argus and pasted into the xlsm doc. No importing of data from Argus to the doc occurs, just copy/paste.

The user is on Windows 10

If you need any further info, please let me know, and thanks again!

**TMS** · 08-22-2019, 05:27 PM

Ok, that's really helpful. It doesn't tell us what it is exactly but it tells us what it isn’t.

You have experienced the problem, so it isn’t just the one user. Others may not use the file as much, not notice, or not care.

You have both tried it on your desktop and the shared location so it isn't the location or the network causing the issue.

Saving the file as an .xslx file removes the problem so it isn't related to formulae ... multiple, complex, array, full column references, etc. Anything that might affect performance eliminated.

Which, as far as I can see, just leaves the code. So, do you have a Calculation event handler? Do you have a Before Close event handler that might generate a loop? Any Worksheet Change event handlers?

When you say, "all of a sudden", does that imply that it hasn't always done this? If so, has anything changed? Structure of the worksheets? Number of worksheets? Volume of data ... rows, columns, whatever?

Can you upload a sanitised version of the workbook?

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**jkotsonas** · 08-22-2019, 05:47 PM

Thanks again Trevor.

re: "Calculation event handler? Do you have a Before Close event handler that might generate a loop? Any Worksheet Change event handlers"

I'm checking with the user, but regarding the above, are these scripts, or codes that are created w/in excel that run and collect data, or are they
apps that you can scan an excel doc with? I'm googling :-) and it looks like it's more a script or code, but not being at all familiar with this, figure
I'd ask.

**TMS** · 08-22-2019, 05:30 PM

After thought: could the output from Argus have changed?

**TMS** · 08-22-2019, 06:21 PM

Ok, VBA is Visual Basic for Applications and is an inbuilt programming language for Office Applications.

Users can record macros to faithfully repeat keystrokes. However a recorded macro is rigid and inefficient and usually needs to be edited to make it more generic, flexible and efficient. Macros are also referred to as Subroutines.

There are numerous "event handlers" that can be adapted to process specific events that might occur. The "Before Close" event handler determines what happens when the workbook is closed. Similarly, "Before Save" caters for when the workbook is saved. The "Calculate" event handler is fired when Excel decides to recalculate the workbook. Google: excel vba event handler.

To take this discussion forward, I would need to see all the code and, ideally, have access to a sample workbook.

**jkotsonas** · 08-26-2019, 11:16 AM

Hello again.
Sorry for the belated update - was out sick on Friday.

So it doesn't appear I will be able to upload the file. There's too much proprietary info in it. I'm checking to see if there's another
file (he's having this issue with most of the xlsm files) we could use.

The issue started last Tuesday evening. Tuesday work day the files were fine, then Weds they started having the issue.

The user is unable to relay who was the last person to work on that file, or if the data output for the Argus app has changed.

I think they have macros created for this file. I've attached a screenshot. Last week I stumbled onto what appeared to be
more specific macros created - one of which is pwd protected, but I can't locate that now.

I'm thinking of making a copy and then restoring the file to a prior version, a date before the issue started to see if the issue
goes away, which may narrow it down some.