Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver: 2nd function of SolverSolve
Hello. New guy here so please be gentle. :) From searching the internet, I've learned that the 2nd function of SolverSolve can be called to count the number of iterations performed by Solver, ie: SolverSolve (1st_function, 2nd_function). Here's the code I found, however I can not make it to run. Where do I put the code in the VBA program? I mean what would be the complete SolverSolve command? Code: -------------------- Global count as integer Sub test() count = 0 ret = solveroptions(stepthru:=True) '"checks" the Show Iteration checkbox in Tools/Solver/Options ret = solversolve(True, "showtrial") MsgBox count End Sub Function showtrial(reason As Integer) If reason = 1 Then 'reason = 1 means that the function will be called on each iteration. count = count + 1 'increments global variable End If showtrial = 1 ' continues Solver End Function -------------------- Thanks in advance! -- zaina ------------------------------------------------------------------------ zaina's Profile: http://www.excelforum.com/member.php...o&userid=24316 View this thread: http://www.excelforum.com/showthread...hreadid=379230 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver: 2nd function of SolverSolve
Hi. Place your code in a regular vba module. Then go to Tool | References
| and select Solver. Note that there are documentation errors with this method. I can't find my notes right now. I think this method was messed up with a previous version of Excel. However, it seems fixed with Excel XP & 2003. I believe the correct method to end your ShowTrial function is with: ShowTrial = False The following is a very general outline of a solver macro. Hopefully, this will give you some ideas for your own code. SolverReset ' Clear everything. ' Set it up SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="130", ByChange:="$C$4:$C$6" ' Add constraints... SolverAdd CellRef:="$C$4:$C$6", Relation:=1, FormulaText:="$E$4:$E$6" SolverAdd CellRef:="$C$4:$C$6", Relation:=3, FormulaText:="$D$4:$D$6" ' Previous documentation had errors here also... ' Set to True for ShowTrial SolverOptions StepThru:=True Results = SolverSolve(True, "ShowTrial") ' 0 Solver found a solution. If Results = 0 Then SolverFinish True ' Or 1 or 2. HTH. Good Luck. :) -- Dana DeLouis Win XP & Office 2003 "zaina" wrote in message ... Hello. New guy here so please be gentle. :) From searching the internet, I've learned that the 2nd function of SolverSolve can be called to count the number of iterations performed by Solver, ie: SolverSolve (1st_function, 2nd_function). Here's the code I found, however I can not make it to run. Where do I put the code in the VBA program? I mean what would be the complete SolverSolve command? Code: -------------------- Global count as integer Sub test() count = 0 ret = solveroptions(stepthru:=True) '"checks" the Show Iteration checkbox in Tools/Solver/Options ret = solversolve(True, "showtrial") MsgBox count End Sub Function showtrial(reason As Integer) If reason = 1 Then 'reason = 1 means that the function will be called on each iteration. count = count + 1 'increments global variable End If showtrial = 1 ' continues Solver End Function -------------------- Thanks in advance! -- zaina ------------------------------------------------------------------------ zaina's Profile: http://www.excelforum.com/member.php...o&userid=24316 View this thread: http://www.excelforum.com/showthread...hreadid=379230 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver: 2nd function of SolverSolve
Hi Dana: Thanks for your quick respond. Sorry, I still don't get it. Can you please elaborate a little more. I have Excel 2003 and added the codes in Module1. Below is the edited code. Can you tell if my codes is correct or not? And which variable that shows the number of iterations? Is it "count" or "Result"? I tried to run and it gave me error, plus the MsgBox = 0 although the variable in the spreadsheet changes so I think there are iterations take place. Thanks. Global count As Integer ------- Sub test() count = 0 SolverOptions StepThru:=True Results = SolverSolve(True, "ShowTrial") If Results = 0 Then SolverFinish True ' Or 1 or 2. MsgBox count End Sub -------- Function ShowTrial(reason As Integer) If reason = 1 Then 'reason = 1 means that the function will be called on each iteration. count = count + 1 'increments global variable End If ShowTrial = False ' continues Solver End Function -------- Sub SolveProblem() SolverReset SolverAdd cellRef:="$B$6", relation:=3, formulaText:="$B$4", Comment:="", _ Report:=True SolverOk SetCell:="$B$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$E$9:$E$13", _ Engine:=1, EngineDesc:="Standard GRG Nonlinear" test 'is this right? End Sub -------- Dana DeLouis Wrote: Hi. Place your code in a regular vba module. Then go to Tool | References | and select Solver. Note that there are documentation errors with this method. I can't find my notes right now. I think this method was messed up with a previous version of Excel. However, it seems fixed with Excel XP & 2003. I believe the correct method to end your ShowTrial function is with: ShowTrial = False The following is a very general outline of a solver macro. Hopefully, this will give you some ideas for your own code. SolverReset ' Clear everything. ' Set it up SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="130", ByChange:="$C$4:$C$6" ' Add constraints... SolverAdd CellRef:="$C$4:$C$6", Relation:=1, FormulaText:="$E$4:$E$6" SolverAdd CellRef:="$C$4:$C$6", Relation:=3, FormulaText:="$D$4:$D$6" ' Previous documentation had errors here also... ' Set to True for ShowTrial SolverOptions StepThru:=True Results = SolverSolve(True, "ShowTrial") ' 0 Solver found a solution. If Results = 0 Then SolverFinish True ' Or 1 or 2. HTH. Good Luck. :) -- Dana DeLouis Win XP & Office 2003 Hello. New guy here so please be gentle. :) From searching the internet, I've learned that the 2nd function of SolverSolve can be called to count the number of iterations performed by Solver, ie: SolverSolve (1st_function, 2nd_function). Here's the code I found, however I can not make it to run. Where do I put the code in the VBA program? I mean what would be the complete SolverSolve command? Code: -------------------- Global count as integer Sub test() count = 0 ret = solveroptions(stepthru:=True) '"checks" the Show Iteration checkbox in Tools/Solver/Options ret = solversolve(True, "showtrial") MsgBox count End Sub Function showtrial(reason As Integer) If reason = 1 Then 'reason = 1 means that the function will be called on each iteration. count = count + 1 'increments global variable End If showtrial = 1 ' continues Solver End Function -------------------- Thanks in advance! -- zaina ------------------------------------------------------------------------ zaina's Profile: http://www.excelforum.com/member.php...o&userid=24316 View this thread: http://www.excelforum.com/showthread...hreadid=379230 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver: 2nd function of SolverSolve
Hi. Your code is almost correct. Your SolverAdd & SolverOk lines have
"extra" stuff that are not part of the syntax. However, your Test() subroutine never calls SolveProblem(), so that is why Solver never got reset, and probably worked a little from previous attempts. It looks like you want to generate a report also based on Report:=True. This method did not work very well in previous versions of Excel. It just didn't work, and had a few other bugs as well. In addition, the documentation had a few errors as well. Most of the big issues are fixed in the latest version of Excel, however, I see that most of the documentation has disappeared. I want to point you to some articles, but they no longer exists. Anyway, here's the difference between the variables. Result is a variable that holds the "result" of Solver. A return value of 0,1,or 2 means that Solver found a solution. Count holds the number of times that Solver called your macro. If reason = 1 Then 'reason = 1 means that the function will be called on each iteration. Actually, the above statement is not totally correct. However, I can no longer find any documentation on this feature. Anyway, if your macro is running correctly, your function is going to be called anyway. The variable that we are passing to our function (we are using the variable 'Reason') holds a number that indicates "Why" we are calling this function. A value of 1 means that Solver called this function instead of showing the iteration message box. We would see this box normally when we select the solver option "Show iteration results." This function is not called on each iteration, but is called after an small, unknown number of iterations. The other two reasons the function is called is listed below in the code example. I included a small example code below to give you some ideas. Run this code ("Demo") with a blank active worksheet. The code will set it up for you. You should see about 5 iteration results on the sheet after it runs. Write back if you have any questions. I included one test for an undocumented bug. I've never been able to figure out why Solver's code breaks the workbook name if there's a space in the name. Option Explicit Public Count As Long ' Counter Sub Demo() Dim Results Count = 1 [A2:A4] = 1 [D:F].Clear '// Spaces in the workbook name is a major undocumented bug with this method: If InStr(1, ThisWorkbook.Name, Space(1)) 0 Or _ InStr(1, ActiveWorkbook.Name, Space(1)) 0 Then MsgBox "IMPORTANT: Remove all Spaces from workbook names", vbCritical End End If '// Use a random Polynomial equation: [A6].Formula = "=A2+ 2 * (A3)+ 3 * (A4^ 2) + 10" SolverReset SolverOk SetCell:="A6", MaxMinVal:=3, ValueOf:="310", ByChange:="A2:A4" SolverAdd CellRef:="A2:A4", Relation:=3, FormulaText:=4 'A2:A4 = 4 SolverAdd CellRef:="A2:A4", Relation:=1, FormulaText:=10 'A2:A4 <= 10 SolverOptions StepThru:=True Results = SolverSolve(True, "ShowTrial") Select Case Results Case 0, 1, 2 ' Keep final values & generate answer report Cells(Count, 4) = Count Cells(Count, 6) = Range("A6") SolverFinish KeepFinal:=1, ReportArray:=Array(1) Case 4 'Target does not converge 'Perhaps stop, or give different starting values and do again Case 5 'Solver could not find a feasible solution 'Your code here Case Else 'Your code End Select End Sub Function ShowTrial(Reason As Integer) '// = = = = = = = = = = = = = = = = = = = = = = = = = = = = '// Why was this function called while Solver was running? '// = = = = = = = = = = = = = = = = = = = = = = = = = = = = Const xContinue As Boolean = False 'Excel XP Const xStopRunning As Boolean = True 'Excel XP ' Select Case Reason Case 1 ''// = = = = = = = = = = = = = = = = = = = = = = = = = = = = ' ' The Show Iteration Results box in the Solver Options dialog is checked, ' ' ' OR called because the user pressed ESC to interrupt the Solver. ''// = = = = = = = = = = = = = = = = = = = = = = = = = = = = Cells(Count, 4) = Count Cells(Count, 5) = Reason Cells(Count, 6) = Range("A6") Count = Count + 1 ShowTrial = xContinue Case 2 ''// = = = = = = = = = = = = = = = = = = = = = = = = = = = = ' ' The Max Time option in the Solver Options dialog was exceeded. ''// = = = = = = = = = = = = = = = = = = = = = = = = = = = = '// Is answer close enough, or do we want to keep going? '// We'll quit for now... ShowTrial = xStopRunning Case 3 ''// = = = = = = = = = = = = = = = = = = = = = = = = = = = = ' 'The Max Iterations option in the Solver Options dialog was exceeded ''// = = = = = = = = = = = = = = = = = = = = = = = = = = = = ShowTrial = xStopRunning End Select End Function -- Dana DeLouis Win XP & Office 2003 "zaina" wrote in message ... Hi Dana: Thanks for your quick respond. Sorry, I still don't get it. Can you please elaborate a little more. I have Excel 2003 and added the codes in Module1. Below is the edited code. Can you tell if my codes is correct or not? And which variable that shows the number of iterations? Is it "count" or "Result"? I tried to run and it gave me error, plus the MsgBox = 0 although the variable in the spreadsheet changes so I think there are iterations take place. Thanks. Global count As Integer ------- Sub test() count = 0 SolverOptions StepThru:=True Results = SolverSolve(True, "ShowTrial") If Results = 0 Then SolverFinish True ' Or 1 or 2. MsgBox count End Sub -------- Function ShowTrial(reason As Integer) If reason = 1 Then 'reason = 1 means that the function will be called on each iteration. count = count + 1 'increments global variable End If ShowTrial = False ' continues Solver End Function -------- Sub SolveProblem() SolverReset SolverAdd cellRef:="$B$6", relation:=3, formulaText:="$B$4", Comment:="", _ Report:=True SolverOk SetCell:="$B$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$E$9:$E$13", _ Engine:=1, EngineDesc:="Standard GRG Nonlinear" test 'is this right? End Sub -------- Dana DeLouis Wrote: Hi. Place your code in a regular vba module. Then go to Tool | References | and select Solver. Note that there are documentation errors with this method. I can't find my notes right now. I think this method was messed up with a previous version of Excel. However, it seems fixed with Excel XP & 2003. I believe the correct method to end your ShowTrial function is with: ShowTrial = False The following is a very general outline of a solver macro. Hopefully, this will give you some ideas for your own code. SolverReset ' Clear everything. ' Set it up SolverOk SetCell:="$C$11", MaxMinVal:=3, ValueOf:="130", ByChange:="$C$4:$C$6" ' Add constraints... SolverAdd CellRef:="$C$4:$C$6", Relation:=1, FormulaText:="$E$4:$E$6" SolverAdd CellRef:="$C$4:$C$6", Relation:=3, FormulaText:="$D$4:$D$6" ' Previous documentation had errors here also... ' Set to True for ShowTrial SolverOptions StepThru:=True Results = SolverSolve(True, "ShowTrial") ' 0 Solver found a solution. If Results = 0 Then SolverFinish True ' Or 1 or 2. HTH. Good Luck. :) -- Dana DeLouis Win XP & Office 2003 Hello. New guy here so please be gentle. :) From searching the internet, I've learned that the 2nd function of SolverSolve can be called to count the number of iterations performed by Solver, ie: SolverSolve (1st_function, 2nd_function). Here's the code I found, however I can not make it to run. Where do I put the code in the VBA program? I mean what would be the complete SolverSolve command? Code: -------------------- Global count as integer Sub test() count = 0 ret = solveroptions(stepthru:=True) '"checks" the Show Iteration checkbox in Tools/Solver/Options ret = solversolve(True, "showtrial") MsgBox count End Sub Function showtrial(reason As Integer) If reason = 1 Then 'reason = 1 means that the function will be called on each iteration. count = count + 1 'increments global variable End If showtrial = 1 ' continues Solver End Function -------------------- Thanks in advance! -- zaina ------------------------------------------------------------------------ zaina's Profile: http://www.excelforum.com/member.php...o&userid=24316 View this thread: http://www.excelforum.com/showthread...hreadid=379230 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver problem where 'IF Function' is bad | Excel Discussion (Misc queries) | |||
is there anyway to call the Solver add-in as a function? | Excel Programming | |||
is there anyway to call the Solver add-in as a function? | Excel Programming | |||
How does Solversolve Control Macro | Excel Programming | |||
How to call SolverSolve portably for Excel XP and Excel 2000 | Excel Programming |