Excel International Calendar

Create easily and instantly a calendar for any given year with highlighted public holidays of the selected country using Excel International Calendar. In this article we will go through the main features and macros that make it work.

Calendar Year

Excel International Calendar creates the full year calendar in a new worksheet upon selection of the calendar year in the main console form shown below. In that form, user can also select the country and calendar design.

The macro adds a new worksheet with the name of the year (CalendarYear) if it does not already exist as follows:

  ‘checking the worksheet does not already exist

  For Each ws In Worksheets

      If ws.Name = CalendarYear Then

          MsgBox ("Calendar already exists")

          Exit Sub

      End If

  Next

  ‘adding new worksheet

  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CalendarYear

Then it loops through each month and keeps adding days underneath. We create the following two arrays for months and week days to pick the corresponding month or week day while looping.

  CalWeekDays = Array("S", "M", "T", "W", "T", "F", "S")

  CalMonths = Array("January", "February", "March", "April", "May", "June", _

  "July", "August", "September", "October", "November", "December")

The loop within each month continues until the month’s last day. That day is different for each month (and dependent on year) and is calculated as follows:

  iMonthDate = "1/" & iMonth & "/" & CalendarYear

  monthend = Day(DateSerial(Year(iMonthDate), Month(iMonthDate) + 1, 1) - 1)

The variable iMonth is the month name taken from the array CalMonths.

The Excel VBA function Weekday determines the position for each day throughout the loop. Weekday assigns values from 1 to 7 following the American calendar (1 for Sunday, 2 for Monday, etc), so we need to tweak the code with some conditions to put Sunday last (to follow the European calendar, which is what I used at the time of creating this macro).

The macro will check the Holidays worksheet for each day and apply the corresponding formatting if it is indicated as a national, public, bank, or federal holiday (each country has a different naming convention).

Calendar Holidays

Excel International Calendar uses an automated Web Query to import the table of public holidays for a given country from the internet. Here’s how to import data from all tables of a given website (URL) by adding a Web Query to the active worksheet.

  Dim URL As String

  Dim qt As QueryTable

  Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, _

  Destination:=ActiveSheet.Range("A1"))

 

  With qt

          .WebSelectionType = xlAllTables

          .Refresh BackgroundQuery:=False

  End With

We use a popular date and time website to get that data. The Web Query may take a few seconds as it opens a new instance of Internet Explorer and navigates to the website. Here’s how it looks the imported table.

Another procedure looks for public holidays, formats the date it adding the proper year, and copies the result into the column F, which serves as a reference while looping through the days in the CreateCalendar procedure.

Please note Microsoft Excel, the operating system, and the browser language settings you are using may interfere with the date format if other than English. If no holidays are found this procedure will need some adjustments.

Calendar Format

Excel International Calendar comes with three different designs: Office, Professional, and Executive. The “Format” module contains procedures that apply formatting to tittle, months, weekdays, days and holidays accordingly.

 

We use all three possible VBA color properties to achieve the desired format. The simplest way to set or get color for objects such as a Cell or a Range of cells is using the Excel VB color, but the range of colors is very limited – only 8. The ColorIndex property allows for 56 basic colors, while the RGB property makes possible choosing from thousands of combinations of Red, Green, and Blue basic colors.

Click Here to download Excel International Calendar