Solved: Code for vba to replace below formula

[p45cal]

I've managed to cut down the time taken to about 1/5th, though I think I could do more since I concentrated on speeding up only the slowest part, that is the part which pastes and calculates the biggest range.
First, I tried mdmackillop's solutions but they had no significant speed advantage, then I tried removing and shortening your code a bit:
First I noticed that your formula seems a little longer than necessary and shortened it from:[vba]=IF($R35="",0,IF(N$33<$O35,0,IF(N$33>=$O35,IF(0<($AA35),MIN(($AA35-SUM(M35:$AF35)),$AB35,SUMIF(Shadow_km_Module,$R35,N$3:N$32)-SUMIF($R34:$R$34,$R35,N$34)),0))))
to:
=IF($R35="",0,IF(N$33<$O35,0,IF(0<($AA35),MIN(($AA35-SUM(M35:$AF35)),$AB35,SUMIF(Shadow_km_Module,$R35,N$3:N$32)-SUMIF($R34:$R$34,$R35,N$34)),0)))
[/vba]by taking out the:
If(N$33>=$O35,
since it has to be true as you've previously asked:
If(N$33<$O35
The shorter formula gives the same results throughout, tested on the data you supplied.

Second, I removed lines of code which assigned variables never used elsewhere, removed selecting ranges/cells and activating sheets, so now one of the sheets can remains hidden throughout.
Didn't make a sausage of a difference to the time though.

So this is what was left:
[vba]Sub formulation()
Dim LR As Long
With ThisWorkbook.Sheets("Shadow_k_mins")
LR = .Range("B" & .Rows.Count).End(xlUp).Row
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Range("Para_Formulation").Copy Range("Shadow_Range_Formulation").Resize(LR - Range("Shadow_Range_Formulation").Row + 1)
Application.Calculation = xlCalculationAutomatic
Range("Shadow_km_loading_zone").Value = Range("Shadow_km_loading_zone").Value
'>>> USE TO CALCULATE NUMBER OF DAYS LOADED TO DERIVE END DATES.
Control_Point_date_col = Range("Shade_Km_Control_point_end_date_Col").Column
Shade_Km_Pos_Col = Range("Shade_Km_Pos_Col").Column
Loading_Row_Start = Range("Shadow_Km_Header_row").Row + 1
'>>>> apply countif on the control point column
Range("Para_Countif_Formula").Copy .Range(.Cells(Loading_Row_Start, Shade_Km_Pos_Col), .Cells(Loading_Row_Start, Shade_Km_Pos_Col).End(xlDown)).Offset(, Control_Point_date_col - Shade_Km_Pos_Col)
End With
'>>> select the the control column bottom part as their is formula working on the module number
Range("Shadow_Control_Column").Value = Range("Shadow_Control_Column").Value
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Cursor = xlDefault
Application.ScreenUpdating = True
End Sub
[/vba] Perhaps you could try this on your larger data set and hopefully it won't freeze on you.

It was the lines in red above which were by far the slowest to execute.
So on to arrays, this has been a bit of a challenge; Since in the sheet, each formula relies on the results of similar formulae above and to the left of it (and one of those is a SumIf dependent on cells outside the grid). I tried Application.Index to slice the array to use with Sum and SumIf but this slowed it down enormusly (worse than just using the formuale in the sheet).

Anyway, after much testing I reached an in-memory array solution:
[vba]Sub formulation2()
Dim LR As Long
With ThisWorkbook.Sheets("Shadow_k_mins")
LR = .Range("B" & .Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False

blah Range("Shadow_Range_Formulation").Resize(LR - Range("Shadow_Range_Formulation").Row + 1)

'>>> USE TO CALCULATE NUMBER OF DAYS LOADED TO DERIVE END DATES.
Control_Point_date_col = Range("Shade_Km_Control_point_end_date_Col").Column
Shade_Km_Pos_Col = Range("Shade_Km_Pos_Col").Column
Loading_Row_Start = Range("Shadow_Km_Header_row").Row + 1
'>>>> apply countif on the control point column
Range("Para_Countif_Formula").Copy .Range(.Cells(Loading_Row_Start, Shade_Km_Pos_Col), .Cells(Loading_Row_Start, Shade_Km_Pos_Col).End(xlDown)).Offset(, Control_Point_date_col - Shade_Km_Pos_Col)
End With
'>>> select the the control column bottom part as their is formula working on the module number
Range("Shadow_Control_Column").Value = Range("Shadow_Control_Column").Value
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Cursor = xlDefault
Application.ScreenUpdating = True
End Sub

Sub blah(theRange As Range)
Dim myArray()
rowMax = theRange.Rows.Count
ColmMax = theRange.Columns.Count
ReDim myArray(1 To rowMax, 1 To ColmMax)
With ThisWorkbook.Sheets("shadow_k_mins")
'some row and column numbers from named ranges:
ShadowKmHeaderRowNo = Range("Shadow_Km_Header_row").Row '34
ShadeKmCapacityAreaStartRow = Range("Shade_km_Capacity_area").Row '3
ShadeKmCapacityAreaEndRow = Range("Shade_km_Capacity_area").Row + Range("Shade_km_Capacity_area").Rows.Count - 1 '32

ModuleColmNo = Range("Shade_Km_Module_Col").Column '18
TotalMinsColmNo = Range("Shade_KM_Total_Mins_Load_Cells_Col").Column '27
MinsProduceAsPerCapColmNo = Range("Shade_Km_Mins_Prod_As_Per_Cap_Col").Column '28
KshadowLoadingZoneMarkerStartColmNo = Range("Kshadow_Loading_Zone_Marker_Start_Column").Column '32
ModuleData = Application.Transpose(.Cells(theRange.Row, ModuleColmNo).Resize(rowMax))
For rw = 1 To rowMax
SheetRow = theRange.Rows(rw).Row
RowSumSoFar = 0
For Colm = 1 To ColmMax
SheetColm = theRange.Columns(Colm).Column
If Cells(SheetRow, ModuleColmNo) = "" Then
myArray(rw, Colm) = 0
Else
If Cells(33, SheetColm) < Cells(SheetRow, 15) Then
myArray(rw, Colm) = 0
Else
If 0 < Cells(SheetRow, TotalMinsColmNo) Then
'calculate 2nd sumif:
ThisModule = ModuleData(rw)
SecondSumif = 0
For i = 1 To rw - 1
If ModuleData(i) = ThisModule Then SecondSumif = SecondSumif + myArray(i, Colm)
Next i
Result = Application.Min((.Cells(SheetRow, TotalMinsColmNo) - RowSumSoFar), .Cells(SheetRow, MinsProduceAsPerCapColmNo), Application.SumIf(Range("Shadow_km_Module"), .Cells(SheetRow, ModuleColmNo), .Range(.Cells(ShadeKmCapacityAreaStartRow, SheetColm), .Cells(ShadeKmCapacityAreaEndRow, SheetColm))) - SecondSumif)
myArray(rw, Colm) = Result
RowSumSoFar = RowSumSoFar + Result
Else
myArray(rw, Colm) = 0
End If
End If
End If
Next Colm
Next rw
End With
theRange = myArray
End Sub
[/vba] The code relies partially on the named ranges you have put in place, so those names might require maintenance if you change things.
The attached file has all the above code, with an extra button added so a speed comparison can be made between the two.

If time is critical, the bit:
[vba]Range("Para_Countif_Formula").Copy .Range(.Cells(Loading_Row_Start, Shade_Km_Pos_Col), .Cells(Loading_Row_Start, Shade_Km_Pos_Col).End(xlDown)).Offset(, Control_Point_date_col - Shade_Km_Pos_Col)
End With
'>>> select the the control column bottom part as their is formula working on the module number
Range("Shadow_Control_Column").Value = Range("Shadow_Control_Column").Value
[/vba]could be converted to pure vba too, but I don't know how significant those time savings might be.
This was developed with Excel 2010 in compatibility mode.