| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| This sub runs to a function in a Module. Why? | Excel Programming | |||
| add a button on a spreadsheet that runs a macro | Excel Programming | |||
| Function runs more than once | Excel Programming | |||
| Run macro but let user update spreadsheet while it runs | Excel Programming | |||
| macro runs except from function | Excel Programming | |||
Linear Mode
