Solver macro

[Whiox]

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

 

[kweaver]

Re: Help with solver macro please

Here's a good reference:

https://peltiertech.com/Excel/SolverVBA.html

 

[Whiox]

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

 

[Jon Peltier]

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.

 

[Whiox]

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.

 

[Jon Peltier]

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:

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

With Application.Run it looks like:

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.

 

[Whiox]

Re: Help with solver macro please

Fantastic Jon - that works great. Thanks so much for your help.

 

[Whiox]

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

 

[Jon Peltier]

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).

 

[Whiox]

Re: Help with solver macro please

Can I activate the sheet via my VBA code?