Daily Fantasy (DFS) Lineup Generator in Excel VBA

Without knowing what SolverReset, SolverAdd, SolverOK, SolverSolve, and Calculate are doing specifically, there are limits to how much optimization can be suggested. That said, there are some general comments that may be useful that can have some effect on speed, but probably not of the magnitude you are hoping for.

1. (Best Practice) Always declare Option Explicit at the top of all modules. This forces all variables to be declared explicitly within the module. In this case, adding Option Explicit did not identify any variables being used that were not declared..congrats!...still, it is recommended to avoid a number of hard to debug issues that arise from using undeclared variables (including typos of declared variables).

2. (Best Practice) When setting and resetting Application flags, it is best to cache the original values, set the values desired, and reset to the prior values at the end of an operation. Further, to absolutely guarantee that the values are reset, always call the 'business' code with error handling protection.

3. There is no need to Select cells to copy if you are using VBA to assign values/content. Select is required for a 'human-client' to accomplish the operation - but not a 'code-client'. This should provide some speed improvement - though probably minor.

4. Within the loop, there is only a single statement that depends on the index i (Range("$C$12").Offset(i, 0).Select).

   4a. This means that any statement within the loop that does not depend on the iteration value (or is not modified during each iteration) should be set prior to the loop. The only opportunity here (without knowing the contents of the subroutines listed above) is that Sheets("ITAvENG Lineups") need only be called once (rather than 2000 times).

   4b. Placing all the calls to SolverReset, SolverAdd, SolverOK, SolverSolve, and Calculate within the loop implies that each call is probably modifying content in the CellRef parameter of all the SolverAdd calls during each iteration. If this is not true, then set these cell formulas once outside the loop.

Below is a version that incorporates the comments above.

    Option Explicit

    Sub Showdown()
        Dim originalApplicationCalcSetting As Long
        Dim originalApplicationScreenUpdating As Boolean
        Dim originalApplicationDisplayStatusBar As Boolean
        
        originalApplicationCalcSetting = Application.Calculation
        originalApplicationScreenUpdating = Application.ScreenUpdating
        originalApplicationDisplayStatusBar = Application.DisplayStatusBar

        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
        Application.DisplayStatusBar = False
        
        Dim StartTime As Double
        Dim MinutesElapsed As String
        StartTime = Timer
        
        Dim succeeded As Boolean
        succeeded = False
    On Error GoTo ErrorExit
        
        ShowdownImpl
        
        MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
        
        MsgBox "This code ran successfully in " & MinutesElapsed & "minutes", vbInformation
        succeeded = True
    ErrorExit:
        Application.Calculation = originalApplicationCalcSetting
        Application.ScreenUpdating = originalApplicationScreenUpdating
        Application.DisplayStatusBar = originalApplicationDisplayStatusBar
        
        If succeeded = False Then
           MsgBox "Error Encountered"
        End If
    End Sub

    Private Sub ShowdownImpl()
        
        Dim i As Integer
        
        Dim itAvENG As Worksheet
        Set itAvENG = Sheets("ITAvENG Lineups")
        
        For i = 0 To 999
            Calculate
            
            itAvENG.Range("N16:R54").Copy
            
            ActiveWorksheet.Range("S16").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            
            SolverReset
            
            'Are these cells modified each iteration?...if not, set these values outside of the loop
            SolverAdd CellRef:="$AC$16:$AC$39", Relation:=1, FormulaText:="1"
            SolverAdd CellRef:="$Z$3", Relation:=2, FormulaText:="1"
            SolverAdd CellRef:="$Z$4", Relation:=2, FormulaText:="5"
            SolverAdd CellRef:="$AC$3", Relation:=1, FormulaText:="50000"
            SolverAdd CellRef:="$Z$6", Relation:=3, FormulaText:="1"
            SolverAdd CellRef:="$Z$7", Relation:=3, FormulaText:="1"
            
            SolverOK SetCell:="$AC$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$AA$16:$AB$39", _
            Engine:=2, EngineDesc:="Simplex LP"
            
            SolverAdd CellRef:="$AA$16:$AB$39", Relation:=5, FormulaText:="binary"
            
            SolverSolve True
                
            ActiveWorksheet.Range("AD16:AD81").Copy
            
            itAvENG.Range("$C$12").Offset(i, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=True, Transpose:=True
        Next i
    End Sub

    'Subroutines added below to allow the code to compile
    Private Sub SolverReset()
    End Sub

    Private Sub SolverAdd(CellRef As String, Relation As Long, FormulaText As String)
    End Sub

    Private Sub SolverOK(SetCell As String, MaxMinVal As Long, ValueOf As Long, ByChange As String, Engine As Long, EngineDesc As String)
    End Sub

    Private Sub SolverSolve(val As Boolean)
    End Sub

    Private Sub Calculate()
    End Sub