Which Excel references used for Workday Formula in MS Excel 2003?

[T. Johnson]

when we used the workday formula in the VBA macro, some users facing problem
in workday calculation (MS EXCEL 2003) and they get #VALUE!".

Can you please help me which reference or Library is missing?

We have already activated the 2 VBA toolpak
(Analysis Toolpak & Analysis Toolpak - VBA)

We also have the VBE6.DLL file in the following file path
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\

We also have the Excel.exe file in the following file path
C:\Program Files\Microsoft Office\OFFICE 11\

We also have the stdole.tlb & FM20.dll file in the following file path
C:\Windows\System 32\

 

[Peter T]

The fact you are getting a #VALUE! error suggests the function is working
fine. Most likely is one or more invalid arguments is being passed to the
function.

Regards,
Peter T

 

[Rick Rothstein]

Can you show us the code line where you make use of the "workday formula in
the VBA macro"?

 

[Mike H]

Post the offending line(s) of code

 

[arjen van...]

You need to add a reference to atpvbaen.xls. The simplest way to do this is
in Visual Basic editor. Tools > References >
Look for atpvbaen.xls and put a checkmark beside it.

 

[arjen van...]

I should mention this can also be done via code. An example I've used:

calc_dt = Application.Run("ATPVBAEN.XLA!WorkDay", Now, -1,
Sheets("Procedures").Range("P157"))

 

[Peter T]

No addin or additional reference is required. The Weekday function is
built-in to all versions of Excel. It is also built-in to VBA, though it
looks like the OP is only using VBA to write a cell formula that includes
the Weekday function.

Regards,
Peter T

 

[Rick Rothstein]

You read the post too quickly Peter... the OP's post said he was trying to
use the WORKDAY function from the Analysis ToolPak, not Excel's built-in
WEEKDAY function.

 

[Peter T]

You read the post too quickly Peter...

Let me bounce that back to you <g>

The OP merely mentioned the ATP adidns are installed and some other
references. A "missing" ref of any type could impact on the function not
working if called in VBA (DateTime & Strings functions are particularly
sensitive to missing ref's). However the #Value! error suggests he is using
it in a cell formula, or conceivably but unlikely in VBA as a
"Worksheetfunction".

Regards,
Peter T

 

[Peter T]

Oh dear, Let me retract that !!!

Indeed, I misread Workday as Weekday - sorry!

Regards,
Peter T

 

[Rick Rothstein]

There is no question that the OP's question is unclear... his opening
paragraph where he mentions "macro" and "#VALUE!" error is an example of
that, which is why I asked him to post the code that is causing his troubles
in the hopes of narrowing down his real problem. However, my comment to you
had to do with your talking about the WEEKDAY function rather than the
WORKDAY function he specified he was using. Or am I simply missing something
relevant in your having done that.

 

[Rick Rothstein]

<g> No problem... and you can ignore my 'what amounted to a "huh?" message'
that I just sent in response to your previous posting... these messages
appear to have crossed each other in the ether.

 

[Peter T]

Lucky I got in with my retraction just in time!

Irrespective of my failure to read workday/weekday the diagnosis remains the
same. The #Value! error suggests all is working fine, just invalid
argument(s)

Regards,
Peter T

 

[T. Johnson]

Hello All,
Thanks for the reply.
Here is the codings...
FD declared as 'date' and
cell - H2 refers 7/12/2009 and cell - P2 refers 4

j = 2
k = 24
FD = Sheet1.Cells(8, k)
Sheet1.Cells(6, 47) = "=WORKDAY(H" & j & ",P" & j & ")"
If FD < Sheet1.Cells(6, 47) Then
Sheet1.Cells(j, k).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ColorIndex = 16
End With
End If

Kind regards
Johnson

 

[Rick Rothstein]

Sheet1.Cells(6, 47) = "=WORKDAY(H" & j & ",P" & j & ")"

For the above line of code, I would think you should be assigning that text
string to the Formula property of the cell...

Sheet1.Cells(6, 47).Formula = "=WORKDAY(H" & j & ",P" & j & ")"