In some excel files, I am using a macro to tell it in which moment to do the report. For instance, if I am doing a monthly report, and the month value is important, the result is not the same if I am doing the beginning of the next month than the end of the current month.

 

When I use the macro ?

This macro is useful, for instance, when I am doing a report based on the time or the day. For instance, doing a daily report, for the morning, do the macro1 and for the afternoon, do the macro2.

This macro can be modified to use the day instead of the time. For example, between 1 and 10 do the macro1 and between 20 and 31, do the macro2, the other dates do nothing.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How to use the macro ?

The macro has to be with the other macros that you are going to ask to execute so either:

• In the same Microsoft excel object, for instance, sheet1
• Or in the same module, for instance, module1

For instance, if your macro1 is in the “sheet1”, put this one also in the “sheet1”. If it is not in the “sheet1” but in the “module1”, put it in the “module1”. It will not work if your macro1 is in the “sheet1” and you put this macro in the “module1”.

 

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

For day:

Sub test()
Dim datbeg As Date
Dim datend As Date
' DateSerial(Year(Date), Month(Date), Day(Date)) so 1 and 15 = day, if specific month, put number for instance March is 3
datbeg = DateSerial(Year(Date), Month(Date), 1)
datend = DateSerial(Year(Date), Month(Date), 15)
If Date >= datbeg And Date <= datend Then ' if current day is between 1 and 15
Call macro1 ' if yes, run this macro
Else
Call macro2 ' if no, run this macro
End If
End Sub

Alternatively:

Sub test()
' DateSerial(Year(Date), Month(Date), Day(Date)) so 1 and 15 = day, if specific month, put number for instance March is 3
If Date >= DateSerial(Year(Date), Month(Date), 1) And Date <= DateSerial(Year(Date), Month(Date), 15) Then
Call macro1 ' if yes, run this macro
Else
Call macro2 ' if no, run this macro
End If
End Sub

For time:

Sub test()
If Hour(Now) >= Hour("8:00:00") And Hour(Now) <= Hour("14:00:00") Then ' if time is between 8 and 14 in 24h format
Call macro1 ' if yes, run this macro
Else
Call macro2' if no, run this macro
End If
End Sub

Alternatively, this one works better with minutes:

Sub test()
If Time >= TimeValue("8:30:00") And Time <= TimeValue("14:30:00") Then ' if time is between 8h30 and 14h30 in 24h format
Call macro1 ' if yes, run this macro
Else
Call macro2' if no, run this macro
End If
End Sub