Getting Day Names and Month Names in Microsoft Excel using VBA

 

Microsoft Excel 2010 - LogoMy last post I blogged about how to get the Day Names and Month Names using VLOOKUPs.  However, today let’s look at doing exactly the same but, using VBA (Visual Basic for Applications).  Now for the non-coders reading this it may seem kinda scary but, give it a go and you may be surprised about how easy coding in Excel can be.

I would recommend though that you learn in a practice document that isn’t going to affect real data or an existing document.

 

Preparing to create a function

The first thing that you need to do is open Microsoft Excel (and for the purposes of this exercise I’ll be using Microsoft Excel 2010).  I know that Excel 2013 has been released but, I don’t think that many businesses have moved over just yet.

When you’re in a new worksheet.

Press ALT + F11

And you’ll be presented with this screen:

 

Now what we are trying to do is create a function which will work across all worksheets in a workbook (in a single file).  However, you need to add a MODULE to the workbook.

To do this right click on the MICROSOFT EXCEL OBJECTS and INSERT then select MODULE.

As shown below:

VBA Microsoft Excel Image 002

 

 Once clicked you will see the following part added and the right hand on the screen becomes workable in (it goes from greyed out to white).

 

VBA Microsoft Excel 2010 - Image 3

You are now ready to create your FUNCTION

 

Creating the function

Believe it or not creating a function starts with the word FUNCTION

If you type

FUNCTION myfunc()

you will see that underneath END FUNCTION automatically appears.  The code that you want to write is written between these two lines.  The open and closed brackets are for the variable that you want to pass this function.  In this case a date from a cell.

 So we already know that the variable we want to interrogate is a date and in this example we’ll create a function which turns a date into a DAYNAME e.g. Monday, Tuesday, Wednesday.

So first thing is to get the number of the day.  As shown in the last blogpost there is an embedded function which turns a date into a day number (WEEKDAY) however, this number is completely unused in anything other that coding.  Sunday = 1, Monday = 2, Tuesday =3 and so on.

So we have two options, using a complicated IF and ELSE statement.  The easiest way is using a SELECT CASE statement – which is the example I will go through now

Select Case weekday([date from function])

Case = 1

myfunc = “Sunday”

Case =2

myfunc = “Monday”

Case =3

myfunc = “Tuesday”

End Case

 So you can continue the remainder of the SELECT CASE statement (well I can’t do all the work for you).

Calling the function

 When you create a function as a module it becomes available in the dropdown when you start typing any function. As shown below:

Image Excel Function Drop Down

When you go into a cell and press the equals (“=”) and follow that with a letter (in the case of the function above … press “m”).  Your function will appear in the drop down and the only thing that you need to do is make sure that you pass it a valid date.

Making it work for Month Names

Once you have understood about how to use SELECT CASE statements and also the easiness of how to create functions.  All you need to do is change two things.  One is change the WEEKDAY function (embedded within the myfunc function) and also change the SELECT CASE bits to twelve options (January = 1 and so on).

Any queries then just complete a contact us form and we’ll try and help you out.

 

This post was written by .

More Posts by   Visit 's Website

Leave a Reply