UDF not recalculating

[tp2468]

Hi All,

I have a UDF that works fine until I make an edit in another workbook. On return to the workbook with the UDF, it is not calculating properly. A press of F9 will force correct calculation. I have the line Application.volatile at the start of the function. If I edit any cell in the workbook with the UDF, the formula will recalculate correctly.
Does anyone have any idea why switching between workbooks causes this issue? Function is shown below if it helps.

Many Thanks

Tom

Function markbookaverage()Application.Volatile
On Error GoTo errorhandler
Dim vol As Single, sum As Single, weight As Single
Dim myrow As Long
Dim desc As String
Dim gradedesc As String
Dim typer As String
vol = Application.WorksheetFunction.CountA(Range("L4:OU4"))
Set Rng = Application.Caller
For i = 1 To vol
gradedesc = Cells(6, 4 * i + 8)
desc = Cells(3, 4 * i + 8)
typer = Cells(6, 4 * i + 8)
If Cells(Rng.row, 4 * i + 11) = "" Then
Else
If Cells(Rng.row, 4 * i + 11).width < 0.1 Then
Else
If typer = "No_Grading" Then
Else
'Only multiples when worktype is 'weighted'
If desc = "Weighted" Then
weight = weight + Cells(41, 4 * i + 11)
sum = sum + Cells(Rng.row, 4 * i + 11) * Cells(41, 4 * i + 11)
Else
weight = weight + 1
sum = sum + Cells(Rng.row, 4 * i + 11)
End If


End If
End If
End If
Next i






markbookaverage = Round(sum / weight, 0) + 0
Application.Calculate
Exit Function
errorhandler:
If Err.Number = 11 Then
markbookaverage = "E"
Exit Function
Else
End If
Resume Next


End Function

 

[Joe4]

First thing, do you have your calculation mode on the worksheet set to Manual or Automatic?

Secondly, you mention "another workbook". Is the workbook where this UDF resides pulling data/information from another workbook?
And is the UDF ultimately using that data?
If so, maybe changes to the other workbook aren't enough to trigger your UDF to run, since no native data in that workbook is actually changing.

What happens if after you update the data in the other workbook, you update any cell in your workbook. Is that enough to trigger the UDFs to update?
Just curious if that does anything.

 

[tp2468]

Thanks for the quick reply Joe,

Calculation mode is set to automatic. No the second book can be any other workbook. The UDF is stored in the same workbook in which it is used. The problems seems to occur most often when I copy and paste some data that is not on the worksheet where the UDF is being used. This can be another sheet in the same workbook or another workbook.
I am not sure how the copy and paste is affecting the calculation of the UDF. For the UDF's to update correctly, I either have to recalculate or edit a cell on the worksheet where the UDF is being used.

Not sure if this helps at all..hopefully

Thanks

 

[Joe4]

If the data you are copying isn't being used by the UDF, why would the values that the UDF returns change?
Is there any relationship between the data being copied and the UDF?

 

[tp2468]

Nope the data being copied has no reference to the udf but for some reason the act of copying is forcing the udf to recalculate to an incorrect value. Cannot get my head around this one...

 

[RoryA]

You've got unqualified ranges in your UDF - Range("L4:OU4") and various Cells calls. These therefore refer to the sheet that is active when the UDF calculates, and since you've made it volatile (which you have to because of the indirect reference to cells) it recalculates based on the other workbook when you change that. Ideally you should pass all the necessary ranges as arguments to the function, then it wouldn't need to be volatile and wouldn't be dependent on what sheet was active.