These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 11, 2014

Word Calendar Templates

Business Overview
Vertex42 LLC creates calendars, which it provides via download on its website The calendars are attractive, easy to customize, and even include holidays; these features make them some of the most popular calendars on the internet. The site appears among the first internet results when someone searches for “calendar template” on Google or other search engines.
Currently, all of the calendars available on are Excel files or PDF’s. However, customers have recently expressed a desire to have Word-based calendars. Many of them feel more comfortable using Word than Excel, and they would like the customizability that is available through Word over the static PDF versions.

Desired Outcome
It would be possible for employees of Vertex42 LLC to copy the existing Excel calendars available online into Word documents and format them in that application. However, this process would be time consuming and repetitive for a single year, and would be unreasonable to execute for multiple years.
Because the website offers many different kinds of calendars for almost any year, the company hopes to be able to generate the Word calendars quickly and accurately.
Accordingly, the CEO of Vertex42 LLC asked me to expand the functionality of the company’s Excel-based calendar generator to also produce calendars in Word. He provided me with the Excel workbook company employees use to create Excel-based calendars, which has formulas that compute the day-of-the-week and holidays. Working from this foundational Excel workbook, I was to build new functionality that would enable the resulting calendars to be transferred directly into Word.

Solution Overview
The solution spans both applications. The VBA code I wrote is all held within the Excel workbook and relies on the workbook’s existing formulas. It opens an existing Word template and generates individual calendars for the twelve months of a user-specified year. These calendars are each copied into the Word-based template and are formatted to present a clean layout, responsive to changes in page size or orientation.

This solution will enable Vertex42 LLC to quickly generate 12-month calendars for any number of years. The company will provide these calendars online, giving customers the flexibility of downloading Word-document calendars instead of Excel-based calendars. I will make the code available to the company, so future employees can make small changes to the formatting and generate additional calendars with a different look and feel, to appeal to a large and varied customer base. 

Due to the proprietary nature of this solution, the solution files will not be accessible through this site.
However, a more detailed writeup can be found here.

No comments:

Post a Comment

Blog Archive