Solved

add a custom function to excel

Posted on 2014-09-25
6
129 Views
Last Modified: 2014-09-26
MonthName( number, [ abbreviate ] ) I am looking to somehow get this VBA function into excel as a custom function
Can anyone help?
0
Comment
Question by:Serena2345
6 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 167 total points
Comment Utility
MonthName is built into Excel. Here's an explanation. If you already know that then please explain further what you need.
0
 
LVL 6

Assisted Solution

by:johnb25
johnb25 earned 166 total points
Comment Utility
Serena,

Paste this into a module in your Personal.xlsx workbook:
Public Function NameMth(MonthNo As Long) As String

NameMth = MonthName(MonthNo)

End Function

Open in new window


Then in Excel type =NameMth(3) and the formula returns March.

John
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 167 total points
Comment Utility
Is there a real need for a custom function?
You can use the TEXT function as below:

=TEXT("25/" & 1 &"/2014","mmmm")
You can use a cell ref in place of the 1 in the middle (this is the month number).
"mmmm" = full month name
"mmm" = short month name

A lot of users prefer not to receive workbooks with VBA.
A lot of IT departments do not like VBA in workbooks either.
So there is an argument to avoid VBA User Defined Functions.

In VBA you can use Format("25/" & 1 & "/2014", "mmmm")
FORMAT can be quite versatile in VBA

ATB
Steve
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:frankhelk
Comment Utility
I agree to the anwers who recommend to avoid custom functions ... On the other side I see the use in a functional example, and I won't spare the opportunity to give one to those who wnat to implement a custom function - even if only for educational purpose:

To add some fine art to the Example of johnb25, I would add some parameter check to the function and return a proper error code - the code below would return a #Value! error if the month number is out of the desired range [1...12]:
Public Function NameMth(MonthNo As Long) As Variant

If (MonthNo < 1 ) Or (MontNo > 12) then
   NameMth = CVErr(xlErrValue)
Else
  NameMth = MonthName(MonthNo)
End If

End Function

Open in new window

See here for some more info about returning errors to Excel from VBA.
0
 
LVL 24

Expert Comment

by:Steve
Comment Utility
frank... typo on line 3...

'MontNo' should read 'MonthNo'

;)
0
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
Another formula solution:

=IFERROR(CHOOSE(A1,"January","February","March","April","May","June","July","August","September","October","November","December"),"")

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now