function runs in VBE, but not from spreadsheet

[salgud]

Been fooling with this for a while, trying to figure out why this function
works fine when called or when I insert a debug.print or a message box, but
it won't run from a formula in the spreadsheet.

Public Function ConvertTxtMo2NumMo(iMonth)

Select Case iMonth
Case Is = 1
sMonth = "Jan"
Case Is = 2
sMonth = "Feb"
Case Is = 3
sMonth = "Mar"
Case Is = 4
sMonth = "Apr"
Case Is = 5
sMonth = "May"
Case Is = 6
sMonth = "Jun"
Case Is = 7
sMonth = "Jul"
Case Is = 8
sMonth = "Aug"
Case Is = 9
sMonth = "Sep"
Case Is = 10
sMonth = "Oct"
Case Is = 11
sMonth = "Nov"
Case Is = 12
sMonth = "Dec"
End Select

End Function


Any suggestions?

 

[Dave Peterson]

What happens when you use it in a formula like:

=ConvertTxtMo2NumMo(A1)
What did you put in A1?

And some other questions...

Where did you put this procedure? It belongs in a general module.

If it's in a different workbook's project, you'll need:
=book2.xls!ConvertTxtMo2NumMo(A1)

 

[Jellifish]

Close, but no cigar:

Public Function ConvertTxtMo2NumMo(iMonth)

Select Case iMonth
Case Is = 1
ConvertTxtMo2NumMo = "Jan"
Case Is = 2
ConvertTxtMo2NumMo = "Feb"
Case Is = 3
ConvertTxtMo2NumMo = "Mar"
Case Is = 4
ConvertTxtMo2NumMo = "Apr"
Case Is = 5
ConvertTxtMo2NumMo = "May"
Case Is = 6
ConvertTxtMo2NumMo = "Jun"
Case Is = 7
ConvertTxtMo2NumMo = "Jul"
Case Is = 8
ConvertTxtMo2NumMo = "Aug"
Case Is = 9
ConvertTxtMo2NumMo = "Sep"
Case Is = 10
ConvertTxtMo2NumMo = "Oct"
Case Is = 11
ConvertTxtMo2NumMo = "Nov"
Case Is = 12
ConvertTxtMo2NumMo = "Dec"
End Select

End Function

 

[JBeaucaire]

Put this one extra line at the bottom just above the END FUNCTION:

ConvertTxtMo2NumMo = smonth

 

[Dave Peterson]

Thanks.

Put this one extra line at the bottom just above the END FUNCTION:

ConvertTxtMo2NumMo = smonth

 

[Rick Rothstein]

JBeaucaire has given you the solution to your particular question and
Jellifish showed you how to do it without the extra variable; however, I
would like to offer you a much shorter function than the one you
developed...

Public Function ConvertTxtMo2NumMo(iMonth)
ConvertTxtMo2NumMo = MonthName(iMonth, True)
End Function

By the way, there are several ways you could produce the output you seek
using built-in Excel functions directly on your worksheet; this is my
favorite...

=TEXT(28*A1,"mmm")

where I have assumed A1 contains your month number.

 

[Jacob Skaria]

From the Insert Function window select category as 'User Defined' and select
the below function.

However the below function can be simplified using

strMonth = Format(intMonth,"MMM")

If this post helps click Yes

 

[Rick Rothstein]

That won't work... intMonth is a number from 1 to 12... the Format function
will return Jan for each one of them. Following the structure I used in the
worksheet function I posted, you would need to do this to get it to work...

strMonth = Format(28 * intMonth, "MMM")

 

[Rick Rothstein]

I should have added this to the end of my previous response to you...

"but using the MonthName function that I used in my other posting would be
the easiest way to do it using VB code."

--
Rick (MVP - Excel)

That won't work... intMonth is a number from 1 to 12... the Format
function will return Jan for each one of them. Following the structure I
used in the worksheet function I posted, you would need to do this to get
it to work...

strMonth = Format(28 * intMonth, "MMM")

 

[salgud]

Been fooling with this for a while, trying to figure out why this function
works fine when called or when I insert a debug.print or a message box, but
it won't run from a formula in the spreadsheet.

Public Function ConvertTxtMo2NumMo(iMonth)

Select Case iMonth
Case Is = 1
sMonth = "Jan"
Case Is = 2
sMonth = "Feb"
Case Is = 3
sMonth = "Mar"
Case Is = 4
sMonth = "Apr"
Case Is = 5
sMonth = "May"
Case Is = 6
sMonth = "Jun"
Case Is = 7
sMonth = "Jul"
Case Is = 8
sMonth = "Aug"
Case Is = 9
sMonth = "Sep"
Case Is = 10
sMonth = "Oct"
Case Is = 11
sMonth = "Nov"
Case Is = 12
sMonth = "Dec"
End Select

End Function


Any suggestions?

Want to thank all who replied, I got it all worked out with your help.