Run-time error 424 - Object required (vba)

Question

I am new to VBA and try to build my own automated Gantt chart in Excel. I get the run time 424 error, where object is required. This happens when I call a function in a module (all vars are publically defined constants, refering to specific row and column numbers):

Call Colour_Spans(Define_Time_Spans_2(i, Fixed_rows, Fixed_columns, Start_week, Start_year, End_week, End_year, OPT, Task_dependency))

The colouring function, which colours the bars, is:

Public Sub Colour_Spans(s As Range)

   s.Interior.ColorIndex = 1

End Sub

Everything works if condition If IsEmpty(Cells(Fixed_rows + i, Task_dep)) Then is executed in the code below, but I get the error when condiction ElseIf Not IsEmpty(Cells(Fixed_rows + i, Task_dep)) Then is executed. What is listed below basically defines the bars, which are afterwards coloured. The additional condition intends to introduce dependency in the time spans between the tasks. If I take it away or just suppress it, everything works smooth.

I used a lot of time on my own, but cannot find the reason. Much of the second part of the code is identical to the working part. Any help would be highly appreciated.

I thus copy the whole code, where the issue is. Let me know if you need the rest. Thank you in advance!

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






'THE PROBLEMATIC PART


ElseIf Not IsEmpty(Cells(Fixed_rows + i, Task_dep)) Then
    ref_task = Cells(Fixed_rows + i, Task_dep) ' Reads the number of task to depend on => nums in B should be unique!
    Dep_year = Cells(Fixed_rows + ref_task, year_end_column) ' Start_year
    Dep_week = Cells(Fixed_rows + ref_task, week_end_column) ' Start_week
    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

**On which line do you get the error?** And you should reference your `Cells()` using an Object variable (here a sheet) with `Set ... = ...`, knowing that it is Workbook.Sheet.Cell ;) — R3uK, Aug 03 '15 at 11:37
@R3uK , I basically get the error here: Call Colour_Spans(Define_Time_Spans_2(i, Fixed_rows, Fixed_columns, Start_week, Start_year, End_week, End_year, OPT, Task_dependency)) , when the code executes the part beneath "'THE PROBLEMATIC PART" (the second part of the code above). I also did not understand your comment about the objects. I have activated the sheet before using it and referring to Cells() by Worksheets("Gantt").Activate. — Pavlin Todorov, Aug 03 '15 at 12:55
For the references, I didn't notice your `.Activate` the first time, but I was refering something like it is used in this answer : http://stackoverflow.com/questions/30608332/copying-a-variable-based-range-of-cells-to-another-range/30731549#30731549 To narrow down the culprit line of code, place break points at each line of your **Problematic Part** and rerun the code step by step. I'll take a closer look too! — R3uK, Aug 03 '15 at 13:13

score 0 · Accepted Answer · answered Aug 03 '15 at 13:15

0

Ok, I just spotted the problem!

Something pretty minor, but you forgot to set the output/result of your function in the problematic part, so there is no output there, and so no object to input in your arguments for the other routine!

Just add another line like this at the end of the problematic part :

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))

answered Aug 03 '15 at 13:15

R3uK

14,417
7
43
77

Magic! :-) Thank you so much! It is quite impressive how rapidly you spotted this amongst all the lines! :-) – Pavlin Todorov Aug 03 '15 at 13:28
Hehe, after seeing that you used this function inside the other, I search (Ctrl+F) for "Set Define_Time_Spans_2" and didn't saw one in your *problematic part*! Glad I could help! Enjoy SO and down forget to up/down vote the posts that find useful/useless! ;) – R3uK Aug 03 '15 at 15:00

Run-time error 424 - Object required (vba)

1 Answers1