The article about the way to build an automatic calendar is one of the most viewed on this website. But it is also the one with the most comments about the VBA code.
The purpose of this article is to explain each line of code and the logic of the program.
Sub Hide_Day() Dim Num_Col As Long 'This instruction cleans the content of the cells in your calendar Range("B7:AF13").ClearContents For Num_Col = 30 To 32 ' Test if the month of the cells (row 6) is the same of the month selected (cell A1 or cells(1,1)) If Month(Cells(6, Num_Col)) >= Cells(1, 1) Then Columns(Num_Col).Hidden = True Else Columns(Num_Col).Hidden = False End If Next End Sub
Line 1: Creation of the name of the subroutine
In any VBA program all the code is written inside subroutines (
Sub). Each subroutine must have a unique name in the project.
This name will be used in the calendar project to link the code with the dropdown list.
Line 2 : Declare the variable
To evaluate the day's value in the different columns, we need a create variable in order to read each column index (1, 2, 3, ....)
Num_Col is declared (
Dim) as a Whole Number (
As Long) because the column index is obviously without decimal
Dim Num_Col As Long
Line 3 : Beginning of the loop
Now, we are going to create a loop to extend the variable
Num_Col from 30 till 32.
For Num_Col = 30 To 32
The reason why we start at the value 30 it's because for any month, the days 29, 30 et 31 will be always in columns 30, 31 and 32
Line4 : Test between the month calculated in the cells and the month selected
Here is the trick of the program 😉😎
How Excel calculates date?
As you know, all the months have 28 days. But February could have 29 days each 4 years and 4 months have 30 days (April, June, September, November).
Just like that, it seems complex to create a test for each case. But on the other hand, Excel calculates precisely a date even if the number of days to add extend to the end of the month.
For instance, if we add 30 days to the 1st February 2019, Excel will return the date of the 3rd March 2019.
=DATE(2019;2;1)+30 => 03/03/2019
How to code a cell in VBA
In VBA, to read the contents of a cell, you just have to write
Cells(index row, index column). So for A1, you will write
Cells(1, 1) and for the cell AD6 with day 29, the code is
But you can also replace one of the arguments of
Cells by a variable like this
Construction of the test
Back to the calendar, for the month of February 2019, columns 30, 31 and 32 will have dates of March (and not February) because of the rule explained in the previous formula.
So, we will write a test between the month (return by the VBA function
Month) of columns 30, 31 et 32 and the value returned by the dropdown menu for the month.
So, the test will check if the month in the cells AD6, AE6 and AF6 (or
Cells(6, 30), Cells(6, 31) et Cells(6,32)) is greater or equal to A1 (the cell linked to the dropdown menu for the month). But we don't need to test the 3 columns, the variable will do it for us 😀
If Month(Cells(6, Num_Col)) &gt;= Cells(1, 1) Then
Line 5 : Hide the column
If the test is True, the column
Num_Col is hidden (
Hidden = True).
Columns(Num_Col).Hidden = True
Line 6 and 7 : Unhide the column
Else), which means the test is False, then the column
Num_Col is un-hidden (
Hidden = False)
Else Columns(Num_Col).Hidden = False
This test looks stupid but in the case, your selected month has 31 days, you want to be able to display the previous column hidden (sooo clever 😉)
Line 8 and 9 : Close the instructions
In VBA, when you create a test or a loop, you must indicate when the test or the loop is ended.
At the end of the test
End If, and the loop
For, you must write the instruction
End If Next
Line 10 : Clear the data
After the loop on the 3 columns, the program will clear the contents of the range B6 to AF13
Range("B6:AF13") with the instruction
Line 11 : End of the subroutine
Exactly like to indicate the end of a test or a loop, you must indicate that your subroutine is ended with the instruction