Solver FAQs

If you run into an error running the Solver Add-in that ships with Excel or the Analytic Solver COM add-in you may need to enable Macros from within Excel's Trust center.

More information on this can be found here:

https://support.office.com/en-us/article/enable-or-disable-macros-in-office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6

You will want to Enable Macros and enable "Trust access to the VBA project object model".

If you continue to experience an issue that we may be able to assist with please send us an email to [email protected] with a description of the problem and any screenshots of error messages you may be able to provide.

If you are a student enrolled in a University course that requires the Analytic Solver Platform and your professor has provided you with a course code for registration you are eligible for a discounted 140-day or 1-year license of Analytic Solver for Education (Analytic Solver Basic).

To acquire this license:You must be registered with your courses book and course code combination. Both should be provided by your professor.

-New registrations should occur at www.analyticsolver.com/Student.

-Registration updates - log in to www.analyticsolver.com then go to www.analyticsolver.com/Student to update your registration information.

You must have the Analytic Solver Platform v2019 (or newer) installed.

You should complete the license purchase process during your 15-day trial period to avoid loss of access.

"Maintaining" use of any license does require an internet connection.

Once you have your registration updated or you are newly registered:

Desktop Excel Users

1) Open Excel

2) Go to the Analytic Solver tab

3) Click the arrow underneath License

4) Select Manage Licenses.

If your trial license has expired, you should see a dialog box similar to this:

--Click the link for the appropriate length of license you wish to purchase and a new dialog box to our FastSpring purchase page should open.Please use your course code as the coupon code to receive your discount and your registered email address in the email field to ensure your license is created for you.

If your trial license is not yet expired, you should see a dialog box similar to this:

Click "Purchase License" to the right of the entry for "Trial License". DO NOT USE THE "Purchase 1-year License" OPTION IF YOU SEE IT UNLESS YOU WISH TO PURCHASE A FULL PRICE COMMERCIAL LICENSE. When you click "Purchase License" you should see a drop down allowing you to select 140-day or 1-year.

Click the link for the appropriate length of license you wish to purchase and a new dialog box to our FastSpring purchase page should open.Please use your course code as the coupon code to receive your discount and your registered email address in the email field to ensure your license is created for you.

Once the purchase is complete:

Close all currently open Excel instances

Open any workbook

Go to the Analytic Solver tab

Click the arrow underneath License

Select Login/Logout.

--This should open a Login dialog box (it may log you out first, this is okay)

Login with your registered credentials

--This will "pull" your license from our server for use on your machine.

Note: "Maintaining" this license does require an internet connection, at the very least when you first open Excel.

If you run into any issues pre or post purchase please let us know via email to [email protected].

The current commercial release (v2019, 19.0.3.0) of the Analytic Solver Platform and Solver SDK work with a web licensing system in which your license is tied to the email address that you registered with.

To access and use your license with the Analytic Solver Platform Excel add-in

Open Excel, go to the Analytic Solver tab, click the arrow underneath License, select Login/Logout and log in with the credentials your registered/purchased with:

[email protected]

Your license should be automatically implemented. You can check the amount of time remaining on your license by clicking the arrow underneath the Help button and selecting "Welcome Screen":

To access and use your license with the Solver SDK or XLMiner SDK

Open the license manager program:

Click on the Start button within Windows and go to All Apps -> Frontline Systems -> License Manager

and click Login in the left window pane (you may have to increase the screen size to see the Log In button):

Log in through there with the email address that you registered/purchased with and your license purchase(s) should populate in the right hand window pane. Once this is done you should be ready to start use of the SDK.

If you experience any issues post log in through Excel or the License Manager, please email us at for assistance.

If your school/company is behind a Proxy Server then this could block your login/license from working.

To allow for passthrough with this proxy server you can set the proxy information in our License Manager then proceed to Login with your Log/Pass to get your license working with Analytic Solver or the Solver SDK Platform.

Click on the Start button within Windows and go to All Apps-Frontline Systems-License Manager

When the box comes up it will list the products found by type. Web/Registry/File.

Clicking on the options button will allow you to type in the Proxy Server information along with your domain login to access.

Note: The Proxy "Address" should be in the following format:https://<proxyIP>:8080

With this set you can hit 'Login' on the left and log in with your e-mail/password that you registered on our http://analyticsolver.com site.

It would be best to include your IT staff for this information along with checking that REST calls can be enabled or set.

Here are some additional details that may be useful:

1. Ensure that email from [email protected] is not blocked and reaches the users email account. These emails are necessary to confirm the users account after registering and to send the users a password reset link. From: [email protected] Subject Line: "Confirm your account", "Reset Password", or "Confirm your account-Resend" This email also contains a link to confirm the account, or reset the users password. For example, the password reset link: https://analyticsolver.com/Account/ResetPassword?userId=[userID]&code=[codeString] or the account confirmation link: https://analyticsolver.com/Account/ConfirmEmail?userId=[userID]&code=[codeString] 2. Ensure that the licensing REST requests from the Excel Desktop software are not blocked and reach the end users machine. These are necessary for the user to obtain the proper license and run the software. REST requests are: GET https://analyticsolver.com/api/License/UserInfo GET https://analyticsolver.com/api/License/UserLicense GET https://analyticsolver.com/api/License/CheckOut GET https://analyticsolver.com/api/License/Renew GET https://analyticsolver.com/api/License/CheckIn Note: The above also have additional path and query parameter elements. Those are just the base URL's.

This happens most often when there is a typo or mixing of the course and/or book code.

As a student, if you are required to use the Analytic Solver Platform you should receive both the course code and textbook code from your professor, our support team can not supply these directly to students.

Note: Your course code should end in a 2 digit year "indicator" and a one letter "semester indicator" (I.E. 18F would be for Fall of 2018). While this is not always the case, looking for something similar to this (any type of Year/Semester indication) can help differentiate the course code from the book code.

If you're a professor who has reached this page and are in need of a course code for your students, please email us at [email protected] to begin the process.

This article is a "quick overview" of possible causes for the error message:

Invalid cell value at: <cell address>

This error message can occur during diagnosis or "Solve time", and looks like similar to this in the Solver Options and Model window pane Output tab:

[email protected]

If you encounter this error, please check the cells indicated for the following:

- Any #<Error> values in those cells (I.E. #NUM, #VALUE, #DIV/0, etc). These are Excel based errors and typically mean a value can not be "evaluated" properly by Excel's own calculations. Wrapping functions in cells containing these errors in an IFERROR function (more information here ) can help get past them, though you will typically want to find and resolve the root of the error to ensure the optimization performs properly.

-- Note: Use of IFERROR can be helpful with global optimizations (I.E. using GRG with Multi-start or the Evolutionary engine) that may test points where a denominator will be 0 (causing a #DIV/0 error).

- Any "merged" cells involving the cell addresses listed. This includes cells containing variables, constraints, or the objective (any cell used in the Optimization process). For example, if you set cell A1 to be a variable in an optimization model, merge cells A1 and A2 within Excel, and try to execute a solve you will receive the above error message at "Solve time". More information on "merging and unmerging" cells can be found here.

If the error persists and you can not find the root of the cause based on the information above, please let us know via email to .

If you receive the bellow error message during installation:

here

Important Note - Before Proceeding:

Please go to Excel and select File >> Account. Look at the About Excel area and it should have a version number, build number, and should say "Microsoft Store" or "Click-to-Run".

If it says "Microsoft Store" you are using a partial/trial version of Excel that does not allow for COM add-ins. In this case, if you can obtain a full copy from office.com you should be able to complete the install of the Analytic Solver Platform post install of a full version of Office.

Note: Installing a full version of Office will remove any templates (or contacts in the case of Outlook) from the Microsoft Store App version of Office you have installed.

If you already have a full version of Office installed:

Procedure 1 - for general user set-ups:

Open your Windows Start Menu.

Locate Excel.

Right Click Excel and select Run as Administrator.

Once Excel is open, open a new blank workbook.

Close Excel completely and retry the installation process.

Procedure 2 - if Procedure 1 does not work or your system uses Domain accounts:

Open a command prompt with administrator privileges - click the Start Button (bottom left of your screen) and in the search dialog box type in cmd, the top option from there should be Command Prompt. Right click this and select Run as Administrator.

In the command prompt window type the following command:

net user administrator /active:yes

If done correctly, you should see a "command completed successfully" message. If so, log out of your current account and you should see an administrator account available at the log-in screen. Log into this account, open Excel, and open a new blank workbook. Close Excel and retry the installation from your user account.

If this does not work, log back in to the local administrator account and run the installation from there. If the installation completes, log back into your own user account and follow the steps available to activate the add-in for your user's Excel.

Note: To deactivate this other account follow the same steps but use /active:no instead of /active:yes. It is suggested that you deactivate the local administrator account for system security.

This walkthrough is for situations when the Analytic Solver and Data Mining tabs in Excel are either missing from the Excel ribbon or you need to add/register the addon for additional users besides the Administrator account.

Solution

There are two procedures to follow. Its recommended you attempt the Auto Load Addin option below first as you may not have to do any more than that.

Procedure 1: Auto Load Addin

Log in to the Windows user account that you would like the Analytic Solver available to.

Make sure Excel is closed.

Open your Windows Start Menu and type in ASP to "Search", the ASP Application Manager should appear at the top of the list:

Select the ASP Application Manager and left click the 'Register RSP Excel Add-in' button.

Click OK on the confirmation dialog box and re-open Excel, the tabs should now be available again.

Procedure 2: If the above does not work

Open a command prompt with administrator privileges - click the Start Button (bottom left of your screen) and in the search dialog box type in cmd, the top option from there should be Command Prompt. Right click this and select Run as Administrator. In the command prompt window type the following commands (one at a time, pressing enter after each command to execute it. Do not enter the or any text after it, these are just descriptions/hints): cd \ cd Program -Press tab to auto fill to Program Files cd front -Press tab to auto fill to Frontline Systems -If it does not auto-fill to Frontline Systems type in cd .. and press enter to go back --type in cd Program ---Press tab twice, this should auto-fill to program Files (x86) instead, then try cd front and press tab to auto-fill to Frontline Systems cd analytic -Press tab to auto fill to Analytic Solver Platform cd bin regsvr32.exe rspaddin.xll Once this is done you should get a pop up message indicating that the command completed successfully and the dll is registered. Follow the steps in Procedure 1 above to ensure the add-in is fully "activated".

Startup Script (good for Lab installs or Domain Accounts with multiple users)

The name of this register program is here (depending if you are using 32bit or 64bit):

C:\Program Files\Frontline Systems\Analytic Solver Platform\Bin\RegRSPAddin.exe

(Might need to adjust the location based on 32bit/64bit)

Here is the command usage when adding to a login script.

Usage: RegRSPAddin [/s] [/r] [/u]

/r or /reg or /register or -r or -reg or -register registers ASP/XLMINER add-in

/u or /unreg or /unregister or -u or -unreg or -unregister unregisters ASP/XLMINER add-in

/s or /silent doesnt show message boxes. Registers ASP/XLMINER add-in silently

/? or /help or /h shows this help string

To register: RegRSPAddin /r /s

To unregister: RegRSPAddin /u /s

Example script entry:

C:\Program Files\Frontline Systems\Analytic Solver Platform\Bin\RegRSPAddin.exe /r /s

Example-

Create a shortcut to C:\Program Files\Frontline Systems\Analytic Solver Platform\Bin\RegRSPAddin.exe added the switches /r /s and placed it in the "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Startup\." directory.

STEP 1: Before getting started, make sure you have anOffice 365 subscription, and that your Excel version is up to date.

Excel Online: Requires no update, as it is always up to date -- proceed to Step 2!

Excel for Mac: Requires Version 16.25 or later. (To Update: System menu - Help - Check for Updates)

Excel for Windows: Requires Version 1904 or later. (To Update in excel:File - Account - Update Options - Update Now)

If youcan'tget Version 1904 or later, you are on the "Semi-Annual Channel" - Click here for steps to switch to the "Monthly Channel"

STEP 2: Access the "Store" - Microsoft AppSource.

Excel Online: Click Insert tab, then Office Add-ins

Excel for Mac / Windows: Click theInserttab, thenGet Add-ins. In the Office Add-ins dialog (pictured below), click theStore

You can also access the apps outside of Excel:

Analytic Solver Data Mining Click here

Analytic Solver Click here

STEP 3: Find and insert the Analytic Solver Add-in.

Search for Analytic Solver. You'll see both "Analytic Solver" and "Analytic Solver Data Mining". First click theAddbutton next to Analytic Solver.

STEP 4: Find and insert the Analytic Solver Data Mining Add-in.

Microsoft requires that you install both parts of the product separately. Repeat steps 2 and 3, but this time click theAddbutton next to Analytic Solver Data Mining.

You've nowcompleted the installation process!

STEP 5: Login to use Analytic Solver.

Click theAnalytic Solvertab on the Ribbon. If you don't see the Task Pane on the right side, click theModelbutton on the Ribbon.

Click theGet Startedbutton in the Task Pane. ALogin dialogpops up. (If you don't see the button, you are already logged in.)

Either the email address and password you use on Solver.com, or Create an Account.

You're Ready to Start Solving!

ChooseHelp - Help Centerfrom the Ribbon to access User Guides, Example Models, our Knowledge Base, Support Live Chat, and more. ChooseLicense - Manage Licensesto check your license status, renew or upgrade. You can also Login and Logout via this menu.

The following error message:

here

Is most often indicative of the incorrect "bit version" of the Analytic Solver Platform being installed.

It is recommend that you uninstall the Analytic Solver Platform at this point in order to perform a reinstall of the correct "bit version".

Please follow the steps available here to uninstall.

Once this is complete, please contact [email protected] and let us know your current "bit version" of Excel so that we may supply you with the correct next steps. To find out what "bit version" of Excel you are running, please go .

Some extra details:

If you select No at the above dialog, Excel should open normally but you will not see the Analytic Solver tabs within Excel. This is okay, as the Analytic Solver COM add-in can not load properly unless the installed bit version matches you Office/Excel bit version. It is recommended you do not attempt to load the add-in after receiving the above error message to avoid unnecessary confusion.

If you select Yes, a workbook will open and you will see something similar to this:

This is the expected behavior, closing this workbook will not disable the add-in. Next time you open Excel, you will likely receive the error message depicted near the beginning of this article again if you have not yet gone through the uninstall and reinstall steps above.

Step 1: Download Analytic Solver on AnalyticSolver.com

Before you can fully install Solver, you will need to register on AnalyticSolver.com first.

Once you are logged in, download the software from the menu.

here

Note: youre using a Mac computer,don'tdownload the software. Go to this page to learn about your options.

Step 2: Open and Run Analytic Solver

If installation doesnt automatically start running, you may need to look for Solver in your downloads folder.

You will begin to see installation wizards that indicate that Solver is beginning to install.

Step 3: Open Excel and Log In to Solver using the account you registered with AnalyticSolver.com

Step 4: Understand your license

Trials last 15 days, but if you have a license, your registration will connect excel to your license.

From this screen, you can get acquainted with your license, and how many days remain on your license.

If you want to access this screen again, click on help in the Ribbon.

Note: The installation "registers" the add-in for the installing user.The add-in within Excel must be "registered on a per user basis". For multi-user systems please review the Help Desk articlewhich describes registering the add-in for a single user and creating a "startup script" if all users of a system will be using the Analytic Solver within Excel.

This issue is introduced within the Windows Operating System itself, most often seen in Windows 10. There should be no need to uninstall and reinstall the Analytic Solver or Microsoft Office. When the issue is present, you may see dialog boxes that look like this:

[email protected]

Or you may be missing some Parameters for specific operations, such as Simulation Parameter Analysis:

Lastly, if you create a Chart using the Data Mining Chart Wizard you may not be able to interact with it and must force close Excel to get it to go away.

Such issues are "tied together" and are generally resolved with the same set of steps.

Please try these two options:

Completely update your Windows OS through Windows update if possible. If not possible, skip to 2 below.

https://support.microsoft.com/en-us/help/4027667/windows-10-update

Note: You will want to ensure your Windows Operating System build is at least build 1803 to ensure full compatibility. More information on how to check this can be found here.

Change your Excel "When using multiple displays" setting to "Optimize for compatibility". This will take affect even if only one Monitor/Display is active.

Open an Excel file (new or pre-created)

Click File at the top left of Excel

Select Options

Ensure you are at the "General" tab on the left

Locate the "User Interface options" - at the top by default, you may have to scroll up to find it

Select the radio button for "Optimize for compatibility"

Click OK

Close and Reopen Excel and test the problematic operation(s)

If you have a question about the above information please let us know via email to .

This is a quick overview of the most common cause of the following error message:

"The linearity conditions required by this Solver engine are not satisfied"

Linear models use functions that are linearly defined, though they do not have to be "two or three dimensional". For example, 1a + 5b + 3c + 20d is a linear function where a, b, c, and d are variables. More information on linear functions can be found here.

This error message occurs post "start" of an Optimize operation and means your model was "diagnosed" as non-linear and/or non-smooth. When this occurs, it can help to check the Output tab of the Solver Options and Model window pane, which will report the model diagnosis as well as non-linear or non-smooth operations such as in the screenshot below:

[email protected]

Note in the screenshot the follow lines:

- "Warning: Non-linear operation ..." which tells you the first operation performed that is non-linear or non-smooth and the cell the operation was found at as well as the operation "type" (non-linear in this case).

-- Note: The warning also indicates the number of non-linear or non-smooth operations but only reports where the first one was found for a particular "type" (either non-linear or non-smooth, these will be separate "warnings").

- "Model diagnosed as" which tells you your "model type". NLP in Non-linear Problem, NSP is Non-smooth Problem, and there are variations to both (I.E. NLP/MIP for non-linear mixed integer). If the model is not diagnosed as some variation of LP, you will not be able to use the LP/Quadratic engine (or one of our "plug-in" linear solvers such as Gurobi) to solve it.

Some models can be "linearized" if a user did intend to have a linear model (suggested, if possible). For example IF functions (which are non-smooth) can be linearized with Binary variables.

More information on linearizing IF functions can be found here.

More information on linearizing IF and ABS functions can be found here.

If you believe your model is linear and are unsure why you are receiving the above error message please let us know via email to . Note that it is helpful in these cases to have a copy of the model if it is possible to send one with your initial request.

In Excel on the Analytic Solver tab on the Ribbon, click the Help dropdown menu and choose Examples. This opens a workbook. Select "Optimization Examples and another new workbook will open which contains the examples used for conventional optimization in the User Guide.

The Link/Worksheet "Inventory Planning" is the one you're interested in. We recommend running this example with Guided Mode on (Help - Operating Mode - Guided Mode), since it will give you a better explanation of what happens, as the Platform first finds the IF functions, then attempts to transform them into equivalent sets of integer and continuous variables and linear constraints, and succeeds and solves the model to optimality.

To read about the transformations that the Platform is making in the Inventory Planning example, click Help - User Guides - User Guide and go to the User Guide's last chapter, "Building Large-Scale Models". In the section "Improving the Formulation of Your Model", read the subsection "Techniques Using Linear Functions and Binary Integer Variables", especially the part about IF functions.

To do this, you would create one binary variable for each variable you have andfor each value you want that variable to take. For example, say you have 5 variables and 5 different values that each variable can take, set-up so the variables values are in A1:A5 and the variable cells haven't been assigned yet. In B1:B5 you can set binary variables, and in B6:B10 you set =A1 * B1 matching the rows as you go down (so A1 * B1 is in B6, A2 * B2 is in B7 etc, etc). You would then have to do the same for the next 4 variables and ranges (if they have different ranges, this gets a bit more complicated). Note you will need one more thing: You want to SUM your binary variables that are associated with a single variable, and set a constraint that, that SUM must = 1, I.E. the constraint on a cell that contains SUM(B6:B10) = 1. This ensures that solver uses one of the values in the list of available values, no more, and no less. From there the rest depends on your objective function, if you want a simple SUM you can SUM(B6:<end of binary multiplications>) in your objective, and Solver will add a bunch of 0's and one actual value, based on the set-up above. We understand that the instructions above may not be the easiest to follow, so we set-up a very simple "mock" example that shows the above set-up with a very simple objective function, the numbers and functions used are "meaningless" in this case as everything in the workbook is arbitrary. With the way the workbook it set-up, the bottom row of binaries will always be chosen (since the objective is set to maximize at the moment). If you change the objective to minimize the top row will be chosen. You can further adjust the available values for variables and the objective function to get different results, but the idea will be the same as that displayed in the workbook. Note that you will not "see" the iterations, but Solver is testing every possible combination of binary values in its solving process (at least, very close to it) in order to "prove" the optimal solution.

This article is meant to help users with the implementation of "soft constants" within their model(s). A "soft constant" is a variable or formula result where you may want to reach a particular value, however you are unsure (or you do know for sure) if it is possible to reach the desired value and it is okay if the Solving process gets "as close as possible".

For example, you construct a distribution model that determines the minimum cost to ship items from three different warehouses that all have their own level of demand. Initially, you would want to guarantee all three warehouses have their demand completely met, and would add an equality constraint that "product provided" = "demand". Such constraints can not always be met in real world situations, in which case the "next best" would be to have your product provided be as close to the demand as possible.

To do this, you would include the "product provided" in your objective function. Note you want the "product provided" to have a positive affect on the objective - this means that if you're minimizing you will subtract your "product provided", if you're maximizing you will add your "product provided". This gives "weight" to your "product provided" in terms of the objective, and the "product provided" will in turn be optimized as well to find the best objective possible.

Following this you will want to adjust your equality constraint to a less than or equal to (<=) constraint, this allows solver to consider solutions that do not absolutely meet the previously implemented constraint. However, due to the adjusted objective function the value on the left hand side of the constraint will get "as close as possible" to the right hand side value.

To demonstrate this, please review the workbook available at the end of this article. This workbook is a modification of our "Supplier Choice" example.

On sheet Supplier Choice 1 - the model is not feasible, there are less contracts available than are required. On Supplier Choice Mod, we create a feasible model by changing the constraint "Total_awarded" >= "Contracts_required" to be "Total_awarded" <= "Contracts_required" and subtract (since we're minimizing cost) 100 times the SUM of the "Total_awarded" values from the objective. The extra multiplication is for "scaling", any one contract that is awarded will "add" more to the objective than is lost with just the SUM, so we "scale" the SUM such that when a contract is awarded, the Total Cost will become smaller instead of larger.

Since we have changed our original objective, but we still want to know what our actual cost is, the original objective function is now in Cell C25 so we can quickly see the cost of awarding the contracts. Note that cell C25 is not part of the model, it is just a "data cell" now telling us a useful piece of information that was "lost" due to our model modifications.

This normally means you are missing a constraint (or multiple constraints) that is allowing the objective to go to positive or negative infinity. For example: Set A1 as a variables Set A2 as = A1 * 2 Set A2 as the objective to maximize When you run this, you should get that same error message, since A1 is not restricted and A2 can potentially become positive infinity. Please check your constraints and ensure the objective is not being allowed to range from positive to negative infinity. You can also run a quick test by applying a constraint directly to your objective cell which should also resolve the error but is not generally good modeling practice unless you have known minimum or maximum value for it to be.

Open the Constraints section of the Solver App:

Click the "Add" button:

Enter your variable cell or range in the Left Hand Side box at the top and click the arrow pointing down on the right hand side of the middle box to open the drop down menu:

Select the "type" of constraint you wish to add.

Click OK to exit the constraint add/edit dialog or click Add to add another constraint.

Notes:

In the case of <=, =, or >= you will need to enter the Right Hand Side of the constraint afterwards.

In the case of integer, binary, or alldifferent your Left Hand Side must be a variable or set of variables to work properly, you do not need to enter a Right Hand Side, and you can not apply integer, binary, or alldifferent constraints to non-variable cells.

To save a workbook to the Analyticsolver.com web interface, please do the following:Go to the Solver Home tab

[email protected]

Select Save

Select To Other Source to save the workbook directly to your system. The workbook will be downloaded automatically to your Downloads folder and be saved as a .xlsx file.

Select To AnalyticSolver.com to save the workbook to the web interface for access to it later from the same or different machine while working in the web interface.

If you experience any issues pre or post save of your workbook, please let us know via email toor start a live chat from www.solver.com.

If you are not sure which version of Excel you have you can quickly check.

For Excel 2010 click File - Help, and you'll see 64-bit or 32-bit listed after your version number under "About Microsoft Excel".

For Excel 2013 and Excel 2016, click File - Account - About Excel, and you see 64-bit or 32-bit listed after your version numberat the top of the dialog.

To help demonstrate how a user can convert a VBA model built within Excel to the Solver SDK Platform Java language, attached are two items: productMixVBA.xlsm and productMix.java.

The .xlsm contains a Macro that builds and solves a model in the workbook with comments throughout. A user can execute the Macro with the Run Macro button in the workbook and the code should be "public" and available to view and modify if desired. The Solver reference must be set in your VBA references.

The .java contains the Java code necessary to build a .class file that can be "executed" via command line to solve the same model that is solved in the .xlsm. There are comments throughout the code.

Notes:

The SDK version solves to a slightly different answer. This is due to mechanical precision available within the SDK that is not available within Excel. Rounded off, the solutions are the same. No "manual" rounding was done in the Java code.

You must have the Solver SDK installed and the JDK (8xxx or newer should suffice, we can not guarantee compatibility with older versions of the JDK) installed to test the .java file.

The Java code should be convertible to any other supported language in the SDK (C, C++, C#, R, Python, MATLAB, VB.Net) with some minor modifications.

The model example is very simple and does not demonstrate all parameters/capabilities of the SDK. Please review the Solver SDK User Guide and Reference Guide (available in the Help folder of your install path) for more information.

There are a multitude of pre-built examples for the different supported languages of the Solver SDK. For more complex examples in code only (no workbook comparison) please review the examples in the Examples folder of your SDK install path.

If you run into any issues with the SDK or have further questions about conversion from VBA to SDK, we will do our best to help. Please direct inquiries to [email protected].

Installing the Analytic Solver Platform which includes Data Mining should be done post registration at:

www.analyticsolver.com/Account/Student

here

Your Professor will need to assign you a course and book code. If youdon'tknow the course and book code to register with then youll need to first reach out to them for these codes to register. Please be aware that our Analytic Solver Platform only works on Windows operating systems. It does NOT work on Macs. If you have a virtual machine running on your Mac which is running Windows then you can install this within that Windows Virtual machine.

If this registration fails such as it reports an incorrect Course or Book code, then youll need to ask your Professor FIRST, to clarify what the correct codes are and if theyve even registered their course with us.

When successfully registered, youll be able to log in to www.analyticsolver.com. Once logged in you should see a Download Analytic Solver button near the top right of the page just to the left of your registered email address:

Clicking the Download Analytic Solver button will take you to the download page, use the Download Now button to download your SolverSetup program:

The download/setup will typically go to your Downloads folder.NOTE: Your own SolverSetup program will have different "trailing numbers"

Double click/Run the Setup to start the installation. Please note that it takes some time to run this (5-10 minutes). The setup is downloading/extracting the full program on the internet, so it can take some time for this process to complete. DO NOT run the setup more than once or it will stall the initial setup and the installation will fail. If you accidentally do run this more than once, reboot your machine and run it again (just once).

From here, proceed with the installation with Next and Agreement confirmation.

After successful installation of the Analytic Solver Platform you should see the tabs listed within Excel along with a task pane on the right to enter the parameters for your Optimization/Simulation or Data-Mining projects and assignments.

If for some reason the Analytic Solver and/or Data Mining tabs are missing, please follow the instructions available .

Our web interface does its best, but it does work within certain limitations. The most common "violations" of these limits involve the following:

The workbook must be saved in .xlsx format.

The workbook must have less than approximately 50,000 "filled" cells. Occasionally, data may be entered in a cell well outside the range of what a user expects, and every cell between the first cell (A1) and that "far away" cell will attempt to load, causing the browser to fail the upload.

The workbook can not contain any external workbook references.

Please note that not all Excel formulas (especially "custom functions) will not work within the web interface but many of the most common ones will such as SUM, SUMPRODUCT and IF.

You can find more information on the web interface here:

www.analyticsolver.com/Home/About

We do suggest using Chrome as your web browser when using the web interface, but it is not a requirement.

If you are experiencing issues with your workbook (I.E. formulas showing instead of values or "out of memory" error in the web browser) and you're not sure why, please contact us at [email protected] and include a copy of the workbook you're trying to upload.

To upload a whole workbook to the Analyticsolver.com web interface, please do the following:Go to the Solver Home tab

[email protected]

Select Open

Select From Other Source

This should open a File Explorer window on your system. Navigate to the file you wish to upload, select it and click Open.

The web interface will then attempt to upload and "open" the workbook. Please note the following limitations:The workbook must be saved in .xlsx format.

The workbook must have less than approximately 50,000 "filled" cells. Occasionally, data may be entered in a cell well outside the range of what a user expects, and every cell between the first cell (A1) and that "far away" cell will attempt to load, causing the browser to fail the upload.

The workbook can not contain any external workbook references.

If you experience any issues pre or post upload of your workbook, please let us know via email to or start a live chat from www.solver.com.

It is possible to call the Solver programatically through a user defined macro written in VBA. This might be desirable when the Solver model needs to be protected or hidden, for example, when the model is distributed to one or several end users. This article explains how to use the Object-Oriented API inAnalytic Solver Platform to create, modify and solve an optimization model under the control of your custom application written in VBA.

You can define a Problem and instantiate it from the spreadsheet with two lines of code, then access the elements of your model via Variable and Function objects. You can perform an optimization, access the values of decision variables, constraints and the objective, access trial data and summary statistics for uncertain variables and functions, and present them the way you want to your end user. All the power of the Excel object model is available, including database access, charts and graphs, and custom dialogs and controls.

Adding a Reference in the VBA Editor

To use the new object-oriented API in VBA, you must first add a reference to the type library for theAnalytic Solver Platform COM server. To do this:

With Excel open, press Alt-F11 to open the VBA Editor.

Select menu choice Tools -> References.

Scroll down until you findAnalytic Solver Platform 2018 Type Library.

Check the box next to this entry, and click OK to close the dialog.

Use File Save to save your workbook.

Invent_DLL using VBA for KB.xls

Note that this is a different reference from Solver, which is the reference you add in order to use the traditional VBA functions.

Example VBA Code

The example VBA Code below shows how a user could use the Object Oriented API to define a new Problem, reset the Task Pane Platform, Engine, and Model Tabs, add variables, constraints, and an objective, setup evaluator functions to monitor the progress of the Solver, and finally solve the model.

Please open the attached model, InventDLL using VBA for KB.xlsm. The model that we will be solving has already been added to the Task Pane Model Tab. This model is nonlinear with 4 variables, 2 constraints, and 8 variable bounds. Please take a moment to become familiar with this model before proceeding with the steps below.

1. Open Microsoft Visual Basic for Applications - If using Office 2007/2010 click Developer > Visual Basic to open Microsoft Visual Basic. If the Developer tab does not appear on the Excel Ribbon, click the Microsoft Office Button (located in the upper left hand corner) > Excel Options > Popular and check "Show Developer Tab in the Ribbon".

If using Excel 2003, click Tools > Macro > Visual Basic Editor

Inside the VBA window, click Insert > Module, then define a new Analytic Solver Platform problem (prob) and instantiate it from the spreadsheet using the two lines of code below.

Dim prob As New RSP.Problem

prob.Init ActiveSheet

You can use the Init method to instantiate the Problem from a named model or worksheet this will create all of the Variable and Function objects for the problem defined in that model or worksheet. (If youdon'tuse Init, the Problem is instantiated from the active worksheet.) At this point, if your model has already been setup in the Task Pane Model Tab, you can proceed directly to Step 10 to solve the model. In this example, we clear the model from the task pane to illustrate how to setup and define a model from the very beginning.

2. Clear Previous Model and Reset Parameter Defaults Next we clear the previous model in the Task Pane Model Tab and reset all option values to their defaults in the Task Pane Platform and Engine Tabs.

prob.Variables.Clear

prob.Functions.Clear

prob.Engine.ParamReset

prob.Model.ParamReset

3. Turn off Guided Mode To bypass the Guided Mode Solver dialogs, the Guided Mode parameter is set to False.

prob.Model.Params("GuidedMode").Value = False

4. Set the Initial Starting Point The model appearing in this article is a nonlinear model that will be solved using the Standard LSGRG Nonlinear Engine inAnalytic Solver Platform, Risk Solver Platformor Premium Solver Platform, or the Nonlinear GRG engine in Premium Solver Pro. Since the final solution found by a nonlinear engine can depend on the initial starting point, the four lines of code below set the variable values in cells C13:F13 to 50. Therefore, each time this macro is run, the initial starting point will always be the same.

Cells(13, 3).Value = 50

Cells(13, 4).Value = 50

Cells(13, 5).Value = 50

Cells(13, 6).Value = 50

5. Defining the Decision Variables Next we define our decision variables in cells C13:F13, set them as Integers and add lower and upper bounds of .001 and 100, respectively. A lower bound of .001 is added to prohibit Solver from setting the variable values to 0 which would cause a division by 0 in cells C15:F15. Since the GRG engine performs better with both upper and lower variable bounds, an upper bound of 100 is also added.

Dim vars As New RSP.Variable

vars.Init "C13:F13"

vars.VariableType = Variable_Type_Decision

vars.IntegerType.Array = Integer_Type_Integer

vars.LowerBound.Array = 0.001

vars.UpperBound.Array = 100

prob.Variables.Add vars

6. Defining Constraints Next, we add the two constraints: G17 <= H17 (Cost of Products <= Budget) and G16 <= H16 (Space Used <= Space Available). When adding each constraint, we first define the left hand side of the function as con1 (G17) or con2 (G16), designate the function as a constraint (Function_Type_Constraint), define the right hand sides of each function (H17 and H16, respectively), then add each function to the collection with prob.Function.Add.

Dim con1 As New RSP.Function

con1.Init "G17"

con1.FunctionType = Function_Type_Constraint

con1.UpperBound.Array = "H17"

prob.Functions.Add con1

Dim con2 As New RSP.Function

con2.Init "G16"

con2.FunctionType = Function_Type_Constraint

con2.UpperBound.Array = "H16"

prob.Functions.Add con2

7. Defining the Objective Function Finally, we define the objective function in cellG15 to be minimized.

Dim obj As New RSP.Function

obj.Init "G15"

prob.Solver.SolverType = Solver_Type_Minimize

obj.FunctionType = Function_Type_Objective

prob.Functions.Add obj

8. Progress Evaluator In order to monitor the progress of Solver during the solution process, we set up an evaluator to be called on every iteration. Evaluators are implemented inAnalytic Solver Platform using an event handler which must be defined within a Class. In VBA, a Class must reside in a Class module. Here, we create the evals class which will reside inside the Class1 class module.

Dim evals As New Class1

Next, we create the MonitorProgress evaluator (Set evals.MonitorProgress = New RSP.Evaluator) and finally assign the evaluator type (Eval_Type_Iteration). There are several different types of evaluators supported byAnalytic Solver Platform, Risk Solver Platform,Premium Solver Platform and Premium Solver Pro. For more information on the various types of evaluators supported, please see the article, "Using a Callback Function when calling the RSP through VBA".

Set evals.MonitorProgress = New RSP.Evaluator

prob.Evaluators.Item(Eval_Type_Iteration) = evals.MonitorProgress

9. Monitor Progress EvaluatorThe MonitorProgress evaluator of type Eval_Type_Iteration must be written inside the Class1 VBA Class Module as mentioned in Step 8 above. To add the Class1 VBA Class Module, click Insert > Class Module.

Before we write our evaluator, we must declare a custom event handler. We declare this event handler directly underneath Option Explicit as shown below.

Option Explicit

Public WithEvents MonitorProgress As RSP.Evaluator

Once this line of code is in place, we can click the down arrow next to General and select MonitorProgress from the menu. VBA will automatically create our nonlinear evaluator, MonitorProgress_Evaluate. Inside this evaluator, you can monitor the progress of Solver by checking the iteration number, the objective, constraint or variable values, etc. In the example below, we check the number of iterations, along with the objective function value. (Since over 150 iterations are performed in this model, weve chosen to display the objective on every 10th iteration using the VBA modulus (Mod) operator in the line of code, If Evaluator.Problem.Engine.stat.Iterations Mod 10 = 0. This specifies that if the number of iterations is divisible by 10, then display the number of iterations and the current objective function.) Finally, we tell Solver to continue optimizing by returning Engine_Action_Continue. (Important Note: If Number of Threads is set to 0 or >1 on a multiple core machine running a permanent license, the message box inside the evaluatorwill cause an error in V12.x. This issue will be addressed in the next version of Analytic Solver Platform. In the meantime, everything inside the evaluator must be thread safe.)

There is one additional Engine_Action symbolic constant, Engine_Action_Stop, which stops the solution process immediately if returned.

Public Function MonitorProgress_Evaluate(ByVal Evaluator As RSP.IEvaluator) As RSP.Engine_Action

If Evaluator.Problem.Engine.stat.Iterations Mod 10 = 0 Then

MsgBox "Iteration Number = " & Evaluator.Problem.Engine.stat.Iterations & vbCrLf & _

"Objective = " & Evaluator.Problem.FcnObjective.Value.Item(0)

End If

MonitorProgress_Evaluate = Engine_Action_Continue

End Function

10. Choose Solver Engine and Solve the Model Back inside the main program in Module1, we choose the Standard LSGRG Nonlinear engine (Nonlinear GRG engine if using Premium Solver Pro) to solve the nonlinear model and solve the problem.

Analytic Solver Platform, Risk Solver Platform, Premium Solver Platform and Premium Solver Pro offer additional Solver engines to solve models of different types. For example, if solving a linear model, the LP/Quadratic engine is available in Risk Solver Platform or Premium Solver Platform, and the LP Simplex engine is available in Premium Solver Pro. To confirm your model type, click back to Excel and then click Optimize > Analyze without Solving on the Solver ribbon or menu. The model will be diagnosed and the problem type will be reported on the bottom half of the Task Pane Model Tab.

prob.Engine = prob.Engines(prob.Engine.GRGName)

prob.Solver.optimize Solve_Type_Solve

When the solution process ends, the property OptimizeStatus will return an integer value that indicates the stopping condition. Analytic Solver Platform, Risk Solver Platform, Premium Solver Platform and Premium Solver Pro return values from 1 to 21. When the Interval Global Solver or field-installable Solver engines are used,Analytic Solver Platform may return engine-specific values for custom stopping conditions, starting at 1000. See the table below for descriptions of each. In this example, we simply stop and advise the user to check the Task Pane Output Tab for the Final Result Message, then turn Guided Mode back on before the macro finishes.

If prob.Solver.OptimizeStatus = 0 Then

MsgBox "Finished! Please see the Task Pane Output Tab for the Final Result Message and your Excel sheet for the final variable values."

Else

MsgBox "Problem with Solve! Please see the Task Pane Output Tab for the Final Result Message."

End If

prob.Model.Params("GuidedMode").Value = True

TheAnalytic Solver Platform Reference Guide includes a chapter explaining theAnalytic Solver Platform Object Model in more detail. Please refer to this guide for more detailed information on calling Analytic Solver Platform, Risk Solver Platform, Premium Solver Platform and Premium Solver Pro through VBA.

Value

Stopping Condition*

0

Solver found a solution. All constraints andoptimality conditions are satisfied.

1

Solver has converged tothe current solution. All constraints aresatisfied.

2

Solvercannot improve the current solution. All constraints are satisfied.

3

Stop chosen when the maximum iteration limit was reached.

4

TheSet Cell values do not converge.

5

Solver could not find afeasible solution.

6

Solver stopped at user's request.

7

The linearity conditions required by this Solver engineare not satisfied.

8

The problem is too large for Solverto handle.

9

Solver encountered an error value in a target orconstraint cell.

10

Stop chosen when the maximum timelimit has been reached.

11

There is not enough memory available to solve theproblem.

13

Error in model. Please verify that all cells andconstraints are valid.

14

Solver found an integersolution within tolerance. Allconstraints are satisfied.

15

Stopchosen when the maximum number of feasible (integer) solutions wasreached.

16

Stop chosen when the maximum number of feasible (integer)subproblems was reached.

17

Solver converged in probability to aglobal solution.

18

All variables must have both upper and lower bounds.

19

Variable bounds conflict in binaryor alldifferent constraint.

20

Lower and upper bounds on variables allow no feasiblesolution.

21

Solver encountered an errorcomputing derivatives.

1000

Interval Global Solver requires Solve with Automatic andstrictly smooth functions.

1001

Function cannot be evaluated forgiven real or interval arguments.

1002

Solution found, but not proven globally optimal.

*For more information on each stopping condition, please refer to the chapter, "Solver Result Messages", in theAnalytic Solver Platform Reference Guide.

This is most often due to a "back up" of model solving for our server. This can occur when there are many solve requests being sent through. Most often canceling and restarting the solve 2-3 minutes after attempting the first one resolves the issue.

One alternative is to try out the Analytic Solver Platform, which also allows models to be solved directly on a users machine. If you have not already, you may start a 15-day free trial by going to www.solver.com and registering for a new account. For technical issues with the Analytic Solver Platform installation or licensing, please start a live chat from www.solver.com or email our support team at [email protected].

A second alternative to using the Solver App is to use the Standard Solver that is built into Excel, this "solves" directly on your machine so you should not experience issues like this one when using it. To enable the Standard Solver, please follow the steps available here: https://support.office.com/en-us/article/load-the-solver-add-in-in-excel-612926fc-d53b-46b4-872c-e24772f078ca?ui=en-US&rs=en-US&ad=US

If you're receiving the error message:

"You don't have sufficient permissions for this action"

in Excel 2016 for Windows when you begin a "Solve" through the Solver App this usually means that you have an older "version" of Excel installed that does not support the required functionality of the Solver App.

If possible, update your Office installation with the steps available here:

https://support.office.com/en-us/article/install-office-updates-2ab296f3-7f03-43a2-8e50-46de917611c5

Post update, ensure your office version is at least Version 1708 (Build 8431.2094) or newer. If it is not, you may need to perform an Office Repair to update properly:

https://support.office.com/en-us/article/repair-an-office-application-7821d4b6-7c1d-4205-aa0e-a6b40c5bb88b

If neither of these options allows your build/version to reach or exceed1708 (Build 8431.2094) you may need to contact Microsoft's Office Support team for assistance with updating your installation.

Alternately, you can try uploading/opening your workbook in Excel Online and running your Solve through the Solver App available through Excel Online.

If the issue persists after you are updated to or beyond Version1708 (Build 8431.2094) in Office 2016 or Office 365 please contact us at [email protected].

This issue should now be resolved. If your play/solve button is still covered by the Info button in the Solver App, please unload/reload the app and clear your Internet Explorer browser cache (even if you use an alternate browser regularly, out App runs code through IE11 and some previous settings may be stored there that are causing an issue).

If the issue persists, please email us at [email protected].

If you attempt a solve through the Solver App and receive the following error message at the bottom right of the App:

[email protected],

This means there is an "Excel specific" function implemented into your model that our App does not know how to evaluate. Examples of these functions are COVERIANCE.P,HYPERLINK, and INFO. This is not a comprehensive list, but may help give you an idea of what to look for in your model.

Generally, "generic" functions that do simple calculations or checks such as SUM, IF and SUMPRODUCT are not an issue.

If you unable to locate the problematic function(s) please contact our support team via email to if possible include the workbook that is exhibiting the issue and we'll do our best to help investigate.

Generating reports with the Solver App is done with the "Reports" button along the top of the App, between the Reset and Solve buttons. You must first run a Solve to produce the Answer and Sensitivity report.

For a Sensitivity report, the following conditions for the model must be met, otherwise you will only receive an Answer Report:

1) A solution must be found

2) A linear or non-linear engine must be used to solve

3) There can be no integer constraints within the model

The Solvebutton is a blue arrow pointing to the right at the top right of the Solver App:

When started properly, you will see this:

And upon proper completion, you should see a blue pop up box at the bottom right of the app indicating any errors or that results were found.

This can happen when there is an add-in conflict.

Open Excel, open a blank workbook, go to File (top left), Options, Add-ins, switch the drop down at the bottom the of window to COM add-ins, and disable (uncheck all the boxes) everything there, including the Analytic Solver COM Add-in if it is available, and click OK. Once this is done follow the steps available here for registering only the Analytic Solver Platform add-in. Once this is done reopen Excel.

If the problem persists please feel free to contact us at [email protected].

Go to http://analyticsolver.com

Installing Analytic Solver

Fill out the registration form.

Check your e-mail account to confirm your registration.

Then proceed to re-log into http://analyticsolver.com

Once successfully logged in you can either download the Analytic Solver for Excel(Windows Desktop version) or you can hit 'Get Started' which allows you to use our cloud version spreadsheet. This cloud version allows full access to the Analytic Solver functionality as it would in Excel.

From here you can perform Optimization, Monte-Carlo Simulation and Data-mining functionality then save your work into an Excel file format onto your machine or the cloud.

Here is an installation guide to install Analytic Solver for Desktop Excel:

Summary

Analytic Solver Platform might display, "Checking dependencies" for a significant amount of time when a model with many constraints and bounds is opened. During this time, Solver is separating the constraints from the bounds in order to display them correctly and obtain correct counts on the Task Pane Model Tab.

Solution

If your model contains many constraints and bounds, then it's possible that the Analytic Solver Platform status bar displays "Checking dependencies" for a considerable amount of time while the Solver categorizes the constraints and bounds on the Task Pane Model Tab. Setting Scan for Bounds to False under Advanced on the Task Pane Platform Tab effectively turns this feature off which will result in a faster model load. Doing so will cause the Solver to assume all bound entries are constraints which will result in erroneous Constraint and Bound counts (found at the bottom of the Task Pane Model Tab). However, there will be no impact during the solving process or on the solution found.

If, for some reason, the correct number of bounds and constraints is needed, the model can be diagnosed by clicking the down arrow under Optimize (on the Risk Solver Platform ribbon) and selecting Analyze without Solving from the menu. Solver will diagnose the model and correctly display the number of constraints and bounds.

Is it possible to easily run a single optimization problem that spans many worksheets of the same workbook...if yes, how?

Yes, with any of our advanced Solvers, you can do this, and it should "just work" -- many of our users build models in this form. Each worksheet can define an optimization model; it is the location of the objective cell that determines which model is associated with a worksheet. The decision variables and the constraints can be references to cells on other worksheets. There is a modest performance "hit" when you spread a model across multiple worksheets, but this is usually not enough to worry about. Sometimes users build worksheets that include elements of their optimization model, and tens of thousands of other formulas whose values are unaffected by the decision variables -- this can slow things down and is not recommended, but it is permitted.

With the basic Solver that comes with Excel, I'm a little less sure, but I believe you would need to put all of the decision variables ("changing cells") on the "home" worksheet that includes the objective cell. Constraint and objective formulas can refer to cells on other worksheets, but the model ultimately depends on the decision variables, whose values are adjusted during the optimization.

Using PSI Functions for Optimization will not necessarily put the model into "auto-resolve" mode. That is controlled by the option "Use Interactive Optimization", just below the option "Use Psi Functions to Define Model on Worksheet." The PSI functions are just an alternative way to define your model.

These PSI functions are described in the Frontline Solver Reference Guide in the chapter "PSI Function Reference" starting on PDF page 159. The PSI Functions for Optimization are described in the first section. An easy way to see the PSI functions for an existing model that you've defined interactively is to use the Save Model function:

With the worksheet containing a Solver model active, select a cell in an empty area, go to the Ribbon Tools group, click the Load/Save button and select Save Model. In the dialog that appears, switch the Format dropdown from Classic to Psi Functions, and click OK. Now inspect the cells written to the worksheet.

Note that you can define the model - variables, constraints, objective -- and Solver options interactively through the GUI, on the worksheet using PSI functions, or programmatically in VBA. And these three methods are "interoperable." For many users, it is easy to first define the model interactively and save the workbook, then go into VBA and instantiate a Problem object with:

Dim prob As New RSP.Problem

At this point, the Problem.Variables collection is defined and includes a Variable object for each contiguous range of decision variable cells that you defined interactively, and similarly for the constraints and objective.

Q: Using ASP to solve a non-linear problem I got excellent results and an "optimum" solution. Decision variables range from 0-700 and constraints and one set of constraints is in the 60,000 range. However, when the range of those constraints is increased by a factor of ten, ASP comes up with an answer that is clearly not optimum. My results are graphed in such a manner that it is easy to see a less than optimum solution. Furthermore, Solver states it has a "converged" solution rather than an optimum solution. I am using scaling, but the solution is still clearly worse than the original case. When I reduced the constraint values back the original levels, the model ran perfectly and gave consistently accurate results. I cannot understand how just changing the constraints by a factor of 10 can have such a huge impact on the results.

Help indicates changing the "converge" value might help. I tried this but there was no change. Are there some recommended "options" values which can help?

A: I'm assuming you are using the LSGRG Nonlinear Solver, since you're seeing "Solver converged to a solution" and you're adjusting the Convergence tolerance. (One option would be to try the LSSQP Solver or KNITRO Solver, but let's stick with the built-in Solver engines for the moment.) There are two topics to consider here - the way Automatic Scaling works inside the Solver engine, and the way scaling can affect objective and constraint values in your worksheet formulas, before the Solver engine ever sees them.

When you set the "Use Automatic Scaling" option to True, the LSGRG Solver uses "typical value scaling". Essentially it takes the values of the objective and constraint functions at the initial values of the decision variables (what you have on the spreadsheet when the optimization starts), and internally divides the objective and constraint values it works with, on future iterations, by these initial values. If the initial values are very different in magnitude from the subsequent values on each iteration, and the final values as the Solver approaches the optimal solution, this scaling will lose its effectiveness, and the Kuhn-Tucker local optimality conditions (that would give you the message "Solver found a solution") won't be satisfied. So the take-away is to use initial values for the decision variables that make the objective and constraints as close in magnitude as you can to the expected final solution.

This all happens inside the Solver engine. But it is possible for scaling to affect your Excel model itself. To take an extreme example, if A1: = 1E+10 and A2: =1E-10. A3: =A1+A2 will be 1E+10, and A4: =A3=A1 will be TRUE -- that is, the value of A2 will "disappear". This is called a "loss of significance" in numerical computation. This example involves 20 decimal places; IEEE floating-point numbers are able to represent approximately 15 decimal digits (52 bits). If you have a large model and it is difficult to find the cells or formulas where there can be a loss of significance, you can sometimes find them (not always, this is an approximate test) by selecting the Scaling Report (Reports - Optimization Reports - Scaling) when the LSGRG Solver has stopped.

I hope this is helpful.

Daniel+Fylstra [email protected] Frontline Systems Inc. 775-831-0300 x120

To uninstall our Analytic Solver Platform:

Step 1: Close Excel.

Step 2: Go to your 'Control Panel' then access the sub-menu of 'Programs' which is 'Uninstall a Program'.

When your Program list is done populating, locate:

Frontline Excel Solvers

Step 3: Select this item and at the top menu should be an 'Uninstall' option.

Finish with the dialog menus to uninstall.

The Analytic Solver Platform should now be removed.

Hello,

We would simply like permission to use the following link in our course for lecture and discussion:

http://www.solver.com/optimization-examples.htm

Please advise if we have your permission to direct students to these links during course lectures/discussion.

Thank you[from a university]

This warrants a little explanation, and I recommend that you forward this message to the instructor(s) who may present a lecture or lead a discussion where this link is used. You do have our permission to use the link http://www.solver.com/optimization-examples.htm in your course(s) for lecture and discussion. But this may have consequences that would make it better to give a different link to students.

The page http://www.solver.com/optimization-examples.htm describes a number of examples of optimization models. If all you want to do is mention these as possible applications of optimization, and move on to other topics, a link to this page is sufficient. But the page also invites visitors to register on Solver.com (by completing the form at the bottom) in order to gain access to the actual optimization models downloadable as Excel workbooks, which can be solved using the Solver built-in Excel (that we created for Microsoft).

If students register on this page, they can download the Excel workbooks, use them with the Solver in Excel, and they don't have to do anything else. But they will receive a follow-up email inviting them to download a 15-day free trial of our advanced Solvers for Excel. The form also asks them to choose a "role" (Student, Commercial User, Consultant, Government/Military, etc.) -- and what emails they receive next depends on this; experience shows that students often choose something other than "Student" -- in that case they'll receive additional emails and follow-up (even a Sales contact) on the assumption they they are a commercial prospect.

I mention this because we have another program that is designed specifically for students, which is often better for them. But if students register on http://www.solver.com/optimization-examples.htm or other pages of Solver.com, they won't get the main benefit of this program (a longer license) and -- especially if they choose a role other than Student, they will get follow-up contact that some students may not appreciate (though students who are also working at companies with industrial applications may find it helpful).

What I've just described happens a lot, consuming our time and the students' time, precisely because instructors don't make arrangements with us and send their students to the correct entry point for the program designed for students, which is http://www.solver.com/student ; instead they *send them* to some other page on Solver.com (like http://www.solver.com/optimization-examples.htm ).

The special version of our software offered under the student program is described at http://www.solver.com/analytic-solver-platform-education-simplifies-teaching-and-saves-classroom-time. We offer course-length licenses in conjunction with specific textbooks that are written around our software, and low cost licenses for enrolled students who aren't using these textbooks, or who need to use the software over a longer period. To use this program, instructors must make advance arrangements with us, to obtain a Course Code that they can give to their students. To register at http://www.solver.com/student, students must enter a valid Textbook Code and Course Code, to obtain the free semester-long licenses..

If you are getting an error similar to "Risk Solver Engine Not Loading#Name Errors" please give the steps below a try:

Step 1: Open a blank workbook/worksheet of Excel.

Step 2: Click on the top left File tab then Options on the left bottom menu bar.

A new Excel Options box should come up.

Step 3: On the left menu click Add-ins, then at the bottom where it says Manage: Excel Addins

Click 'Go'.

Step 4: Check to see if Analytic Solver Platform is in there and checked. If it isn't, click 'Browse'.

Step 5: Then browse in windows explorer to C:\Program Files (x86)\Frontline Systems\Analytic Solver Platform\Bin

Below where it allows you to view the File Type select All Files.

Click on the RSPAddin.xll file and then open.

Step 6: Make sure the Analytic Solver Platform is now in there and checked then click OK button.

Step 7: Re-run a Simulation and see if the #NAME cells remain.

If they #NAME is still there, then make absolute sure you have associated uncertain function(or PsiOutput functions) that use each distribution you have set for the simulation to work correctly. Either that or run a simulation and simply double click on the #NAME cells to see if they at least show a graph.

Our Analytic Solver is a larger Addon which can slow the initial opening of Excel depending on how fast your computer is in regards the processor speed (such as dual core processors or laptops). This can also have any effect if your workbook has a lot of sheets in which the solver refresh speed has an impact and overall Excel use is slower.

Solutions

Excel is slow to open OR Excel is slower in general (especially when using a lot of sheets)

If generally opening Excel is taking to long, you can choose to disable the Analytic Solver for when you need it. To do this you can disable (Analytic Solver/XLMiner/Data-mining) when youd simply like to use Excel with out the addon for faster speeds. Then vice-versa when youd like to perform Solves/Data-mining with the addon enabled.

To enable/disable the addon you can do this:

Enable the Developer tab in Excel so you can access COM Addins option under the Developer tab within Excel.

https://support.office.com/en-us/article/Show-the-Developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45

To enable the Developer tab in Excel, please see the link below for a walkthrough.

Summary

The message, "A document with the name 'SOLVER.XLA' is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that's currently open, or rename one of the documents."might appear upon opening of Excel 2003 while Risk Solver Platform is installed. It is triggered when Excel attempts to open both the standard Excel Solver's Solver.xla and Risk Solver Platform's Solver.xla. (This was done in the earliest version of Premium Solver Platform so users could run their macros immediately without having to perform any additional steps.) This article gives the reason why this message is appearing and two different methods for resolving the error.

Solution

The solution to this problem is to prevent Excel from attempting to open both files (standard Solver's Solver.xla or Risk Solver Platform's Solver.xla) at the same time either by disabling the Standard Solver or by not allowing the Risk Solver Platform's Solver.xla to open. The user can decide which file not to open to meet his/her specific needs.

Each time Excel is opened, an OPEN statement inside the Registry notifies Excel to open the standard Excel Solver's Solver.xla and places Solver on the Tools menu. If this OPEN statement is removed, this Solver.xla will not be opened and the error will not be displayed. To remove this OPEN statement, simply remove Solver from the Excel Addins menu by clicking Tools > Addins and uncheck Solver. Solver will be removed from the Tools menu. Now close and reopen Excel to confirm that the error message is not displayed. To use the standard Solver, you must add the Solver back to the Tools menu by clicking Tools > Addins and check the Solver Addin. Note: You will need to remember to uncheck Solver in the Addin list before Excel is closed. Otherwise, you will receive the message above upon re-opening of Excel.

If checking/unchecking Solver each time Excel is opened/closed seems tedious, please read on for a possible alternative option.

In Risk Solver Platform, Solver.xla is only necessary (and it only is used) for controlling Solver through VBA from an Excel macro using the Legacy API Solver functions, such as SolverSolve, SolverOK, etc. If no such macro exists, then the option Load Vx.0 VBA Macros (Tools > Premium Solver > Model > Options tab) can be unchecked, where "Vx.0" refers to the version of Risk Solver Platform currently in use. If this option is unchecked, Risk Solver Platform will not open its own Solver.xla file and the error will not be displayed. Warning: If Load Vx.0 VBA Macros is unchecked, you will not be able to run Risk Solver Platform through VBA using the Legacy API functions such as SolverSolve, SolverOK, etc. However, you will be able to call the Solver through VBA by using the new Object-Oriented API functions, which can do everything that the Legacy API functions can do, and much more.

In later versions of Excel, the standard Excel Solver's file has been renamed to Solver.xlam. As a result, this message will never appear in Excel 2007 or Excel 2010.

Summary

This article illustrates how to use a callback function when controlling the standard Excel Solver programmatically through VBA. The callback function, which you write in VBA, allows you to get control and check conditions during the solution process.

If Show Iteration Results is checked in the Solver Options dialog, your callback function will be called on each iteration taking the place of the regular Show Trial Solution message box so you can track the progress of the solve (e.g. check the objective function or constraint values in the Excel worksheet). Your callback function will also be called each time the ESC key is pressed, and each time a limit on the solution process is reached (such as the Max Time Limit or Max Iteration Limit).

Note: Risk Solver Platform can also be called programmatically through VBA, either using our Traditional API (explained here) or a more powerful object oriented API. Please see the chapter, "VBA Object Model Reference", in the Risk Solver Platform Reference Guide under the "Evaluator Object" topic for more information.

Solution

When controlling the Solver through VBA, a user-defined function can be called whenever the Solver would normally pause the solution process, for example, on each iteration if "Show Iteration Results" is checked in the Solver Options dialog (Data > Solver > Options in Excel 2007/2010 or Tools > Solver > Options in Excel 2003); when the ESC key is pressed; or each time a limit on the solution process is reached. As with the Show Trial Solution dialog, users can choose to continue on with the solution process or stop the Solver immediately. Note: This function must not alter the formulas of the objective or the constraints or the values in the variable cells

To use VBA functions such as SolverSolve, your Visual Basic module must include a reference to the Solver add-in (Solver.xla). In Microsoft Excel, open the Solver Parameters dialog (Data > Solver > Options in Excel 2007/2010 or Tools > Solver > Options in Excel 2003), to ensure that standard Excel Solver add-in is loaded. Then (after closing the Solver dialog) press Alt + F11 to open the Visual Basic Editor, choose Tools > References... and make sure that the box next to Solver is checked.

SolverSolve ShowRef Argument

Here is an example of defining a callback function, and passing its name to the SolverSolve function via the argument ShowRef.

VBA Syntax

Function ShowTrial(Reason As Integer) Msgbox Reason ShowTrial = FalseEnd Function

Sub Test

answer = SolverSolve(UserFinish:=True, ShowRef:="ShowTrial)End Sub

The argument Reason, which must be present, is an integer value from 1 to 5:

Function called (on every iteration) because the Show Iteration Results box in the Solver Options dialog was checked, or function called because the user pressed ESC to interrupt the Solver.

Function called because the Max Time limit in the Solver Options dialog was exceeded.

Function called because the Max Iterations limit in the Solver Options dialog was exceeded.

Function called because the Max Subproblems limit was exceeded.

Function called because the Max Integer Sols limit was exceeded.

Your callback function must return True if Solver should stop (same as the Stop button in the Show Trial Solution dialog) or False if it should continue running (same as the Continue button).

Your VBA function can inspect the current solution values on the worksheet, or take other actions such as saving or charting the intermediate values. However, it should not alter the values in the variable cells, or alter the formulas in the objective and constraint cells, as this could adversely affect the solution process.

SolverSolve UserFinish Argument

SolverSolve also takes an argument UserFinish you can use to control what happens at the end of the solution process. When the solution process ends and the UserFinish argument is True, no final dialog is displayed. If UserFinish is False, the Solver Results dialog is displayed.

If a Solver problem has not been completely defined, SolverSolve returns the #N/A error value. Otherwise the solution process is started (and if the ShowRef argument is supplied, your callback function will be called). When the solution process ends, SolverSolve returns an integer value that indicates the stopping condition. The standard Excel Solver returns values from 0 to 13. See below for descriptions of each. (When SolverSolve is used to call Risk Solver Platform, additional integer values may be returned.)

Value

Stopping Condition

0

Solver found a solution. All constraints and optimality conditions are satisfied.

1

Solver has converged to the current solution. All constraints are satisfied.

2

Solver cannot improve the current solution. All constraints are satisfied.

3

Stop chosen when the maximum iteration limit was reached.

4

The Set Cell values do not converge.

5

Solver could not find a feasible solution.

6

Solver stopped at user's request.

7

The linearity conditions required by this Solver engine are not satisfied.

8

The problem is too large for Solver to handle.

9

Solver encountered an error value in a target or constraint cell.

10

Stop chosen when the maximum time limit has been reached.

11

There is not enough memory available to solve the problem.

13

Error in model. Please verify that all cells and constraints are valid.

To all our users: Software evolves, and old versions cannot be supported forever. Windows XP was released more than 12 years ago, and Office 2003 was released more than 10 years ago.

Because of all the infrastructure involved (Windows APIs, COM and .NET, inability to use the Ribbon and Task Pane in Excel 2003), it is becoming ever harder to build a single application that will run on Windows 7 and 8 and also Windows XP, or a single add-in that will work in Excel 2013 and also Excel 2003. We have to focus on the future rather than the past.

Microsoft is officially ending support for Windows XP and Office 2003 on April 8, 2014. Frontline will also officially end support for Excel 2003 on that date.

If you are still using Windows XP and/or Office 2003, it is definitely time to upgrade.

In September 2012 weintroduced Version 12.0 of Frontline Solvers -- exploiting new technology in CPUs, GPUs and cloud servers, and making truly advanced optimization and simulation analytics even more accessible to non-specialists.

Works with Excel 2013

Frontlines Solvers V12.0 work with Excel 2013, Excel 2010 and Excel 2007, and even with Excel 2003.Of course,they're upward compatible from the Solver included in Excel, which Frontline developed for Microsoft. There are nearly 50 new built-in computational functions in Excel 2013 -- and our PSI Interpreter has been upgraded to support all of these functions.

Evolutionary Solver: Genetic Algorithms and Tabu/Scatter Search

We've made the biggest enhancements ever in our Evolutionary Solver in V12.0. It now includes both genetic algorithm based methods, and tabu search and scatter search methods. You can choose either set of methods via a new Task Pane option, and find out which methods work best on your model. Both methods also take advantage of classical linear, smooth nonlinear, and derivative-free optimization methods for local search.

Note: In V12.0 for a newly created model, the Evolutionary Solver's default choice under "Global Search" is Scatter Search; change this choice to Genetic Algorithm if you want to use the GA-based methods.

Exploiting Massively Parallel GPUs for Even Faster Solutions

The Evolutionary Solver was one of the first tools to exploit multi-core CPUs in 2009. Now the Evolutionary Solver is again first to exploit massively parallel GPUs (graphics processing units such as Nvidia GeForce and AMD Radeon devices), for even faster performance in its local search algorithms. You may already have one of these devices in your desktop or laptop PC.

New Decomposition Method Solves Largest-Ever Stochastic Linear Programs

Since its 2008 debut, Risk Solver Platform has featured uniquely powerful methods for solving linear programming problems with uncertainty (usually called stochastic LPs). Besides automatically formulating and solving the so-called deterministic equivalent problem for a stochastic LP, Risk Solver Platform offered the first-ever commercial implementation of robust optimization methods for these problems, handling both chance constraints and recourse decision variables.

Now in V12.0, Risk Solver Platform goes even further to offer the stochastic decomposition algorithm, first created by Dr. Suvrajeet Sen and Dr. Julia Higle, as a third highly scalable approach for solving stochastic LPs. Stochastic decomposition has been used to solve the largest examples of stochastic LPs ever solved, on any platform. Risk Solver Platform V12.0 now brings this power, in easy-to-use form, to the typical Excel user.

Visualizing Solutions for Uncertain Objectives and Recourse Constraints

Risk Solver Platform has a unique ability to solve stochastic optimization models with recourse decision variables -- models that accurately describe many real-world plans and decisions, but that cannot even be defined in many competitive products, let alone solved. Visualizing the solution to such a model has been a challenge, since recourse decisions have many different optimal values, corresponding to different realizations of uncertainty.

In V11.5, Risk Solver Platform introduced built-in charts and statistics for recourse decision variables. In V12.0, we've extended this capability to include built-in charts and statistics for uncertain objectives and recourse constraints (constraints that depend on recourse decision variables). Most other software for stochastic optimization offers little to support the need for visualization -- but Risk Solver Platform V12.0 offers comprehensive visualization support.

All Frontline Solvers V12.0 can Solve on Desktops or Cloud-Based Servers

The biggest change in V12.0 is the ability to easily solve your optimization or simulation model, either on your own desktop PC, or on a server run by your company or by Frontline Systems. Why solve your model on a remote server? You might be able to use a more powerful machine, or make use of a large-scale Solver Engine license that is available on the server -- it offers you more flexibility.

Risk Solver Platform, Premium Solver Platform, Risk Solver Pro and Premium Solver Pro now include a client that can talk to our Solver Server, which is part of Solkver SDK Platform V12.0. This is very easy to use -- you simply select "Run on a Solver Server" from the dropdown menu below the Optimize and Simulate buttons on the Ribbon. Your Excel workbook model is automatically uploaded to the server, and solved by an appropriate Solver Engine on the server. The solution is downloaded to your PC and automatically updates your workbook in Excel.

Perhaps even more interesting is the ability to define a model "on the fly" in JavaScript, inside a web browser or a mobile phone, solve that model "over the wire" with Solver Server, and display the solution to the browser or mobile phone user.

Version 11.5 introduces a wide range of enhancements in our Platform and Solver Engine products, designed to help you build models more quickly, solve them more quickly, and obtain better solutions.

New Distribution Wizard

For most users, the main challenge when building a simulation model is how to choose the right probability distribution and the right parameters for each uncertain variable. The new Distribution Wizard in V11.5 can help! Try it by choosing Distributions Distribution Wizard from the Ribbon, and simply following the prompts.

New Constraint Wizard

The main challenge when building an optimization model is specifying constraints. The new Constraint Wizard in V11.5 makes this easier for normal constraints, chance constraints, and restrictions such as integer on decision variables. Try it by choosing Constraints Constraint Wizard from the Ribbon, and following the prompts.

Improved Guided Mode Dialogs

Guided Mode can give you great insight into why your model is difficult or easy to solve but when youre in a hurry, its easy to overlook its messages. Weve re-worded nearly all of the Guided Mode dialogs, so you can determine at a glance what the analysis means for your model. Try it leave Guided Mode on for a while.

More Example Models, Easier to Find

Weve expanded the set of example models installed with Risk Solver Platform, and made it much easier to find the examples most relevant to you. Try it choose Help Examples from the Ribbon, and select examples by their summary descriptions, special features, and industry or functional area.

Faster Solutions for Large Quadratic and Nonlinear Models

Weve speeded up the PSI Interpreter, when its analyzing your model type, and when its being used by a Solver Engine to compute rate-of-change and curvature information to guide the Solver to an optimal solution. For large quadratic and nonlinear models, the speedup can be quite dramatic!

Next Generation Methods in Evolutionary Solver

To help you solve arbitrary Excel models with IF, CHOOSE, LOOKUP and similar functions, weve greatly enhanced the algorithmic methods in our popular Evolutionary Solver, so it finds high-quality solutions faster on many models, and much faster on some models. Try it - select the Evolutionary Solver engine on the Task Pane Engine tab, then set the Legacy Mode option to True (to use the older algorithms) or False (to use the newer algorithms) and see which works better for your particular model.

Visualization of Solution Values for Recourse Decision Variables

Risk Solver Platform is unique in its ability to solve stochastic optimization models with recourse decision variables models that accurately describe many real-world plans and decisions, but that cannot even be defined in competitive products, let alone solved. When such a model is solved, each recourse decision variable has many solutions, corresponding to different realizations of uncertainty. In V11.5, recourse decision variables have built-in charts and statistics, much like uncertain variables giving you greater insight into the optimal solution. Try it -- just double-click on a recourse decision variable cell after solving your model.

New Decision Tree Capabilities in Risk Solver Pro

Risk Solver Pro now includes decision tree capabilities formerly only available in Risk Solver Platform. Decision trees can be used alone to model risky, high-stakes decisions, and you can easily apply sensitivity analysis and simulation to decision tree models, to gain much greater insight into the decisions and outcomes you are modeling. Click on Help Examples and then click on the Decision Tree Examples button to see for yourself how useful decision trees can be.

Faster and More Flexible Distribution Fitting

Weve made distribution fitting more flexible, with a new PsiFit() function that fits distributions dynamically when your model changes. Weve also speeded up distribution fitting dramatically when you're fitting a large number of sample points.

Large Scale Solver Engines Feature Best-Ever Performance

Several of our plug-in large-scale Solver Engines have major enhancements in V11.5 notably the KNITRO Solver, Large-Scale SQP Solver (on non-smooth models), Gurobi Solver, and XPRESS Solver. If you have a large model or one thats difficult to solve, be sure to download, install, and try the V11.5 plug-in Solver Engines. If you check Automatically Select Engine at the top of the Task Pane Engine tab and then solve your model, you can see quickly in the Output tab which Solver engine was identified as best for your model.

In these phases, the Platform is analyzing your Excel model, and extracting a linear, quadratic and/or mixed-integer programming problem from it. For example, the LP coefficients often are not explicit in the spreadsheet, they have to be computed as gradients of the objective and the constraints, through a process called algorithmic differentiation. If you were using a custom modeling language rather than Excel, the modeling language processor would have to do similar work, and this can easily take longer than the actual solution process.

It is possible to write your Excel model in a way that minimizes the setup time, but the tradeoff is that you have to be careful and do more work to explicitly put all the coefficients in cells, and use only certain functions such as SUM and SUMPRODUCT. This is what we call "Fast Problem Setup". This is described in the Frontline Solvers User Guide, in the last chapter "Building Large-Scale Models" and section "Organizing Your Model for Fast Solution".

Finally, you could bypass this setup time if you were to rewrite your model in a programming language (C++, C#, Java, etc.) and use our Solver SDK Platform product. However you have to do more work to write in a programming language, and you give up much of the flexibility and interactivity of Excel.

Originally from ticket #47701.

I have recently installed Excel 2013 and have reinstalled Analytic Solver Platform. In cells where I have "=PsiNormal(5%,10%)" formulas I now see the message #NAME? instead of a random variable. When I run the simulations I see the same message.

----------

As a quick check, open the Function Wizard in Excel (click the fx icon), then open the dropdown list of function categories. If you don't see PSI Distribution, PSI Statistics, etc. in the list, this means the add-in functions are not enabled.

On the File tab, click Options, then in the left column of the dialog box, click Add-Ins. You should see both 'Analytic Solver Platform' Excel Add-In and "Analytic Solver Platform Addin' COM Add-In in the list. The PSI functions are defined by the Excel Add-In.

At the bottom of the dialog, you should see, or be able to select from the dropdown list, Manage Excel Add-Ins -- then click the Go button. This opens another dialog with a list of add-ins. You should see Analytic Solver Platform in the list -- make sure the box is checked next to it (click the box to check or uncheck). Then click the OK button.

Now go back to the Home tab. If the #NAMEs don't disappear immediately, just close and reopen the workbook. You should see a numeric sample value instead of #NAME.

This is a bug in Excel (that we cannot fix or change) that can occur when you have both a password-protected VBA add-in, such as the Solver add-in, and a COM add-in, such as Google Desktop or our Risk Solver Platform / Analytic Solver Platform add-in. See for example http://www.excelforum.com/excel-programming-vba-macros/749838-vba-password-prompt-after-excel-closes.html.

When you see the Solver password prompt, if you press and hold the ESC key to dismiss the dialog (over and over) it will go away after a few seconds.

There are web pages such as http://www.add-ins.com/password_problem.htm that recommend that you go into the Registry and change the COM add-in's LoadBehavior entry from 3 to 2, and this can make the Password prompt go away but it also means that the COM add-in will not be loaded when you start Excel. So this is not a recommended fix.