Excel 2007 Formulas and charts won't update

[Ikester]

I have developed a rather large macro-enabled spreadsheet analyzing radioactive decay. It takes perhaps 5 minutes to recalculate when entering a particular batch of initial isotopes. There are two main worksheets each with 140 columns (time period) and ~5000 rows (isotope emission type) that work in concert to yield # of emissions and energies per time period that are then mapped to charts (# or energies vs time) on another worksheet. Because of the spreadsheet size (~50MB), I have calculation options set to manual.

When I input a particular combination of initial isotope amounts and click on the 'Calculate Now' button, the charts are not reliably updated. I've tested it enough to know there are no circular references (certainly none are indicated and I even tried the use of iterations without success) I find I have to resort to a combination of "Calculate Sheet" and "Calculate Now" actions before the charts are updated properly. Often when I examine the charts during my attempts to have them updated, it seems there's a discontinuity at some point in time (i.e. recalculation stopped part way through the columns).

I've googled for any such problems and while stackoverflow in particular has a thread with many solutions offered to a known Excel shortcoming (bug?) WRT charts failing to update Excel chart won't update - Stack Overflow, I haven't located anything that discusses failure of formulas to update (other than the quite-common inadvertent setting of Calculation Options to Manual and the occasional problem whereby text formatted cell(s) prevent calculation).

I would much appreciate any suggestions as to what I could try including, if necessary, the use of macros and/or custom functions.

 

[ww4612]

i think the problem may caused by the size of your vba module or the memory in empty cells
you can use

activesheet.used range

to release the memory in empty cells to reduce the size of the file.
in addition, you need to create a new vba module, cut and paste the code from the old module to the new module. then delete the old module. the module size increase everything you use the code inside.
if i were you, i will try to simplify the file, remove everything optional. and design hardware friendly code.

 

[Ikester]

Thanks for the quick response ww!

I'm using two vba modules actually. So I should create two new modules, copy code to them, then delete both existing modules?

Also where would I place that command, in each function and/or macro or would that be merely a single command macro run once for each sheet?

Finally, what do you mean by "hardware friendly code"? I have no idea what that entails.

 

[ww4612]

you need to do it with both of your vba module.
you can press ctrl+g to enter single command
for hardware friendly code means create less light code. for example, use with, reduce the number of loop, avoid write macro recoder like code, use .find to replace complete loop and so on. you can post your code here, so we can help you.

 

[Ikester]

Thanks for the tips! I just had to use the activesheet.usedrange instruction on a couple of sheets. I couldn't seem to get the ctrl+g shortcut to work for me (brings up the name manager?) Anyway, I just created a one-line macro and ran it on two sheets. Thereafter when I have the spreadsheet recompute based on a new set of values, all graphs are updated. Strange though. The sheets I shortened, are not involved in the main computational load.

 

[Kurt]

Thanks for the tips! I just had to use the activesheet.usedrange instruction on a couple of sheets. I couldn't seem to get the ctrl+g shortcut to work for me (brings up the name manager?) Anyway, I just created a one-line macro and ran it on two sheets. Thereafter when I have the spreadsheet recompute based on a new set of values, all graphs are updated. Strange though. The sheets I shortened, are not involved in the main computational load.

Can you post or upload your sheet with some of your data to show everyone?

A picture is always worth at least a thousand words.

 

[Ikester]

Hi Kurt,

That wouldn't be practical as the spreadsheet is about 50MB in size. Besides I don't think it has anything to do with my spreadsheet structure, rather it turns out that two worksheets being larger than need be, was somehow preventing complete calculations of the other worksheets.