时间戳

首页 > 解决方案 > WeekNum VBA 函数不会在特定计算机上输出值

excel - WeekNum VBA 函数不会在特定计算机上输出值

问题描述

我一直在寻找答案，但我失败了。我编写了以下代码，基本上在表中添加了一个新行，并在新行的第一个单元格中添加了当前周数：

Set table = Workbooks("EMEA Day 2 Chasing Audit Master Report.xlsm").Worksheets("Team Stats").ListObjects.Item("TableLilla")
Set oLastrowStats = Workbooks("EMEA Day 2 Chasing Audit Master Report.xlsm").Worksheets("Team Stats").ListObjects("TableLilla").ListRows.Add
Worksheets("Team Stats").ListObjects("TableLilla").ListColumns(1).DataBodyRange(oLastrowStats.Index, 1).Value = "W" & WorksheetFunction.WeekNum(Now, vbMonday)

奇怪的部分从这里开始：代码在两台计算机上正常工作，但在第三台计算机上却没有。在第 3 台计算机上，添加了新行，但第 3 行代码未插入周数。我很确定它与这个特定的笔记本有关，但是不知道在哪里寻找解决方案。我没有注意到 Excel 设置有任何重大差异。我还检查了 Win10 区域和日期设置，它们与我的笔记本上的相同。

你知道如何解决这个问题吗？

谢谢！

标签： excelvbaexcel-2016week-number

解决方案

尝试使用返回 ISO 8601 周数（和年份）的函数：

' Constants.

    Public Const MaxWeekValue           As Integer = 53
    Public Const MinWeekValue           As Integer = 1
    Public Const MaxMonthValue          As Integer = 12
    Public Const MinMonthValue          As Integer = 1


' Returns the ISO 8601 week of a date.
' The related ISO year is returned by ref.
'
' 2016-01-06. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Week( _
    ByVal Date1 As Date, _
    Optional ByRef IsoYear As Integer) _
    As Integer

    Dim Month       As Integer
    Dim Interval    As String
    Dim Result      As Integer
    
    Interval = "ww"
    
    Month = VBA.Month(Date1)
    ' Initially, set the ISO year to the calendar year.
    IsoYear = VBA.Year(Date1)
    
    Result = DatePart(Interval, Date1, vbMonday, vbFirstFourDays)
    If Result = MaxWeekValue Then
        If DatePart(Interval, DateAdd(Interval, 1, Date1), vbMonday, vbFirstFourDays) = MinWeekValue Then
            ' OK. The next week is the first week of the following year.
        Else
            ' This is really the first week of the next ISO year.
            ' Correct for DatePart bug.
            Result = MinWeekValue
        End If
    End If
        
    ' Adjust year where week number belongs to next or previous year.
    If Month = MinMonthValue Then
        If Result >= MaxWeekValue - 1 Then
            ' This is an early date of January belonging to the last week of the previous ISO year.
            IsoYear = IsoYear - 1
        End If
    ElseIf Month = MaxMonthValue Then
        If Result = MinWeekValue Then
            ' This is a late date of December belonging to the first week of the next ISO year.
            IsoYear = IsoYear + 1
        End If
    End If
    
    ' IsoYear is returned by reference.
    Week = Result
        
End Function

像这样：

Worksheets("Team Stats").ListObjects("TableLilla").ListColumns(1).DataBodyRange(oLastrowStats.Index, 1).Value = "W" & Week(Date)

推荐阅读