Solved

add a custom function to excel

Posted on 2014-09-25
6
131 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

862 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

30 Experts available now in Live!

Get 1:1 Help Now