VBA ~20x slower after saving; closing; reopening file

**easperhe** · 09-24-2018, 08:28 PM

All,

I've tried a million things and I can't figure this one out. I have VBA code that processes input data (usually around 100 rows of data); creates new columns of data based on input data; runs the excel solver; and creates some additional columns of data after solver runs. When I run the VBA macro the first time, it runs in ~5 seconds. But if I save the .xlsm file; reopen and run the EXACT same VBA on the EXACT same data; it now takes ~60 seconds, i.e. 20x slower. And, this behavior only occurs on a small subset of input files. This seems like a bug. But, I can't figure out how to fix or work-around.

This occurs for multiple users on this file (so not my machine or profile). And, I've tried both excel 2013 and 2016 with similar performance degradation results.

Any idea what is causing this or how to debug? I was wondering if there are some good VBA profiling tools that might help. Thanks!

**alansidman** · 09-24-2018, 09:35 PM

Show us your code for analysis. After reviewing, there may be some suggestions.

**MarvinP** · 09-24-2018, 09:48 PM

Hi easperhe and welcome to the forum,

Are you saving this file on OneDrive? If you are then try to save it on your desktop to see if the same problem occurs. If you are saving on OneDrive, it might be trying to save changes between the two versions?

**easperhe** · 09-24-2018, 09:50 PM

All,

Wow - this is getting strange. I've boiled the problem down to the bold statement below. When the code runs in a normal amount of time (5s), writing the "fpcideal" value to the cell takes .06 seconds for 100 iterations of the loop. When the code runs slow, 100 iterations takes 47 seconds - a ~800x increase in time.

Plus - the "trace dependents" command on the "fpcideal" cell works correctly when code runs in normal time and doesn't work in the slow case - EVEN THOUGH OTHER CELLS STILL DEPEND ON THE "fpcideal" CELL. So this must be related to the slow down. Any idea why "trace dependents" could get broken??

Please Login or Register  to view this content.

**easperhe** · 09-24-2018, 09:52 PM

MarvinP,

Very interesting question about OneDrive as a file explorer window pointing to OneDrive and asking me to setup OneDrive will open when I'm running VBA code. It seems to be related to me running VBA code but I don't use OneDrive.

**easperhe** · 09-24-2018, 10:05 PM

MarvinP, All,

I uninstalled OneDrive, rebooted, and reran testcase and still have the same slow VBA code issue. So, it doesn't appear related to OneDrive. But, I'm pretty convinced that whatever is causing "trace dependents" to not work is the culprit.

**alansidman** · 09-24-2018, 10:16 PM

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

**MarvinP** · 09-24-2018, 10:38 PM

So your claim is that this line is the slow one?

Please Login or Register to view this content.

The "Cells(r,c)" should have r be the row and c be the column you want to put the value of fpcIdeal in.

What does columnDict("Model Data " & "Param Values" evaluate to? Is it a named range? Is it on another sheet? Why do you need to add zero to the row value?

**easperhe** · 09-24-2018, 11:05 PM

MarvinP, All,

Yes - that single line takes ~800x longer to execute when I have the performance issue. The columnDict() evalues to an integer. And the "ipcParam + 0" is just for my visual clarity as I have "ipcParam + 1" later. So, you can ignore the " + 0" there.

But as I slog through this issue, the underlying problem is not related to trace dependents issue after all. though I still want to figure that out, the performance problem is related to a function I've created to calculate a cell value in a particular column of data. All the cells in this one column have this value:

Please Login or Register  to view this content.

If I replace this function with a constant value, e.g. = 1, then I don't get the slowness issue. Now that I've gotten here, I've seen issues with this function in that randomly (or at least I can't figure out why) this function will not evaluate.

Any ideas why a function call in a column of cell values would cause severe slow down in one excel .xlsm workbook and not another?

**MarvinP** · 09-24-2018, 11:40 PM

If I understand what you are doing...

I'd take the function call out of the Cells() parameters and do it in a line above. I'd assign a value to the function call and then put this variable into the Cells().

IE:

instead of doing
Cells(fcnrow(a,b), fcncol(c,d))

try something like
Dim FuncRow as double
Dim FuncCol as double
FuncRow = fcnrow(a,b)
FuncCol = fcncol(c,d)
Cells(FuncRow,FuncCol)….

See if that makes sense..

VBA ~20x slower after saving; closing; reopening file

LinkBack

Thread Tools

Rate This Thread

Display

VBA ~20x slower after saving; closing; reopening file

Re: VBA ~20x slower after saving; closing; reopening file

Re: VBA ~20x slower after saving; closing; reopening file

Re: VBA ~20x slower after saving; closing; reopening file

Re: VBA ~20x slower after saving; closing; reopening file

Re: VBA ~20x slower after saving; closing; reopening file

Re: VBA ~20x slower after saving; closing; reopening file

Re: VBA ~20x slower after saving; closing; reopening file

Re: VBA ~20x slower after saving; closing; reopening file

Re: VBA ~20x slower after saving; closing; reopening file

Thread Information

Users Browsing this Thread

Similar Threads

Excel 2013 VBA - Operning CSV file in loop becomes slower and slower

[SOLVED] Items missing after closing and reopening.

Data validation drop down list doesn't work after saving closing and reopening workbook.

Speed changes after closing and reopening workbook

Closing Files and Reopening them in VBA

My comments reformat after closing and reopening.

macro statement will not run a second time without closing and reopening

Bookmarks

Bookmarks

Posting Permissions