excel vba - Passing Dates to VBA UDF, returns NUM error -

excel vba - Passing Dates to VBA UDF, returns NUM error -

the next vba code works if run sub(), when run udf, #num! error.

i suspecting there problem while passing values it.

public function servicetaxinterest(paymentdate date, duedate date, taxamount integer) integer dim involvement double involvement = 1e-32 if duedate > paymentdate involvement = 0 elseif taxamount <= 0 involvement = 0 else to_day = duedate paymentdate if to_day < dateserial(2014, 10, 1) involvement = involvement + (taxamount * 0.18 / daysinyear(to_day)) elseif monthsdelay(duedate, to_day) < 6 involvement = involvement + (taxamount * 0.18 / daysinyear(to_day)) elseif monthsdelay(duedate, to_day) < 12 involvement = involvement + (taxamount * 0.24 / daysinyear(to_day)) else involvement = involvement + (taxamount * 0.3 / daysinyear(to_day)) end if next end if servicetaxinterest = round(interest, 0) end function

'

public function monthsdelay(startdate, enddate) integer if datevalue(startdate) > datevalue(enddate) = 0 elseif day(enddate) >= day(startdate) = ((year(enddate) - year(startdate)) * 12) + (month(enddate) - month(startdate)) else = ((year(enddate) - year(startdate)) * 12) + (month(enddate) - month(startdate)) - 1 end if monthsdelay = end function

'

public function daysinyear(x) integer if int(year(x) / 4) = year(x) / 4 daysinyear = 366 else daysinyear = 365 end if end function

please help in identifying mistake.

thanks

your code works correctly udf (or @ to the lowest degree produces number , no error) me, without modification.

i believe error, suspect, in you're passing parameters function.

what udf needs excel can evaluate number. if have values in cells formatted dates, can pass reference cell. if date in cell text (for example, copied outside source), can pass datevalue(text) function.

my guess you're manually typing in 3/25/2015 parameter. excel read little number (with slashes division) , interpret wrong date. passing "3/25/2015" quotes prepare that. excel's date object recognizes string date , converts value correctly.

edit: safer enclosing in quotes, there may localization issues (my american-ness showing month/day/year format), can utilize date(year,month,day) function input instead. referencing cell that's formatted date safe too, date number that's independent of how excel formatting (yyyy/mm/dd, dd/mm/yyyy, etc.))

vba excel-vba user-defined-functions