"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) An
(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 Functio

 

[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
"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 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