CountIf not automatically calculating

Question

My countif function if not calculating automatically - am I missing something?

    Option Explicit

Function my3CountIfs(Rng1 As Range, Criteria1 As String, Rng2 As Range, Criteria2 As String, Rng3 As Range, Criteria3 As String) As Long

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    Select Case ws.Name
        Case "Summary-Sheet", "Notes", "Results", "Instructions", "Template"
            ' do nothing

        Case Else
             my3CountIfs = my3CountIfs + Application.CountIfs(ws.Range(Rng1.Address), Criteria1, ws.Range(Rng2.Address), Criteria2)

    End Select
Next ws

End Function

When I use the following command I have to manually click on it and hit return for it to bring up the updated answer

=my3COUNTIFS(I49,"Yes",I7,"Yes",I1, "Active")

and yes, I have excel set to automatic calculations.

Thanks for your help - giving a presentation on this excel sheet tonight and just discovered its not working correctly! - yikes!

does the cell filled with that UDF modify any value in the ranges you pass to that UDF? furthermore your function has `Rng3` and `Criteria3` arguments but it doesn't use them — user3598756, Jan 20 '17 at 14:08
UDF's NEVER recalculate, unless you have `Application.Volatile` set for that UDF. However - if the UDF is used a lot, this is not advisable, since it will severely impact performance. — Rik Sportel, Jun 05 '17 at 14:40

score 0 · Answer 1 · answered Aug 01 '17 at 14:31

As cyboashu mentioned in the comments, you'll need to put Application.Volatile in the function. But as Rik mentioned, this also can lead to performance issues. This particular post and its answers are relevant to your issue.

Just a summary of the options available to you:

Function xyz()
    Application.Volatile 'or Application.Volatile = True in later versions
    ...
End Function

Or some manual keypresses

F9 Recalculates all worksheets in all open workbooks
Shift+ F9 Recalculates the active worksheet
Ctrl+Alt+ F9 Recalculates all worksheets in all open workbooks (Full recalculation)
Shift + Ctrl+Alt+ F9 Rebuilds the dependency tree and does a full recalculation

Or an option that worked for me (since I only needed to have my UDFs recalculated each time I ran a specific macro) was to use SendKeys like so:

Sub xyz()
    ...
    'Recalculates all formulas by simulating keypresses for {ctrl}{alt}{shift}{F9}
    SendKeys "^%+{F9}"
    ...
End Sub

(Here's the MSDN page for SendKeys)

CountIf not automatically calculating

1 Answers1