Problem with counting multiple parameters in VBA

[sirDexter]

Hello,

I've the following problem. I use the following code.

Function SumAll(ParamArray var() As Variant) As Integer
    For i = 0 To UBound(var)
        MsgBox var(i)
    Next
End Function

When I execute this function =SumAll(4,3,2) it displays the following messages:4,3,2. This is fine, but when I add comment

Function SumAll(ParamArray var() As Variant) As Integer
    'test
    For i = 0 To UBound(var)
        MsgBox var(i)
    Next
End Function

I get the following messages: 4,3,2,1,26,3,4,5,6,6,1,3,5,4

Then I execute the same function one more time and I get: 4,3,2.

If I change or delete comment, I get: 4,3,2,1,26,3,4,5,6,6,1,3,5,4.
Execute one more time and I get: 4,3,2.

I use Excel 2013.

Thanks.

 

[RickXL]

Hi,

How are you executing the function?

My guess would be that you have one cell with =SumAll(4,3,2) in it and other cells with other numbers in. When you add the comment to the macro - or make any change to the macro - when you recalculate then every instance will be recalculated.

Excel does that because it likes to make sure that all the formulas reflect the changes.

I changed the MsgBox to a Debug.Print because I get fed up pressing the Enter key. You need to be looking at the Immediate Window in the VB Editor to see the results. I also added a line to display the calling address so you could see which cell was causing which response:

Function SumAll(ParamArray var() As Variant) As Integer
    'Test
    Debug.Print Application.Caller.Address
    For i = 0 To UBound(var)
        Debug.Print Now, var(i)
    Next
End Function

The addition of "Now" means that I get a log of which recalculate caused which results.
I had =SumAll(9,8,7,6) in D3 and =SumAll(1,2,3) in C3.
A sample of my log is below:
$D$3
17/08/2015 12:45:01 9
17/08/2015 12:45:01 8
17/08/2015 12:45:01 7
17/08/2015 12:45:01 6
$C$3
17/08/2015 12:45:01 1
17/08/2015 12:45:01 2
17/08/2015 12:45:01 3
$D$3
17/08/2015 12:51:59 9
17/08/2015 12:51:59 8
17/08/2015 12:51:59 7
17/08/2015 12:51:59 6
$C$3
17/08/2015 12:51:59 1
17/08/2015 12:51:59 2
17/08/2015 12:51:59 3