Solver Reference to Add-in

[Chad]

I have an add-in that contains a time series forecasting routine in VBA that
requires the use of the Solver add-in. I want to use VBA to establish a
reference to the Solver add-in for the forecasting add-in. I have attempted
the following with limited success. Any suggestions would be much
appreciated.

Thanks.

Private Sub Workbook_Open()

'Install the custom menu
Call ThisWorkbook.CreateMenu

'Check to make sure that Solver add-in is installed
Call ThisWorkbook.CheckSolver

'Check the version of Excel and warn the user if an older version.
Call ThisWorkbook.CheckVersion

'Establish a reference to solver if not already established.
'Creates an error 400 if already installed
On Error Resume Next
With ThisWorkbook.VBProject.References
.Remove .Item("SOLVER")
.AddFromFile Application.LibraryPath & "\SOLVER\SOLVER.xla"
End With

With AddIns("Solver Add-In")
.Installed = False
.Installed = True
ThisWorkbook.VBProject.References.AddFromFile _
Application.LibraryPath & "\SOLVER\SOLVER.xla"
End With
On Error GoTo 0

End Sub

Sub CheckSolver()
Dim a
'Subroutine checks for solver when Excel is opened
Set a = AddIns("Solver Add-In")
If a.Installed = False Then
MsgBox "The Solver add-in is not installed!" & vbCr & _
"Some functionality may be lost."
End If
End Sub

 

[Jim Cone]

Why not let the user take care of it...

Sub MakeThemDoIt()
Dim strName As String
strName = "SOLVER.XLA"
If IsItOpen(strName) = False Then
Application.Cursor = xlDefault
MsgBox "The SOLVER.XLA add-in is required." & vbCr & _
"Go to the Tools menu and select Add-Ins. " & vbCr & _
"Checkmark... Solver Add-In", vbInformation, "Chad Said So"
End If
End Sub
'--
Function IsItOpen(ByRef strName As String) As Boolean
On Error Resume Next
Dim WB As Excel.Workbook
Set WB = Excel.Workbooks(strName)
IsItOpen = (Err.Number = 0)
Set WB = Nothing
End Function
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Chad" <[email protected]>
wrote in message
I have an add-in that contains a time series forecasting routine in VBA that
requires the use of the Solver add-in. I want to use VBA to establish a
reference to the Solver add-in for the forecasting add-in. I have attempted
the following with limited success. Any suggestions would be much
appreciated.
Thanks.

Private Sub Workbook_Open()
'Install the custom menu
Call ThisWorkbook.CreateMenu

'Check to make sure that Solver add-in is installed
Call ThisWorkbook.CheckSolver

'Check the version of Excel and warn the user if an older version.
Call ThisWorkbook.CheckVersion

'Establish a reference to solver if not already established.
'Creates an error 400 if already installed
On Error Resume Next
With ThisWorkbook.VBProject.References
.Remove .Item("SOLVER")
.AddFromFile Application.LibraryPath & "\SOLVER\SOLVER.xla"
End With

With AddIns("Solver Add-In")
.Installed = False
.Installed = True
ThisWorkbook.VBProject.References.AddFromFile _
Application.LibraryPath & "\SOLVER\SOLVER.xla"
End With
On Error GoTo 0

End Sub

Sub CheckSolver()
Dim a
'Subroutine checks for solver when Excel is opened
Set a = AddIns("Solver Add-In")
If a.Installed = False Then
MsgBox "The Solver add-in is not installed!" & vbCr & _
"Some functionality may be lost."
End If
End Sub

 

[Chad]

Makes sense. Thanks Jim.

Chad