如何在 VBA 的语句中使用 WEEKNUM 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38282079/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use the WEEKNUM function in a statement in VBA?
提问by Ali
I want to write a VBA, to check if WEEKNUM or ISOWEEKNUM equal to the value, then run the rest of macro. I've tried to do that but I got an error because of using TODAY as arg.
我想编写一个 VBA，检查 WEEKNUM 或 ISOWEEKNUM 是否等于该值，然后运行宏的其余部分。我曾尝试这样做，但由于使用 TODAY 作为参数而出现错误。
采纳答案by Gary's Student
Here is one way to use both WEEKNUM()and TODAY()in VBA:
这是在 VBA 中同时使用WEEKNUM()和 的一种方法TODAY()：
Sub dural()
If Evaluate("=weeknum(today())") = 28 Then
MsgBox 28
Else
MsgBox "not 28"
End If
End Sub
回答by
Use the WorksheetFunction objector Excel Application objectto call native functions into VBA.
使用WorksheetFunction 对象或Excel Application 对象将本机函数调用到 VBA 中。
debug.print WorksheetFunction.WeekNum(Date)
debug.print application.WeekNum(Date)
debug.print WorksheetFunction.IsoWeekNum(Date)
debug.print application.IsoWeekNum(Date)
if application.WeekNum(Date) = 28 then
'it is currently 28
end if
In VBA, the TODAY()function is replaced by Date.
在 VBA 中，该TODAY()函数被替换为Date.
回答by skkakkar
The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard.
WEEKNUM 函数将包含 1 月 1 日的那一周视为一年中的第一周。但是，有一个欧洲标准将第一周定义为新年的大部分天数（四天或更多）。这意味着对于一月第一周有三天或更短时间的年份，WEEKNUM 函数会返回根据欧洲标准不正确的周数。
Sub test()
Dim wn As Integer
wn = Format(Date, "ww")
Debug.Print wn
End Sub
ISO Weeknum UDF is.
The ISO weeknumber
ISO Weeknum UDF 是。
ISO 周数
The ISO-criteria for the first week in a year: - a week starts at monday; so monday is the first day of any week - the first week in a year must contain at least 4 days in that year
一年中第一周的 ISO 标准： - 一周从星期一开始；所以星期一是任何一周的第一天 - 一年中的第一周必须至少包含该年的 4 天
- the 1st of january is in week 1 if it's a monday, tuesday, wednesday or thursday
- the 4th of january is always in week 1 of any year
the first thursday in a year is always in week 1
Public Function ISOweeknum(ByVal v_Date As Date) As Integer ISOweeknum = DatePart("ww", v_Date - Weekday(v_Date, 2) + 4, 2, 2) End FunctionTo test this Function another small routine can be added.
Sub test_today_weeknum() Dim dt As Date Dim wno As Integer 'In this example, the variable called LDate would now contain the current system date. dt = Date Debug.Print dt wno = ISOweeknum(dt) Debug.Print wno End Sub
- 如果是星期一、星期二、星期三或星期四，则 1 月 1 日在第 1 周
- 1 月 4 日总是在任何一年的第 1 周
一年中的第一个星期四总是在第 1 周
Public Function ISOweeknum(ByVal v_Date As Date) As Integer ISOweeknum = DatePart("ww", v_Date - Weekday(v_Date, 2) + 4, 2, 2) End Function为了测试这个功能，可以添加另一个小程序。
Sub test_today_weeknum() Dim dt As Date Dim wno As Integer 'In this example, the variable called LDate would now contain the current system date. dt = Date Debug.Print dt wno = ISOweeknum(dt) Debug.Print wno End Sub
For today i.e. 9-7-2016, First one gives weeknum 28 whereas ISO weeknum function gives 27
对于今天，即 9-7-2016，第一个给出 weeknum 28 而 ISO weeknum 函数给出 27
For 9-7-2015 both the above routines will give weeknum 28
对于 2015 年 9 月 7 日，上述例程都将给出第 28 周
To get the current day (without the time portion) in a cell formula in Excel, use:
要在 Excel 的单元格公式中获取当前日期（不含时间部分），请使用：
=TODAY()
In the VBA programming environment, however, the function would be DATE().
Nowcontains both the current date and time while Dateand Timeare seperate commands for the date and time.It all depends on which information you need.
但是，在 VBA 编程环境中，该函数将是DATE().
Now包含而当前日期和时间都Date和Time都为单独约会的命令和time.It一切都取决于你需要的信息。
