User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

200-Year Calendar in Excel VBA

 

 

The Calendar has been created with vba code, in excel. It spans 2 centuries, covering years 1900 to 2100. Select the year from the drop-down list (yellow color cell), and the Calendar will auto-generate for that year, showing all 12 months. See below for a snapshot, and click relevant links for a Free Download. Please enable macros to run the (Calendar) Excel files.

 

Click to Download the Calendar (excel 2003 file).

 

Click to download the Calendar (excel 2007 file).

 

 

 

 

 

Creating a Calendar with VBA

 

Some key aspects in creating a calendar:

 

Determine day 1 for the year (ie. first month of the year)

 

Determine day 1 for each month, and its placement in the first row

 

Determine number of days in each month and the leap year in which February will have 29 days

 

Create 7 columns for each week day of Monday to Sunday

 

Account for a maximum of 6 rows to accomodate all days of a month

 

Dates placement for the first row (and also subsequent rows) for each month

 

 

The above aspects can be understood per comments inserted at relevant places in the vba code, in the calendar's VB Editor. Procedure has been entered in the code module of the worksheet named "Calendar" - as a worksheet change event, so that selection of the relevant year will auto-generate the calendar. We have also entered a procedure in the code module for "ThisWorkbook" in VBE, Project Explorer, as a workbook open event, which generates the calendar years range ie. a validation list, when the workbook is opened.