Solved

add a custom function to excel

Posted on 2014-09-25
6
132 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 46

Accepted Solution

by:
Martin Liss earned 167 total points
ID: 40345045
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
ID: 40345107
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
ID: 40345485
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 14

Expert Comment

by:frankhelk
ID: 40345585
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
ID: 40345605
frank... typo on line 3...

'MontNo' should read 'MonthNo'

;)
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40345689
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

815 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

12 Experts available now in Live!

Get 1:1 Help Now