3

I am working on a "lesson" to help users understand how certain practices can unnecessarily flag Excel cells for calculation.

I am aware that the Dirty method can be used to flag a cell for calculation in VBA. Is there a way to determine whether a particular cell is flagged for calculation (in VBA)?

I want to be able to loop through cells in a sheet and highlight that cells, which need recalculation. I want users to be able to visually check how many dirty cells were created due to an action.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Ejaz Ahmed
  • 598
  • 7
  • 13
  • ' Force a recalculation of range A3. ` Application.Range("A3").Dirty ` MsgBox "Try to close the file without saving and a dialog box will appear." – skkakkar Feb 04 '19 at 13:39
  • ?ThisWorkbook.Saved This will return True if it has not been changed and False if it has. – skkakkar Feb 04 '19 at 13:45
  • 1
    @skkakkar These both won't let you find out **which** cells have to be calculated. – Pᴇʜ Feb 04 '19 at 13:47
  • @PEH there is a reference to identify dirty cell here – skkakkar Feb 04 '19 at 13:53
  • @skkakkar The `Worksheet_Change` can only determine which cell constants changed but not which cell formulas would change on re-calculation. So that won't give you the dirty cells but only the cells where the value (constant) changed. – Pᴇʜ Feb 04 '19 at 13:56

1 Answers1

1

I think there is no way to determine which cells are currently dirty.

But you could do a trick (even if it is not exactly what you were looking for, but I think the closest you can get):

Read the range that you want to check for dirty cells into an array (don't perform this on the whole sheet or you might run out of time!) …

Dim RngToCheck() As Variant
RngToCheck = Range("A1:B10").Value

then calculate the sheet, and then compare the re-calculated sheet against the values in the array. The changed values were dirty before the calculation.

Note that there is no possibility to revert to the state before calculation. So you can only determine which cells were dirty before they got re-calculated.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • What if the value of the cell did not change after the recalculation? I need to know whether or not it was calculated irrespective of whether the values changed or not. – Ejaz Ahmed Feb 12 '19 at 08:11
  • 1
    I told you *"not exactly what you were looking for, but I think the closest you can get"*, sorry that feature is just not implemented. Actually Excel should not re-calculate a cell if no one of the cells it depends on changed value (besides volitle functions or forced calculation of course). • Besides showing for educational purpose, I actually don't see any practical use for that feature, so that's maybe why it is not built in. • If you plan a "lesson" just write a demo to show the principle that would probably be sufficient to understand. – Pᴇʜ Feb 12 '19 at 08:18