Solver won't open

[Jerry W. Lewis]

Under what conditions will the solver dialog fail to open in Excel 2003
(11.8220.8132 SP2)? When I click on the (active) Solver menu entry, nothing
happens -- no solver dialog, no error dialog; nothing. The interface is not
frozen, since I can still navigate, enter formulas, etc.

My workbook has 2 populated sheets (~410 populated cells per sheet). 33
cells per sheet (column A) pull values from another workbook (not open and
links not updated) using the SMALL function. The remainder of the cells use
relatively uncomplicated non-array formulas, alhough most cells use a well
vetted VBA function from an .XLA within an IF function, and have conditional
formatting, of which one condition calls a local VBA function.

All formulas and conditional formatting appear to be working properly; The
inability to start Solver is the only indication of a problem. I have saved
the file and rebooted the system with no improvement. If I close that
workbook and open a new workbook in the same session then Solver will open.

Unless someone recognizes the situation, my next step will be to rebuild the
workbook from scratch to see if it was somehow corrupted.

Jerry

 

[Jerry W. Lewis]

The issue appears to be associated with the conditional format that calls a
VBA UDF. If I build the workbook from scratch, the problem starts when I add
that conditional format, and goes away when I delete it. In the original
workbook, I can get Solver to open on a sheet by deleting the conditional
format from that sheet, even though the conditional format remains on the
other sheet (and Solver still will not open on that other sheet).

That does define a workaround, since I can delete the conditional formats
from one sheet, work with solver there, then restore the conditional formats
from the other sheet. However this workaround seems very kludged, and I
remain uncomfortable at not understanding what is going on.

I was not able to reproduce the problem in a fresh workbook with a single
formula condition of =tryit(A1) in cell A1, where
Function tryit(x As Range) As Boolean
tryit = True
End Function

In the real workbook, each of cells B3:L35 has 3 formula conditions. The
first and third do not cause the problem. The second condition calls a VBA
function because the formula, while not too long to be a cell formula, is
apparently too long to be a conditional format formula (does anyone know the
exact restrictions?)

The 2nd format condition (for cell G9) is
=exchangeable(G9,$B9:$L9,$B$1)

which calls the VBA function

Function exchangeable(cell As Range, row As Range, alpha As Range) As Boolean
' test whether cell is exchangeable in the sense of Crow 1956 Biometrika
423-435
' cell is in row and row gives pmf_binomial(n,x,p) values for for 0<=x<=p
'
' formula for cell G9
'
=ISNA(MATCH(LARGE($B9:$K9,RANK(G9,$B9:$K9)-1),F9:H9,0))*(SUMPRODUCT(($B9:$K9<=G9)*$B9:$K9)<=$B$1)*(SUMPRODUCT(($B9:$K9<=G9)*$B9:$K9)+LARGE($B9:$K9,RANK(G9,$B9:$K9)-1)>$B$1)*(SUMPRODUCT(($B9:$K9<=G9)*$B9:$K9)+LARGE($B9:$K9,RANK(G9,$B9:$K9)-1)<=$B$1+G9)
' 1. the next larger cell is not adjacent (would give discontinuous
confidence set)
' ISNA(MATCH(LARGE($B9:$K9,RANK(G9,$B9:$K9)-1),F9:H9,0))
cnd1 = Evaluate("ISNA(MATCH(LARGE(" & row.Address & ",RANK(" &
cell.Address & "," & row.Address & ")-1)," & Range(cell.Offset(0, -1),
cell.Offset(0, 1)).Address & ",0))")
' 2. cell gives the largest probabilitiy in a Sterne test
'
(SUMPRODUCT(($B9:$K9<=G9)*$B9:$K9)<=$B$1)*(SUMPRODUCT(($B9:$K9<=G9)*$B9:$K9)+LARGE($B9:$K9,RANK(G9,$B9:$K9)-1)>$B$1)
cnd2 = Evaluate("(SUMPRODUCT((" & row.Address & "<=" & cell.Address &
")*" & row.Address & ")<=" & alpha.Address & ")*(SUMPRODUCT((" & row.Address
& "<=" & cell.Address & ")*" & row.Address & ")+LARGE(" & row.Address &
",RANK(" & cell.Address & "," & row.Address & ")-1)>" & alpha.Address & ")")
' 3. cell can be exchanged with the next larger value and still give an
alpha level test
'
(SUMPRODUCT(($B9:$K9<=G9)*$B9:$K9)+LARGE($B9:$K9,RANK(G9,$B9:$K9)-1)<=$B$1+G9)
cnd3 = Evaluate("(SUMPRODUCT((" & row.Address & "<=" & cell.Address &
")*" & row.Address & ")+LARGE(" & row.Address & ",RANK(" & cell.Address & ","
& row.Address & ")-1)<=" & alpha.Address & "+" & cell.Address & ")")
exchangeable = cnd1 * cnd2 * cnd3
End Function


Sorry about the long lines that will doubtless wrap. There are only 4
active lines in the body of the function
cnd1 = ...
cnd2 = ...
cnd3 = ...
and
exchangeable = cnd1 * cnd2 * cnd3

This function and the two non-offending native function formula conditions
all use SUMPRODUCT to effectively produce array formulas without array entry
in the conditional format formulas. That does not seem to be the issue,
since the other two do not cause the problem.

Jerry