Try:

Function RoundToNearest(x, Optional Threshold = 1) As Double
Dim dTmp As Double
If Not IsError(x) Then dTmp = x
RoundToNearest = Application.Round(dTmp / Threshold, 0) * Threshold
End Function



Note1:
escape from circulating errors

Note2:
iso worksheetfunction use application
(no intellisense, but different error handling
(see dick kusleika's blog)




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brian wrote :

Hello,

I use circular references all the time without issues, however Im
having trouble when using user-defined functions in conjunction with
circular references.

Take the following simplified example:

Cell1: Revenue = 1000000 + Expenses * 3%

Cell2: Expenses = 700000 + Revenue * 2%

Where "Revenue" and "Expenses" are named ranges

The above formula works just fine assuming the calculation settings
are setup to allow circular references, but changing the formula to
the following creates problems:

Cell1: Revenue = RoundToNearest(1000000 + Expenses * 3%,5000)

Cell2: Expenses = 700000 + Revenue * 2%

Where RoundToNearest is a simple user-defined formula I created
to round numbers that's a bit more flexible than Excel's
built-in Round function (in the example above Revenue gets
rounded to the nearest $5,000). The error, however, occurs
regardless of which user-defined function I use.

When I open the worksheet containing the second example above, Cell1
(Revenue) and Cell2 (Expenses) contain #NAME? and #VALUE!
respectively. Oddly enough, if I delete Cell1 and press undo the
error fixes itself. However, I have to perform the delete and undo
procedure every time I open the file.

Does anyone have any thoughts on how to correct this problem? Thanks!



PS: The error occurs regardless of what user-defined function I use
so I don't think the error is specific to my RoundToNearest function.
Nevertheless, here's the function code:

Function RoundToNearest(x, Optional Threshold = 1) As Double

RoundToNearest = WorksheetFunction.Round(x / Threshold, 0) *
Threshold

End Function