Excel - force recalculation of function

Asked By Valeria on 28-Apr-10 07:38 AM
Daer experts,
I have a UDF which I would like to force to recalculate for every input.
I have a sheet with a Worksheet_Change macro to make all user inputs on a
range red in color; and the UDF which is put in a nearby column should
immediately recalculate to show "Filter for Changes".
What happens today with my code is that the recalculation has a 1 input
delay: that is to say, I make my input, nothing happens, I enter a second
input and here the formula recalculates on the 1st input
Does this have something to do with the worksheet_Change event?
Here is my code:

Function FilterForRed(c As Range) As String
Application.Volatile True
If Worksheets("Sheet1").Cells(c.Row, 19).Font.ColorIndex = 3 Or
Worksheets("Sheet1").Cells(c.Row, 21).Font.ColorIndex = 3 _
Or Worksheets("Sheet1").Cells(c.Row, 23).Font.ColorIndex = 3 Then
FilterForRed = "Filter For Changes"
Else
FilterForRed = ""
End If
End Function


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 18 And Target.Column < 24 Then
Target.Font.ColorIndex = 3
End If
End Sub

Thank you very much in advance for your help!
Kind regards
--
Valeria




John Bundy replied to Valeria on 28-Apr-10 08:47 AM
This seems to work fine for me. Is your function in a module and your
Worksheet Change event on the worksheet? If so, can you give an example of
what you are putting in the cells and in which order?
--
-John http://www.jmbundy.blogspot.com/
Please rate when your question is answered to help us and others know what
is helpful.
Valeria replied to John Bundy on 28-Apr-10 09:35 AM
Hi John,
yes, the function is in a module and my Worksheet Change event on the
worksheet.

I am putting numebrs on the cells and the order is random - it is a volume
forecast.
I have 3 columns (19, 21 and 23) corresponding to 3 months and the sales
people put in their forecast on the row correpsonding to their customers
(therefore it is a random input). The colums are already pre-filled with the
historical plan, this is why I want it to become red when something is
changed, so that we have a trace for the change.

Thanks!
Kind regards



--
Valeria
Valeria replied to John Bundy on 28-Apr-10 10:51 AM
By doing it over and over again i have the impression that the first thing
the code does is to calculate the function (so when you type in the number it
is black in the first step), so the function returns "". Then the worksheet
code is activated so that the number becomes red, but it so no longer
recognized by the function. At the second time I type something in, then the
function recognizes the first number which has ebcome red but not second one
etc.

Is there a way to put an action priority to fucntion & worksheet code? First
the code, then the fucntion?
Thanks!
--
Valeria
Dave Peterson replied to Valeria on 28-Apr-10 01:52 PM
Just a curiosity question...

Is there a reason you do not just add code to the worksheet_change event to put
that string in whatever cell you want it in?

====

When you (or your macro) change the format of a cell, there is nothing that
notifies excel that it should recalculate.

Even the application.volatile instruction will not help.  That just tells excel
that the next time it is doing a recalculation to include the cells with this
function.

An alternative (I would not do this!) would be to force a recalc in your _change
event.


application.recalculate '<-- added

I'd drop the UDF completely and use something like:

But depending on how long the recalc takes, this could drive you batty!

=============
I am kind of confused over why you are not changing the function for all the cells
in S:W and I do not know what cell gets that warning message.  And I do not know
how you clear the colors and that warning message.  (that is a lot!)

But this may be a way you want to look at to see if it is worth pursuing.  (I
used column Z as the indicator column.)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myIntersect As Range
Dim myCell As Range
Dim RngToCheckForChanges As Range
Dim RngToCheckForResetting As Range

Set RngToCheckForChanges = Me.Range("S:W")
Set RngToCheckForResetting = Me.Range("Z:Z")

Set myIntersect = Intersect(Target, RngToCheckForChanges)
If Not (myIntersect Is Nothing) Then
'in columns S:W
For Each myCell In myIntersect.Cells
myCell.Font.ColorIndex = 3
'I do not know why you are only checking columns S, U, W,
'but you are!
Select Case myCell.Column
Case Is = 19, 21, 23
'stop the macro change from firing the _change event
Application.EnableEvents = False
Me.Cells(myCell.Row, "Z").Value = "Filter For Changes"
Application.EnableEvents = True
End Select
Next myCell
Else
Set myIntersect = Intersect(Target, RngToCheckForResetting)
If Not (myIntersect Is Nothing) Then
'in column Z
For Each myCell In myIntersect.Cells
If myCell.Value = "" Then
'it is been cleared, so reset the colors
Intersect(myCell.EntireRow, RngToCheckForChanges) _
.Font.ColorIndex = xlAutomatic
End If
Next myCell
End If
End If

End Sub
Valeria replied to Dave Peterson on 29-Apr-10 06:21 AM
the only reason being that I had not thought about it - very nice and simple,
many thanks!
--
Valeria