VBA jumps to a function for no apparent reason ...

[David Evans]

Dim pt As PivotTable
'Application.ScreenUpdating = False
Sheets("Data for Proformas").Activate
Set pt = ActiveSheet.PivotTables("ClientAccounts")
    pt.PivotFields("Account Number").DataRange.Copy
    'Selection.Copy
    'ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Summary").Select
    Range("A9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

The above snippet works fine EXCEPT that having pasted the Pivot Data it heads off to the Function Code below. I've seen this, apparently random , behavior previously but I can't seem to fathom the root cause of it. Of course, it doesn't help that I am a VBA dilettante

Oh and just to confuse things further, if I reset the code and Step Over it, a very similar piece of code following it does not go to the Function thingy ...

Function SumIntervalCols(WorkRng As Range, interval As Integer) As Double


Dim arr As Variant
Dim total As Double
Dim j As Double
total = 0
arr = WorkRng.Value
For j = interval To UBound(arr, 2) Step interval
    If IsNumeric(arr(1, j)) Then total = total + arr(1, j)
Next
SumIntervalCols = total
End Function

 

[Colin Legg]

If your SumIntervalCols() function is being used in formula(s) and th(os)e formula(s) it is used in has a dependency on the range changed by your code snippet (somewhere around Summary!A9) then it will cause th(os)e formula(s) to recalculate and your function to be called.

 

[David Evans]

If your SumIntervalCols() function is being used in formula(s) and th(os)e formula(s) it is used in has a dependency on the range changed by your code snippet (somewhere around Summary!A9) then it will cause th(os)e formula(s) to recalculate and your function to be called.

Correct Sir!

I get around it by turning off the Recalculation - it's a pain as you stepping through the code as there's lots of those functions to calculate.

Appreciate your help!