Excel Solver Compile Error
Contents |
Forums Excel Questions Compile Error When Running VBA Solver Results compile error in hidden module solver code excel 2013 1 to 3 of 3 Compile Error When Running
Compile Error In Hidden Module Solver Code Excel 2010
VBA SolverThis is a discussion on Compile Error When Running VBA Solver within the excel solver error 53 Excel Questions forums, part of the Question Forums category; Can anyone help? I keep receiving a Compile Error: Sub or Function not defined excel solver error 1004 when I try to run a ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Apr 23rd, 2011,02:04 AM
Excel Solver Error In Model
#1 SteveFrancis New Member Join Date Apr 2011 Posts 2 Compile Error When Running VBA Solver Can anyone help? I keep receiving a Compile Error: Sub or Function not defined when I try to run a macro that runs solver. The Sub RunSolverLibContent() ' ' RunSolverLibContent Macro ' ' SolverOk SetCell:="$F$32", MaxMinVal:=3, ValueOf:=0, ByChange:="$F$35", Engine _ :=1, EngineDesc:="GRG Nonlinear" SolverOk SetCell:="$F$32", MaxMinVal:=3, ValueOf:=0, ByChange:="$F$35", Engine _ :=1, EngineDesc:="GRG Nonlinear" SolverSolve SolverOk SetCell:="$G$32", MaxMinVal:=3, ValueOf:=0, ByChange:="$G$35", Engine _ :=1, EngineDesc:="GRG Nonlinear" SolverOk SetCell:="$G$32", MaxMinVal:=3, ValueOf:=0, ByChange:="$G$35", Engine _ :=1, EngineDesc:="GRG Nonlinear" SolverSolve SolverOk SetCell:="$H$32", MaxMinVal:=3, ValueOf:=0, ByChange:="$H$35", Engine _ :=1, EngineDesc:="GRG Nonlinear" SolverOk SetCell:="$H$32", MaxMinVal:=3, ValueOf:=0, ByChange:="$H$35", Engine _ :=1, EngineDesc:="GRG Nonlinear" SolverSolve SolverOk SetCell:="$I$32", MaxMinVal:=3, ValueOf:=0, ByChange:="$I$35", Engine _ :=1, EngineDesc:="GRG Nonlinear" SolverOk SetCell:="$I$32", MaxMinVal:=3, ValueOf:=0, ByChange:="$I$35", Engine _ :=1, EngineDesc:="GRG Nonlinear" SolverSolve SolverOk SetCell:="$J$32", MaxMinVal:=3, V
Use Solver Links Solver and Conventional VBA Solver is a powerful analysis tool, bundled with Excel and used for optimization and simulation of business and engineering models. It can be excel compile error can't find project or library even more powerful if used in conjunction with VBA, to automate solving of multiple excel compile error method or data member not found models which use different input parameters and constraints. In a simple example, there are two factors in B5 and B6. The
Excel Compile Error Object Required
product (=B5*B6) is calculated in B8. Solver will be used to find the maximum value of the target cell (the product in B8), subject to the constraint that both factors (B5:B6) shall not exceed http://www.mrexcel.com/forum/excel-questions/545612-compile-error-when-running-visual-basic-applications-solver.html a value of 4. Select Solver from the Tools menu, and enter the appropriate conditions and constraints in the Solver Parameters dialog. Click the Solve button, and another dialog indicates whether a solution is found and offers some options. If you record a macro while you use Solver, you will get something like the following: Sub SolverMacro1() ' ' SolverMacro1 Macro ' Macro recorded by Jon Peltier ' SolverOk http://peltiertech.com/Excel/SolverVBA.html SetCell:="$B$8", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$5:$B$6" SolverAdd CellRef:="$B$5:$B$6", Relation:=1, FormulaText:="4" SolverOk SetCell:="$B$8", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$5:$B$6" SolverSolve End Sub SolverAdd adds constraints to the Solver model. SolverOK defines the cell to optimize, how to optimize it, and what cells to change during the Solver optimization. The macro recorder wrote this line twice, so the first occurrence can be removed. To prevent parameters from a different Solver optimization interfering with the macro's optimization, Solver should be reset prior to running, using SolverReset. SolverSolve has an optional UserFinish argument; if UserFinish is False or omitted, the second dialog shown above will ask the user whether to save the optimization, but if UserFinish is True, Solver will end without the dialog. A modified Solver macro is shown below: Sub SolverMacro2() ' ' SolverMacro2 Macro ' Macro fixed up by Jon Peltier ' SolverReset SolverAdd CellRef:="$B$5:$B$6", Relation:=1, FormulaText:="4" SolverOk SetCell:="$B$8", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$5:$B$6" SolverSolve True End Sub When you try to run this macro, you get a compile error. The command SolverReset is highlighted, and the following error message appears. In order to use a macro based on an installed add-in, you must first make sure that the add-in is installed, then you must set a reference to the add-in in the workb
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of http://stackoverflow.com/questions/21915646/excel-vba-compile-error-sub-of-function-not-defined this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question http://superuser.com/questions/264257/excel-vba-macro-sub-or-function-not-defined x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up compile error Excel VBA: Compile Error: Sub of Function not Defined up vote 1 down vote favorite I'm trying to solve a problem with the following code but I just can't find a way around it. Whenever I try the Macro, I get an error "Sub or Function not defined". and the debugger highlights in yellow "Sub Solver_Overtime() and highlights in blue "SolverRest" Sub Solver_OverTime() excel solver error Application.ScreenUpdating = False Sheets("OverTime").Activate SolverReset SolverOptions MaxTime:=100, _ Iterations:=100, _ Precision:=0.000001, _ AssumeLinear:=True, _ StepThru:=False, _ Estimates:=1, _ Derivatives:=1, _ SearchOption:=1, _ IntTolerance:=5, _ Scaling:=False, _ Convergence:=0.0001, _ AssumeNonNeg:=True SolverAdd CellRef:="NET", Relation:=3, FormulaText:="NET_LIMIT" SolverAdd CellRef:="shftCount", Relation:=1, FormulaText:="shftCountLimit" SolverAdd CellRef:="schTemplate", Relation:=4, FormulaText:="integer" SolverOk setcell:=Sheets("OverTime").Range("Intervals[[#Totals],[OT]]"), MaxMinVal:=2, ValueOf:="0", ByChange:=Sheets("OverTime").Range("Template_Schedule[COUNT]") 'SolverOk SetCell:="TargetCell", MaxMinVal:=2, ValueOf:="0", ByChange:="schTemplate" SolverSolve True End Sub I'm not certain what the problem is because it runs just fine on another computer. Thanks in advance. excel vba excel-vba share|improve this question asked Feb 20 '14 at 17:48 BlueSun3k1 1863620 4 Did you add a reference to the Solver library? (Tools>References>Solver) –Tim Williams Feb 20 '14 at 17:56 @pnuts that was a typo, it's SolverReset. –BlueSun3k1 Feb 20 '14 at 18:01 1 @Tim Williams: I did not but now I did and it works. Thank you, that takes care of the issue. –BlueSun3k1 Feb 20 '14 at 18:01 1 @user1582396 do make it an answer and accept it please. –nawfal Sep 22 '14 at 13:14 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted I followed up on Tim
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Super User Questions Tags Users Badges Unanswered Ask Question _ Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Excel VBA Macro Sub or Function not defined up vote 1 down vote favorite I used the macro recorder to generate a macro in Excel 2007. When I try to the run the macro, it tells me: When I click Ok the definition of the macro gets highlighted. I'm really confused about why VBA would throw this error. I just defined it! Do you have any ideas what I did wrong? microsoft-excel microsoft-excel-2007 macros vba share|improve this question edited Mar 7 '12 at 14:29 Raystafarian 17.1k94378 asked Mar 30 '11 at 1:07 wdkrnls 3551516 add a comment| 3 Answers 3 active oldest votes up vote 1 down vote accepted 1st - check if there's no missing reference in the VBA project. 2nd - It seems it needs the Solver Addin. Ensure it's checked in Excel Addins. share|improve this answer answered Mar 30 '11 at 1:34 Tiago Cardoso 23615 I'll check this out. I didn't think about references. Hopefully that is it. Thanks. –wdkrnls Mar 30 '11 at 6:58 You were right. I didn't add Solver to the references for the project. Thanks. –wdkrnls Mar 31 '11 at 20:55 add a comment| up vote 2 down vote This answer is pretty late (I'm a new arrival to SuperUser). You need to go to VBA editor screen, choose References... from the Tools menu, find Solver and check the box to allow VBA to use Solver references in your code. share|improve this answer answered Mar 4 '14 at 13:24 joseph.billo 212 add a comment| up vote 0 down vote I had the same problem. I copied my code and created another module. Right clicking Module1 in the Project Tree, I used the "Module1