VBA Solver Loop running very slowly.

najibk

New Member
Joined
Jun 15, 2009
Messages
13
Hi everyone

I need to run solver on about 365 rows individually, so I wrote the below code. The issue I am facing is the sheet is running ridiculously slow. It is only using 10-12% of the CPU power (only 1 core is ever used). Is there a way to optimize it to either use more cores or change the way it is coded below to get it to work faster? I think solver usually runs multithreaded when launching it myself, but I guess using VBA limits it?
Code:
[INDENT][FONT='inherit']Sub Macro4()

Application.ScreenUpdating = False

Dim i As Integer

For i = 0 To 364

SolverReset

SolverAdd CellRef:=Sheets("Sheet1").Range("$AQ$2").Offset(i, 0).Address, Relation:=2, FormulaText:=Sheets("Sheet1").Range("$AR$2").Offset(i, 0).Address

SolverAdd CellRef:=Sheets("Sheet1").Range("$AQ$2").Offset(i, 0).Address, Relation:=2, FormulaText:=Sheets("Sheet1").Range("$AS$2").Offset(i, 0).Address

SolverAdd CellRef:=Sheets("Sheet1").Range("$AV$2").Offset(i, 0).Address, Relation:=2, FormulaText:="1"

SolverAdd CellRef:=Sheets("Sheet1").Range("$AP$2").Offset(i, 0).Address, Relation:=3, FormulaText:="0"

SolverOk SetCell:=Sheets("Sheet1").Range("$AT$2").Offset(i, 0).Address, MaxMinVal:=2, ValueOf:=0, ByChange:=Sheets("Sheet1").Range("$AN$2:$AO$2").Offset(i, 0).Address, \_Engine:=1, EngineDesc:="GRG Nonlinear"

SolverSolve (True)

SolverSolve userfinish:=True

solverfinish keepfinal:=1

Next i

Application.ScreenUpdating = True

End Sub

[/FONT][/INDENT]
Any tips on what I can do? Solver above is basically re-balancing a 3-asset portfolio on a monthly basis (365 rows) based on daily data back to 1988 (around 8000 rows). The sheet itself is not super fast (takes 0.4 seconds to do Application.CalculateFullRebuild) but I believe its formulas are as optimized as they can be for now.

Thanks in advance
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top