with using ontime i want to activiate macro at every month end

Question

with using ontime i want to activiate macro at every month end . in this i can put day as 31 but what about month which has day 30 or like feb month . If Day(Now) = 1 Then Application.ontime TimeValue("6:30:00"), "monthly" End If

There is a worksheet function for end of month, `EOMONTH`. – Darrell H Aug 10 '20 at 12:25 — Darrell H, Aug 10 '20 at 12:25
`=if day (date+1) = 1 then ...` – Ron Rosenfeld Aug 10 '20 at 12:30 — Ron Rosenfeld, Aug 10 '20 at 12:30

FaneDuru · Answer 1 · 2020-08-10T12:59:42.087

0

So, I would propose the next approach. Otherwise, somebody must work on the file and fier a specific event:

Create some events able to call the function you need for next day at a specific time (06:30:00):

Private Sub Workbook_Open()
   Application.OnTime TimeSerial(6, 30, 0), "DoSomething_Eomonth"
End Sub
Private Sub Workbook_SheetActivate(ByVal sh As Object)
   Application.OnTime TimeSerial(6, 30, 0), "DoSomething_Eomonth"
End Sub

Be sure that in the previous days the workbook in discussion is opened and (maybe) some work has been done on it. It must stay open.

The daily called recursive Sub will look like the next one:

Sub DoSomething_EndOfMonth()
  If Day(Date + 1) = 1 Then
    'Do what is to be done
  Else
     Application.OnTime TimeSerial(6, 30, 0), "DoSomething_Eomonth"
  End If
End Sub

edited Aug 10 '20 at 12:59

answered Aug 10 '20 at 12:52

FaneDuru

38,298
4
19
27

Could this not just be triggered through windows scheduler? Have it launch a script (which checks for EOM) once a day and if it's end of month then launch the excel file and "DoSomething_Emonth"? Although, their might be better alternatives to do this depending on OP's requirements – Zac Aug 10 '20 at 15:21
@Zac : Of course if can be done in many different ways. Even a service running and doing what is to be done (secret, enyhow... :)). But the question tags were Excel, VBA and OnTime... :) – FaneDuru Aug 10 '20 at 15:28
Yup, completely agree. Just think excel is a very heavy file to open everyday just to check EOM which can easily be done with a simple script. But like you said, OP tagged the post with Excel and VBA :) – Zac Aug 10 '20 at 15:30
@Zac : I am afraid you did not understood the code to well... It has a recursive `Sub` calling itself, in order to run next day at the same hour and **doing nothing**, except calling itself, if not end for month, when it will do whatever is to be done.. It is enough that the workbook to be used once per month and let it open. It will run by itself until end of month, when it will do whatever is to be done... :) I recommended to use it some days before only because some unexpected thinks may happen and the workbook to be closed... – FaneDuru Aug 10 '20 at 15:37
1

I do understand what the code is meant to do (and it does exactly what OP requested so please don't take my comments as any kind of reflection on your code. I have seen the code you provide here and I like your code and approach to the code). I'm more concerned about OP's approach – Zac Aug 10 '20 at 15:43
1

@Zac : i didn't want it to sound strange... I only tried to describe the way it works in order to answer the question like it was... :) – FaneDuru Aug 10 '20 at 15:48
@ashish arya: Isn't the above solution convenient for you? – FaneDuru Aug 11 '20 at 17:53

with using ontime i want to activiate macro at every month end

1 Answers1