Sleeper: When references stop... referencing

[Shrout1]

I have a whole bunch of formulas scattered througout my spreadsheet that all reference back to a single page where VBA dumps its data. For some reason, when I clear this page out (replace all the data with Zeros) and then refresh it, Excel stops tracking the changes.

If I change the existing data to different data, I don't have the problem - trouble is that I'm trying to build a template and I can't have any residual information. Any thoughts?

Thanks!

[Zack Barresse]

Hi Shrout,

What do you mean by 'refresh it'? Do you mean perform a Calculate event? Can you upload a zipped copy of your spreadsheet? (If sensitive data, only post what is necessary.)

[Shrout1]

Perhaps it's calculate - I'll try to make an example:

Sheet 1 Cell A1 references to Sheet 2 Cell A1.

Sheet2's value = 5, so Sheet1's value = 5.

If Sheet 2's value is erased then Sheet1 should = 0

I change Sheet2's value to 0.

I now change Sheet2's value to 7.

Sheet1 still says that Sheet 2 Cell A1 = 0.

If I click inside of the cell, click the cursor on the formula (making no changes) and then press enter, the cell will now update. The term may be "calculate" or perhaps "validate" but it definitely does neither. Someone suggested some form of a validation routine at one point, but it didn't help.

As for the sensitivity of this project, I don't really know and therefore it may be better to keep public access limited (I don't have any security clearances, but it is for the U.S. Government, so...)

[Zack Barresse]

Well if it's for the Government .. good call. :)

I'd check your calculation .. Tools -> Options -> Calculation (tab), ensure Automatic is checked.

[Shrout1]

This is odd - all the options were checked off, but for some reason when I went into the calculation tab and clicked "Calc Now" it updated everything. Funny thing about this is that I already have the options set the way you recommended...

[Shrout1]

Is there any way to execute that "calc now" button through VBA?

Thanks!

[Jacob Hilderbrand]

Is there any way to execute that "calc now" button through VBA?

Thanks!


Sheet1.Calculate

Or


Sheets("Sheet1").Calculate

[Shrout1]

Could one say "ActiveWorkbook.Calculate"?

[edit] I just coded this into the program:


i = 0
For Each Sheet In ActiveWorkbook.Sheets
i = i + 1
Sheets(i).Calculate
Next


For some reason, the references still don't update...

[edit again] On top of that, it looks like the "Calc Now" button (in options) is having absolutely no effect.

[Zack Barresse]

That's right, you can do it something like this ...


Sub calcSheet()
ActiveSheet.Calculate
End Sub

Sub calcBook()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Calculate
Next ws
End Sub

[Shrout1]

Well - this is weird. If I save the file, there's no effect. If I "Save As" the file, a new filename is given and all the values update.

This isn't the perfect solution, however: The values only change from 0 when you do a "Save As". Now, on the positive side, once the values have been updated (i.e. no longer Zero) they will continue to reflect any updates the user makes. This means, though, that the first time you run it you must perform a "Save As" in order to see the values.

This is weird. Any idea why? And also, might this little note have anything to do with it?

http://img.photobucket.com/albums/v34/Shrout1/ProgrammingStuff/CCBWoValue.jpg

[Zack Barresse]

So is this an actual cell, or an object? Did you try the full calculate? Can you post an example of your spreadsheet? (Must be zipped)

[rcbricker]

this might be really simplistic but since it has already been suggested to go to Options to set the calc to auto...

have you tried hitting F9 whenever it does recalculate? also I have found that if i do a reference and change a value that sometimes I have to be on the origin sheet and hit F9 to get it to calculate the formula. Not sure why new to all this but thought i would share.