You haven't got a declaration for Count.
This will work
'************** *************** *************** *******
' Version 1
'************** *************** *************** *******
Private Type DayCounts
Mon As Long
Tue As Long
Wed As Long
Thu As Long
Fri As Long
Months As Long
TotDays As Long
End Type
Private Count As DayCounts
Private Sub CalcDays_Click( )
Call HowManySpecific Days("01/01/2002", "31/12/2002")
Debug.Print "Between these 2 dates there are:"
Debug.Print Count.Mon & " Mondays"
Debug.Print Count.Tue & " Tuesdays"
Debug.Print Count.Wed & " Wednesdays"
Debug.Print Count.Thu & " Thursdays"
Debug.Print Count.Fri & " Fridays"
Debug.Print Count.Mon + Count.Tue + Count.Wed + Count.Thu +
Count.Fri & " Chargeable Days"
Debug.Print Count.TotDays & " Total Days"
End Sub
Public Function HowManySpecific Days(StartDate As Date, EndDate As Date) As
DayCounts
On Error GoTo HowManySpecific Days_Error
Dim dteDateStart As Date
Dim dteDateEnd As Date
Dim intMondays As Integer
Dim intTuesdays As Integer
Dim intWednesdays As Integer
Dim intThursdays As Integer
Dim intFridays As Integer
Dim intTotDays As Integer
Dim intDay As Integer
Dim intMonth As Integer
Dim intMonthCount As Integer
dteDateStart = Format(StartDat e, "dd/mm/yyyy")
dteDateEnd = Format(EndDate, "dd/mm/yyyy")
intMonthCount = 1
intMonth = Month(dteDateSt art)
'Loop through the date range and work out how many days in the period
Do Until dteDateStart = dteDateEnd + 1
intDay = Weekday(dteDate Start)
If Not intMonth = Month(dteDateSt art) Then
intMonth = Month(dteDateSt art)
intMonthCount = intMonthCount + 1
End If
Select Case intDay
Case 2 'Mondays
intMondays = intMondays + 1
Case 3 'Tuesdays
intTuesdays = intTuesdays + 1
Case 4 'Wednesdays
intWednesdays = intWednesdays + 1
Case 5 'Thursdays
intThursdays = intThursdays + 1
Case 6 'Fridays
intFridays = intFridays + 1
Case Else
End Select
dteDateStart = dteDateStart + 1
intTotDays = intTotDays + 1
Loop
With Count
.Mon = intMondays
.Tue = intTuesdays
.Wed = intWednesdays
.Thu = intThursdays
.Fri = intFridays
.Months = intMonthCount
.TotDays = intTotDays
End With
HowManySpecific Days = Count
On Error GoTo 0
Exit Function
HowManySpecific Days_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
HowManySpecific Days"
End Function
'************** *************** *************** *******
' /Version 1
'************** *************** *************** *******
But this is better
'************** *************** *************** *******
' Version 2
'************** *************** *************** *******
' In a module on it's own
Public Type DayCounts
Mon As Long
Tue As Long
Wed As Long
Thu As Long
Fri As Long
Months As Long
TotDays As Long
End Type
Public Function HowManySpecific Days(StartDate As Date, EndDate As Date) As
DayCounts
On Error GoTo HowManySpecific Days_Error
Dim dteDateStart As Date
Dim dteDateEnd As Date
Dim intMondays As Integer
Dim intTuesdays As Integer
Dim intWednesdays As Integer
Dim intThursdays As Integer
Dim intFridays As Integer
Dim intTotDays As Integer
Dim intDay As Integer
Dim intMonth As Integer
Dim intMonthCount As Integer
dteDateStart = Format(StartDat e, "dd/mm/yyyy")
dteDateEnd = Format(EndDate, "dd/mm/yyyy")
intMonthCount = 1
intMonth = Month(dteDateSt art)
'Loop through the date range and work out how many days in the period
Do Until dteDateStart = dteDateEnd + 1
intDay = Weekday(dteDate Start)
If Not intMonth = Month(dteDateSt art) Then
intMonth = Month(dteDateSt art)
intMonthCount = intMonthCount + 1
End If
Select Case intDay
Case 2 'Mondays
intMondays = intMondays + 1
Case 3 'Tuesdays
intTuesdays = intTuesdays + 1
Case 4 'Wednesdays
intWednesdays = intWednesdays + 1
Case 5 'Thursdays
intThursdays = intThursdays + 1
Case 6 'Fridays
intFridays = intFridays + 1
Case Else
End Select
dteDateStart = dteDateStart + 1
intTotDays = intTotDays + 1
Loop
With HowManySpecific Days
.Mon = intMondays
.Tue = intTuesdays
.Wed = intWednesdays
.Thu = intThursdays
.Fri = intFridays
.Months = intMonthCount
.TotDays = intTotDays
End With
On Error GoTo 0
Exit Function
HowManySpecific Days_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
HowManySpecific Days"
End Function
' Then called like this from any module
Private Sub CalcDays_Click( )
Dim Count As DayCounts
Count = HowManySpecific Days("01/01/2002", "31/12/2002")
Debug.Print "Between these 2 dates there are:"
Debug.Print Count.Mon & " Mondays"
Debug.Print Count.Tue & " Tuesdays"
Debug.Print Count.Wed & " Wednesdays"
Debug.Print Count.Thu & " Thursdays"
Debug.Print Count.Fri & " Fridays"
Debug.Print Count.Mon + Count.Tue + Count.Wed + Count.Thu +
Count.Fri & " Chargeable Days"
Debug.Print Count.TotDays & " Total Days"
End Sub
'************** *************** *************** *******
' /Version 2
'************** *************** *************** *******
Although whether the code for the HowManySpecific Days function is correct I
wouldn't like to say.
Terry
"David Mitchell" <da************ **@talk21.com> wrote in message
news:c3******** *************** **@posting.goog le.com...
I have tried using the following code to count the specific number of
each weekday but get a compile error "User defined type not defined"
which I think relates to the first line of the function: -
Public Function HowManySpecific Days(StartDate As Date, EndDate As
Date) As DayCounts
and specifically the word "DayCounts"
Can anyone help?
Full code is as follows.
Private Sub CalcDays_Click( )
Call HowManySpecific Days("01/01/2002", "31/12/2002")
Debug.Print "Between these 2 dates there are:"
Debug.Print Count.Mon & " Mondays"
Debug.Print Count.Tue & " Tuesdays"
Debug.Print Count.Wed & " Wednesdays"
Debug.Print Count.Thu & " Thursdays"
Debug.Print Count.Fri & " Fridays"
Debug.Print Count.Mon + Count.Tue + Count.Wed + Count.Thu +
Count.Fri & " Chargeable Days"
Debug.Print Count.TotDays & " Total Days"
End Sub
Public Function HowManySpecific Days(StartDate As Date, EndDate As
Date) As DayCounts
On Error GoTo HowManySpecific Days_Error
Dim dteDateStart As Date
Dim dteDateEnd As Date
Dim intMondays As Integer
Dim intTuesdays As Integer
Dim intWednesdays As Integer
Dim intThursdays As Integer
Dim intFridays As Integer
Dim intTotDays As Integer
Dim intDay As Integer
Dim intMonth As Integer
Dim intMonthCount As Integer
dteDateStart = Format(StartDat e, "dd/mm/yyyy")
dteDateEnd = Format(EndDate, "dd/mm/yyyy")
intMonthCount = 1
intMonth = Month(dteDateSt art)
'Loop through the date range and work out how many days in the
period
Do Until dteDateStart = dteDateEnd + 1
intDay = WeekDay(dteDate Start)
If Not intMonth = Month(dteDateSt art) Then
intMonth = Month(dteDateSt art)
intMonthCount = intMonthCount + 1
End If
Select Case intDay
Case 2 'Mondays
intMondays = intMondays + 1
Case 3 'Tuesdays
intTuesdays = intTuesdays + 1
Case 4 'Wednesdays
intWednesdays = intWednesdays + 1
Case 5 'Thursdays
intThursdays = intThursdays + 1
Case 6 'Fridays
intFridays = intFridays + 1
Case Else
End Select
dteDateStart = dteDateStart + 1
intTotDays = intTotDays + 1
Loop
With Count
.Mon = intMondays
.Tue = intTuesdays
.Wed = intWednesdays
.Thu = intThursdays
.Fri = intFridays
.Months = intMonthCount
.TotDays = intTotDays
End With
HowManySpecific Days = Count
On Error GoTo 0
Exit Function
HowManySpecific Days_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure HowManySpecific Days"
End Function
