运行时错误 424 - 对象必需 (vba)
运行时错误 424 - 对象必需 (vba)
我是VBA的新手，尝试在Excel中构建自己的自动甘特图。我遇到了运行时错误424，需要对象。当我在模块中调用函数时出现这个错误（所有变量都是公共定义的常量，引用特定的行和列号）：
Call Colour_Spans(Define_Time_Spans_2(i, Fixed_rows, Fixed_columns, Start_week, Start_year, End_week, End_year, OPT, Task_dependency))
着色函数如下所示：
Public Sub Colour_Spans(s As Range) s.Interior.ColorIndex = 1 End Sub
如果下面的代码中执行了条件If IsEmpty(Cells(Fixed_rows + i, Task_dep)) Then，则一切正常，但是当执行条件ElseIf Not IsEmpty(Cells(Fixed_rows + i, Task_dep)) Then时，我会遇到错误。下面的代码基本上定义了条形图，然后对其进行着色。附加的条件意图在任务之间的时间跨度中引入依赖关系。如果我将其删除或者只是抑制它，一切都会顺利进行。
我自己花了很多时间，但找不到原因。第二部分的代码与工作部分非常相似。非常感谢您的帮助。
因此，我将整个代码复制在这里，问题就在这里。如果需要其他部分，请告诉我。非常感谢！
Public Function Define_Time_Spans_2(i, Fixed_rows, Fixed_columns, week_start_column, year_start_column, week_end_column, year_end_column, duration_weeks, Task_dep)
Dim Cancel_start_week_flag As Boolean
Dim Cancel_start_year_flag As Boolean
time_span_start_year = Cells(Fixed_rows + i, year_start_column) 'Iso_year_no(Cells(fixed_rows + i, week_start_column))
Cells(Fixed_rows + i, week_start_column).NumberFormat = "General"
If IsEmpty(Cells(Fixed_rows + i, Task_dep)) Then
' DEFINE YEAR INPUT
If Cells(Fixed_rows + i, year_start_column) < 2015 Or Cells(Fixed_rows + i, year_start_column) > 2020 Then
check_start_year = MsgBox("The specified start year " & Cells(Fixed_rows + i, year_start_column) & " in row " & Fixed_rows + i & " is outside the range 2015-2020!" & vbCrLf & "Would you like to change it?", vbYesNo + vbQuestion, "Wrong input!")
Select Case check_start_year
Case 6
Do While Cells(Fixed_rows + i, year_start_column) < 2015 Or Cells(Fixed_rows + i, year_start_column) > 2020
Cells(Fixed_rows + i, year_start_column) = InputBox("Enter start year number")
Loop
Case 7
Cancel_start_year_flag = True
End Select
If Cancel_start_year_flag = True Then
MsgBox "The time bars have not been updated!", vbExclamation, "The code was terminated!"
End
End If
time_span_start_year = Cells(Fixed_rows + i, year_start_column)
End If
' DEFINE WEEK INPUT
If time_span_start_year = 2015 Then
If Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 53 Then
check_start_week = MsgBox("The specified start week " & Cells(Fixed_rows + i, week_start_column) & " in row " & Fixed_rows + i & " does not exist in year " & time_span_start_year & "!" & vbCrLf & "Would you like to change it?", vbYesNo + vbQuestion, "Wrong input!")
Select Case check_start_week
Case 6
Do While Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 53
Cells(Fixed_rows + i, week_start_column) = InputBox("Enter start week number")
Loop
Case 7
Cancel_start_week_flag = True
End Select
If Cancel_start_week_flag = True Then
MsgBox "The time bars have not been updated!", vbExclamation, "The code was terminated!"
End
End If
End If
time_span_start_week = Cells(Fixed_rows + i, week_start_column) ' Iso_week_no(Cells(fixed_rows + i, week_start_column))
ElseIf time_span_start_year = 2016 Then
If Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 52 Then
check_start_week = MsgBox("The specified start week " & Cells(Fixed_rows + i, week_start_column) & " in row " & Fixed_rows + i & " does not exist in year " & time_span_start_year & "!" & vbCrLf & "Would you like to change it?", vbYesNo + vbQuestion, "Wrong input!")
Select Case check_start_week
Case 6
Do While Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 52
Cells(Fixed_rows + i, week_start_column) = InputBox("Enter start week number")
Loop
Case 7
Cancel_start_week_flag = True
End Select
If Cancel_start_week_flag = True Then
MsgBox "The time bars have not been updated!", vbExclamation, "The code was terminated!"
End
End If
End If
time_span_start_week = Cells(Fixed_rows + i, week_start_column) + 53 'Iso_week_no(Cells(fixed_rows + i, week_start_column)) + 53 ' 2016
ElseIf time_span_start_year = 2017 Then
If Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 52 Then
check_start_week = MsgBox("The specified start week " & Cells(Fixed_rows + i, week_start_column) & " in row " & Fixed_rows + i & " does not exist in year " & time_span_start_year & "!" & vbCrLf & "Would you like to change it?", vbYesNo + vbQuestion, "Wrong input!")
Select Case check_start_week
Case 6
Do While Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 52
Cells(Fixed_rows + i, week_start_column) = InputBox("Enter start week number")
Loop
Case 7
Cancel_start_week_flag = True
End Select
If Cancel_start_week_flag = True Then
MsgBox "The time bars have not been updated!", vbExclamation, "The code was terminated!"
End
End If
End If
time_span_start_week = Cells(Fixed_rows + i, week_start_column) + 53 + 52 ' Iso_week_no(Cells(fixed_rows + i, week_start_column)) + 53 + 52 ' 2017
ElseIf time_span_start_year = 2018 Then
If Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 52 Then
check_start_week = MsgBox("The specified start week " & Cells(Fixed_rows + i, week_start_column) & " in row " & Fixed_rows + i & " does not exist in year " & time_span_start_year & "!" & vbCrLf & "Would you like to change it?", vbYesNo + vbQuestion, "Wrong input!")
Select Case check_start_week
Case 6
Do While Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 52
Cells(Fixed_rows + i, week_start_column) = InputBox("Enter start week number")
Loop
Case 7
Cancel_start_week_flag = True
End Select
If Cancel_start_week_flag = True Then
MsgBox "The time bars have not been updated!", vbExclamation, "The code was terminated!"
End
End If
End If
time_span_start_week = Cells(Fixed_rows + i, week_start_column) + 53 + 52 + 52 ' Iso_week_no(Cells(fixed_rows + i, week_start_column)) + 53 + 52 + 52 ' 2018
ElseIf time_span_start_year = 2019 Then
If Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 52 Then
check_start_week = MsgBox("The specified start week " & Cells(Fixed_rows + i, week_start_column) & " in row " & Fixed_rows + i & " does not exist in year " & time_span_start_year & "!" & vbCrLf & "Would you like to change it?", vbYesNo + vbQuestion, "Wrong input!")
Select Case check_start_week
Case 6
Do While Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 52
Cells(Fixed_rows + i, week_start_column) = InputBox("Enter start week number")
Loop
Case 7
Cancel_start_week_flag = True
End Select
If Cancel_start_week_flag = True Then
MsgBox "The time bars have not been updated!", vbExclamation, "The code was terminated!"
End
End If
End If
time_span_start_week = Cells(Fixed_rows + i, week_start_column) + 53 + 52 + 52 + 52 ' Iso_week_no(Cells(fixed_rows + i, week_start_column)) + 53 + 52 + 52 + 52 ' 2019
ElseIf time_span_start_year = 2020 Then
If Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 53 Then
check_start_week = MsgBox("The specified start week " & Cells(Fixed_rows + i, week_start_column) & " in row " & Fixed_rows + i & " does not exist in year " & time_span_start_year & "!" & vbCrLf & "Would you like to change it?", vbYesNo + vbQuestion, "Wrong input!")
Select Case check_start_week
Case 6
Do While Cells(Fixed_rows + i, week_start_column) < 1 Or Cells(Fixed_rows + i, week_start_column) > 53
Cells(Fixed_rows + i, week_start_column) = InputBox("Enter start week number")
Loop
Case 7
Cancel_start_week_flag = True
End Select
If Cancel_start_week_flag = True Then
MsgBox "The time bars have not been updated!", vbExclamation, "The code was terminated!"
End
End If
End If
time_span_start_week = Cells(Fixed_rows + i, week_start_column) + 53 + 52 + 52 + 52 + 52 ' Iso_week_no(Cells(fixed_rows + i, week_start_column)) + 53 + 52 + 52 + 52 + 52 ' 2020
End If
time_span_end_week = time_span_start_week + Cells(Fixed_rows + i, duration_weeks) - 1
Set Define_Time_Spans_2 = Range(Cells(Fixed_rows + i, Fixed_columns + time_span_start_week), Cells(Fixed_rows + i, Fixed_columns + time_span_end_week))
' OUTPUT
If time_span_end_week <= 53 Then
end_year_output = 2015
end_week_output = time_span_end_week
ElseIf time_span_end_week > 53 And time_span_end_week <= 53 + 52 Then
end_year_output = 2016
end_week_output = time_span_end_week - 53
ElseIf time_span_end_week > 53 + 52 And time_span_end_week <= 53 + 52 + 52 Then
end_year_output = 2017
end_week_output = time_span_end_week - (53 + 52)
ElseIf time_span_end_week > 53 + 52 + 52 And time_span_end_week <= 53 + 52 + 52 + 52 Then
end_year_output = 2018
end_week_output = time_span_end_week - (53 + 52 + 52)
ElseIf time_span_end_week > 53 + 52 + 52 + 52 And time_span_end_week <= 53 + 52 + 52 + 52 + 52 Then
end_year_output = 2019
end_week_output = time_span_end_week - (53 + 52 + 52 + 52)
ElseIf time_span_end_week > 53 + 52 + 52 + 52 + 52 And time_span_end_week <= 53 + 52 + 52 + 52 + 52 + 53 Then
end_year_output = 2020
end_week_output = time_span_end_week - (53 + 52 + 52 + 52 + 52)
End If
'问题所在的部分
ElseIf Not IsEmpty(Cells(Fixed_rows + i, Task_dep)) Then
ref_task = Cells(Fixed_rows + i, Task_dep) ' 读取要依赖的任务编号=> B中的数字应该是唯一的！
Dep_year = Cells(Fixed_rows + ref_task, year_end_column) ' 开始年份
Dep_week = Cells(Fixed_rows + ref_task, week_end_column) ' 开始周数
time_span_end_week = Dep_week + Cells(Fixed_rows + i, duration_weeks) '- 1
If Dep_year = 2015 Then
If time_span_end_week <= 53 Then
end_year_output = 2015
end_week_output = time_span_end_week
ElseIf time_span_end_week > 53 And time_span_end_week <= 53 + 52 Then
end_year_output = 2016
end_week_output = time_span_end_week - 53
ElseIf time_span_end_week > 53 + 52 And time_span_end_week <= 53 + 52 + 52 Then
end_year_output = 2017
end_week_output = time_span_end_week - (53 + 52)
ElseIf time_span_end_week > 53 + 52 + 52 And time_span_end_week <= 53 + 52 + 52 + 52 Then
end_year_output = 2018
end_week_output = time_span_end_week - (53 + 52 + 52)
ElseIf time_span_end_week > 53 + 52 + 52 + 52 And time_span_end_week <= 53 + 52 + 52 + 52 + 52 Then
end_year_output = 2019
end_week_output = time_span_end_week - (53 + 52 + 52 + 52)
ElseIf time_span_end_week > 53 + 52 + 52 + 52 + 52 And time_span_end_week <= 53 + 52 + 52 + 52 + 52 + 53 Then
end_year_output = 2020
end_week_output = time_span_end_week - (53 + 52 + 52 + 52 + 52)
End If
ElseIf Dep_year = 2016 Then
If time_span_end_week <= 52 Then
end_year_output = 2016
end_week_output = time_span_end_week
ElseIf time_span_end_week > 52 And time_span_end_week <= 52 + 52 Then
end_year_output = 2017
end_week_output = time_span_end_week - 52
ElseIf time_span_end_week > 52 + 52 And time_span_end_week <= 52 + 52 + 52 Then
end_year_output = 2018
end_week_output = time_span_end_week - (52 + 52)
ElseIf time_span_end_week > 52 + 52 + 52 And time_span_end_week <= 52 + 52 + 52 + 52 Then
end_year_output = 2019
end_week_output = time_span_end_week - (52 + 52 + 52)
ElseIf time_span_end_week > 52 + 52 + 52 + 52 And time_span_end_week <= 52 + 52 + 52 + 52 + 53 Then
end_year_output = 2020
end_week_output = time_span_end_week - (52 + 52 + 52 + 52)
End If
ElseIf Dep_year = 2017 Then
If time_span_end_week <= 52 Then
end_year_output = 2017
end_week_output = time_span_end_week
ElseIf time_span_end_week > 52 And time_span_end_week <= 52 + 52 Then
end_year_output = 2018
end_week_output = time_span_end_week - 52
ElseIf time_span_end_week > 52 + 52 And time_span_end_week <= 52 + 52 + 52 Then
end_year_output = 2019
end_week_output = time_span_end_week - (52 + 52)
ElseIf time_span_end_week > 52 + 52 + 52 And time_span_end_week <= 52 + 52 + 52 + 53 Then
end_year_output = 2020
end_week_output = time_span_end_week - (52 + 52 + 52)
End If
ElseIf Dep_year = 2018 Then
If time_span_end_week <= 52 Then
end_year_output = 2018
end_week_output = time_span_end_week
ElseIf time_span_end_week > 52 And time_span_end_week <= 52 + 52 Then
end_year_output = 2019
end_week_output = time_span_end_week - 52
ElseIf time_span_end_week > 52 + 52 And time_span_end_week <= 52 + 52 + 53 Then
end_year_output = 2020
end_week_output = time_span_end_week - (52 + 52)
End If
ElseIf Dep_year = 2019 Then
If time_span_end_week <= 52 Then
end_year_output = 2019
end_week_output = time_span_end_week
ElseIf time_span_end_week > 52 And time_span_end_week <= 52 + 53 Then
end_year_output = 2020
end_week_output = time_span_end_week - 52
End If
ElseIf Dep_year = 2020 Then
end_year_output = 2020
end_week_output = time_span_end_week
End If
End If
Cells(Fixed_rows + i, week_end_column).NumberFormat = "General"
Cells(Fixed_rows + i, week_end_column) = end_week_output
Cells(Fixed_rows + i, year_end_column) = end_year_output
End Function
Run-time error 424 - Object required (vba)
出现这个错误的原因是在问题部分你忘记设置函数的输出/结果，所以在那里没有输出，因此没有对象可以输入到其他程序中的参数中！只需在问题部分的末尾添加另一行代码即可解决：
Set Define_Time_Spans_2 = Range(Cells(Fixed_rows + i, Fixed_columns + time_span_start_week), Cells(Fixed_rows + i, Fixed_columns + time_span_end_week))
神奇！非常感谢！令人印象深刻的是，在所有这些行中，您如此迅速地发现了这个问题！
嘿嘿，在看到您在其他函数中使用此函数之后，我使用搜索功能（Ctrl+F）搜索“Set Define_Time_Spans_2”，在您的问题部分中没有看到一个！很高兴我能帮到您！享受Stack Overflow，并不要忘记为您觉得有用/无用的帖子投票！