"ByRef argument type mismatch" Error

[Baapi]

"ByRef argument type mismatch" Error for WeekNum(Comp_Date)
Help please...

Sub TAT()
Dim Effect_Recd_Date, Effect_Recd_Time As Date
Dim Recd_Date, Recd_Time, Comp_Date, Comp_Time, Shift_Start, Shift_End As Date
Dim Week_End As Date
R = 6
C = 35
Shift_Start = Range("D2").Value
Shift_End = Range("H2").Value
Week_End = DateSerial(2005, 1, 10) + (2 * Shift_Start) - DateSerial(2005, 1, 7) - Shift_End
Do
Recd_Date = Cells(R, C).Value
Recd_Time = Cells(R, C + 1).Value
'Calculation of Effective Received Time
If ((Recd_Time < Shift_End) Or ((Recd_Time >= Shift_Start) And (Recd_Time < 1))) Then
Effect_Recd_Time = Recd_Time
Else: Effect_Recd_Time = Shift_Start
End If
'Calculation of Effective Received Date
If ((Recd_Time <> Effect_Recd_Time) And (Weekday(Recd_Date, vbMonday) > 5)) Then
Effect_Recd_Date = Recd_Date + 8 - Weekday(Recd_Date, vbMonday)
Else: Effect_Recd_Date = Recd_Date
End If
Cells(R, C + 2).Value = Effect_Recd_Date
Cells(R, C + 3).Value = Effect_Recd_Time
'TAT Hour Calculation
Comp_Date = Cells(R, C - 11).Value
Comp_Time = Cells(R, C - 10).Value
If (WeekNum(Comp_Date) <> WeekNum(Effect_Recd_Date)) Then
TAT_Hour = Comp_Date + Comp_Time - Recd_Date - Recd_Time
End If

R = R + 1
Loop
'INT((X6+Y6-AA6-AB6)*24)-IF(WEEKNUM(X6)<>WEEKNUM(AA6),(WEEKNUM(X6)-WEEKNUM(AA6))*54.5,0)

End Sub

Function WeekNum(WeekDate As Date)
WeekNum = Round((DateSerial(Year(WeekDate), Month(WeekDate), Day(WeekDate)) _
- DateSerial(Year(WeekDate), 1, 0)) / 7, 0)
End Function

[Jim Cone]

B,

Most of your variables are declared as Variants not Dates.
You have to explicitly declare each one.
As a result you are trying to pass a "Variant" to a function requiring a "Date".
Also 3 of your variables are not declared at all.
It is strongly recommended that one use "Option Explicit" at the top of
each module. That will force a notification if any variables are not declared.

1.To get around your problem, you can explicitly declare each variable...
Dim Effect_Recd_Date as Date
Dim...etc
or
2.You can change the way the variable is passed to the function
by changing from "ByRef" (the default) to "ByVal"...
Function WeekNum(ByVal WeekDate As Date)

The first option is probably the best.

Jim Cone
San Francisco, USA


"Baapi"
<[email protected]>
wrote in message
news:[email protected]
"ByRef argument type mismatch" Error for WeekNum(Comp_Date)
Help please...
Sub TAT()
Dim Effect_Recd_Date, Effect_Recd_Time As Date
Dim Recd_Date, Recd_Time, Comp_Date, Comp_Time, Shift_Start, Shift_End
As Date
Dim Week_End As Date
R = 6
C = 35
Shift_Start = Range("D2").Value
Shift_End = Range("H2").Value
Week_End = DateSerial(2005, 1, 10) + (2 * Shift_Start) -
DateSerial(2005, 1, 7) - Shift_End
Do
Recd_Date = Cells(R, C).Value
Recd_Time = Cells(R, C + 1).Value
'Calculation of Effective Received Time
If ((Recd_Time < Shift_End) Or ((Recd_Time >= Shift_Start) And
(Recd_Time < 1))) Then
Effect_Recd_Time = Recd_Time
Else: Effect_Recd_Time = Shift_Start
End If
'Calculation of Effective Received Date
If ((Recd_Time <> Effect_Recd_Time) And (Weekday(Recd_Date,
vbMonday) > 5)) Then
Effect_Recd_Date = Recd_Date + 8 - Weekday(Recd_Date,
vbMonday)
Else: Effect_Recd_Date = Recd_Date
End If
Cells(R, C + 2).Value = Effect_Recd_Date
Cells(R, C + 3).Value = Effect_Recd_Time
'TAT Hour Calculation
Comp_Date = Cells(R, C - 11).Value
Comp_Time = Cells(R, C - 10).Value
If (WeekNum(Comp_Date) <> WeekNum(Effect_Recd_Date)) Then
TAT_Hour = Comp_Date + Comp_Time - Recd_Date - Recd_Time
End If
R = R + 1
Loop
'INT((X6+Y6-AA6-AB6)*24)-IF(WEEKNUM(X6)<>WEEKNUM(AA6),(WEEKNUM(X6)-WEEKNUM(AA6))*54.5,0)
End Sub

Function WeekNum(WeekDate As Date)
WeekNum = Round((DateSerial(Year(WeekDate), Month(WeekDate),
Day(WeekDate)) _
- DateSerial(Year(WeekDate), 1, 0)) / 7, 0)
End Function
--
Baapi

[Jim Thomlinson]

Further to Jim's comments check out this web site. Everything you ever wanted
to know about declaring and using variables...

http://www.cpearson.com/excel/variables.htm
--
HTH...

Jim Thomlinson


"Jim Cone" wrote:

> B,
>
> Most of your variables are declared as Variants not Dates.
> You have to explicitly declare each one.
> As a result you are trying to pass a "Variant" to a function requiring a "Date".
> Also 3 of your variables are not declared at all.
> It is strongly recommended that one use "Option Explicit" at the top of
> each module. That will force a notification if any variables are not declared.
>
> 1.To get around your problem, you can explicitly declare each variable...
> Dim Effect_Recd_Date as Date
> Dim...etc
> or
> 2.You can change the way the variable is passed to the function
> by changing from "ByRef" (the default) to "ByVal"...
> Function WeekNum(ByVal WeekDate As Date)
>
> The first option is probably the best.
>
> Jim Cone
> San Francisco, USA
>
>
> "Baapi"
> <[email protected]>
> wrote in message
> news:[email protected]
> "ByRef argument type mismatch" Error for WeekNum(Comp_Date)
> Help please...
> Sub TAT()
> Dim Effect_Recd_Date, Effect_Recd_Time As Date
> Dim Recd_Date, Recd_Time, Comp_Date, Comp_Time, Shift_Start, Shift_End
> As Date
> Dim Week_End As Date
> R = 6
> C = 35
> Shift_Start = Range("D2").Value
> Shift_End = Range("H2").Value
> Week_End = DateSerial(2005, 1, 10) + (2 * Shift_Start) -
> DateSerial(2005, 1, 7) - Shift_End
> Do
> Recd_Date = Cells(R, C).Value
> Recd_Time = Cells(R, C + 1).Value
> 'Calculation of Effective Received Time
> If ((Recd_Time < Shift_End) Or ((Recd_Time >= Shift_Start) And
> (Recd_Time < 1))) Then
> Effect_Recd_Time = Recd_Time
> Else: Effect_Recd_Time = Shift_Start
> End If
> 'Calculation of Effective Received Date
> If ((Recd_Time <> Effect_Recd_Time) And (Weekday(Recd_Date,
> vbMonday) > 5)) Then
> Effect_Recd_Date = Recd_Date + 8 - Weekday(Recd_Date,
> vbMonday)
> Else: Effect_Recd_Date = Recd_Date
> End If
> Cells(R, C + 2).Value = Effect_Recd_Date
> Cells(R, C + 3).Value = Effect_Recd_Time
> 'TAT Hour Calculation
> Comp_Date = Cells(R, C - 11).Value
> Comp_Time = Cells(R, C - 10).Value
> If (WeekNum(Comp_Date) <> WeekNum(Effect_Recd_Date)) Then
> TAT_Hour = Comp_Date + Comp_Time - Recd_Date - Recd_Time
> End If
> R = R + 1
> Loop
> 'INT((X6+Y6-AA6-AB6)*24)-IF(WEEKNUM(X6)<>WEEKNUM(AA6),(WEEKNUM(X6)-WEEKNUM(AA6))*54.5,0)
> End Sub
>
> Function WeekNum(WeekDate As Date)
> WeekNum = Round((DateSerial(Year(WeekDate), Month(WeekDate),
> Day(WeekDate)) _
> - DateSerial(Year(WeekDate), 1, 0)) / 7, 0)
> End Function
> --
> Baapi
>
>