Fun with VBA functions!

So far we've only been using the built in VBA functions (such as MsgBox, CInt or IsNumeric) as we've needed them. Today we're going to have some fun finding out what else is out there! Check out the list of VB functions at Microsoft's Visual Basic Scripting Functions (right-click and open in a new window).

How many of the functions do you recognise? We'll see how many we can count in class!

Open a new Excel Spreadsheet and save it as "vba_functions.xls". See if you can use this reference to achieve the following:

1. Find a function in the reference that will return the current date.
2. Now find another function that will format your date however you choose (ie. "16/9/05" or "16th September, 2005", depending on your choice). Create a new Sub called DisplayCurrentDate that uses a MsgBox to display your formatted date.
   
3. Find a function that will take a date and return a number from 1 to 12 corresponding to the month part of the date. Create a new Sub called CurrentMonthNum that uses a MsgBox to display the number of the current month.
4. Find a function that will take a number corresponding to a month, and return the name of the month (ie. you give it 9 and it returns "September"). Now use all three functions in a new Sub called CurrentMonthName that uses a MsgBox to display the name of the current month.
5. Now create a new Sub called BirthdayMonth that uses an InputBox to ask the user to type in their birthday. Your sub will then use a MsgBox to respond with the month they were born. For example, if I type: 1/8/75, then the sub will use a MsgBox to say "Oh, you were born in August!".
6. Find a few functions that will allow you to create a new Sub called BirthdayDay. This Sub will ask the user to type in their birthday, and then tell them what day of the week they were born (e.g. "Monday").
   

Probably a good time to take a break, stretch your legs, before trying the next set!