User defined function doesn't work in PERSONAL.XLSB

Question

User defined function doesn't work in PERSONAL.XLSB

Anonymous

I want to create a user defined function in Excel 2007 to return the name of a month from the month number. If I create the function within a module in VBAProject (Book1), then it works; but if I put the function in VBAProject (PERSONAL.XLSB) it just returns #NAME? in the worksheet cell. I want this function to be universally available to any worksheet - how can I achieve this?

The function that I coded is:

Function MonthName(MonthNumber As Integer) As String

''

' Returns the name of the month specified by the month number (1 = January, 2 = February, etc).

'

Dim RequiredMonth

RequiredMonth = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

If (MonthNumber < 1 Or MonthNumber > 12) Then

MonthName = ""

Else

MonthName = RequiredMonth(MonthNumber - 1)

End If

End Function

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

1 additional answer

Answer 1

You can call it by using the workbook name, e.g.:

=Personal.xlsb!MonthName(B1)

FWIW, it would be more efficient to just use a regular XL function:

=TEXT(DATE(1,B1,1),"MMMM")

And this would be a bit more efficient UDF:

Public Function MonthName2(ByVal MonthNumber As Integer) As String

If MonthNumber >0 And MonthNumber < 13 Then

MonthName2 = Format(DateSerial(1, MonthNumber, 1), "MMMM")

End If

End Function

Answer 2

Anonymous

Thanks jemcgimpsey, the TEXT function is a much neater solution.

0 comments

Share via

User defined function doesn't work in PERSONAL.XLSB

1 additional answer