My 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:
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).
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
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”
myfunc = “Monday”
myfunc = “Tuesday”
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:
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.