VBA Function error

Question

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

Actually you must not do this, or you get a compile error. — Andre
– Andre, Commented Jul 20, 2017 at 16:25

Kostas K. · Accepted Answer · 2017-07-20 16:23:35Z

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

This is a great way to do it! Thank you so much! I have one last question. The answer comes out as a number, not a short date format. Even though I indicated that the function as date. Do I need to use a different code just to convert the answer to a date format?
Excel treats dates as numbers, so if it's on a cell, you need to format it as date. In VBA as the example shows above are shown as actual dates.

sourceCode · Accepted Answer · 2017-07-20 16:15:40Z

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

Collectives™ on Stack Overflow

VBA Function error

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related