Topics

Worksheet stuck "calculating" upon fresh open


Evan
 

Hi There,

I've got a moderately complex worksheet which is pulling in data via XLQ from various sources while also maintaining an active DDE bridge to IBKR to manage my portfolio. I've got lots of VBA code behind the sheet but all of those routines require manual kick off before they come alive. 

Starting about a week or two ago upon opening the worksheet for the first time of the day right before the market open, it's getting stuck in a seemingly endless loop with "calculate" flashing endlessly in the bottom left of Excel which then prevents anything else from getting done or from my mouse being able to click on anything (including closing the sheet):


Ultimately it never finishes, I have to brute force task manager close Microsoft Excel, I reopen the worksheet and 90% of the time, it fires right up with no issues. Whatever is clogging the pipe on that initial load that doesn't seem to persist the next time around. 

I did upgrade to Microsoft 10 about 3 weeks ago and I'm wondering if either the environment change has caused issues or if I had previous Excel preferences that I've forgotten about that will suppress the calculate from hanging up the rest of the sheet. 

Does anyone have advice on what could be going on or how to best debug what it's up to when Calculating? 

Thanks! 


Robert Hall
 

 
I can't help you with what the problem may be but you could maybe try opening the spreadsheet with Automatic Calculation turned off and then turn it on after you have opened the sheet to see if that will prevent your calculation loop.  I have a couple of little VBA macros initiated with a button that do this for me since I do not wish updating of my worksheet to take place either before or after the market closes.

Regards,
Rob



From: XLQ@groups.io [mailto:XLQ@groups.io] On Behalf Of Evan
Sent: Wednesday, January 08, 2020 11:18 PM
To: XLQ@groups.io
Subject: [XLQ Users] Worksheet stuck "calculating" upon fresh open

Hi There,

I've got a moderately complex worksheet which is pulling in data via XLQ from various sources while also maintaining an active DDE bridge to IBKR to manage my portfolio. I've got lots of VBA code behind the sheet but all of those routines require manual kick off before they come alive. 

Starting about a week or two ago upon opening the worksheet for the first time of the day right before the market open, it's getting stuck in a seemingly endless loop with "calculate" flashing endlessly in the bottom left of Excel which then prevents anything else from getting done or from my mouse being able to click on anything (including closing the sheet):


Ultimately it never finishes, I have to brute force task manager close Microsoft Excel, I reopen the worksheet and 90% of the time, it fires right up with no issues. Whatever is clogging the pipe on that initial load that doesn't seem to persist the next time around. 

I did upgrade to Microsoft 10 about 3 weeks ago and I'm wondering if either the environment change has caused issues or if I had previous Excel preferences that I've forgotten about that will suppress the calculate from hanging up the rest of the sheet. 

Does anyone have advice on what could be going on or how to best debug what it's up to when Calculating? 

Thanks! 


James Heiman
 

Not sure if this will help but I had a similar type of issue a while back.  I was using a .xlsx spreadsheet.  Leo suggested this which solved my problem:

But there is a simple solution, instead of using xlsx choose save as and save your xls as  a .xlsb and continue using that. It is then stored in binary instead of xml. It not only eliminates the problem, but it is also more efficient.

 Probably you are already using . xlsb but if not that should work. 

Jim



On Thu, Jan 9, 2020, 1:12 AM Evan <evanm@...> wrote:
Hi There,

I've got a moderately complex worksheet which is pulling in data via XLQ from various sources while also maintaining an active DDE bridge to IBKR to manage my portfolio. I've got lots of VBA code behind the sheet but all of those routines require manual kick off before they come alive. 

Starting about a week or two ago upon opening the worksheet for the first time of the day right before the market open, it's getting stuck in a seemingly endless loop with "calculate" flashing endlessly in the bottom left of Excel which then prevents anything else from getting done or from my mouse being able to click on anything (including closing the sheet):


Ultimately it never finishes, I have to brute force task manager close Microsoft Excel, I reopen the worksheet and 90% of the time, it fires right up with no issues. Whatever is clogging the pipe on that initial load that doesn't seem to persist the next time around. 

I did upgrade to Microsoft 10 about 3 weeks ago and I'm wondering if either the environment change has caused issues or if I had previous Excel preferences that I've forgotten about that will suppress the calculate from hanging up the rest of the sheet. 

Does anyone have advice on what could be going on or how to best debug what it's up to when Calculating? 

Thanks! 


Lawrence Steinmeyer <whitewater365@...>
 

Don’t know if this will be of use:

Any chance you changed from 32 to 64 bit excel during the upgrade?   If you google ‘ 32 vs 64 bit excel vba’ it might be informative if you’ve made the change.  

Besides keeping open workbooks to a minimum, you could hit alt+F11, jump into one of the modules, choose a spot in your VBA code and hit F9 so the process stops one it hits the code break.
Run the macro, then repeat, moving the break deeper into the code each time till you find the spot where it’s taking forever.  



On Jan 9, 2020, at 7:14 AM, James Heiman <jrheiman@...> wrote:


Not sure if this will help but I had a similar type of issue a while back.  I was using a .xlsx spreadsheet.  Leo suggested this which solved my problem:

But there is a simple solution, instead of using xlsx choose save as and save your xls as  a .xlsb and continue using that. It is then stored in binary instead of xml. It not only eliminates the problem, but it is also more efficient.

 Probably you are already using . xlsb but if not that should work. 

Jim



On Thu, Jan 9, 2020, 1:12 AM Evan <evanm@...> wrote:
Hi There,

I've got a moderately complex worksheet which is pulling in data via XLQ from various sources while also maintaining an active DDE bridge to IBKR to manage my portfolio. I've got lots of VBA code behind the sheet but all of those routines require manual kick off before they come alive. 

Starting about a week or two ago upon opening the worksheet for the first time of the day right before the market open, it's getting stuck in a seemingly endless loop with "calculate" flashing endlessly in the bottom left of Excel which then prevents anything else from getting done or from my mouse being able to click on anything (including closing the sheet):
<Screen Shot 2020-01-08 at 2.02.04 PM.png>


Ultimately it never finishes, I have to brute force task manager close Microsoft Excel, I reopen the worksheet and 90% of the time, it fires right up with no issues. Whatever is clogging the pipe on that initial load that doesn't seem to persist the next time around. 

I did upgrade to Microsoft 10 about 3 weeks ago and I'm wondering if either the environment change has caused issues or if I had previous Excel preferences that I've forgotten about that will suppress the calculate from hanging up the rest of the sheet. 

Does anyone have advice on what could be going on or how to best debug what it's up to when Calculating? 

Thanks! 


Leo
 

Hi Evan

 

From you description I do not think xlq would be behind the flashing calculate, but if you have no luck and can send me the xls, I can try and monitor it in my debug environment to see if I can spot anything.

 

if you do send it, you can send a modified version as all I would need is the functioning and not contents or values, just ensure the problem still occurs and send it to me directly.

 

How are you opening the xls, if clicking a link, instead try open excel, then open it from within excel.

 

Regards

 

Leo

 


On Wed, Jan 8, 2020 at 10:12 PM, Evan wrote:
Hi There,

I've got a moderately complex worksheet which is pulling in data via XLQ from various sources while also maintaining an active DDE bridge to IBKR to manage my portfolio. I've got lots of VBA code behind the sheet but all of those routines require manual kick off before they come alive. 

Starting about a week or two ago upon opening the worksheet for the first time of the day right before the market open, it's getting stuck in a seemingly endless loop with "calculate" flashing endlessly in the bottom left of Excel which then prevents anything else from getting done or from my mouse being able to click on anything (including closing the sheet):


Ultimately it never finishes, I have to brute force task manager close Microsoft Excel, I reopen the worksheet and 90% of the time, it fires right up with no issues. Whatever is clogging the pipe on that initial load that doesn't seem to persist the next time around. 

I did upgrade to Microsoft 10 about 3 weeks ago and I'm wondering if either the environment change has caused issues or if I had previous Excel preferences that I've forgotten about that will suppress the calculate from hanging up the rest of the sheet. 

Does anyone have advice on what could be going on or how to best debug what it's up to when Calculating? 

Thanks! 


Evan
 

Thanks for the replies everyone. Unfortunately I'm still having the issue but I haven't tried everyone's advice yet, here are the steps taken so far. 

  1. I was running the worksheet from Onedrive cloud location for the occasional remote open when necessary. Moved to local, ran from local, still same issues.
  2. Enabled maximum iteration calculations to 100 for formulas to potentially cap infinite loop issue, same issue. 
  3. I have verified that XLQ taskbar symbol bottom right goes green fairly quick into opening suggesting to me the data lookup finishes fairly quick, which makes sense because there's about 50 active symbols in my stock table.
  4. I do notice the infinite calculate flashing doesn't happen until I make my first click anywhere on the sheet. In other words I open the worksheet, it's seemingly fine, as soon as I click my mouse anywhere (random empty cell) the calculation blinking starts and freezing ultimately occurs. During this period I still have free action over the rest of my computer and programs, task manager only shows about 20% resources to Excel. 

Next steps

  1. Going to set evaluate formula calculations to manual and reopen to see if that solves. 
  2. Going to attempt to find an older version of my worksheet and see if that works, suggesting some of my latest code or formatting is doing this.  

Thanks for offer to debug Leo, I may take you up on that, just trying to work through some obvious things before I take up your time. To your question though, I always launch Microsoft Excel and go through the open process to load the worksheet. 


Bill Holton
 

This always works for me.

I have one formula near the top with TDA specifically mentioned.  When my sheet won’t recalculate I change to Yahoo and it calculates.  When I change back to TDA everything in the sheet pops up.

 

 

From: XLQ@groups.io <XLQ@groups.io> On Behalf Of Evan
Sent: Friday, January 10, 2020 10:13 AM
To: XLQ@groups.io
Subject: Re: [XLQ Users] Worksheet stuck "calculating" upon fresh open

 

Thanks for the replies everyone. Unfortunately I'm still having the issue but I haven't tried everyone's advice yet, here are the steps taken so far. 

  1. I was running the worksheet from Onedrive cloud location for the occasional remote open when necessary. Moved to local, ran from local, still same issues.
  2. Enabled maximum iteration calculations to 100 for formulas to potentially cap infinite loop issue, same issue. 
  3. I have verified that XLQ taskbar symbol bottom right goes green fairly quick into opening suggesting to me the data lookup finishes fairly quick, which makes sense because there's about 50 active symbols in my stock table.
  4. I do notice the infinite calculate flashing doesn't happen until I make my first click anywhere on the sheet. In other words I open the worksheet, it's seemingly fine, as soon as I click my mouse anywhere (random empty cell) the calculation blinking starts and freezing ultimately occurs. During this period I still have free action over the rest of my computer and programs, task manager only shows about 20% resources to Excel. 

Next steps

  1. Going to set evaluate formula calculations to manual and reopen to see if that solves. 
  2. Going to attempt to find an older version of my worksheet and see if that works, suggesting some of my latest code or formatting is doing this.  


Thanks for offer to debug Leo, I may take you up on that, just trying to work through some obvious things before I take up your time. To your question though, I always launch Microsoft Excel and go through the open process to load the worksheet. 


Evan
 

So a quick update, similar to what Robert Hall suggested (thanks by the way), I've included manual calculations = true in my macro that closes down the sheet at the end of the day so when I fire it up the next morning it's not immediately trying to load everything, refresh all formulas, and crunch all of the numbers at the start. 

I let the sheet load and get up and running for a couple of minutes and then turn automatic calculations back on at which point it definitely does chug a bit and usually freezes for about 30 seconds, but then it starts working relatively well for the rest of the session. 

So I guess my sheet has gotten a bit too big (or inefficient) after all. Need to work on slimming things down and optimizing what I can.