RSS

Calendars with Excel VBA macros

You can do much with macros (VBA) in Excel, and I have had fun making a program for making calendars with different layouts. A new calendar is placed in a new workbook.

The program uses various tools available in VBA, but isn't that complicated, and you can download the workbook with the macros here.

Below you can read more about the possibilities, see some screenshots, and I explain the program structure.

Holidays

The user must select a year, and then the dates of Easter Sunday and the Easter derived holidays are calculated. They are Maundy Thursday, Good Friday etc.

The program can "only" calculate Easter (Gregorian Calendar) for the years 1900 - 2299, sufficient for most of us, I presume.

There is an additional 10 holidays with fixed dates (e.g. Christmas). They are primarily Christian holidays. I know too little about holidays in other religions or cultures, but you can define up to 30 holidays or anniversaries of your own choice.

That can be the Ramadan, birthdays, wedding day, the day Elvis didn't die, The Battle of Austerlitz or whatever.

Userform

You decide if a holiday's name/text will be written to the calendar, and if it will be highlighted with a fill colour. You can select the fill colour using Excel's built in colour picker.

In no time you can make a calendar for the year you were born and see the weekday of your birthday, or see if Christmas in year xxxx is on a Sunday, or if it will be an early or late Easter.

The code reads your system settings to use the right date format (i.e. if the day is positioned before or after the month, first day of week, week numbers and more). Your selected standard language determines the names of days and months.

So it shouldn't matter where in the world you are, or what language you speak - at least I hope so!

There is too much code to show on this page, but as said earlier you can download the lot here. The workbook is zip compressed, and the code comments are in English.

Below I show, what some of the calendar layouts look like, and I describe the program structure - it is not as complicated as you might think.

Calendar types

The first is the well known type with two "pages" and 6 months on each. I have reduced the picture's size - the real thing is bigger.

Calendar screenshot

You can make a calendar with "boxes" for days and 7 "boxes" in each row with x rows for each month (12 named worksheets). I have no screenshot of that one, but below is a (reduced size) calendar with rows for days and a named worksheet for each month.

Calendar screenshot

It is good for short diary entries or the like, and the text cells are word wrapped, so the row height will adjust automatically, if you write more than one line.

Finally there is a calendar with columns for days and a worksheet for each month. The number of rows is user defined, or you can select 24 and get one with 24 hours like the one below. Holiday names/text will not be written in this type of calendar.

Calendar screenshot

I have used a similar calendar on my job to keep track of hours etc.

The calendar program

Most of the code is in class modules and class collections, but before you flee, screaming, you must understand that using classes makes it a lot easier to understand, because it is much simpler and better structured.

Having 12 months and +360 days, class collections are ideal. Then you just need code for one month and one day, and to get all the months and all the days you just create new instances of the classes.

You could say that the classes are like cookie cutters, and in a collection you can have as many cookies as you like.

It is very similar to the way Excel and other programs are structured. In Excel you have e.g. the Workbook object. It contains a worksheet collection; each worksheet contains other collections right down to the "elementary particle", the cell, which again has properties.

When you define a class of your own, you can give it the properties you need. Properties are like variables, but you define them just once, because they are connected to (encapsulated in) the class.

For instance the clDay class in the calendar program has a property, bHoliday, that is set to True or False if it is a holiday or not.

Properties can also execute code. If for instance the bHoliday property is set to True, it could trigger some code written in the property itself.

In other programs the class structure offers built in security, because the various properties are encapsulated by the class, and it is easy to control the right to read and write.

The class structure explained

The classes and class collections in the calendar program lie within each other like Chinese boxes. This makes the code structured and makes it easy to navigate and locate the month or day you are looking for.

At the top of the hierarchy we have the calendar class that represents the year and contains all the other classes.

On top of that you could have another class collection allowing you to make a collection of years. I haven't done that, but it wouldn't take long to implement, because the calendar class is already there like an Easter egg with all the stuff inside.

Most of the procedures and functions that make the calendar are in the calendar class itself. It also has a handful of properties, e.g. one that stores the day's position in a date determined by the system settings.

At the same time the calendar class is a class collection of clMonth classes, and it will not come as a surprise that there are 12 clMonth classes in the collection, and that the collection starts with January and ends with December.

This is also very practical when you loop through the class collection, e.g.:

For lM = 1 To 12
   'Do something
Next

Then the value of lM always tells you what month (1 - 12) you are dealing with. Because the calendar class is a class collection, you can find/address e.g. March by writing:

Calendar.Item(3)

or

Me.Item(3)

if it is code in the calendar class itself.

Each of the 12 clMonth classes is a class collection of clDay classes - the same number of clDay classes as days in the month. Each day's number in the collection equals the date, and you can construct the whole date with the day's collection number, the "parent" month and the year. Of course the whole date is one of the day class' properties.

Because every month is a class collection of day classes, you can address e.g. March 4th by writing:

Calendar.Item(3).Item(4)

and you can define December 25th as a holiday by saying:

Calendar.Item(12).Item(25).bHoliday = True

The calendar class contains yet another class, namely the clHolidays class.

clHolidays is a class collection of clHoliday classes. Each clHoliday in the collection represents a holiday (or anniversary).

The first 8 items are Easter and Easter derived holidays (calculated dates), the next 10 are fixed date holidays, and the last 30 are user defined holidays/anniversaries.

The user can define the name of each holiday in a userform, and he can decide if the name will be written to the calendar, and if it will be highlighted with a fill colour.

The first time the program runs there are no saved "My settings", so it checks your country code in the system settings.

If it matches one of the few predefined country codes (North America, Denmark, Germany, France, Italy and Spain), it will load the predefined holidays. If there is no match, North America will be used as default until the user has saved his settings.

The holiday userform

As mentioned there is a userform (see screenshot near the page top), where the user can define his holiday settings.

I'll not say much about it, you can study the code by yourself. However, I will mention the practical aspect of putting the various userform controls (labels, textboxes, checkboxes) in different frames.

When the userform loads it loops the different frames, and the various types of controls are added to different collections, so the sequence of collection elements matches the sequence of holidays in the clHolidays class collection.

Then, when the user clicks "Save", you don't need to loop the controls in the userform and check their value. Instead (and much easier) you loop through the different collections of controls.

Because the sequence matches the sequence in the clHolidays collection, it is dead easy to show old settings and save the new ones for future use.

Maybe I'll dedicate a webpage to this technique, but until then you can see it in the userform's code.

Use the code as you like and for whatever you like. Have fun!


Related: