function runs in VBE, but not from spreadsheet
 

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?

function runs in VBE, but not from spreadsheet
 

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)





salgud wrote:

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

function runs in VBE, but not from spreadsheet
 

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



"salgud" wrote in message
.. .
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?

function runs in VBE, but not from spreadsheet
 

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

ConvertTxtMo2NumMo = smonth

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Dave Peterson" wrote:

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)





salgud wrote:

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

function runs in VBE, but not from spreadsheet
 

Thanks.

JBeaucaire wrote:

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

ConvertTxtMo2NumMo = smonth

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

"Dave Peterson" wrote:

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)





salgud wrote:

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


--

Dave Peterson

function runs in VBE, but not from spreadsheet
 

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.

--
Rick (MVP - Excel)


"salgud" wrote in message
.. .
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?

function runs in VBE, but not from spreadsheet
 

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
---------------
Jacob Skaria

function runs in VBE, but not from spreadsheet
 

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 (MVP - Excel)


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria

function runs in VBE, but not from spreadsheet
 

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)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria

function runs in VBE, but not from spreadsheet
 

On Fri, 20 Mar 2009 16:03:10 -0600, salgud wrote:

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.