vba 函数可选参数 DATE 类型失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18497942/
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
Function optional parameter DATE type Fails
提问by Playing with GAS
I have a function that finds the following Monday for a date. When no optional date parameter is passed it goes to a default of zero. I think I've included comments to explain problem
我有一个函数可以找到下一个星期一的日期。当没有传递可选的日期参数时，它会变为默认值零。我想我已经包含了解释问题的评论
Function NextMondayFromADateOrToday(Optional StartDate As Date) As Date
' objective: if date param not supplied should set StartDate to today
' ??? Problem: with no arg provided StartDate is zero = date value of 1/1/1900
' ??? how to add default to optional paramater of date type?
' ??? or, how to check if an arg was provided if the parameter is date type?
If Not (IsDate(StartDate)) Then StartDate = Date
Select Case Weekday(StartDate)
Case 1: NextMondayFromADateOrToday = StartDate + 1
Case 2: NextMondayFromADateOrToday = StartDate + 0
Case 3: NextMondayFromADateOrToday = StartDate + 6
Case 4: NextMondayFromADateOrToday = StartDate + 5
Case 5: NextMondayFromADateOrToday = StartDate + 4
Case 6: NextMondayFromADateOrToday = StartDate + 3
Case 7: NextMondayFromADateOrToday = StartDate + 2
End Select
End Function
回答by techturtle
VBA variables generally have a default value, and any optional parameters are automatically set to their default, so this behavior is expected. Since you already know that the optional parameter will default to 0 (1/1/1900) if not passed, why not just test for that value instead of testing to see if a date was passed in?
VBA 变量通常有一个默认值，任何可选参数都会自动设置为其默认值，因此这种行为是意料之中的。既然您已经知道如果不传递可选参数将默认为 0 (1/1/1900)，为什么不只测试该值而不是测试是否传入日期？
If, on the other hand, you think that someone might need to pass in 1/1/1900, then you should set the optional parameter as a Varianttype. Variants are not initialized when passed in optionally, so it will not have a default value (unlike dates).
另一方面，如果您认为有人可能需要传入 1/1/1900，那么您应该将可选参数设置为Variant类型。变量在可选传入时不会初始化，因此它没有默认值（与日期不同）。
回答by FreeMan
I know there's an accepted answer and that I'm late to the party, however...
我知道有一个可以接受的答案，而且我参加聚会迟到了，但是......
Perhaps provide a default date in the function declaration and test for that:
也许在函数声明和测试中提供一个默认日期：
Function NextMondayFromADateOrToday(Optional StartDate As Date = #12/31/1592#) As Date
' objective: if date param not supplied should set StartDate to today
' ??? Problem: with no arg provided StartDate is zero = date value of 1/1/1900
' ??? how to add default to optional paramater of date type?
' ??? or, how to check if an arg was provided if the parameter is date type?
If StartDate = #12/31/1592# Then
StartDate = Date
End If
'If Not (IsDate(StartDate)) Then StartDate = Date
Select Case Weekday(StartDate)
Case 1: NextMondayFromADateOrToday = StartDate + 1
Case 2: NextMondayFromADateOrToday = StartDate + 0
Case 3: NextMondayFromADateOrToday = StartDate + 6
Case 4: NextMondayFromADateOrToday = StartDate + 5
Case 5: NextMondayFromADateOrToday = StartDate + 4
Case 6: NextMondayFromADateOrToday = StartDate + 3
Case 7: NextMondayFromADateOrToday = StartDate + 2
End Select
End Function
If 12/31/1592 isn't sufficiently out of expected range, you could go earlier or later until you start hitting the limits of what a date can handle - a quick test shows Excel 2010 can take date of #1/1/100# and #1/1/9999#
如果 12/31/1592 未充分超出预期范围，您可以更早或更晚，直到您开始达到日期可以处理的限制 - 快速测试显示 Excel 2010 可以采用 #1/1/100 日期# 和 #1/1/9999#
回答by user3357963
Date cannot be null so perhaps use a variant in this case
日期不能为空，因此在这种情况下可能使用变体
Function NextMondayFromADateOrToday(Optional StartDate As Variant) As Date
' objective: if date param not supplied should set StartDate to today
' ??? Problem: with no arg provided StartDate is zero = date value of 1/1/1900
' ??? how to add default to optional paramater of date type?
' ??? or, how to check if an arg was provided if the parameter is date type?
If IsMissing(StartDate) Then StartDate = Date
Select Case Weekday(StartDate)
Case 1: NextMondayFromADateOrToday = StartDate + 1
Case 2: NextMondayFromADateOrToday = StartDate + 0
Case 3: NextMondayFromADateOrToday = StartDate + 6
Case 4: NextMondayFromADateOrToday = StartDate + 5
Case 5: NextMondayFromADateOrToday = StartDate + 4
Case 6: NextMondayFromADateOrToday = StartDate + 3
Case 7: NextMondayFromADateOrToday = StartDate + 2
End Select
End Function
