determining system setting variables (first day of week)

[directormac]

ALMOST originally posted by DirectorMac:

I'm sure Pat or Mile or Doc_Man or mjava or a thousand other people have an easy answer for this one, but I haven't found it posted or in Help...

I'm updating some date and time related functions that I originally created while working at a place that ALWAYS used Sun-Sat weeks. They seem fairly useful, so I want to add them to my "useful stuff" collection--BUT:

I want to be able to specify the day of the week used as the "first" day of the week, in the same manner that the standard VBA functions do; and, as with the standard functions, if no argument is supplied specificying the weekday to use, I want to use the system setting.

So how can I find out what the system setting IS?

I've tried using the vbUseSystemDayOfWeek but, of course, it always inserts the actual value of the constant which is 0.

I've been trying various clever combinations of ...]

At that point I suddenly recalled the way we used to figure out if a number was even or not by comparing the results of (number/2) with INT(number/2). At least, I used to use that as a test, back on my C64

Anyway, using the same idea and a known date (Jan 1, 2001 was a Monday), I came up with the following. I'm posting it only in case anyone else ever has this question. I'm convinced, though, that there's a more elegant solution. Anyone who has it, PLEASE feel free to post it!

--Chagrinned Mac

Public Function Find_vbUseSystemDayOfWeek() As VbDayOfWeek
' Returns a value matching the VB constant that corresponds to
' the day that the system is set to use as the first day of the week.
' Robert McMaster (directormac) - December, 2003

    Dim dtmTestDay As Date ' This will be the day used for testing (duh...)
    
    ' initialize variables...
    Find_vbUseSystemDayOfWeek = 0
    dtmTestDay = "1/1/2001"

    ' check against each possibility and set value to the one that tests true...
    If Weekday(dtmTestDay, vbSunday) = Weekday(dtmTestDay, vbUseSystemDayOfWeek) Then Find_vbUseSystemDayOfWeek = vbSunday
    If Weekday(dtmTestDay, vbMonday) = Weekday(dtmTestDay, vbUseSystemDayOfWeek) Then Find_vbUseSystemDayOfWeek = vbMonday
    If Weekday(dtmTestDay, vbTuesday) = Weekday(dtmTestDay, vbUseSystemDayOfWeek) Then Find_vbUseSystemDayOfWeek = vbTuesday
    If Weekday(dtmTestDay, vbWednesday) = Weekday(dtmTestDay, vbUseSystemDayOfWeek) Then Find_vbUseSystemDayOfWeek = vbWednesday
    If Weekday(dtmTestDay, vbThursday) = Weekday(dtmTestDay, vbUseSystemDayOfWeek) Then Find_vbUseSystemDayOfWeek = vbThursday
    If Weekday(dtmTestDay, vbFriday) = Weekday(dtmTestDay, vbUseSystemDayOfWeek) Then Find_vbUseSystemDayOfWeek = vbFriday
    If Weekday(dtmTestDay, vbSaturday) = Weekday(dtmTestDay, vbUseSystemDayOfWeek) Then Find_vbUseSystemDayOfWeek = vbSaturday
    
End Function

 

[raskew]

Robert-

Have to admit I'm not sure where to go to set the first day of week default setting.

Nonetheless, the following might 'pretty-up' your code a tad, although I'm not sure it's any more efficient:

Public Function Find_vbUseSystemDayOfWeek2() As Integer
' Returns a value matching the VB constant that corresponds to
' the day that the system is set to use as the first day of the week.
' Robert McMaster (directormac) - December, 2003
' Modified by raskew - December, MMIII

    Dim dtmTestDay As Date 
    Dim n As Integer
    
    dtmTestDay = #1/1/01#
    
    For n = 1 To 7
       If WeekDay(dtmTestDay, n) = WeekDay(dtmTestDay, vbUseSystemDayOfWeek) Then
          Exit For
       End If
    Next n
    
    Find_vbUseSystemDayOfWeek2 = n

End Function

Best wishes - Bob

 

[directormac]

Indeed - much cleaner - thank you!

Using all those individual tests instead of a simple loop...
/me smacks forhead - HARD

--Idiot Mac

 

[darin]

FYI: Here is a more condensed method of getting the SystemDayOfWeek:

Public Function SystemDayOfWeek() As Integer
Dim tmpSDOW
tmpSDOW = Weekday(Date, vbSunday) - Weekday(Date, vbUseSystemDayOfWeek)
If tmpSDOW < 0 Then SystemDayOfWeek = tmpSDOW + 8 Else SystemDayOfWeek = tmpSDOW + 1
End Function


FYI#2: For anyone who wants to test the code to verify if it really is doing what it should you can run some test code similar to the code below. Just tie the Command0_Click sub below to a command button on a form and what it does is count 1 through 7 for each possible day of week the system may have and then repeats once for each possible day of the week today could be.


Public Function TestSystemDayOfWeek(ByVal DayOfs As Integer, PretendSDOW As VbDayOfWeek) As Integer
Dim tmpSDOW
tmpSDOW = Weekday(Date + DayOfs, vbSunday) - Weekday(Date + DayOfs, PretendSDOW)
If tmpSDOW < 0 Then TestSystemDayOfWeek = tmpSDOW + 8 Else TestSystemDayOfWeek = tmpSDOW + 1
End Function

Private Sub Command0_Click()
Dim DateOfs As Byte, TmpLng&
For DateOfs = 1 To 7
MsgBox TestSystemDayOfWeek(DateOfs, vbSunday)
MsgBox TestSystemDayOfWeek(DateOfs, vbMonday)
MsgBox TestSystemDayOfWeek(DateOfs, vbTuesday)
MsgBox TestSystemDayOfWeek(DateOfs, vbWednesday)
MsgBox TestSystemDayOfWeek(DateOfs, vbThursday)
MsgBox TestSystemDayOfWeek(DateOfs, vbFriday)
MsgBox TestSystemDayOfWeek(DateOfs, vbSaturday)
Next DateOfs
End Sub