Explanation of the VBA code for the calendar

Explanation of the VBA code for the calendar
Last Updated on 12/05/2024
Reading time: 3 minutes

The article will explain each line of the VBA code to hide the column of the automatic calendar.

For a calendar that saves data across months, consider exploring the resources in this article.

Remind of the VBA code to hide the last days in the calendar

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 to create a variable to read each column index (1, 2, 3,...).
  • The variable Num_Col is declared (Dim) as a Whole Number (As Long)
  • Because the column index of Excel is never express with a decimal

Line 3: Beginning of the loop

Now, we are going to create a loop to extend the variable Num_Col from 30 till 32.

The reason why we start our loop 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

The column 30, 31 and 32 of the VBA in the calendar

Line 4: 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).

It seems complex to create a test for each case. On the other hand, Excel calculates precisely a date even if the number of days to add extends to the end of the month.

For instance, if we add 30 days to the 1st of February 2019, Excel will return the date of the 3rd of March 2019.

=DATE(2019;2;1)+30 => 03/03/2019

Calculated dates when February is selected

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 Cells(6, 30)

But you can also replace one of the arguments of Cells by a variable like this Cells(6, Num_Col)

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 (returned by the VBA function Month) of columns 30, 31 et 32 and the value returned by the dropdown menu for the month.

Result returns by the dropdown menu. This value will be used in the VBA code for the calendar 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 😀

Line 5: Hide the columns of the calendar in VBA

If the test is True, the column Num_Col is hidden (Hidden = True).

Line 6 and 7: Unhide the column

Otherwise (Else), which means the test is False, then the column Num_Col is un-hidden (Hidden = False)

This test looks weird but in this case, your selected month has 31 days, and 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, you must indicate when the test is ended. And it's the same for a loop.

At the end of the test If is End If, and the loop For, you must write the instruction 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 ClearContents

Line 11: End of the subroutine

Precisely like to indicate the end of a test or a loop, you must indicate that your subroutine is ended with the instruction End Sub

20 Comments

  1. Karla
    22/02/2023 @ 16:41

    How can I make the data stay for each month?

    Reply

    • Frédéric LE GUEN
      18/03/2023 @ 08:33

      I will publish a workbook with this functionality soon

      Reply

  2. Karla
    22/02/2023 @ 16:39

    Great explanation! How can I make the data stay put each month?

    Thanks!

    Reply

  3. Koko Vidallo
    24/01/2023 @ 14:02

    How to leave the contents in the table. Contents do not remain when I change the months. May I know what should be the command for different content per month

    Reply

  4. Sameer Wagh
    08/01/2023 @ 16:18

    Sir, very nice teaching methods ods, pls introduce vba code

    Reply

  5. Melinda
    06/12/2022 @ 22:36

    I just stumbled upon this and it is amazing what i have learned. Thank you.

    But in reviewing other comments, is there any way that final piece (storing the data entered on the calendar from month to month?

    Reply

    • Frédéric LE GUEN
      16/12/2022 @ 10:25

      Soon 😉

      Reply

  6. Eddy Tuytschaever
    06/10/2022 @ 11:48

    formula is wrong, it should be If Month(Cells(6, Num_Kol)) > Cells(1, 1) Then
    instead of If Month(Cells(6, Num_Kol)) > =Cells(1, 1) Then

    Reply

  7. Wesley
    04/02/2022 @ 19:04

    It is a HTML conversion error...change the line to this:

    If Month(Cells(6, Num_Col)) > Cells(1, 1) Then

    Reply

  8. Andrew
    26/01/2022 @ 12:32

    I actually have the same problem. If I select "February" the visualized days are 28, but then, if I chose another month, let's say "January", the visualized days are still 28 and not 31.
    Is there a way to solve this issue?

    Thank you

    Reply

  9. Jennifer Campos
    23/12/2021 @ 16:11

    Hello, I need to track vacation days in this calendar can you tell me how to adjust the code so that It keeps the days marked as vacation or off and not wipe them out? thank you

    Reply

  10. John
    05/10/2021 @ 22:36

    Hi Sir!

    The code not working.. How I can solve?

    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

    Reply

    • James
      16/11/2021 @ 19:35

      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

      Reply

    • James
      16/11/2021 @ 19:40

      The site is missing the not equal sign in the code between.
      If Month(Cells(6, Num_Col)) equal sign Cells(1, 1) Then

      Reply

      • Frédéric LE GUEN
        17/11/2021 @ 18:42

        Thanks, I check that

  11. Randhirsingh
    28/01/2021 @ 18:05

    Great Sir can I make this in Google sheet please

    Reply

    • Frédéric LE GUEN
      28/01/2021 @ 18:15

      VBA isn't available with Google Sheet. Only for Excel

      Reply

  12. Haris Awan
    16/11/2020 @ 21:28

    Hi Sir!
    I have a question regarding VBA Formula for column hide. Can you kindly assist me?

    Reply

    • Frédéric LE GUEN
      16/11/2020 @ 21:47

      I have added a link for the explanation of the code. This will help you to customize your project

      Reply

      • Jack
        01/12/2021 @ 11:16

        For me it seems it hides columns 30-32 regardless of the day when I use this formula. (calendar made same as your guide)
        It seems like it checks for February and stays there, any way to circumvent that?

Leave a Reply

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

Explanation of the VBA code for the calendar

Reading time: 3 minutes
Last Updated on 12/05/2024

The article will explain each line of the VBA code to hide the column of the automatic calendar.

For a calendar that saves data across months, consider exploring the resources in this article.

Remind of the VBA code to hide the last days in the calendar

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 to create a variable to read each column index (1, 2, 3,...).
  • The variable Num_Col is declared (Dim) as a Whole Number (As Long)
  • Because the column index of Excel is never express with a decimal

Line 3: Beginning of the loop

Now, we are going to create a loop to extend the variable Num_Col from 30 till 32.

The reason why we start our loop 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

The column 30, 31 and 32 of the VBA in the calendar

Line 4: 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).

It seems complex to create a test for each case. On the other hand, Excel calculates precisely a date even if the number of days to add extends to the end of the month.

For instance, if we add 30 days to the 1st of February 2019, Excel will return the date of the 3rd of March 2019.

=DATE(2019;2;1)+30 => 03/03/2019

Calculated dates when February is selected

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 Cells(6, 30)

But you can also replace one of the arguments of Cells by a variable like this Cells(6, Num_Col)

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 (returned by the VBA function Month) of columns 30, 31 et 32 and the value returned by the dropdown menu for the month.

Result returns by the dropdown menu. This value will be used in the VBA code for the calendar 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 😀

Line 5: Hide the columns of the calendar in VBA

If the test is True, the column Num_Col is hidden (Hidden = True).

Line 6 and 7: Unhide the column

Otherwise (Else), which means the test is False, then the column Num_Col is un-hidden (Hidden = False)

This test looks weird but in this case, your selected month has 31 days, and 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, you must indicate when the test is ended. And it's the same for a loop.

At the end of the test If is End If, and the loop For, you must write the instruction 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 ClearContents

Line 11: End of the subroutine

Precisely like to indicate the end of a test or a loop, you must indicate that your subroutine is ended with the instruction End Sub

20 Comments

  1. Karla
    22/02/2023 @ 16:41

    How can I make the data stay for each month?

    Reply

    • Frédéric LE GUEN
      18/03/2023 @ 08:33

      I will publish a workbook with this functionality soon

      Reply

  2. Karla
    22/02/2023 @ 16:39

    Great explanation! How can I make the data stay put each month?

    Thanks!

    Reply

  3. Koko Vidallo
    24/01/2023 @ 14:02

    How to leave the contents in the table. Contents do not remain when I change the months. May I know what should be the command for different content per month

    Reply

  4. Sameer Wagh
    08/01/2023 @ 16:18

    Sir, very nice teaching methods ods, pls introduce vba code

    Reply

  5. Melinda
    06/12/2022 @ 22:36

    I just stumbled upon this and it is amazing what i have learned. Thank you.

    But in reviewing other comments, is there any way that final piece (storing the data entered on the calendar from month to month?

    Reply

    • Frédéric LE GUEN
      16/12/2022 @ 10:25

      Soon 😉

      Reply

  6. Eddy Tuytschaever
    06/10/2022 @ 11:48

    formula is wrong, it should be If Month(Cells(6, Num_Kol)) > Cells(1, 1) Then
    instead of If Month(Cells(6, Num_Kol)) > =Cells(1, 1) Then

    Reply

  7. Wesley
    04/02/2022 @ 19:04

    It is a HTML conversion error...change the line to this:

    If Month(Cells(6, Num_Col)) > Cells(1, 1) Then

    Reply

  8. Andrew
    26/01/2022 @ 12:32

    I actually have the same problem. If I select "February" the visualized days are 28, but then, if I chose another month, let's say "January", the visualized days are still 28 and not 31.
    Is there a way to solve this issue?

    Thank you

    Reply

  9. Jennifer Campos
    23/12/2021 @ 16:11

    Hello, I need to track vacation days in this calendar can you tell me how to adjust the code so that It keeps the days marked as vacation or off and not wipe them out? thank you

    Reply

  10. John
    05/10/2021 @ 22:36

    Hi Sir!

    The code not working.. How I can solve?

    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

    Reply

    • James
      16/11/2021 @ 19:35

      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

      Reply

    • James
      16/11/2021 @ 19:40

      The site is missing the not equal sign in the code between.
      If Month(Cells(6, Num_Col)) equal sign Cells(1, 1) Then

      Reply

      • Frédéric LE GUEN
        17/11/2021 @ 18:42

        Thanks, I check that

  11. Randhirsingh
    28/01/2021 @ 18:05

    Great Sir can I make this in Google sheet please

    Reply

    • Frédéric LE GUEN
      28/01/2021 @ 18:15

      VBA isn't available with Google Sheet. Only for Excel

      Reply

  12. Haris Awan
    16/11/2020 @ 21:28

    Hi Sir!
    I have a question regarding VBA Formula for column hide. Can you kindly assist me?

    Reply

    • Frédéric LE GUEN
      16/11/2020 @ 21:47

      I have added a link for the explanation of the code. This will help you to customize your project

      Reply

      • Jack
        01/12/2021 @ 11:16

        For me it seems it hides columns 30-32 regardless of the day when I use this formula. (calendar made same as your guide)
        It seems like it checks for February and stays there, any way to circumvent that?

Leave a Reply

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