UDF function don't behave like Excel function with the same logic
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
'''
|