Calculate doesn't seem to do anything

[JimmyTheHand]

Hello,

I've got a User Defined Function that has one cell as input parameter, but the result depends on values in the surrounding cells as well. See the uploaded file. Column C has the formulas with my UDF, columns A and B have numbers that are used to calculate the result.

It's not important now, what the function is good for. The only important thing is that Excel recalculates the UDF only if the parameter cell changes. I want Excel to update column C every single time when any of the relevant cells changes. So I use Worksheet_Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:B")) Is Nothing Then Sheets("Munka1").Range("C:C").Calculate
End Sub

This works OK. I mean, Calculate method is executed each time when there's a change in columns A or B. But the recalculation has only visible effect the firts time I change a cell. By all subsequent changes in column A or B, though they initiate execution of Calculate method, column C is not updated... :banghead:

What am I doing wrong? :dunno

Jimmy

[tstom]

See the Volatile Method in VBA help...

Volatile Method

Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

Function xyz2(Cella As Range)
Dim Rng As Range, c As Range

Application.Volatile

'kiindul?si cella
Set Rng = Cella

'keress?k az azonos ?rt?k? cell?kat felefel?
Set c = Cella
Do While (c.Row > 1) And (c = Cella)
Set Rng = Union(Rng, c)
Set c = c.Offset(-1)
Loop

'keress?k az azonos ?rt?k? cell?kat lefel?
Set c = Cella
Do While (c.Row < c.Parent.Rows.Count) And (c = Cella)
Set Rng = Union(Rng, c)
Set c = c.Offset(1)
Loop

'eltoljuk a munkatartom?nyt 1 oszloppal balra
Set Rng = Rng.Offset(, 1)

'v?grehajtjuk a sz?m?t?si m?veletet
xyz2 = 2 * WorksheetFunction.Max(Rng) - WorksheetFunction.Sum(Rng)
End Function

[JimmyTheHand]

Thanks, Tom, for saving the day for me. :thumb

There's a tiny syntax error in your post, which I suggest that you correct, by editing your post, for the sake of those who may want to find a solution to this same problem in the future. In volatile method, True is not a value but an input parameter.
Application.Volatile (True)

I'm half inclined to mark the thread solved, because now I have the workaround. But it only changed my state of mind from desperate to curious. I still want to understand the logic in Excel's calculating/not calculating UDFs. It's one thing that volatile UDFs are recalculated when any calculation occurs on the sheet. But why can't I force it by Application.Calculate method?

Still, I give you full credit for the tip, and thank you once again :yes

Jimmy

[Bob Phillips]

In essence, you have a badly designed UDF. Despite what tstom said, the last thing that you should do is insert Application.Volatile ina a UDF, as it means it will recalculate every time anythin on the spreadsheet changes, not just the cells you are interested in.

If you want the udf to be dependent upon more than one cell, and to recalculate whenever any of those cells change, pass that range as an parameter to to the UDF. You could just add that range as a second argument, but seeing as you use it, it would be better to recut the code.

[JimmyTheHand]

In essence, you have a badly designed UDF. That may be right.



If you want the udf to be dependent upon more than one cell, and to recalculate whenever any of those cells change, pass that range as an parameter to to the UDF. The only problem is that I can't pre-define the range that I should use as a second argument, because the range, itself, depends on the current value of the surrounding cells.
For example, let's see cell C2.
It's primary argument is A2. The function looks up all cells in column A that have the same value as A2, and are in the same contiguous range. In the example, it is Range("A2:A6"). This is offseted to column B, and Range("B2:B6") is the final range that is the base of a simple mathematical calculation.

Now, if I change the value of A6 from 700 to 701, all UDFs in Range("C2:C9") have their dependency range changed. I can't seem to hardcode this. Could you?


...but seeing as you use it, it would be better to recut the code.
What do you mean?

[Bob Phillips]

The only problem is that I can't pre-define the range that I should use as a second argument, because the range, itself, depends on the current value of the surrounding cells.
For example, let's see cell C2.
It's primary argument is A2. The function looks up all cells in column A that have the same value as A2, and are in the same contiguous range. In the example, it is Range("A2:A6"). This is offseted to column B, and Range("B2:B6") is the final range that is the base of a simple mathematical calculation.

Now, if I change the value of A6 from 700 to 701, all UDFs in Range("C2:C9") have their dependency range changed. I can't seem to hardcode this. Could you?

No, you do it smarter, in the code. Pass the entire range and let the code handle it.


What do you mean?



Function xyz3(StartCell As Range, Compare As Range, Target As Range)
Dim cell As Range
Dim rng As Range
Set rng = Target.Cells(StartCell.Row - Compare.Row + 1, Target.Column - Compare.Column)
For Each cell In Compare
If cell.Value = StartCell.Value Then
Set rng = Union(rng, cell.Offset(Target.Row - Compare.Row, Target.Column - Compare.Column))
End If
Next cell
xyz3 = 2 * WorksheetFunction.Max(rng) - WorksheetFunction.Sum(rng)
End Function


=xyz3(A2,$A$2:$A$9,$B$2:$B$9)

[tstom]

the last thing that you should do is insert Application.Volatile ina a UDF

Agreed. If it actually creates a bottleneck. If not, use it. In some cases, such as referring to dynamic named ranges as arguments, you have to use this method.

[Bob Phillips]

As I said, the last thing to do, not a thing never to do.

Here it was totally unnecessary and wasteful.