Tools > References...

[Gary''s Student]

I distributed a macro that uses Solver. I supplied instructions to the users
to make sure the Solver checkbox in Tools > References... was checked before
trying the macro.

A significant number of people called saying the macro did not work (meaning
they ignored the instructions).

Is there any way for the macro to switch on the reference itself??

 

[Norman Jones]

Hi Gary,

See Dana de Louis's code posted
by Tom Ogilvy:

How to run Solver from Visual Basic?
http://tinyurl.com/6fvkaz

 

[Dave Peterson]

From a Rob Bovey post:

The Application.Run approach is the one I would suggest. It doesn't
require a reference to the Solver add-in, but it does require that the
Solver add-in be open in Excel and that the calls to Solver procedure names
be fully qualified, e.g.:

Application.Run "Solver.xla!SolverSolve", False

Because Solver is a demand-loaded add-in, you have to do something a
little strange to make sure it's actually open in the user's instance of
Excel. Run the following two lines of code prior to calling any Solver
procedures:

Application.AddIns("Solver Add-in").Installed = False
Application.AddIns("Solver Add-in").Installed = True

The reason for this is that if the user already has Solver selected under
Tools/Add-ins when they open Excel, Excel will consider the add-in loaded
even though Solver.xla doesn't actually open until you select its menu.
Explicitly unloading it and then reloading it in VBA forces Solver.xla to
open no matter what the user's initial settings were.

 

[RB Smissaert]

Try this one. Note that this doesn't need a reference to the VBE
Extensibility.

Sub SolverInstall()

Dim oWB As Workbook
Dim strSolverPath As String

On Error Resume Next

Set oWB = ActiveWorkbook

strSolverPath = Application.LibraryPath & "\SOLVER\SOLVER.XLA"

'to load the .xla
With AddIns("Solver Add-In")
.Installed = False
.Installed = True
End With

'to set the reference
oWB.VBProject.References.AddFromFile strSolverPath

End Sub


RBS

 

[Gary''s Student]

Thanks!
--
Gary''s Student - gsnu200789

Try this one. Note that this doesn't need a reference to the VBE
Extensibility.

Sub SolverInstall()

Dim oWB As Workbook
Dim strSolverPath As String

On Error Resume Next

Set oWB = ActiveWorkbook

strSolverPath = Application.LibraryPath & "\SOLVER\SOLVER.XLA"

'to load the .xla
With AddIns("Solver Add-In")
.Installed = False
.Installed = True
End With

'to set the reference
oWB.VBProject.References.AddFromFile strSolverPath

End Sub


RBS

 

[Gary''s Student]

Thank you
--
Gary''s Student - gsnu200789

Hi Gary,

See Dana de Louis's code posted
by Tom Ogilvy:

How to run Solver from Visual Basic?
http://tinyurl.com/6fvkaz

 

[Gary''s Student]

Thanks
--
Gary''s Student - gsnu200789

From a Rob Bovey post:

The Application.Run approach is the one I would suggest. It doesn't
require a reference to the Solver add-in, but it does require that the
Solver add-in be open in Excel and that the calls to Solver procedure names
be fully qualified, e.g.:

Application.Run "Solver.xla!SolverSolve", False

Because Solver is a demand-loaded add-in, you have to do something a
little strange to make sure it's actually open in the user's instance of
Excel. Run the following two lines of code prior to calling any Solver
procedures:

Application.AddIns("Solver Add-in").Installed = False
Application.AddIns("Solver Add-in").Installed = True

The reason for this is that if the user already has Solver selected under
Tools/Add-ins when they open Excel, Excel will consider the add-in loaded
even though Solver.xla doesn't actually open until you select its menu.
Explicitly unloading it and then reloading it in VBA forces Solver.xla to
open no matter what the user's initial settings were.