Workday Function in VBA

[charbona]

Something is not working right and I am not sure what it is...

I am trying to use the Workday function as a part of a macro - if myLTD is greater than myBMLT then I want to set myIRD by using the workday function - taking mySD (start date) plus a set # of days (my LTD + 2 days), and include the holidays located at "Worksheets("Parameters").Range("A2:A30"))". See code below...

Set mySD = ActiveCell
Set myLTD = ActiveCell.Offset(0, 9)
If myLTD > myBMLT Then
    Set myIRD = WorksheetFunction.WorkDay(mySD, myLTD + 2, Worksheets("Parameters").Range("A2:A30"))
 
End If
 
MsgBox myIRD

 

[mvptomlinson]

Don't use "Set". You only use Set when dealing with objects (ranges, OLEObjects, worksheets, etc.), not numeric/string variables.

 

[charbona]

Do you know where I can get a good example of how to use the workday function in VBA?

 

[mvptomlinson]

Google tells me:

http://msdn.microsoft.com/en-us/library/bb224770(v=office.12).aspx
http://msdn.microsoft.com/en-us/library/aa227592(v=vs.60).aspx

Amongst examples of its use in forums like this. (Search: "vba workday")

 

[Jonmo1]

The Workday Function is part of the Analysis Toolpack Addin.

To use ATP functions in VBA, you need to set a refernce to the Analysis Toolpack..
In VBA window, click tools - references
Check atpvbaen.xls

Then, you don't use the Application or Worksheet function object..
And as pointed out above, you don't use Set with this function..

Instead of
Set myIRD = WorksheetFunction.WorkDay(....

You write
myIRD = WorkDay(....

 

[mvptomlinson]

The Workday Function is part of the Analysis Toolpack Addin.

To use ATP functions in VBA, you need to set a refernce to the Analysis Toolpack..
In VBA window, click tools - references
Check atpvbaen.xls

Then, you don't use the Application or Worksheet function object..

This only applies if you're using Excel 2003 or older, which we don't know because you never told us. The WORKDAY function is built into Excel and VBA without any references in Excel 2007+, and you do use "Application.WorksheetFunction.Workday..."

 

[PaddyD]

duplicate, but movement on both threads so not going to lock this one:

http://www.mrexcel.com/forum/showthread.php?t=608584