excel - UDF Circular reference -

i have task creating couple udf's compare sales figures against benchmark , return integer based on same value returned on previous sheet. i've run infamous circular error makes no sense me because while referring address of cell no-no in vba, it's in context of sheet.

if enable iterative calculations, works on occasion continues iterate , jacks return value up. affects entire workbook. feel i'm missing simple here lack experience vba know explicitly. if has quick easy fix might overlooking i'd appreciate it.

i'm @ wits , and going in python using xlwings or java using apache poi. consider hail mary pass @ giving on vba. ideas?

function tier1(sales double) integer     'constant declaration     'change these alter benchmark values     const bench double = 133000#     const variance double = 0.9      'variable declaration     dim callcell string     dim sheet integer     dim oldvalue integer     dim returnvalue integer      'assigns values callcell & sheet     sheet = activesheet.index     callcell = application.caller.address      if sheet > 1         oldvalue = worksheets(sheet - 1).range(callcell).value     else         oldvalue = 0     end if      select case sales         case >= bench             select case oldvalue                 case > 0                     returnvalue = oldvalue - 1                 case > 2                     returnvalue = 2                 case else                     returnvalue = 0             end select         case < bench             returnvalue = oldvalue + 1             if sales > (bench * variance) , returnvalue > 2                 returnvalue = 2             end if         end select          tier1 = returnvalue end function 

have careful when referring other sheets within udf sheet expect. references should qualified, otherwise default might not expect.

for example:

oldvalue = worksheets(sheet - 1).range(callcell).value 

is same as:

oldvalue = activeworkbook.worksheets(sheet - 1).range(callcell).value 

so if workbook containing formula isn't active workbook, results not expect.

a few suggested edits:

function tier1(sales double) integer     'constant declaration     'change these alter benchmark values     const bench double = 133000#     const variance double = 0.9      'variable declaration     dim callcell range     dim sheet integer     dim wb workbook     dim oldvalue integer     dim returnvalue integer      set callcell = application.caller     set wb = callcell.worksheet.parent     sheet = callcell.worksheet.index      if sheet > 1         oldvalue = wb.worksheets(sheet - 1).range(callcell.address).value     else         oldvalue = 0     end if      select case sales         case >= bench             select case oldvalue                 case > 0                     returnvalue = oldvalue - 1                 case > 2                     returnvalue = 2                 case else                     returnvalue = 0             end select         case < bench             returnvalue = oldvalue + 1             if sales > (bench * variance) , returnvalue > 2                 returnvalue = 2             end if         end select          tier1 = returnvalue end function