Workday Function in VBA
 

Hi

I am trying to use the excel workday function within my VBA code as follows:

Dim Today As Date
Dim PrevWrkDay As Date

Today = Now()

PrevWrkDay = application.worksheetfunction.workday(today, -1)


I get a runtime error '438' - Object doesn't support this property or method


Can you please advise if I can get around this, without having to refernce a
cell in the spreadsheet.

Thanks in Advance
Richard

Workday Function in VBA
 

Richard,

Try this

PrevWrkDay = Application.Run("ATPVBAEN.XLA!Workday", Date, -1)
PrevWrkDay = Format(PrevWrkDay, "dd/mm/yyyy")

Mike


"Richard" wrote:

Hi

I am trying to use the excel workday function within my VBA code as follows:

Dim Today As Date
Dim PrevWrkDay As Date

Today = Now()

PrevWrkDay = application.worksheetfunction.workday(today, -1)


I get a runtime error '438' - Object doesn't support this property or method


Can you please advise if I can get around this, without having to refernce a
cell in the spreadsheet.

Thanks in Advance
Richard

Workday Function in VBA
 

Mike

Thanks looks good right now, best test is either a Monday or first day of
month. I'll let you know how it goes.

"Mike H" wrote:

Richard,

Try this

PrevWrkDay = Application.Run("ATPVBAEN.XLA!Workday", Date, -1)
PrevWrkDay = Format(PrevWrkDay, "dd/mm/yyyy")

Mike


"Richard" wrote:

Hi

I am trying to use the excel workday function within my VBA code as follows:

Dim Today As Date
Dim PrevWrkDay As Date

Today = Now()

PrevWrkDay = application.worksheetfunction.workday(today, -1)


I get a runtime error '438' - Object doesn't support this property or method


Can you please advise if I can get around this, without having to refernce a
cell in the spreadsheet.

Thanks in Advance
Richard

Workday Function in VBA
 

PrevWrkDay = application.Evaluate("WORKDAY(TODAY(),-1)")

--
__________________________________
HTH

Bob

"Richard" wrote in message
...
Hi

I am trying to use the excel workday function within my VBA code as
follows:

Dim Today As Date
Dim PrevWrkDay As Date

Today = Now()

PrevWrkDay = application.worksheetfunction.workday(today, -1)


I get a runtime error '438' - Object doesn't support this property or
method


Can you please advise if I can get around this, without having to refernce
a
cell in the spreadsheet.

Thanks in Advance
Richard