Excel VBA Calendar

Excel Calendar Generator – generate calendar in Excel VBA

It is 1 day before the end of 2015 hence I thought it might be interesting to create an automatically generated Excel Calendar using VBA that I could populate with holidays, anniversaries and other recurring events. But why repeat this manual process every year when we can force Excel VBA to do the work for us and knock up a Visual Basic Macro! Today’s goal: building an automatically generated Monthly Calendar using Excel VBA!

Excel Calendar: The result

Let’s start from the back – what we need is a Monthly Calendar that would generate automatically for any given year. Apart from that let’s also to be able to:

  • Add Holidays – provide month and day
  • Add custom dates such as anniversaries, birthdays, appointments etc. – again by just providing the month and day, and make sure that dates with more than one record with stack them row by row

The Excel Calendar generator needs to be reusable every year and require as little work as possible to copy our holidays and dates to the next year. This is my example for July 2016:
Excel Calendar Example

Generating the Excel VBA Calendar

Let’s have a look at the Excel VBA Calendar generator and how it works. It all comes down to 5 Easy Steps!
Steps to generating the Excel Calendar


Step 1: Excel Calendar Settings
Start by providing the Year for which you want to generate the calendar, the First day of the week and… your Language. Yes! – the Excel Calendar will appropriately name the Months and Days of the week in the language of your choosing.

Adjust Formatting

Step 2: Excel Calendar Adjust Formatting
You probably want your Excel Monthly Calendar a little more personalized. You can adjust the formatting of key elements.


Step 3: Excel Calendar Holidays
Add your Holidays in this step. Working days marked as Holidays will be formatted similarly as weekends.


Step 4: Excel Calendar Dates
Add your anniversaries, birthdays or other important Dates in this step, and admire how your personalized Excel Calendar stacks them.


Step 5: Excel Calendar Generate
Last but not least simply hit the Generate button to generate the Excel Calendar and fill it with your input Holidays and Dates. Enjoy!


Feel free to download the Excel Calendar generator here:

Be sure to share your feedback in the comments below or on my Social websites!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.