Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WinXP: Solver: An unexpected internal error occurred ...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WinXP: Solver: An unexpected internal error occurred ...
Ken,
Whilst your app may require Solver, users may have valid reasons why they do not want to have it as an add-in. And you also uninstall it if it's not located where you think it should be. This whole routine would not be necessary if check for the add-in, if not present inform the user that it is required. Then leave it up to the user as to whether they wish to use your app or not. NickHK "Ken Dahlberg" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
solver an uxexpected internal error ocurred, or available memory | Excel Worksheet Functions | |||
Solver Error - an unexpected internal error has occurred | Excel Discussion (Misc queries) | |||
Solver Error - an unexpected internal error has occurred | Excel Discussion (Misc queries) | |||
Solver Error - an unexpected internal error has occurred | Excel Discussion (Misc queries) | |||
Solver Error - an unexpected internal error has occurred | Excel Discussion (Misc queries) |