K
Ken Dahlberg
The complete message is "Solver: An unexpected internal error
occurred, or available memory was exhausted". Windows XP, Excel 2002.
I've searched the posts on this topic and haven't found a real answer.
I have a sub that invokes the Solver when a command button on a
worksheet is clicked. I know how to make this work. When I compile
the code, save the .xls file, exit and re-open, the call to Solver
works just fine.
Here is where the complication starts. There is more code that runs in
the Workbook_Open event that sets the VBA reference to Solver, so that
when it is installed on a different system, the reference will be set
automatically without the user having to intervene. This code also
works just fine.
But the problem is, for this code to have the desired effect on the
new machine, I want to store the workbook in a state where the Solver
reference is not already set. This means the code isn't compiled,
because the Solver calls won't compile without the reference being
set.
When I open the workbook from this state, the code starts compiling
and the Solver reference gets set correctly. Now I have two choices.
ONE, I can save, close, and reopen; then, if I click the Solver button
it works perfectly. TWO, I can go directly to the Solver button and
click it (without the save, close, and reopen); then, I get this
ridiculous message. What is this all about?????
In case it helps, I'll include below the code that is called from
Workbook_Open to set up the Solver. This code evolved through many
trials and it borrows from posts I found here.
Another quirk of Solver that plagues me is, when its reference is not
set correctly, a bunch of standard VBA function calls won't compile.
That is why the code below involving the "oname" variable doesn't
simply use the ucase function instead. Please don't quibble with the
oddities in the code - it works.
The only part that I don't know how to test is the last section that
is supposed to correct a "broken" reference. If I knew this would
work, then maybe I could store the thing in compiled form and bypass
the whole stupid problem with the "unexpected internal error".
Does this make sense to anyone? Is there a solution? Thanks......
Ken Dahlberg
Private Sub SolverLoad()
'// Adds Solver installation and reference
Dim wb As Workbook
Dim ai As AddIn
Dim ref As Variant
Dim solref As Variant
Dim NoSolver As Boolean, IsInstalled As Boolean
Dim lastError As Integer
Dim oname As String, SolverPath As String
'// First check to see if the SOLVER.XLA exists where it should be
SolverPath = Application.LibraryPath & "\SOLVER"
If Not FileExists(SolverPath, "SOLVER.XLA") Then
MsgBox "Please install SOLVER.XLA in " & SolverPath
Me.Close False
End If
'// Now check to see if the solver add-in is a member of AddIns
collection
NoSolver = True
For Each ai In AddIns
oname = ai.name
If oname = "SOLVER.XLA" Or oname = "Solver.xla" Or _
oname = "solver.xla" Or oname = "Solver.XLA" Then
NoSolver = False
Exit For
End If
Next ai
'// If SOLVER not member of AddIns, then add it.
'// We know SOLVER.XLA exists in correct location.
'// If AddIn has wrong pathname then uninstall it.
If NoSolver Then
Application.StatusBar = "Adding SOLVER to Add-ins collection"
AddIns.Add SolverPath & "\SOLVER.XLA"
ElseIf Not _
FileExists(AddIns("Solver Add-In").path, AddIns("Solver
Add-In").name) Then
Application.AddIns("Solver Add-In").Installed = False
End If
'// Be sure the Solver add-in file is OPEN
On Error Resume Next ' turn off error checking
Set wb = Workbooks("SOLVER.XLA")
lastError = Err
On Error GoTo 0 ' restore error checking
If lastError <> 0 Then
' The add-in workbook isn't currently open. Manually open it.
Application.StatusBar = "Opening SOLVER.XLA"
Set wb = Workbooks.Open(SolverPath & "\SOLVER.XLA")
End If
'// Set wb to the correct workbook before setting Installed property
to True
Set wb = Me
IsInstalled = Application.AddIns("Solver Add-In").Installed
If Not IsInstalled Then
Application.StatusBar = "Installing SOLVER.XLA"
Application.AddIns("Solver Add-In").Installed = True
End If
wb.Activate
'// Now take care of the VBA reference
NoSolver = True
With wb.VBProject
For Each ref In .References
oname = ref.name
If oname = "SOLVER" Or oname = "Solver" Or oname = "solver" Then
NoSolver = False
Set solref = ref
End If
Next ref
If NoSolver Then
Application.StatusBar = "Setting VBA reference for SOLVER.XLA"
.References.AddFromFile SolverPath & "\SOLVER.XLA"
ElseIf solref.IsBroken Then
Application.StatusBar = "Correcting broken VBA reference for
SOLVER.XLA"
.References.Remove Reference:=solref
.References.AddFromFile SolverPath & "\SOLVER.XLA"
End If
End With
Application.StatusBar = False
End Sub
occurred, or available memory was exhausted". Windows XP, Excel 2002.
I've searched the posts on this topic and haven't found a real answer.
I have a sub that invokes the Solver when a command button on a
worksheet is clicked. I know how to make this work. When I compile
the code, save the .xls file, exit and re-open, the call to Solver
works just fine.
Here is where the complication starts. There is more code that runs in
the Workbook_Open event that sets the VBA reference to Solver, so that
when it is installed on a different system, the reference will be set
automatically without the user having to intervene. This code also
works just fine.
But the problem is, for this code to have the desired effect on the
new machine, I want to store the workbook in a state where the Solver
reference is not already set. This means the code isn't compiled,
because the Solver calls won't compile without the reference being
set.
When I open the workbook from this state, the code starts compiling
and the Solver reference gets set correctly. Now I have two choices.
ONE, I can save, close, and reopen; then, if I click the Solver button
it works perfectly. TWO, I can go directly to the Solver button and
click it (without the save, close, and reopen); then, I get this
ridiculous message. What is this all about?????
In case it helps, I'll include below the code that is called from
Workbook_Open to set up the Solver. This code evolved through many
trials and it borrows from posts I found here.
Another quirk of Solver that plagues me is, when its reference is not
set correctly, a bunch of standard VBA function calls won't compile.
That is why the code below involving the "oname" variable doesn't
simply use the ucase function instead. Please don't quibble with the
oddities in the code - it works.
The only part that I don't know how to test is the last section that
is supposed to correct a "broken" reference. If I knew this would
work, then maybe I could store the thing in compiled form and bypass
the whole stupid problem with the "unexpected internal error".
Does this make sense to anyone? Is there a solution? Thanks......
Ken Dahlberg
Private Sub SolverLoad()
'// Adds Solver installation and reference
Dim wb As Workbook
Dim ai As AddIn
Dim ref As Variant
Dim solref As Variant
Dim NoSolver As Boolean, IsInstalled As Boolean
Dim lastError As Integer
Dim oname As String, SolverPath As String
'// First check to see if the SOLVER.XLA exists where it should be
SolverPath = Application.LibraryPath & "\SOLVER"
If Not FileExists(SolverPath, "SOLVER.XLA") Then
MsgBox "Please install SOLVER.XLA in " & SolverPath
Me.Close False
End If
'// Now check to see if the solver add-in is a member of AddIns
collection
NoSolver = True
For Each ai In AddIns
oname = ai.name
If oname = "SOLVER.XLA" Or oname = "Solver.xla" Or _
oname = "solver.xla" Or oname = "Solver.XLA" Then
NoSolver = False
Exit For
End If
Next ai
'// If SOLVER not member of AddIns, then add it.
'// We know SOLVER.XLA exists in correct location.
'// If AddIn has wrong pathname then uninstall it.
If NoSolver Then
Application.StatusBar = "Adding SOLVER to Add-ins collection"
AddIns.Add SolverPath & "\SOLVER.XLA"
ElseIf Not _
FileExists(AddIns("Solver Add-In").path, AddIns("Solver
Add-In").name) Then
Application.AddIns("Solver Add-In").Installed = False
End If
'// Be sure the Solver add-in file is OPEN
On Error Resume Next ' turn off error checking
Set wb = Workbooks("SOLVER.XLA")
lastError = Err
On Error GoTo 0 ' restore error checking
If lastError <> 0 Then
' The add-in workbook isn't currently open. Manually open it.
Application.StatusBar = "Opening SOLVER.XLA"
Set wb = Workbooks.Open(SolverPath & "\SOLVER.XLA")
End If
'// Set wb to the correct workbook before setting Installed property
to True
Set wb = Me
IsInstalled = Application.AddIns("Solver Add-In").Installed
If Not IsInstalled Then
Application.StatusBar = "Installing SOLVER.XLA"
Application.AddIns("Solver Add-In").Installed = True
End If
wb.Activate
'// Now take care of the VBA reference
NoSolver = True
With wb.VBProject
For Each ref In .References
oname = ref.name
If oname = "SOLVER" Or oname = "Solver" Or oname = "solver" Then
NoSolver = False
Set solref = ref
End If
Next ref
If NoSolver Then
Application.StatusBar = "Setting VBA reference for SOLVER.XLA"
.References.AddFromFile SolverPath & "\SOLVER.XLA"
ElseIf solref.IsBroken Then
Application.StatusBar = "Correcting broken VBA reference for
SOLVER.XLA"
.References.Remove Reference:=solref
.References.AddFromFile SolverPath & "\SOLVER.XLA"
End If
End With
Application.StatusBar = False
End Sub