UDF function don't behave like Excel function with the same logic

[soroush.kalantari]

I am used to use a specific formula to convert Shamsi date to milady date(see the attached excel column J). Because this formula is complex and cumbersome to write very often, I want to use a UDF function with the same logic instead.
Based on the formula logic, I have written the UDF Function. It works as expected for rows 2:16 but fails at rows 17:24)
To debug the problem step by step I have written a sub process with the same logic named “S2mtest” . Running this Marco. shows that the UDF function calculates the formula components as expected.(columns B:I) but. as attached picture shows, when inserting them in to final formula (last step) encounters a run time error 6( overflow)
Can you guide me on this issue? (My question is not about the formula logic, but about why the column J result are different to the column K)



The UDF function code:
'''
Function s2m_My(shamsidate As String) As Date
Dim y As Integer, m As Integer, a As Integer, b As Integer, e As Long, c As Integer
Dim cons As Date
Dim d As String


'
'cons = 1931 - 3 - 21
cons = 11403#



y = Left(shamsidate, 4) - 1300


m = Right(Left(Mid(shamsidate, 3, Len(shamsidate) - 1), 5), 2) + 0


d = Right(Mid(shamsidate, 3, Len(shamsidate)), 2)
'd = 1

a = y - 10

b = Int(a / 33)

If a Mod 33 = 32 Then
e = 7
Else

e = Int((a - b * 33) / 4)
End If

If m <= 6 Then
c = (m - 1) * 31 + d

Else

c = 186 + (m - 7) * 30 + d
End If



s2m_My = cons + a * 365 + b * 8 + e + c

End Function
'''

[Logit]

Maybe try 'dimming' the following as LONG :

a As Long, b As Long, e As Long, c As Long

[soroush.kalantari]

Originally Posted by Logit

Maybe try 'dimming' the following as LONG :

a As Long, b As Long, e As Long, c As Long

Thank you very much. Your reply solved the problem.

[Logit]

Glad to help