0

I am writing VBA code that uses solver in excel to minimize error between reference values and values predicted by my equation. But, I would like to keep equations in the VBA code than adding them as columns in the excel. This is to secure my equations using a password protected macro. The codes goes like below:

Cells B2 and C2 are the variables which will be varied by solver to reach a minimum. Cells F9 and G9 are the reference values.

Range("B2").Select
ActiveCell.FormulaR1C1 = "1"
Range("C2").Select
ActiveCell.FormulaR1C1 = "1"

SolverReset

SolverAdd CellRef:="$B$2", Relation:=3, FormulaText:="0.3"
SolverAdd CellRef:="$B$2", Relation:=1, FormulaText:="1.7"

SolverAdd CellRef:="$C$2", Relation:=3, FormulaText:="1"
SolverAdd CellRef:="$C$2", Relation:=1, FormulaText:="20"

value1 = 28.563041 - 0.6895786 * Range("B2").Value - 0.1685979 * 
Range("C2").Value 
value2 = 31.161328 - 0.1194945 * Range("B2").Value - 0.1156287 * 
Range("C2").Value 

error1 = (Range("$F$9").Value - value1 ) ^ 2
error2 = (Range("$G$9").Value - value2) ^ 2

error = error1 + error2
Range("J11").Value = error

SolverOk SetCell:="$J$11", MaxMinVal:=2, ValueOf:=0, ByChange:="$B$2:$C$2", _
    Engine:=1, EngineDesc:="GRG Nonlinear"

SolverSolve

Problem: The variables B2 and C2 are initialized with a value of 1 but are not changed during optimization by solver. I would like to know how to get variables in the loop of solver.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Puneeth
  • 11
  • 3
  • 1
    `error` is a reserverd keyword you cannot use it as variable name! • I highly recommend to activate `Option Explicit` and declare all variables properly: In the VBA editor go to *Tools* › *Options* › *[Require Variable Declaration](https://www.excel-easy.com/vba/examples/option-explicit.html)*. • And you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Feb 11 '19 at 09:04
  • To solve your issue: Get the solver running correctly by running it manually first! If you managed to run it by hand (without VBA) then record a macro to see how the code would look like. • Unless you mange to run it manually it is not a programming question. – Pᴇʜ Feb 11 '19 at 09:07
  • Yes, the solver is running fine without VBA because the equation for "error" is a cell. I did record the macro and the code runs fine also because the "error" is not a reserved keyword because its equations are defined in the cell. The problem is when equation itself is brought into the VBA code. – Puneeth Feb 11 '19 at 09:10
  • 1
    The equations for the solver must be in the cells. You cannot have them in VBA. Once the Solver is started it only recognizes cells. Equations in VBA do not work. And still you cannot use `error` as variable name it **is** a reserved word. – Pᴇʜ Feb 11 '19 at 09:13
  • Ok, I now understand that it is impossible to do it in VBA using equations. The idea was to hide the equations from an external user. The variable "error" was just an example here, the actual code has a different name. Is there any other way to do it in excel? I tried making specific cells password protected but then solver cannot be run on them... – Puneeth Feb 11 '19 at 09:16
  • 1
    protect your sheet with the `.Protect` method and parameter `UserInterfaceOnly:=True` then VBA will be able to access the sheet while it is protected for the user (also refer to https://stackoverflow.com/questions/38353751/vba-excel-sheet-protection-userinterfaceonly-gone). Alternatively you must unprotect the sheet before running the solver. • Actually the cells with formulas for solver don't need to be unprotected. Just the cells with parameters and values that solver changes need to be writeable. – Pᴇʜ Feb 11 '19 at 09:18

0 Answers0