Solver macro

Whiox

New Member
Joined
Feb 19, 2019
Messages
33
Dear Excel community

Goal
I'd like to write a VBA macro using Solver.
I've no previous experience with macros.
I am looking for a dummy guide please.

Solver input
Set Objective: 'Calc optimised'!$J$14
To: Min
By changing variable cells: 'Calc optimised'!$E$3
Subject to the constraints:
'Calc optimised'!$E$3 => 0
'Calc optimised'!$E$3 <= 'Calc optimised'!$E$2

Make unconstrained variables non-negative: yes (note: not sure whether this is important)
Solving method: evolutionary


Thank you
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Re: Help with solver macro please

Thank you very much for your advice.
Unfortunately, I am still quite confused with writing the macro itself.
How do I start, and how can I convert my solver problem into VBA language, please?

Many thanks
 
Upvote 0
Re: Help with solver macro please

Start by turning on the macro recorder, then record a macro while you do the required Solver analysis.

See how your recorded code matches up with my (much) earlier tutorial. The if there are questions, describe them carefully (what you got vs. what you need), and post back.
 
Upvote 0
Re: Help with solver macro please

Hello Jon - thanks for your help.

The macro recorder shows the following VBA script:

Sub Solver190610()
'
' Solver190610 Macro
'

'
Range("E3").Select
ActiveCell.FormulaR1C1 = "0"
Range("E4").Select
SolverOk SetCell:="$J$14", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$3", Engine:= _
3, EngineDesc:="Evolutionary"
SolverSolve
End Sub


I cannot test the macro tough, as I get presented with this error if I do:
Compile error
Sub or function not defined


Under Tools-->References, "Solver" shows as available reference. I cannot select it though, just move it up or down a priority list and then click "ok" or "cancel".
I am working with Excel for Mac 16.16.10

I am very grateful for your help Jon - thank you!

Note: I'll be at a trade show for the rest of this week, and might not be able to respond until early next week again.
 
Upvote 0
Re: Help with solver macro please

Okay, setting a reference might be one of those things that may be problematic on a Mac. At least I recall that it's a potential problem.

In my article Using Solver in Excel VBA, I describe the compile error you've cited. Then it describes how to set a reference to Solver, but you already said that doesn't work. Fortunately halfway through the article is a section called "Avoiding Solver Reference Problems".

The solution is to use Application.Run

The important part of your code seems to be:

Code:
SolverOk SetCell:="$J$14", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$3", Engine:=3, EngineDesc:="Evolutionary"
SolverSolve

With Application.Run it looks like:

Code:
Application.Run "SolverOk", "$J$14", 2, 0, "$E$3", 3, "Evolutionary"
Application.Run "SolverSolve", True
Application.Run "SolverFinish"

I added True in the second line, and I think you need the third line to finish the analysis.

There are a few more details in my article. They may help you get Solver working for you.
 
Upvote 0
Re: Help with solver macro please

Fantastic Jon - that works great. Thanks so much for your help.
 
Upvote 0
Re: Help with solver macro please

One more question relating to this topic please:

I want to link this macro to a button that is located in another tab of the same spreadsheet.

How do I need to change the coding so that the formulas are referring to the correct tab, please?

Many thanks
 
Upvote 0
Re: Help with solver macro please

Solver has to run on the active sheet. If the button on a different sheet has to run the Solver analysis on this sheet, you need to activate this sheet first. Then the references in the code are correct. When Solver finishes, you can reactivate the previously active sheet (the one with the button on it).
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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