CODEWITHSUNDEEP
excelms-accessvba
KateLO
KateLO

Reputation: 67

VBA Function error

What I'm trying to do here is see what the term value equals and go from there. If it's STD then I just need to add the days to the invoice date; If its BONM (begining of next month) I need it to add days to the invoice date and then reference the beginning of next month as the answer. If its EOM (end of month) i need it to add days to the invoice date and then reference the end of that current month. I've attached an image of the error that I'm getting. Tried dimming it in different ways but it's still not working.

Function OldMaturity(term As Range, invoicedate As Range, days As Range) As Date

Dim term As String
Dim invoicedate As Date
Dim days As Long
Dim val1 As Long
Dim val2 As Long


term = Termtype.Value
invoicedate = invoicedate.Value
days = days.Value
val1 = val1.Value
val2 = val2.Value

If term = "STD" Then

OldMaturity = invoicedate + days
Exit Function
End If

If term = "BONM" Then
val1 = invoicedate + days
val2 = DateAdd("m", 1, val1)
OldMaturity = DateSerial(Year(val2), Month(val2), 1)
Exit Function
End If

If term = "EOM" Then
val1 = invoicedate + days
OldMaturity = DateSerial(Year(val1), Month(val1) + 1, 0)
Exit Function
End If
End If


End Function

enter image description here

Upvotes: 0

Views: 132

Answers (2)

Kostas K.
Kostas K.

Reputation: 8518

Try this:

Public Function OldMaturity(term As Range, invoicedate As Range, days As Range) As Date

    Dim d As Date
    Select Case term.Value
        Case "STD":
            OldMaturity = DateAdd("y", days.Value, invoicedate.Value)

        Case "BONM":
            d = DateAdd("y", days.Value, invoicedate.Value)
            OldMaturity = DateAdd("m", 1, DateSerial(Year(d), Month(d), 1))

        Case "EOM":
            d = DateAdd("y", days.Value, invoicedate.Value)
            OldMaturity = DateAdd("y", -1, DateAdd("m", 1, DateSerial(Year(d), Month(d), 1)))

        Case Else:
            'do nothing
    End Select
End Function


Sub Test()
    'invoicedate: 15/03/2017
    'days: 10
    Debug.Print "STD:  " & OldMaturity(Range("A1"), Range("B1"), Range("c1"))
    Debug.Print "BONM: " & OldMaturity(Range("A2"), Range("B2"), Range("c2"))
    Debug.Print "EOM:  " & OldMaturity(Range("A3"), Range("B3"), Range("c3"))
End Sub

'Output:
'STD:  25/03/2017
'BONM: 01/04/2017
'EOM:  31/03/2017

Upvotes: 3

sourceCode
sourceCode

Reputation: 338

Following what @Vincent G mentioned, you do not need to redefine the parameters in the function. Also you have an extra end if statement at the end.

Edit: found one more error. If term = "STD" then OldMaturity return type is not Date. 

Function OldMaturity(term As Range, invoicedate As Range, days As Range) As Date

'variables
Dim val1 As Long
Dim val2 As Long


term = Termtype.Value
invoicedate = invoicedate.Value
days = days.Value
val1 = val1.Value
val2 = val2.Value

If term = "STD" Then
    OldMaturity = invoicedate + days
    Exit Function
End If

If term = "BONM" Then
    val1 = invoicedate + days
    val2 = DateAdd("m", 1, val1)
    OldMaturity = DateSerial(Year(val2), Month(val2), 1)
    Exit Function
End If

If term = "EOM" Then
    val1 = invoicedate + days
    OldMaturity = DateSerial(Year(val1), Month(val1) + 1, 0)
    Exit Function
End If



End Function

Upvotes: 1

Related Questions