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\

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



"T. Johnson" <T. wrote in message
...
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\

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

--
Rick (MVP - Excel)


"T. Johnson" <T. wrote in message
...
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\

Post the offending line(s) of code

"T. Johnson" wrote:

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\

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.

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("P1:P57"))

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


"arjen van..." wrote in message
...

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.

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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"arjen van..." wrote in message
...

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.

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


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"arjen van..." wrote in message
...

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.

Oh dear, Let me retract that !!!

Indeed, I misread Workday as Weekday - sorry!

Regards,
Peter T



"Peter T" <peter_t@discussions wrote in message
...
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


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"arjen van..." wrote in message
...

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.

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 (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"arjen van..." wrote in message
...

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.

<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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
Oh dear, Let me retract that !!!

Indeed, I misread Workday as Weekday - sorry!

Regards,
Peter T



"Peter T" <peter_t@discussions wrote in message
...
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


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"arjen van..." wrote in message
...

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.

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

"Rick Rothstein" wrote in message
...
<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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
Oh dear, Let me retract that !!!

Indeed, I misread Workday as Weekday - sorry!

Regards,
Peter T



"Peter T" <peter_t@discussions wrote in message
...
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


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"arjen van..." wrote in message
...

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.

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" wrote:

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 (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"arjen van..." wrote in message
...

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.

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 & ")"

--
Rick (MVP - Excel)


"T. Johnson" wrote in message
...
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" wrote:

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 (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
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


"arjen van..." wrote in message
...

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.