| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver in VBA
Client is using Excel 03/WinXP. She has some code that uses the Solver. It
worked fine with Excel 2000 but now is giving : Compile error: Sub or Function not defined. The solver addin is turned on in Tools, Addins. We even readded the Solver addin by navigating to the xla file to make sure it was looking at the correct file. Macro security is at Medium and macros are enabled. When we go into the References in VB Editor, we check the Solver reference, save the code but when go back into Excel, the reference is once again unchecked. We even used the browse button here to locate the correct file but it still won't stick. We've also checked the Analysis Toolpack and Analysis Toolpack VBA addins and still having problems. What else can we try??? -- maryj |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Solver in VBA
maryj,
You could try showing us the code and where the error occurs <g. hth, Doug "maryj" wrote in message ... Client is using Excel 03/WinXP. She has some code that uses the Solver. It worked fine with Excel 2000 but now is giving : Compile error: Sub or Function not defined. The solver addin is turned on in Tools, Addins. We even readded the Solver addin by navigating to the xla file to make sure it was looking at the correct file. Macro security is at Medium and macros are enabled. When we go into the References in VB Editor, we check the Solver reference, save the code but when go back into Excel, the reference is once again unchecked. We even used the browse button here to locate the correct file but it still won't stick. We've also checked the Analysis Toolpack and Analysis Toolpack VBA addins and still having problems. What else can we try??? -- maryj |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Solver in VBA
It may be an issue of mismatched references. For instance, when used in
Excel 2000, there would be a reference to Solver 2000. When it is next used in 2003, Excel updates the reference in the workbook. Then when it is sent back to someone on Excel 2000, Excel cannot find the reference to Solver 2003 and doesn't know to downdate the reference, and you get the error. You could manually update the reference every time, which is a pain after the second or third time you go through it; you can even programmatically update references, but this is loaded with issues, including security settings. Alternatively you could use Aplication.Run to call the solver functions, which is more straightforward and doesn't rely on references, only that the addin is installed: Instead of these: SolverReset SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27, ByChange:=Range("Mass") SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=0 Result = SolverSolve(UserFinish:=True) SolverFinish use these: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass" Application.Run "solver.xla!solveradd", "Mass1", 3, 0 Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "maryj" wrote in message ... Client is using Excel 03/WinXP. She has some code that uses the Solver. It worked fine with Excel 2000 but now is giving : Compile error: Sub or Function not defined. The solver addin is turned on in Tools, Addins. We even readded the Solver addin by navigating to the xla file to make sure it was looking at the correct file. Macro security is at Medium and macros are enabled. When we go into the References in VB Editor, we check the Solver reference, save the code but when go back into Excel, the reference is once again unchecked. We even used the browse button here to locate the correct file but it still won't stick. We've also checked the Analysis Toolpack and Analysis Toolpack VBA addins and still having problems. What else can we try??? -- maryj |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Solver in VBA
Hi Jon,
Thanks for you suggestions. Here is how we modified our code: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", SetCell:="$B$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11" Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" It stops at SetCell and gives the error: Compile error: Named argument not found. -- maryj "Jon Peltier" wrote: It may be an issue of mismatched references. For instance, when used in Excel 2000, there would be a reference to Solver 2000. When it is next used in 2003, Excel updates the reference in the workbook. Then when it is sent back to someone on Excel 2000, Excel cannot find the reference to Solver 2003 and doesn't know to downdate the reference, and you get the error. You could manually update the reference every time, which is a pain after the second or third time you go through it; you can even programmatically update references, but this is loaded with issues, including security settings. Alternatively you could use Aplication.Run to call the solver functions, which is more straightforward and doesn't rely on references, only that the addin is installed: Instead of these: SolverReset SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27, ByChange:=Range("Mass") SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=0 Result = SolverSolve(UserFinish:=True) SolverFinish use these: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass" Application.Run "solver.xla!solveradd", "Mass1", 3, 0 Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "maryj" wrote in message ... Client is using Excel 03/WinXP. She has some code that uses the Solver. It worked fine with Excel 2000 but now is giving : Compile error: Sub or Function not defined. The solver addin is turned on in Tools, Addins. We even readded the Solver addin by navigating to the xla file to make sure it was looking at the correct file. Macro security is at Medium and macros are enabled. When we go into the References in VB Editor, we check the Solver reference, save the code but when go back into Excel, the reference is once again unchecked. We even used the browse button here to locate the correct file but it still won't stick. We've also checked the Analysis Toolpack and Analysis Toolpack VBA addins and still having problems. What else can we try??? -- maryj |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Solver in VBA
Hi. Under Help for "Run"...
Remarks You cannot use named arguments with this method. Arguments must be passed by position. Therefore... Run "Solver.xla!SolverOk", "B10", 3, 0, "B11" -- HTH :) Dana DeLouis Windows XP & Office 2003 "maryj" wrote in message ... Hi Jon, Thanks for you suggestions. Here is how we modified our code: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", SetCell:="$B$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11" Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" It stops at SetCell and gives the error: Compile error: Named argument not found. -- maryj "Jon Peltier" wrote: It may be an issue of mismatched references. For instance, when used in Excel 2000, there would be a reference to Solver 2000. When it is next used in 2003, Excel updates the reference in the workbook. Then when it is sent back to someone on Excel 2000, Excel cannot find the reference to Solver 2003 and doesn't know to downdate the reference, and you get the error. You could manually update the reference every time, which is a pain after the second or third time you go through it; you can even programmatically update references, but this is loaded with issues, including security settings. Alternatively you could use Aplication.Run to call the solver functions, which is more straightforward and doesn't rely on references, only that the addin is installed: Instead of these: SolverReset SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27, ByChange:=Range("Mass") SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=0 Result = SolverSolve(UserFinish:=True) SolverFinish use these: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass" Application.Run "solver.xla!solveradd", "Mass1", 3, 0 Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "maryj" wrote in message ... Client is using Excel 03/WinXP. She has some code that uses the Solver. It worked fine with Excel 2000 but now is giving : Compile error: Sub or Function not defined. The solver addin is turned on in Tools, Addins. We even readded the Solver addin by navigating to the xla file to make sure it was looking at the correct file. Macro security is at Medium and macros are enabled. When we go into the References in VB Editor, we check the Solver reference, save the code but when go back into Excel, the reference is once again unchecked. We even used the browse button here to locate the correct file but it still won't stick. We've also checked the Analysis Toolpack and Analysis Toolpack VBA addins and still having problems. What else can we try??? -- maryj |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Solver in VBA
I've got the same problem and procceded as instructed... and still nothing...
VBA says "can't find the macro "solver.xla!solverreset"" What to do?? Thank you very much! I don't know how the world would continue spinning if it weren't people like you =) Regards "Dana DeLouis" wrote: Hi. Under Help for "Run"... Remarks You cannot use named arguments with this method. Arguments must be passed by position. Therefore... Run "Solver.xla!SolverOk", "B10", 3, 0, "B11" -- HTH :) Dana DeLouis Windows XP & Office 2003 "maryj" wrote in message ... Hi Jon, Thanks for you suggestions. Here is how we modified our code: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", SetCell:="$B$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11" Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" It stops at SetCell and gives the error: Compile error: Named argument not found. -- maryj "Jon Peltier" wrote: It may be an issue of mismatched references. For instance, when used in Excel 2000, there would be a reference to Solver 2000. When it is next used in 2003, Excel updates the reference in the workbook. Then when it is sent back to someone on Excel 2000, Excel cannot find the reference to Solver 2003 and doesn't know to downdate the reference, and you get the error. You could manually update the reference every time, which is a pain after the second or third time you go through it; you can even programmatically update references, but this is loaded with issues, including security settings. Alternatively you could use Aplication.Run to call the solver functions, which is more straightforward and doesn't rely on references, only that the addin is installed: Instead of these: SolverReset SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27, ByChange:=Range("Mass") SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=0 Result = SolverSolve(UserFinish:=True) SolverFinish use these: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass" Application.Run "solver.xla!solveradd", "Mass1", 3, 0 Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "maryj" wrote in message ... Client is using Excel 03/WinXP. She has some code that uses the Solver. It worked fine with Excel 2000 but now is giving : Compile error: Sub or Function not defined. The solver addin is turned on in Tools, Addins. We even readded the Solver addin by navigating to the xla file to make sure it was looking at the correct file. Macro security is at Medium and macros are enabled. When we go into the References in VB Editor, we check the Solver reference, save the code but when go back into Excel, the reference is once again unchecked. We even used the browse button here to locate the correct file but it still won't stick. We've also checked the Analysis Toolpack and Analysis Toolpack VBA addins and still having problems. What else can we try??? -- maryj |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Solver with VBA
Hi Jon,
I am trying to minimize reference issues between Excel versions. When I use the following code I get the message: Run-time error '1004'Application-defined or object-defined error Private Sub CommandButton1_Click() Application.Run "solver.xla!solverreset" Run "Solver.xla!SolverOk", "$O$22", 3, 0, "$A$22" Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub Any ideas? Thanks, Don Jon Peltier wrote: It may be an issue of mismatched references. 07-Feb-07 It may be an issue of mismatched references. For instance, when used in Excel 2000, there would be a reference to Solver 2000. When it is next used in 2003, Excel updates the reference in the workbook. Then when it is sent back to someone on Excel 2000, Excel cannot find the reference to Solver 2003 and doesn't know to downdate the reference, and you get the error. You could manually update the reference every time, which is a pain after the second or third time you go through it; you can even programmatically update references, but this is loaded with issues, including security settings. Alternatively you could use Aplication.Run to call the solver functions, which is more straightforward and doesn't rely on references, only that the addin is installed: Instead of these: SolverReset SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27, ByChange:=Range("Mass") SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=0 Result = SolverSolve(UserFinish:=True) SolverFinish use these: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass" Application.Run "solver.xla!solveradd", "Mass1", 3, 0 Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "maryj" wrote in message ... Previous Posts In This Thread: On Wednesday, February 07, 2007 4:01 PM mary wrote: Solver in VBA Client is using Excel 03/WinXP. She has some code that uses the Solver. It worked fine with Excel 2000 but now is giving : Compile error: Sub or Function not defined. The solver addin is turned on in Tools, Addins. We even readded the Solver addin by navigating to the xla file to make sure it was looking at the correct file. Macro security is at Medium and macros are enabled. When we go into the References in VB Editor, we check the Solver reference, save the code but when go back into Excel, the reference is once again unchecked. We even used the browse button here to locate the correct file but it still won't stick. We've also checked the Analysis Toolpack and Analysis Toolpack VBA addins and still having problems. What else can we try??? -- maryj On Wednesday, February 07, 2007 4:03 PM Doug Glancy wrote: maryj,You could try showing us the code and where the error occurs <g. maryj, You could try showing us the code and where the error occurs <g. hth, Doug On Wednesday, February 07, 2007 4:48 PM Jon Peltier wrote: It may be an issue of mismatched references. It may be an issue of mismatched references. For instance, when used in Excel 2000, there would be a reference to Solver 2000. When it is next used in 2003, Excel updates the reference in the workbook. Then when it is sent back to someone on Excel 2000, Excel cannot find the reference to Solver 2003 and doesn't know to downdate the reference, and you get the error. You could manually update the reference every time, which is a pain after the second or third time you go through it; you can even programmatically update references, but this is loaded with issues, including security settings. Alternatively you could use Aplication.Run to call the solver functions, which is more straightforward and doesn't rely on references, only that the addin is installed: Instead of these: SolverReset SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27, ByChange:=Range("Mass") SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=0 Result = SolverSolve(UserFinish:=True) SolverFinish use these: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass" Application.Run "solver.xla!solveradd", "Mass1", 3, 0 Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "maryj" wrote in message ... On Friday, February 09, 2007 9:19 AM mary wrote: Solver in VBA Hi Jon, Thanks for you suggestions. Here is how we modified our code: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", SetCell:="$B$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11" Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" It stops at SetCell and gives the error: Compile error: Named argument not found. -- maryj "Jon Peltier" wrote: On Friday, February 09, 2007 9:55 AM Dana DeLouis wrote: Hi. Under Help for "Run"... Hi. Under Help for "Run"... Remarks You cannot use named arguments with this method. Arguments must be passed by position. Therefore... Run "Solver.xla!SolverOk", "B10", 3, 0, "B11" -- HTH :) Dana DeLouis Windows XP & Office 2003 "maryj" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice Build a Selected Text Favorites Utility for your Web Site http://www.eggheadcafe.com/tutorials...-text-fav.aspx |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Solver with VBA
Don,
Do you realize you responded to a 3 year old thread ? Secondly, you included your unmunged email address, dgulcher at gmail dot com. You have just opened yourself up to more spam than you bargained for. "Don Gulcher" wrote in message ... Hi Jon, I am trying to minimize reference issues between Excel versions. When I use the following code I get the message: Run-time error '1004'Application-defined or object-defined error Private Sub CommandButton1_Click() Application.Run "solver.xla!solverreset" Run "Solver.xla!SolverOk", "$O$22", 3, 0, "$A$22" Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub Any ideas? Thanks, Don Jon Peltier wrote: It may be an issue of mismatched references. 07-Feb-07 It may be an issue of mismatched references. For instance, when used in Excel 2000, there would be a reference to Solver 2000. When it is next used in 2003, Excel updates the reference in the workbook. Then when it is sent back to someone on Excel 2000, Excel cannot find the reference to Solver 2003 and doesn't know to downdate the reference, and you get the error. You could manually update the reference every time, which is a pain after the second or third time you go through it; you can even programmatically update references, but this is loaded with issues, including security settings. Alternatively you could use Aplication.Run to call the solver functions, which is more straightforward and doesn't rely on references, only that the addin is installed: Instead of these: SolverReset SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27, ByChange:=Range("Mass") SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=0 Result = SolverSolve(UserFinish:=True) SolverFinish use these: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass" Application.Run "solver.xla!solveradd", "Mass1", 3, 0 Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "maryj" wrote in message ... Previous Posts In This Thread: On Wednesday, February 07, 2007 4:01 PM mary wrote: Solver in VBA Client is using Excel 03/WinXP. She has some code that uses the Solver. It worked fine with Excel 2000 but now is giving : Compile error: Sub or Function not defined. The solver addin is turned on in Tools, Addins. We even readded the Solver addin by navigating to the xla file to make sure it was looking at the correct file. Macro security is at Medium and macros are enabled. When we go into the References in VB Editor, we check the Solver reference, save the code but when go back into Excel, the reference is once again unchecked. We even used the browse button here to locate the correct file but it still won't stick. We've also checked the Analysis Toolpack and Analysis Toolpack VBA addins and still having problems. What else can we try??? -- maryj On Wednesday, February 07, 2007 4:03 PM Doug Glancy wrote: maryj,You could try showing us the code and where the error occurs <g. maryj, You could try showing us the code and where the error occurs <g. hth, Doug On Wednesday, February 07, 2007 4:48 PM Jon Peltier wrote: It may be an issue of mismatched references. It may be an issue of mismatched references. For instance, when used in Excel 2000, there would be a reference to Solver 2000. When it is next used in 2003, Excel updates the reference in the workbook. Then when it is sent back to someone on Excel 2000, Excel cannot find the reference to Solver 2003 and doesn't know to downdate the reference, and you get the error. You could manually update the reference every time, which is a pain after the second or third time you go through it; you can even programmatically update references, but this is loaded with issues, including security settings. Alternatively you could use Aplication.Run to call the solver functions, which is more straightforward and doesn't rely on references, only that the addin is installed: Instead of these: SolverReset SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27, ByChange:=Range("Mass") SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=0 Result = SolverSolve(UserFinish:=True) SolverFinish use these: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass" Application.Run "solver.xla!solveradd", "Mass1", 3, 0 Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "maryj" wrote in message ... On Friday, February 09, 2007 9:19 AM mary wrote: Solver in VBA Hi Jon, Thanks for you suggestions. Here is how we modified our code: Application.Run "solver.xla!solverreset" Application.Run "solver.xla!solverok", SetCell:="$B$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$11" Result = Application.Run("solver.xla!solversolve", True) Application.Run "solver.xla!solverfinish" It stops at SetCell and gives the error: Compile error: Named argument not found. -- maryj "Jon Peltier" wrote: On Friday, February 09, 2007 9:55 AM Dana DeLouis wrote: Hi. Under Help for "Run"... Hi. Under Help for "Run"... Remarks You cannot use named arguments with this method. Arguments must be passed by position. Therefore... Run "Solver.xla!SolverOk", "B10", 3, 0, "B11" -- HTH :) Dana DeLouis Windows XP & Office 2003 "maryj" wrote in message ... Submitted via EggHeadCafe - Software Developer Portal of Choice Build a Selected Text Favorites Utility for your Web Site http://www.eggheadcafe.com/tutorials...-text-fav.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help using solver | Excel Discussion (Misc queries) | |||
| How to replace Excel solver with some free solver codes in Excel V | Excel Programming | |||
| Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
| Can solver do this? | Excel Worksheet Functions | |||
| Resetting Solver Manually to Fix Solver Bug | Excel Programming | |||
Linear Mode
