excel - Range set in function clears when returning to mainsub -

in following code set 3 ranges equal variables. have no problem rngnext , rngdays, assign correct variable correct range- no worries.

bizarely, rnglast not write worksheet. more precisely (upon stepping code), can see value being written sheet on linernglast.value = lastbilldate expected, disappearing after end function line, when return main sub.

i have never experienced before, thing can think of it's maybe variable losing value after return sub, surely it's written worksheet?

function daysremaining(lngbilldate long, lngrow long)  dim rnglast range, rngnext range, rngdays range dim nextbilldate date, lastbilldate date  set rnglast = worksheets("data").range("g" & lngrow) set rngnext = worksheets("data").range("h" & lngrow) set rngdays = worksheets("data").range("i" & lngrow)  if lngbilldate > day(date)     nextbilldate = cdate(format(lngbilldate, "00") & "/" &      format(month(date), "00") & "/" & year(date))     lastbilldate = dateadd("m", -1, nextbilldate)  else     nextbilldate = cdate(format(lngbilldate, "00") & "/" & format(month(date) +     1, "00") & "/" & year(date))     lastbilldate = dateadd("m", -1, nextbilldate) end if    rngnext.value = nextbilldate rnglast.value = lastbilldate rngdays.value = rngnext.value - date end function 

help appreciated always.

it depends on how using or want use daysremaing procedure:

1. if using in-sheet, i.e. calling =daysremaining(x,y) after function has executed values won't stay expect. excel in-sheet functions built output cell called from, cannot right ranges using rng.value outside of function. function use in-sheet can have multiple inputs, 1 output. (this excel limit). if want multiple outputs need return array result.

2. if using though vba, code works expected (i tried implementing successfully). you'll matter of housekeeping need change public sub daysremaining(...). don't need function per definition of function not returning using procedure remote work on sheet.

the following :

public sub dodaysremianing()     daysremaining clng(date), 2 end sub 

also works fine, calling through vba.