missing: solver.xla

[Martin Quinn]

I have a set of XL2003 files that can be used to orthogonalize functions with respect to one another. These transformations are achieved using SOLVER. I now have Xl2003 and Xl2007 running on a PC and have discovered that SOLVER is not loaded automatically during file opening, because of path errors, with the result that under the References tab in the VBE I find an extra radio button 'Missing: Solver.xla' enabled. Manually this problem is overcome by opening SOLVER under the Data tab of the Ribbon and then, under the Reference tab of the VBE window, selecting the SOLVER button while deselecting 'Missing: Solver.xls'. I believe that the instruction:

ThisWorkbook.VBProject.References.AddFromFile Application.LibraryPath & "\solver\solver.xla"

allows for the SOLVER reference to be made programmatically [http://www.kelley.iu.edu/albrightbooks/Solver.htm]

but this can lead to error with SOLVER already installed and does not unselect the

'Missing: Solver.xla'

radio button under References. Is a full solution possible as a Workbook_Open instruction, which appears to be necessary to remove the problem?

 

[Martin Quinn]

I have found an old solution to this problem, which arises because of the limited portability of SOLVER at:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=272. Ken Puls suggests the addition of the subroutine to Module1
'
Sub References_RemoveMissing()
'Macro purpose: To remove missing references from the VBE

Dim theRef As Variant, i As Long

On Error Resume Next

For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i

If Err <> 0 Then
MsgBox "A missing reference has been encountered!" _
& "You will need to remove the reference manually.", _
vbCritical, "Unable To Remove Missing Reference"
End If

On Error Goto 0
End Sub
'
I am not sure of when the Error Handler might be activated and would be grateful for feedback on this aspect of the solution.

I have added to the Workbook_Open routine the extra instructions:
'
References_RemoveMissing
If Val(Application.Version) > 11 Then
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile Application.LibraryPath & "\solver\solver.xlam"
Else
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile Application.LibraryPath & "\solver\solver.xla"
End If
'
This solution can be checked using the Tools/References tab of the VBE. On opening the file initially the Missing: SOLVER.XLA message appears and code will not compile. After the changes are added to the VBA code the Missing: SOLVER.XLA message is removed and the correct SOLVER radio button is ticked as usual. The files can be saved in the VBE window using File/Save and need not be updated to *.xlsm files unless that is preferred

 

[Martin Quinn]

The solution appears to be a little more complicated than in my post above. Solver using EXCEL files, modified as described previously, appear to work fine when the files are opened in Xl2007 in compatibility mode, when the changes have been made using this version of the EXCEL. However, if an attempt is then made to open such a file in Xl2003 the message
"Solver an unexpected error occurred or available memory was exhausted" can result. This apparently arises because the macro reference pointer is not set correctly.

I find that it is necessary to add the extra instructions

Application.Run "solver.xla(m)!auto_open" to the Workbook_open code as follows

References_RemoveMissing
If Val(Application.Version) > 11 Then
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile Application.LibraryPath & "\solver\solver.xlam"
'*****************************
application.Run "solver.xlam!auto_open"
'*****************************
Else
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile Application.LibraryPath & "\solver\solver.xla"
'****************************
application.Run "solver.xla!auto_open"
'****************************
End If

With this change all my files operate correctly in either Xl2003 and Xl2007!