nagging #value! error in excel udf

Question

I am new to vb and excel but I have to develop a custom udf for excel. I have read I have tried to alter my code below many times with the suggestions on this forum to no avail. What am I missing. This code is for working out a Julian date in integer form.

Option Explicit
Function CUSTOMJULIAN(JYear As Integer, JMonth As Integer, JDay As Integer) As Integer

Application.Volatile

Dim iyear As Integer
iyear = JYear

Dim imonth As Integer
imonth = JMonth + 1

If imonth <= 2 Then
    iyear = iyear - 1
    imonth = imonth + 12
End If

CUSTOMJULIAN = Int(365.25 * iyear) + Int(30.6001 * imonth) + JDay + 1720995

If JDay + (31 * (JMonth + 12 * JYear)) >= (15 + (31 * (10 + 12 * 1582))) Then

    Dim iadjustment As Integer
    iadjustment = Int(0.01 * iyear)

    CUSTOMJULIAN = CUSTOMJULIAN + 2 - iadjustment + Int(0.25 * iadjustment)

End If

End Function

Change all of your `As Integer` to be `As Long` instead and remove the `Application.Volatile`. If it's still not working, can you please provide some sample data and expected results? — tigeravatar, May 25 '16 at 14:42
Still not working here is a sample... CUSTOMJULIAN(1999,1,17) should give 2451156 as per the formula — ssn, May 25 '16 at 15:03

score 0 · Answer 1 · answered May 25 '16 at 15:16

0

Replace following line

If JDay + (31 * (JMonth + 12 * JYear)) >= (15 + (31 * (10 + 12 * 1582))) Then

to

If JDay + (31 * (JMonth + 12 * JYear)) >= (15 + (31 * (10 + 12 * CLng(1582)))) Then

See the details here.

answered May 25 '16 at 15:16

Mrig

11,612
2
13
27

score 0 · Answer 2 · answered May 25 '16 at 15:16

This was able to work

Option Explicit
Function CUSTOMJULIAN(JYear As Integer, JMonth As Integer, JDay As Integer) As Long

Dim iyear As Long
iyear = JYear

Dim imonth As Long
imonth = JMonth + 1

If imonth <= 2 Then
    iyear = iyear - 1
    imonth = imonth + 12
End If

CUSTOMJULIAN = Int(365.25 * iyear) + Int(30.6001 * imonth) + JDay + 1720995

Dim iday As Long
iday = 12 * JYear
iday = iday + JMonth
iday = iday * 31

If iday >= 588829 Then

    Dim iadjustment As Long
    iadjustment = Int(0.01 * iyear)

    CUSTOMJULIAN = CUSTOMJULIAN + 2 - iadjustment + Int(0.25 * iadjustment)

End If

End Function

nagging #value! error in excel udf

2 Answers2