Ageing calculation UDF...in vba

[Dandapani S]

Hi all,

I am very new to vba...taking a small step into that. I need to calculate "Ageing" by date as input..... Pls go thru my vba code & fix the error...

Public Function AgeingV(rng As Date) As String

Dim strdate As Date
Dim strReturn As String
Dim diffv As String

strdate = DateValue("January 31,2014")
diffv = DateDiff("d", strdate, rng)
Select Case diffv.Value
Case 0 To 30
strReturn = "0-30"
Case 31 To 60
strReturn = "31-60"
Case 61 To 90
strReturn = "61-90"
Case 91 To 180
strReturn = "91-180"
Case Else
strReturn = ">180"
End Select

AgeingV = strReturn

End Function

 

[NARAYANK991]

Hi Dandapani ,

One possibility is this :

Public Function AgeingV(rng As Range, refdate As Range) As String
                Dim strReturn As String
                Dim diffv As String

                diffv = DateDiff("d", refdate, rng)

                Select Case diffv
                      Case 0 To 30
                            strReturn = "0-30"
                      Case 31 To 60
                            strReturn = "31-60"
                      Case 61 To 90
                            strReturn = "61-90"
                      Case 91 To 180
                            strReturn = "91-180"
                      Case Else
                            strReturn = ">180"
                End Select

                AgeingV = strReturn
End Function

This can be called as follows :

=AgeingV(E1,F1)

where E1 contains a date such as 1/1/2014 , while F1 contains a reference date or base date , say 11/1/2013.

Note that there is absolutely no error checking.

Narayan

 

[Dandapani S]

Thank you...Mr.Narayan.

 

[rumshar]

Hi all,

I am very new to vba...taking a small step into that. I need to calculate "Ageing" by date as input..... Pls go thru my vba code & fix the error...

Public Function AgeingV(rng As Date) As String

Dim strdate As Date
Dim strReturn As String
Dim diffv As String

strdate = DateValue("January 31,2014")
diffv = DateDiff("d", strdate, rng)
Select Case diffv.Value
Case 0 To 30
strReturn = "0-30"
Case 31 To 60
strReturn = "31-60"
Case 61 To 90
strReturn = "61-90"
Case 91 To 180
strReturn = "91-180"
Case Else
strReturn = ">180"
End Select

AgeingV = strReturn

End Function

Hi Dandapani,
I also use almost similar function. Here is mine
[code = vb]
Function AgingBucket(Age As Integer) As String

If Age >= 120 Then
AgingBucket = "120 Days"

ElseIf Age >= 90 Then
AgingBucket = "90 Days"

ElseIf Age >= 60 Then
AgingBucket = "60 Days"

ElseIf Age >= 30 Then
AgingBucket = "30 Days"

ElseIf Age < 30 Then
AgingBucket = "Current"

End If
End Function
[/code]