Running Solver in an Excel macro

[burntheory]

I want to run the Excel Solver routine in a macro but can't get it to work. In order to run the routine in a macro, it has to be referenced in VBA. When this is done, the spreadsheet won't open and Bet Angel reports the following error message - "Object reference not set to an instance of an object".

It's still possible to run Solver manually but I'd really like to include it in a trigger event.

Any ideas would be much appreciated.

[NileVentures]

Will "Goal Seek" do what you want?

Private Sub Worksheet_Calculate()
CheckGoalSeek
End Sub

Private Sub CheckGoalSeek()
Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)
End Sub

[burntheory]

Cheers Nile.

I never use Goal Seek, so it never occurred to me. It doesn't resolve with the same degree of precision as Solver, but it's more than adequate for my purposes.

Many thanks.

[NileVentures]

Cheers Nile.

I never use Goal Seek, so it never occurred to me. It doesn't resolve with the same degree of precision as Solver, but it's more than adequate for my purposes.

Many thanks.

No worries. I would, however, question your statement about the accuracy of Goal Seek viz-a-viz Solver. For linear problems (and I suspect most betting exchange problems are) the Goal Seek algorithm in Excel will be just as accurate as Solver and has the advantage of speed.

I would only recommend Solver for non-linear and derivative problems where the Generalised Reduced Gradient (GRG2) algorithm of Solver comes into it's own.

I'm not trying to be a smart arse but it is worth knowing if you want to optimise VBA for speed.

Good luck!

[burntheory]

It is non-linear but the maximum difference noted so far is only 0.07%, which is neither here nor there. Speed is not a factor.

Thanks.

[asahenettleton]

Hello, I was wondering if there has been progress on this question in the last five years? Thank you.