REALLY WEIRD problem

[Zaphodski]

I don't know how to look this up it's so weird - at least for me:

I have a 19 Mb .xls file which works fine. Lots of financial calcs for 3 years into the future. I copied it and reduced it to 1 year to lighten the load on LAN & systems(for sharing).

It contains a macro that ends with Application.CalculateFull and runs on open. It works and has always worked without problem. In the daughter (smaller) file, which now has some different data content (but still structurally identical) any second attempt to recalculate results in uniform error return "#VALUE!" in each cell.

This happens when I add new data, for example. What gets me though is if I then save it whilst it displays the "#VALUE!" message, close it and re-open it I get the correct values that I always used to get.

I have identified the function this appears related to, but can't figure out what is going on. Any ideas please?

 

[Actuariojf]

I am not an expert in Excel, howevcer sometimes this kind of problems happen when you have Circular References. Check if you have one.

I hope it helps!

Thanks!

 

[Zaphodski]

Thanks,

But that's not it (

I think it is more likely some system (memory?) conflict with something deep inside excel - since rebooting the problem is less frequent, but still occurs. There is NOTHING fundamentally wrong with the data or the structure - which is why the 'correct' answers come out in the end. It is temperamental; as if Excel has a fuzzy dision process!

 

[applebyd]

What are you getting if you run a trace error
from the auditing tool bar.

That at least will show you where the Error comes from.

HTH
DaveA

 

[Zaphodski]

Thanks, it settles on a function I wrote - ) obvious cause I'd think - but the same function has worked for about a year and I haven't altered the code or any of the values it loads since. The same function also works fine (as does the whole workbook) when restarted after Excel is closed down. I have copied the code to a .txt file and re-inserted it which does seem to have improved things a bit. I am increasingly suspicious this is more a local machine problem than an Excel issue, something is conflicting in memory (TSR?). Thanks for all the advice.