Scheduling Tool Solver

[Ritzyy]

Hi!

I am trying to make a scheduling tool in VBA. I am new to programming but have succeeded in building a VBA scheduling tool which currently works for any number of tasks. Unfortunately, when I try to schedule any more than 7 or 8 tasks, my code is too slow.

The scheduler works by trying to minimise an overall 'result'. Each task initially has an ideal start time and a duration. The 'result' is equal to a function accuracy (how close the tasks are to their ideal start time), plus a function of overlap (how many hours any two tasks are overlapping).

Currently, the scheduler works by iterating each task by 6 hours withing a +- 48 hour period from its 'ideal start time'. The scheduler is based on a recursive function which nests a variable number of for loops (1 for each task) and solves for the 'result' at each iteration, storing the 'result' if it is the smallest.

I think the recursive function which nests for loops and tests for every possible answer is why the code is so slow. I cannot think of a way to find a minimum solution without testing every iteration though. Can somebody please point me in the right direction with how I could achieve a similar result without testing each iteration (using nested for loops)? For interest sake, I have had the code output the result at each iteration for 5 tasks. The resulting graph is below:

As you can see, the minimum solution in this scenario is 0.75.

Because there are many local maxima and minima are there any good numerical methods or solvers which would help me? Would the excel solver work in this scenario? I have never used it before but am happy to look into it.

Thanks in advance for any help!

If it helps, the recursive function I have created is shown below:

Sub Recursive(b, c, depth, Min_Start, Max_Start, Accuracy, Overlap, minresult, result, result1, Count_Services)
' This is the recursive function which iterates through every result
    If b > depth Then
        'Calculate f_accuracy
        f_accruacy = 0
        For x = 1 To Count_Services
            f_accuracy = f_accuracy + Accuracy(c(x), x)
        Next x
        
        'Calculate f_overlap
        Stepup = 0
        f_overlap = 0
        z = 1
        For y = 1 To Count_Services - 1
            For g = 2 + Stepup To Count_Services
                f_overlap = f_overlap + Overlap(z, c(y), c(g))
            z = z + 1
            Next g
        Stepup = Stepup + 1
        Next y
        
        result = f_accuracy + f_overlap
                
        'Find min result
        If result < result1 Then
            For i = 1 To Count_Services
                minresult(i) = c(i)
            Next i
            result1 = result
        End If
        Exit Sub
    End If
    
    For d = Min_Start(b) To Max_Start(b)
            c(b) = d
            b = b + 1
            Recursive b, c, depth, Min_Start, Max_Start, Accuracy, Overlap, minresult, result, result1, Count_Services
            b = b - 1
    Next d
End Sub

 

[Ritzyy]

Bump!

Any help or suggestions in how to write more efficient code is appreciated!

 

[Worf]

Hi

• Minimization problems are suitable for Excel Solver. If a solution exists, it should find it in a few seconds.
• Normally it is set up on worksheet cells, but if necessary, we can have user-defined functions in VBA to perform calculations. I am at work now but later I can post an example of this.
• If you never used Solver before, try googling it to get a feel on how it works.
• Do you think your problem can be expressed by cell formulas or will we need code to do that?

 

[Worf]

This example shows how to include VBA into Solver’s calculations. The goal is to minimize D9 by changing G4:G5.

Public Function Model(xval!, consta!, constb!)

Model = consta * xval + constb

End Function

Sheet1

*	A	B	C	D	E	F	G
1	x	data	model	(error)^2	*	*	*
2	0	2,950	2,971	0,0005	*	*	*
3	1	5,030	4,979	0,0026	*	Solver Parameters	*
4	2	6,890	6,986	0,0093	*	a	2,007487
5	3	9,040	8,994	0,0021	*	b	2,971406
6	4	11,100	11,001	0,0097	*	*	*
7	5	12,930	13,009	0,0062	*	*	*
8	*	*	*	*	*	*	*
9	*	*	Target Cell:	0,0304	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:79px;"><col style="width:64px;"><col style="width:84px;"><col style="width:117px;"><col style="width:48px;"><col style="width:117px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =model(A2,a,b) D2 =(C2-B2)^2 C3 =model(A3,a,b) D3 =(C3-B3)^2 C4 =model(A4,a,b) D4 =(C4-B4)^2 C5 =model(A5,a,b) D5 =(C5-B5)^2 C6 =model(A6,a,b) D6 =(C6-B6)^2 C7 =model(A7,a,b) D7 =(C7-B7)^2 D9 =SUM(D2:D7) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Ritzyy]

This example shows how to include VBA into Solver’s calculations. The goal is to minimize D9 by changing G4:G5.

Public Function Model(xval!, consta!, constb!)

Model = consta * xval + constb

End Function

Thanks mate! I have transferred the problem into spreadsheet form and although it is less elegant, I am loving the solver!