VBA function to return previous week (and year) number on week 1?

[Unicam]

Hello everyone,

Here at work we use complex VBA scripts that import data from files from last week to compare them to this week's results. I have the scripts look up the files by the current year and week number, that's how they are stored.

To determine the week number I use this function:

Public Function WeekNummer(InDate As Date) As Long
   ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
   If ISOWeeknum > 52 Then
       If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
       ISOWeeknum = 1
       End If
   End If
End Function

I simply use 'WeekNummer(Date) - 1' to let the script determine last week's number.

To determine the year number I use this:

Format(Now(), "yyyy")

Both codes work fine, but this is always a problem during the first week of the year. The script obviously doesn't look for the right files and I cannot find a way around this anywhere. This always means I have to merge files manually which is a frustrating and annoying task. Can anyone tell me if there is a way to determine the 'REAL' week and year number of last week?

Any help would be greatly appreciated!

 

[Unicam]

Took me a while but I figured it out. In case anyone else is looking for a solution to the same problem, I'll post what I have in here. I added a little extra bit to change the first 9 weeks into a two-digit result.
I translated most of what I wrote into English, hope this helps anyone.

Function ThisWeek() As String
'   Show current week number as text, like 'Week 01'
    ThisWeek = "Week " & WeekNummer(Date)
'   Make sure the week number is always two digits
    If ThisWeek = "Week 1" Then ThisWeek = "Week 01"
    If ThisWeek = "Week 2" Then ThisWeek = "Week 02"
    If ThisWeek = "Week 3" Then ThisWeek = "Week 03"
    If ThisWeek = "Week 4" Then ThisWeek = "Week 04"
    If ThisWeek = "Week 5" Then ThisWeek = "Week 05"
    If ThisWeek = "Week 6" Then ThisWeek = "Week 06"
    If ThisWeek = "Week 7" Then ThisWeek = "Week 07"
    If ThisWeek = "Week 8" Then ThisWeek = "Week 08"
    If DezeWeek = "Week 9" Then DezeWeek = "Week 09"
End Function

Function ThisWeekNumber() As String
'   Show the current week number, like '01'
    ThisWeekNumber= WeekNummer(Date)
'   Make sure the week number is always two digits
    If ThisWeekNumber = 1 Then ThisWeekNumber = "01"
    If ThisWeekNumber = 2 Then ThisWeekNumber = "02"
    If ThisWeekNumber = 3 Then ThisWeekNumber = "03"
    If ThisWeekNumber = 4 Then ThisWeekNumber = "04"
    If ThisWeekNumber = 5 Then ThisWeekNumber = "05"
    If ThisWeekNumber = 6 Then ThisWeekNumber = "06"
    If ThisWeekNumber = 7 Then ThisWeekNumber = "07"
    If ThisWeekNumber = 8 Then ThisWeekNumber = "08"
    If ThisWeekNumber = 9 Then ThisWeekNumber = "09"
End Function

Function YearForThisWeekNumber() As String
'   Define the current year number. Can be handy during 'week 53', ISO standard dictates it to be week 1 of next year, during the last days of December in this week, the year needs to move up as well
    YearForThisWeekNumber = Format(Now(), "yyyy")
    If WeekNummer(Date) = 1 And Format(Now(), "mm") = "12" Then YearForThisWeekNumber = Format(Now(), "yyyy") + 1 Else YearForThisWeekNumber = Format(Now(), "yyyy")
End Function

Function LastWeekNumber() As String
'   Show the previous week number, like '01'
    LastWeekNumber = WeekNummer(Date) - 1
'   If it is currently the first week of the new year, change last week's number from 0 to 52
    If VorigWeekNummer = 0 Then VorigWeekNummer = 52
'   Make sure the week number is always two digits
    If LastWeekNumber = 1 Then LastWeekNumber = "01"
    If LastWeekNumber = 2 Then LastWeekNumber = "02"
    If LastWeekNumber = 3 Then LastWeekNumber = "03"
    If LastWeekNumber = 4 Then LastWeekNumber = "04"
    If LastWeekNumber = 5 Then LastWeekNumber = "05"
    If LastWeekNumber = 6 Then LastWeekNumber = "06"
    If LastWeekNumber = 7 Then LastWeekNumber = "07"
    If LastWeekNumber = 8 Then LastWeekNumber = "08"
    If LastWeekNumber = 9 Then LastWeekNumber = "09"
End Function

Function YearForLastWeekNumber() As Long
'   Define last week's year number. If it is now week 1, change the year output to last year to go with week 52 from function LastWeekNumber.
    If WeekNummer(Date) - 1 = 0 Then YearForLastWeekNumber = Format(Now() - 1, "yyyy") Else YearForLastWeekNumber = Format(Now(), "yyyy")
End Function