Solved

add a custom function to excel

Posted on 2014-09-25
6
135 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 48

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

688 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