Following on from my last post, I’ve noticed that all the “RefEdit” type controls are no longer present in OpenSolver forms, hence the “undefined variable” error messages.
Further information:
OpenSolver Version 2.9.4 (2021.01.15) running on 64-bit Windows 10.0 with VBA7 in 64-bit Excel 16.0
Microsoft 365
Thank you in advance for your help.
If all the RefEdit controls have gone, then it is probably because of a recent update to Excel in which ActiveX controls (including RefEdit) are now turned off by default. Microsoft make these changes occasionally. Please see https://opensolver.org/help/#refedit for more information on this. Because this is a new issue for us, we would love to know if you are able to get OpenSolver working. Thanks, Andrew
I’m new to OpenSolver and would appreciate some assistance. I work as an IT Administrator, and one of our customers has requested that OpenSolver be installed for multiple users.
I’m planning to deploy OpenSolver using a PowerShell script through MECM, our standard deployment tool.
My main concern is whether there is a way to automate the load behavior of OpenSolver. Ideally, we would like OpenSolver to be available automatically when users open Excel.
Is there a registry key or configuration file we can use to manage the load behavior of this Excel add-in?
Thanks for your query. A few comments.
1) Make sure you copy the folders over as well as the OpenSolver Excel file. Your users will need to be able to run the .exe files for OpenSolver to work. Some newer Mircosoft tools block Office Apps from running .exe files, so you will need to add exceptions if this is the case in your systems.
2) I personally do not set up OpenSolver to autoload. I find it easier to load it when I need it. I just pin it to my recent files within the Excel open dialog.
3) If you really want OpenSolver to autoload, then you either need to put it into a special folder (which the MS documentation should know about; it has changed over the years), or set a registry entry. The About OpenSolver dialog will let you set this registry entry (and you can look at the OpenSolver VBA code to find which registry setting it is; I don’t remember, sorry.)
Hope this helps,
Andrew
Hi, new OneSolver user here. I can download the zip file and run it but my current version of excel (office 365) won’t touch it since it contains macros. I use Windows 11. I have tried the obvious such as going to excel’s Trust Center and relaxing Mother Microsoft’s protections but excel still thinks the xlam file contains dangerous macros. Any help would be appreciated. Also, I am an independent researcher, I no longer have corporate overlords to blame:).
Did you mark the .zip file as safe before you extracted the files as detailed in the download instructions? It must be possible to open .xlsm files in every iteration of Excel; that is a core Excel capability. Andrew
In OpenSolver for Google Sheets, how can I set the constraint to be a sum total of the input variable cells, instead of for each cell? ie. I have cells A1:C1 = D1 in my constraints. D1 is 5. I want the sum of A1:C1 to be the constraint, not have each cell a max of 5 as it is currently doing.
I am using OpenSolver for my thesis. I have 45,000 variables. I have reduced the number of constraints to try and save solving time. The program quit after 7 days, are there recommendations to stop this from happening. Would changing the solver engine help with the solver time?
I would not use Excel for a problem with 45k variables. But, if you have no other choice, then delete everything off the sheet except the bare minimum needed to solve the problem. (And delete any other sheets.) You should also look at SolvetStudio or PuLP or GurobiPy. Cheers, Andrew
Following on from my last post, I’ve noticed that all the “RefEdit” type controls are no longer present in OpenSolver forms, hence the “undefined variable” error messages.
Further information:
OpenSolver Version 2.9.4 (2021.01.15) running on 64-bit Windows 10.0 with VBA7 in 64-bit Excel 16.0
Microsoft 365
Thank you in advance for your help.
If all the RefEdit controls have gone, then it is probably because of a recent update to Excel in which ActiveX controls (including RefEdit) are now turned off by default. Microsoft make these changes occasionally. Please see https://opensolver.org/help/#refedit for more information on this. Because this is a new issue for us, we would love to know if you are able to get OpenSolver working. Thanks, Andrew
It works!
Enabling ControlX in Excel’s security option solved the RefEdit problem.
Many thanks Andrew.
I’m new to OpenSolver and would appreciate some assistance. I work as an IT Administrator, and one of our customers has requested that OpenSolver be installed for multiple users.
I’m planning to deploy OpenSolver using a PowerShell script through MECM, our standard deployment tool.
My main concern is whether there is a way to automate the load behavior of OpenSolver. Ideally, we would like OpenSolver to be available automatically when users open Excel.
Is there a registry key or configuration file we can use to manage the load behavior of this Excel add-in?
I look forward to your guidance. Thank you!
Thanks for your query. A few comments.
1) Make sure you copy the folders over as well as the OpenSolver Excel file. Your users will need to be able to run the .exe files for OpenSolver to work. Some newer Mircosoft tools block Office Apps from running .exe files, so you will need to add exceptions if this is the case in your systems.
2) I personally do not set up OpenSolver to autoload. I find it easier to load it when I need it. I just pin it to my recent files within the Excel open dialog.
3) If you really want OpenSolver to autoload, then you either need to put it into a special folder (which the MS documentation should know about; it has changed over the years), or set a registry entry. The About OpenSolver dialog will let you set this registry entry (and you can look at the OpenSolver VBA code to find which registry setting it is; I don’t remember, sorry.)
Hope this helps,
Andrew
Hi, new OneSolver user here. I can download the zip file and run it but my current version of excel (office 365) won’t touch it since it contains macros. I use Windows 11. I have tried the obvious such as going to excel’s Trust Center and relaxing Mother Microsoft’s protections but excel still thinks the xlam file contains dangerous macros. Any help would be appreciated. Also, I am an independent researcher, I no longer have corporate overlords to blame:).
Thanks
Did you mark the .zip file as safe before you extracted the files as detailed in the download instructions? It must be possible to open .xlsm files in every iteration of Excel; that is a core Excel capability. Andrew
In OpenSolver for Google Sheets, how can I set the constraint to be a sum total of the input variable cells, instead of for each cell? ie. I have cells A1:C1 = D1 in my constraints. D1 is 5. I want the sum of A1:C1 to be the constraint, not have each cell a max of 5 as it is currently doing.
You need to create a cell containing SUM(A1:C1) and apply the limit of 5 to this using a constraint. Andrew
I am using OpenSolver for my thesis. I have 45,000 variables. I have reduced the number of constraints to try and save solving time. The program quit after 7 days, are there recommendations to stop this from happening. Would changing the solver engine help with the solver time?
I would not use Excel for a problem with 45k variables. But, if you have no other choice, then delete everything off the sheet except the bare minimum needed to solve the problem. (And delete any other sheets.) You should also look at SolvetStudio or PuLP or GurobiPy. Cheers, Andrew