CODEWITHSUNDEEP
excelcircular-referencevba
Stratum
Stratum

Reputation: 23

vba circular reference error in function

I'm trying myself on vba with little success. I would like to achieve a simple function that sums the content of a range of cells based on the beginning of the year till today. Unfortunately, I get back a "circular reference" error when I call the function, and I just can't see why. Any help will be appreciated.

Public Function til2day(r As Integer) As Long ''supposed to receive cell("row") as parameter  
  Dim c As Integer  
  Dim c1 As Integer  
  Dim c_here As Integer  

  Application.Volatile True

  c_here = ActiveCell.Column
  c = 0
  c1 = 34 ''column contains 1/1/2013 date

  Range("AH4:OM4").Activate ''contains a timeline
  Do While ActiveCell.Offset(0, c).Value <> Date
    c = c + 1
  Loop
  If ActiveCell.Offset(0, c).Value = Date Then
      c = ActiveCell.Offset(0, c).Column
  End If

  til2day = Application.WorksheetFunction.Sum(Range(Cells(r, c1).Address, Cells(r, c).Address))
  Range(Cells(r, c_here).Address).Activate
End Function

Upvotes: 0

Views: 4203

Answers (1)

Floris
Floris

Reputation: 46365

It is a really bad idea to use "activate" in a function; I can't explain exactly why this is, except that you are changing the selection of the cell during the calculation. In the following scenario this is going to cause a problem:

multiple cells are being calculated with this function, and 
you use `Application.Volatile`, and 
you refer to the active cell inside your function, and 
you allow multi-threaded calculation,

Things will not happen in the order you expect, and at some point the active cell will be different than you thought. Function ends up referring to the cell it's in, and you have a circular reference. This doesn't happen when you run the debugger since it by definition runs as a single thread - which is why you can't find the problem then...

Here is a suggested rewrite of your function - it doesn't do any activating of cells, but attempts to maintain the same functionality:

Public Function til2day(r As Integer) As Long ''supposed to receive cell("row") as parameter  
  Dim c As Integer  
  Dim c1 As Integer  
  Dim dateRange as Range
  Dime dateCell as Range

  Application.Volatile True

  c = 0
  c1 = 34 ''column contains 1/1/2013 date

  set dateRange = Range("AH4:OM4")

  For Each dateCell in dateRange
    If dateCell.Value = Date Then Exit For
  Next dateCell

  c = dateCell.Column

  til2day = Application.WorksheetFunction.Sum(Range(Cells(r, c1).Address, Cells(r, c).Address))

End Function

Note: I attempted to reproduce the functionality of your function - but without a good example of the worksheet you are using, and the values you are expecting to return, it's hard to test. Please try to run this on your worksheet - and let me know if things don't work as you expected.

Note also that the SUMIF function could be used with good effect: 

=SUMIF(range, criteria, sum_range)

In your case, use

=SUMIF($AH$4:$OM$4, "<=" & NOW(), $AH18:$OM18)

Where "18" is whatever row you need it to be (and when you drag the formula to a different row, it will continue to refer to the date row because of the $4 absolute reference, but calculate the sum for a different row because of the relative row reference in $AH18:$OM18.

An example of the use of this function (simplified range...)

enter image description here

As you can see, the function is summing columns C through F only since I did this on June 15th.

Upvotes: 1

Related Questions