Custom Functions get #VALUE! results

[Bandha]

I have tables using a custom function on several sheets of the same format. The function works fine except for calculation issues. I've added application.volatile to the function and each sheet will update a few seconds after it's opened, but the other sheets then slip back to back to #VALUE! results in the cells when I move to any other tab.

There is a metrics summary sheet which drills through each other tab to summarise results , ie sum() & average(), so it always ends up showing #VALUE! as all sheets except the last one viewed will revert to #VALUE!. (It's a bit like the light in the fridge, but I'm pretty sure this is what is happening.)

Can anyone offer a solution which will keep my custom function results calculated and available on all sheets?

Thanks,
Carl

 

[Makrini]

You kind of need to post the code to your functions so we can help

 

[Bandha]

I don't think it's to do with my code but more to do with Excel's behaviour, but here goes:

'Count +ve (days overdue) or otherwise (early or on time) instances in countif range '''''''''''''''''''''''''''''''''''''''
Function CountIfIf(CriteriaRange As Range, Criteria As Variant, CountRange As Range, PosNegZeroNotpos As String) As Integer
Dim c1 As Range, c2 As Range
Dim Total As Integer, RangeCounter As Integer
Dim StartupActiveRange As Range

    Application.Volatile    
    Total = 0: RangeCounter = 0
    PosNegZeroNotpos = LCase(PosNegZeroNotpos)  '''type of metrics to count
    
    For Each c1 In CriteriaRange
        RangeCounter = RangeCounter + 1     'next cell in the range we're checking against criteria
        If c1.Value = Criteria Then
            Set c2 = CountRange.Cells(1, RangeCounter)  'matching cell in the range we're counting
            
            If Not IsEmpty(c2) Then
                Select Case PosNegZeroNotpos
                    Case "pos"
                        If c2.Value > 0 Then Total = Total + 1
                    Case "neg"
                        If c2.Value < 0 Then Total = Total + 1
                    Case "zero"
                        If c2.Value = 0 Then Total = Total + 1
                    Case "notpos"
                        If c2.Value <= 0 Then Total = Total + 1
                    Case Else
                        MsgBox "Invalid last parameter. Use one of 'Pos', 'Neg' or 'Zero'", vbCritical, "Error"
                End Select
            End If
        End If
    Next c1
    ''--------------------------------------
    CountIfIf = Total
End Function

Essentially it is like a SumIf() double criteria for CountIf() but only counts instances of either positive numbers, or zeros, -'ve or anything non-positive.