Get Tenor - Bucket Function

[IrishCharm]

Hi,

I have the below function which buckets tenor points depending on the number of days returned but i cannot return the value of the "bucket" back to excel in the formula.

the formula in Excel is = GetTenor("a2") and this should return, for example, "1M" but returns an error. I have attached the spreadsheet and code.

Thanks

Sarah



Public bucket As String

Public Function GetTenor(bucket As String, x As Integer)
If x < 1 Then
bucket = "ON"
End If
If x = 1 Then
bucket = "1D"
End If
If x > 1 And x <= 7 Then
bucket = "1W"
End If
If x > 7 And x <= 30 Then
bucket = "1M"
End If
If x > 30 Then
bucket = "OVER"
End If
End Function

[Bob Phillips]

Public Function GetTenor(x As Integer)

If x < 1 Then
GetTenor = "ON"
ElseIf x = 1 Then
GetTenor = "1D"
ElseIf x <= 7 Then
GetTenor = "1W"
ElseIf x <= 30 Then
GetTenor = "1M"
ElseIf x > 30 Then
GetTenor = "OVER"
End If
End Function

[IrishCharm]

Thank you so much - I can be such a plank of wood sometimes - should have seen that!!

thanks for all your help,

Have a nice day

Sarah