Find last weekday of the month with vba for all months

C

Cimjet

With VBA I need to detect the last weekday of each month. I need that to trigger an event macro on the last workday of each month.
Regards
Cimjet
 
R

Ron Rosenfeld

With VBA I need to detect the last weekday of each month. I need that to trigger an event macro on the last workday of each month.
Regards
Cimjet

To detect the last workday of the month in VBA, you can use:

Dim dLastWDOM As Date
dLastWDOM = WorksheetFunction.WorkDay(DateSerial(Year(Date), Month(Date) + 1, 1), -1)
If Date = dLastWDOM Then
...
 
C

Cimjet

To detect the last workday of the month in VBA, you can use:



Dim dLastWDOM As Date

dLastWDOM = WorksheetFunction.WorkDay(DateSerial(Year(Date), Month(Date) + 1, 1), -1)

If Date = dLastWDOM Then

...

Thank you Ron
Just could not get my head around it.
Much appreciated.
John
 
R

Ron Rosenfeld

Thank you Ron
Just could not get my head around it.
Much appreciated.
John

Glad to help.
But please note that if you will be using this in versions of Excel prior to 2007, a different algorithm will be required, as the Workday was part of the Analysis ToolPak (and not a built-in worksheetfunction) prior to Excel 2007.
Post back if that might be an issue.
 
C

Cimjet

Glad to help.

But please note that if you will be using this in versions of Excel prior to 2007, a different algorithm will be required, as the Workday was part of the Analysis ToolPak (and not a built-in worksheetfunction) prior to Excel 2007.

Post back if that might be an issue.

Hi Ron
You read my mind, I'm using XL03 and looking at your formula, I was sure it would work,it's almost the same as the cell function but no luck
I still need your help.
Thanks
John
 
R

Ron Rosenfeld

Hi Ron
You read my mind, I'm using XL03 and looking at your formula, I was sure it would work,it's almost the same as the cell function but no luck
I still need your help.
Thanks
John

OK, let me see if I can remember this. And I can't test it as I don't have Excel 2003 installed.

In Excel 2003, the WORKDAY function is part of the Analysis Tool Pak. To use it in VBA, do the following:

Open Excel; and select Tools/Add-Ins
Select: Analysis ToolPak - VBA (If that is not there, you will have to browse for it; try something like C:\Program Files\Microsoft Office\Office\Library or do a search)
Then open the VBA Editor.
Select Tools/References
Select the reference to atpvbaen.xls

You should now be able to use the Workday function directly in your VBA macro. So, in the macro, remove the "worksheetfunction." before the Workday, so it looks like:

dLastWDOM = WorkDay(DateSerial(Year(Date), Month(Date) + 1, 1), -1)
 
C

Cimjet

OK, let me see if I can remember this. And I can't test it as I don't have Excel 2003 installed.



In Excel 2003, the WORKDAY function is part of the Analysis Tool Pak. To use it in VBA, do the following:



Open Excel; and select Tools/Add-Ins

Select: Analysis ToolPak - VBA (If that is not there, you will have to browse for it; try something like C:\Program Files\Microsoft Office\Office\Library or do a search)

Then open the VBA Editor.

Select Tools/References

Select the reference to atpvbaen.xls



You should now be able to use the Workday function directly in your VBA macro. So, in the macro, remove the "worksheetfunction." before the Workday, so it looks like:



dLastWDOM = WorkDay(DateSerial(Year(Date), Month(Date) + 1, 1), -1)
Hi Ron

This time it's tried and tested and it works great.
I knew about the Analysis ToolPak and atpvbaen.xla, I just couldn't get the syntax right.
Thanks again
John
 
R

Ron Rosenfeld

This time it's tried and tested and it works great.
I knew about the Analysis ToolPak and atpvbaen.xla, I just couldn't get the syntax right.
Thanks again
John

Glad to help. Thanks for the feedback.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top