vba circular reference error in function

Question

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

You should be able to track the problem down using the VBA debugger. The F8 key will step you through the program line-by-line. At each step, hover the cursor over your variables to see what their values are. One question though, why do you have the IF on the Value = Date outside of your DO loop? Seems odd. — chuff, Jun 15 '13 at 16:39
the if is redundant really, i'm just double-checking that the column i'm at has todays date as value. As to using the debugger, the problem is that even with checkpoints, running through it with F8, it doesn't show the error and calculates correctly. The problem is, when i call the function from a cell (i probably should've mentioned that, my bad) — Stratum, Jun 15 '13 at 16:48
How are you running the function? Do you have a sample sheet? — SheetJS, Jun 15 '13 at 16:49
hmm, I've no idea how to do a sample sheet. The layout is simple, i got a timeline on top for the entire year and several projects in the rows, sort of gantt style. I then enter hours (number format) in the resulting grid. I want to be able to fill a column with calls to this function, have the function identify the row and sum up the corresponding values, returning it in the cell i placed the function ( =til2day() ). Please let me know if this explanation is not clear (it does sound muddled to me, but I don't know how to do it better, sorry) Thanks — Stratum, Jun 15 '13 at 17:02
A function called from a worksheet formula is **not able to alter the sheet in any way** (so your `.Activate` will fail silently and results, if any, will be unpredictable). — Tim Williams, Jun 16 '13 at 01:16

Floris · Accepted Answer · 2013-06-15T20:13:59.750

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.

You sir are brilliant. I'm going to test the function, but the sumif did exactly what I needed (this is a bit embarrassing, to be honest). Thank you so much — Stratum, Jun 16 '13 at 13:21
Happy to help - and curious to know if the VBA approach worked too. Usually the built in functions will be faster though. — Floris, Jun 16 '13 at 13:44

vba circular reference error in function

1 Answers1