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 HowManySpecificDays("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 HowManySpecificDays(StartDate As Date, EndDate As Date) As
DayCounts
On Error GoTo HowManySpecificDays_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(StartDate, "dd/mm/yyyy")
dteDateEnd = Format(EndDate, "dd/mm/yyyy")
intMonthCount = 1
intMonth = Month(dteDateStart)
'Loop through the date range and work out how many days in the period
Do Until dteDateStart = dteDateEnd + 1
intDay = Weekday(dteDateStart)
If Not intMonth = Month(dteDateStart) Then
intMonth = Month(dteDateStart)
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
HowManySpecificDays = Count
On Error GoTo 0
Exit Function
HowManySpecificDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
HowManySpecificDays"
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 HowManySpecificDays(StartDate As Date, EndDate As Date) As
DayCounts
On Error GoTo HowManySpecificDays_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(StartDate, "dd/mm/yyyy")
dteDateEnd = Format(EndDate, "dd/mm/yyyy")
intMonthCount = 1
intMonth = Month(dteDateStart)
'Loop through the date range and work out how many days in the period
Do Until dteDateStart = dteDateEnd + 1
intDay = Weekday(dteDateStart)
If Not intMonth = Month(dteDateStart) Then
intMonth = Month(dteDateStart)
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 HowManySpecificDays
.Mon = intMondays
.Tue = intTuesdays
.Wed = intWednesdays
.Thu = intThursdays
.Fri = intFridays
.Months = intMonthCount
.TotDays = intTotDays
End With
On Error GoTo 0
Exit Function
HowManySpecificDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
HowManySpecificDays"
End Function
' Then called like this from any module
Private Sub CalcDays_Click()
Dim Count As DayCounts
Count = HowManySpecificDays("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 HowManySpecificDays function is correct I
wouldn't like to say.
Terry
"David Mitchell" <da**************@talk21.com> wrote in message
news:c3*************************@posting.google.co m...
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 HowManySpecificDays(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 HowManySpecificDays("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 HowManySpecificDays(StartDate As Date, EndDate As
Date) As DayCounts
On Error GoTo HowManySpecificDays_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(StartDate, "dd/mm/yyyy")
dteDateEnd = Format(EndDate, "dd/mm/yyyy")
intMonthCount = 1
intMonth = Month(dteDateStart)
'Loop through the date range and work out how many days in the
period
Do Until dteDateStart = dteDateEnd + 1
intDay = WeekDay(dteDateStart)
If Not intMonth = Month(dteDateStart) Then
intMonth = Month(dteDateStart)
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
HowManySpecificDays = Count
On Error GoTo 0
Exit Function
HowManySpecificDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure HowManySpecificDays"
End Function
