Cannot access Solver

Are you certain you have added a reference to Solver in VBA? See here: https://peltiertech.com/Excel/SolverVBA.html

By chance. Do you have any other add-in installed? It would help if you list your 3rd party add-ins.

Some 3rd party add-ins been known to cause this error.
If that's the case, you need to first uninstall the 3rd party add-in. Turn off Solver, and add it back in. And then install the 3rd party add-in.
And see if that fixes. If not, you may need to remove the 3rd party add-in that may be in conflict.

I expect that code opens the Solver add-in, but it doesn't set a reference to the Solver add-in. See this from the linked tutorial:

Originally Posted by Peltier

To set a reference to an add-in, it must first be installed. Then on the VB Editor's Tools menu, select References. This lists all open workbooks and installed add-ins, as well as a huge list of resources installed on the host computer. Find the add-in in the list, and check the box in front of its name.

In another place, Peltier talks about needing to set a reference to Solver in each workbook containing the Solver code. Is there one workbook invoking Solver, or is the Solver invoking code spread over multiple workbooks? If spread over multiple workbooks, does each workbook (each instance of the VBA code) have an active reference to Solver?

I did not find and VBA code in the file, and this file does not have a reference to Solver. I see no Solver model stored. It's possible that this is because I am using an older version of Excel (but that usually doesn't block all code from loading). We might need someone with a newer version of Excel to check and see if they can confirm what I am seeing.

@MrShorty

It's possible that this is because I am using an older version of Excel

Nah. I can confirm that it is same on my machine (MS365, 64bit. Win10).

I suspect issue lies somewhere in local environment and/or something else that is interfering with the add-in.

I see the code in the "sheet1" sheet module. I notice that Peltier stores this code in a regular module (not a sheet module). I don't know if it makes any difference, but I know that some procedures (UDF procedures, for example) work best from a regular module rather than a sheet module.

When I open Trial2, there is no reference to Solver. I'm not sure why Excel is not saving the reference with the file (others verify???).

You did not include your actual Solver procedure that invokes Solver, so I cannot test it. My expectation is that, if you set the reference to Solver, your debugged code should work fine, but I cannot test it.

I looked at your problem. Is this an accurate representation of your real problem? I notice that this problem is relatively easy to solve algebraically, which means that Solver is not even needed for this problem. If this is not representative and you're certain that Solver is needed for the real problem, then we can continue. If this is representative of the real problem, I would spend a few minutes with the algebra of the problem, solve the problem for J2 (rate2), and plug that formula into Excel, and abandon Solver.

The UDF function in file Trial2.xlsm just checks if the add-in solver is installed in Excel as solver are not added by default but it does not set a reference to solver in VBA. A must if solver should be run using a macro.

You need a macro like this:

Sub Macro1()
ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Office16\Library\SOLVER\SOLVER.XLAM"
End Sub

But before you run it you must allow access to Visual Basic Projects:

Start Microsoft Excel.
Open a workbook.
Click File and then Options.
In the navigation pane, select Trust Center.
Click Trust Center Settings....
In the navigation pane, select Macro Settings.
Ensure that Trust access to the VBA project object model is checked.
Click OK.

As for you model you must tell solver what cell or cells it can change to reach the target value (Max value) of cell O2 and changing the value of this cell(s) must have an impact on the O2 value.

Alf

I apologize. I had the impression that the main concern was not being able to access Solver from VBA. If you are also unable to call Solver manually, that suggests to me some problem in your Office installation -- Solver is not installed to the correct location or something else that prevents Excel from finding the Solver add-in even though it is installed.

I know it gets cliche, but the only thing I can think to do is to repair or reinstall Office. I've never had trouble accessing Solver manually.

One could change the cell manually, but the non-Solver solution I proposed was an algebraic solution that should yield and exact solution (well, as exact as double precision floating point arithmetic can get when programmed into Excel). If you are having trouble with the algebra, we should be able to help with that (again, assuming the sample calculation you provided is truly representative of your actual calculation).

Thanks for feedback and rep

Additionally, I found that the code must be inserted in every worksheet within a workbook, in order for it to work in the entire workbook

Depends a bit on the solver model. If it is the same for every sheet you could loop through all sheets

Sub LoopSheet()
Dim i as Integer

For i = 1 To Sheets.Count

Sheets(i).Activate
solver macro added here
Next
End Sub

As solver always run from the Active Sheet you loop through the workbook and activate each sheet run solver on the active sheet and then activate the next sheet as i goes from 1 to 2 and so forth.

PS I don't particularly care for Excel 2019 either. I got it to have something newer, but don't want to pay for 365. What are your reasons?

I'm used to the Office version of Excel. Done 2003 as well as 2007 and was rather happy with 2010. Still struggling with excel 2019 but felt the need to upgraded because number of user for excel 2010 are quit small I think.

Alf