color function causes save dialog?

[Michael Purcell]

I found Chris Pearson's CountByColor function which lets me do exactly
what I want to do. I'm using the function in Excel 2000 on Win98SE.
When I call the function on a worksheet, it causes the save dialog to
appear when the workbook closes, even though there is no new data to
save (for example, open and immediately close the workbook). The save
dialog does not appear when the function call is removed from the
worksheet.

I created a module in the workbook and copied the following code to
the module:

Function CountByColor(InRange As Range, WhatColorIndex _
As Integer, Optional OfText As Boolean = False) As Long
'
' This function returns the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

It doesn't make sense to me that this function will cause this
problem. Appearantly, a flag is getting set somewhere, but I don't
know what to do to solve the problem so that I can use the code.

TIA for your help,
Michael Purcell

 

[Chip Pearson]

Michael,

The reason that the Save dialog is display is that the function
is volatile; that is, it is called whenever a calculation occurs.
This marks that cell as changed, even if the result doesn't
change. Because the cell has been changed, as far as Excel is
concerned, the file is marked as needing to be save. Thus, the
save dialog is displayed when you close the file.

By the way, my name is Chip, not Chris.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Michael Purcell]

Chip,

Thanks for the speedy answer, and sorry for my poor memory.

I thought I tested that by commenting out that line, but I must have
not compiled the modified code. Shows how a casual VBA'er can be
dangerous.

Is there a way to test if the data has changed and mark the cell back
to it's unchanged state, before the function returns, if there was no
change in the data? I think I could do something like that in Delphi,
but I don't know VB and Excel well enough. That save dialog is sure
annoying to the user!

Thanks,
Michael Purcell