EXCEL VBA 类型不匹配错误“13”将日期数据类型分配给变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25369881/
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
EXCEL VBA Type Mismatch Error "13" Assigning Date Data Type to Variable
提问by Evan
I'm trying to loop through a column of dates, and any date that is within two other set dates (First Day of Week, Last Day of Week) or (First Day of Month, Last Day of Month).
我正在尝试遍历一列日期，以及其他两个设置日期（一周的第一天、一周的最后一天）或（一个月的第一天、最后一天）内的任何日期。
When I go to assign the date in the column to a variable, I get a type mismatch error.
当我将列中的日期分配给变量时，出现类型不匹配错误。
Sub JobsDue(ByRef DateStart As Date, ByRef DateEnd As Date)
Dim LastRow As Integer
LastRow = Sheet1.Cells(Rows.count, "B").End(xlUp).Row
Dim JobDueDate As String
Dim JobPN As String
For i = 10 To LastRow
JobDueDate = Sheet1.Range("B" & 10, "B" & i).Value (ERROR OCCURS HERE)
JobPN = Sheet1.Range("C" & 10, "C" & i).Value
If (JobDueDate) >= DateStart And (JobDueDate) <= DateEnd Then
lbJobsDue.AddItem (JobPN & "," & CDate(JobDueDate))
End If
Next i
End Sub
Private Sub comboxJobsDue_Change()
If comboxJobsDue.Value = "This Week" Then
StartOfWeek = Date - Weekday(Date) + 2
endofweek = Date - Weekday(Date) + 6
lblJobsDue.Caption = (StartOfWeek) & "-" & (endofweek)
Call JobsDue((StartOfWeek), (endofweek))
ElseIf comboxJobsDue.Value = "Next Week" Then
StartOfWeek = Date - Weekday(Date) + 9
endofweek = Date - Weekday(Date) + 13
lblJobsDue.Caption = (StartOfWeek) & "-" & (endofweek)
ElseIf comboxJobsDue.Value = "This Month" Then
FirstDayInMonth = DateSerial(Year(Date), Month(Date), 1)
LastDayInMonth = DateSerial(Year(Date), Month(Date) + 1, 0)
lblJobsDue.Caption = FirstDayInMonth & "-" & LastDayInMonth
ElseIf comboxJobsDue.Value = "Next Month" Then
FirstDayInMonth = DateSerial(Year(Date), Month(Date) + 1, 1)
LastDayInMonth = DateSerial(Year(Date), Month(Date) + 2, 0)
lblJobsDue.Caption = FirstDayInMonth & "-" & LastDayInMonth
End If
End Sub
The error occurs on Line 7, when I run the program to debug, JobDueDate gets a date but it is in the format of "9/25/2013", the other dates are of the format 8/18/2014 WITHOUT quotation marks. Can anyone explain to me why this error is happening and how I can go about fixing it?
错误发生在第 7 行，当我运行程序进行调试时，JobDueDate 获取了一个日期，但它的格式为“9/25/2013”，其他日期的格式为 8/18/2014 不带引号。任何人都可以向我解释为什么会发生此错误以及我如何解决它？
Thanks in advance, Evan
提前致谢，埃文
回答by xQbert
See the line declaration reading "Dim JobDueDate As String"(line 4 I think) You're telling the system you want it as a string... Perhaps you want Dim JobDueDate As Dateso you can use date functions on it.
请参阅行声明阅读"Dim JobDueDate As String"（我认为是第 4 行）您是在告诉系统您想要它作为字符串...也许您想要Dim JobDueDate As Date这样您就可以在其上使用日期函数。
DateStart and End are being passed in as dates so they would error on the code calling this sub if they passed in invalid dates.
DateStart 和 End 作为日期传入，因此如果传入无效日期，它们会在调用此子程序的代码上出错。
Additionally, you appear to be attempting to assign a range of dates to a date field. You either need a collection of dates or an array of dates to handle this.
此外，您似乎试图将日期范围分配给日期字段。您需要一组日期或一组日期来处理此问题。
