Resource Scheduling

[Dan Reedy]

Recently, I had a contract worker help me develop a scheduling spreadsheet. Overall the individual did a very nice job and exactly what I asked of him. Unfortunately, I missed 1 detail that I need to fix / upgrade....

The individual created a schedule for our manufacturing facility, scheduling each machine as a finite resource. Only 1 part can run across each machine at any one time. This is perfect except when it comes to outsourced items. How can I tell the spreadsheet to ignore the finite capacity restriction for those few outside services (in our case plating operations).

There are several pages of code, but I have included the one that I think is responsible for the problem I am having.

Dim m_start_date As Date
Dim m_end_date As Date
Dim m_in_use As Boolean
Public Property Get StartDate() As Variant
    StartDate = m_start_date
End Property
Public Property Let StartDate(ByVal vNewValue As Variant)
    m_start_date = vNewValue
End Property
Public Property Get EndDate() As Variant
    EndDate = m_end_date
End Property
Public Property Let EndDate(ByVal vNewValue As Variant)
    m_end_date = vNewValue
End Property
Public Property Get Duration() As Variant
    Duration = DateDiff("n", m_start_date, m_end_date)
End Property
Public Property Get AsRange() As Variant
    Dim r As TimeRange
    Set r = New TimeRange
    r.StartDate = m_start_date
    r.EndDate = m_end_date
    Set AsRange = r
End Property
Public Property Get InUse() As Variant
    InUse = m_in_use
End Property
Public Property Let InUse(ByVal vNewValue As Variant)
    m_in_use = vNewValue
End Property
Public Function Normalized() As TimeBlock
    Dim this_start As Date
    Dim this_end As Date
    this_start = StartDate
    this_end = EndDate
    If (this_end < this_start) Then
        this_end = DateAdd("d", 1, this_end)
    End If
    Dim new_range As TimeBlock
    Set new_range = New TimeBlock
    new_range.StartDate = this_start
    new_range.EndDate = this_end
    Set Normalized = new_range
End Function
Public Function Overlaps(other As TimeBlock) As Boolean
    Dim this_range As TimeBlock
    Set this_range = Normalized()
    
    Dim other_range As TimeBlock
    Set other_range = other.Normalized()
    
    Overlaps = Not ((other_range.EndDate <= this_range.StartDate) Or (other_range.StartDate >= this_range.EndDate))
End Function
Public Function Split(remove As TimeBlock) As TimeBlockPair
    Dim pair As TimeBlockPair
    Set pair = New TimeBlockPair
    If (m_start_date < remove.StartDate) Then
        Dim b As TimeBlock
        Set b = New TimeBlock
        b.StartDate = m_start_date
        b.EndDate = remove.StartDate
        b.InUse = False
        pair.Before = b
    End If
    If (m_end_date > remove.EndDate) Then
        Dim a As TimeBlock
        Set a = New TimeBlock
        a.StartDate = remove.EndDate
        a.EndDate = m_end_date
        a.InUse = False
        pair.After = a
    End If
    Set Split = pair
End Function

A

If this is not an easy one, I can always go back to the developer, but I am trying to learn a bit on my own as well.

Thanks all

Dan