VBA automation of solver for multiple users

[jobstofmoravia]

Hi All,

I've been struggling for a few days to automate solver runs in a spreadsheet in a way that will let users other than myself use them correctly.

I have things working nicely on my own machine, but inevitably when the workbook gets emailed to somebody issues happen and it all falls apart.

The main issue seems to stem from not having a solver reference in VB (that which is found in tools/references in the VB editor). There are also side issues due to circular references existing in the workbook -- if I could get away with not having these I could use goalseek instead of solver, but it seems to crash and burn when it interacts with what it needs to do.

Does anyone have some code that will allow me to automatically add the reference for solver upon opening of the workbook, unhindered by the warning about circular references?

 

[shg]

Welcome to the board.

See http://www.ozgrid.com/forum/showthread.php?t=42486&page=1

 

[jobstofmoravia]

Thanks for the welcome.

So I tried the linked solution, changing the path to where my solver actually resides. I set up a simple test case and also changed the solver parameters to work with that. Upon running I was greeted by this error:

"Run-time error '1004':
Programmatic access to Visual Basic Project is not trusted".

 

[shg]

In Excel 2003, Tools > Macro > Security, Trusted Publishers, tick Trust access to Visual Basic Project.

This is a little more robust if there's already a reference:

Function AddSolverRef() As Boolean
    ' Requires a reference to
    ' Microsoft Visual Basic for Applications Extensibility
    Dim sFile       As String
    Dim oRef        As Reference
 
    sFile = LCase(Application.LibraryPath & _
            "\Solver\Solver.xla" & _
            IIf(Val(Application.Version) < 12, "", "m"))
 
    With ThisWorkbook.VBProject
        For Each oRef In .References
'           Debug.Print oRef.FullPath
            If LCase(oRef.FullPath) = sFile Then
                AddSolverRef = True
                Exit For
            End If
        Next oRef
 
        If Not AddSolverRef Then
            On Error Resume Next
            .References.AddFromFile sFile
            AddSolverRef = Err.Number = 0
            On Error GoTo 0
        End If
    End With
End Function

 

[shg]

See also http://peltiertech.com/Excel/SolverVBA.html for Jon Peltier's clever way to run Solver without a reference. It might be marginally slower, but would probably be convenient for the application you describe.

 

[jobstofmoravia]

Thanks shg, looks like the way I need to go.

If I'm understanding correctly, I first need to prepare solver for the first use, as shown under the heading "Preparing Solver for First Use" at the linked site. I'm currently trying to do this by adding this code block (which is Mr Peltier's code in a cut and paste format), to the Workbook Open section in ThisWorkbook.

'' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved.
Function CheckSolver() As Boolean
Dim bSolverInstalled As Boolean
CheckSolver = True
On Error Resume Next
bSolverInstalled = Application.AddIns("Solver Add-In").Installed
Err.Clear
If bSolverInstalled Then
    Application.AddIns("Solver Add-In").Installed = False
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If
If Not bSolverInstalled Then
    Application.AddIns("Solver Add-In").Installed = True
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If
If Not bSolverInstalled Then
    MsgBox "Solver not found. This workbook will not work.", vbCritical
    CheckSolver = False
End If
If CheckSolver Then
    Application.Run "Solver.xla!Solver.Solver2.Auto_open"
End If
On Error GoTo 0
End Function

The problem I'm getting, no doubt to my lack of knowledge of exactly how to do this, is that when I add the following to ThisWorkbook,

Private Sub Workbook_Open()

'' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved.
Function CheckSolver() As Boolean
Dim bSolverInstalled As Boolean
CheckSolver = True
On Error Resume Next
bSolverInstalled = Application.AddIns("Solver Add-In").Installed
Err.Clear
If bSolverInstalled Then
    Application.AddIns("Solver Add-In").Installed = False
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If
If Not bSolverInstalled Then
    Application.AddIns("Solver Add-In").Installed = True
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If
If Not bSolverInstalled Then
    MsgBox "Solver not found. This workbook will not work.", vbCritical
    CheckSolver = False
End If
If CheckSolver Then
    Application.Run "Solver.xla!Solver.Solver2.Auto_open"
End If
On Error GoTo 0
End Function

End Sub

the final "End sub" changes to "End Function" (so there's two in a row), then when it tries to run it fails with error "compile error: Expected End Sub". I get the same thing when I manually change the second "End Function" to "End Sub" -- It seems to dislike putting the code in between.

I'm obviously doing something wrong here -- can anyone enlighten me as to what it is? As far as I can see it's almost a straight cut and paste job that is required, but I'm stuffing that up!

 

[shg]

You can't have a procedure inside another procedure. Remove Jon's Function and End Function lines, and declare CheckSolver as a Boolean variable.

 

[jobstofmoravia]

Thank you shg! Everything works as intended now.

As a reference for any people searching for the same thing I was, here is the final code I used to prepare solver for it's first use:

Private Sub Workbook_Open()

'' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved.
Dim CheckSolver As Boolean
Dim bSolverInstalled As Boolean
CheckSolver = True
On Error Resume Next
bSolverInstalled = Application.AddIns("Solver Add-In").Installed
Err.Clear

If bSolverInstalled Then
    Application.AddIns("Solver Add-In").Installed = False
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If

If Not bSolverInstalled Then
    Application.AddIns("Solver Add-In").Installed = True
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If

If Not bSolverInstalled Then
    MsgBox "Solver not found. This workbook will not work.", vbCritical
    CheckSolver = False
End If

If CheckSolver Then
    Application.Run "Solver.xla!Solver.Solver2.Auto_open"
End If

On Error GoTo 0

End Sub

And I had to call solver in VBA without requiring a reference, as detailed On Mr. Peltier's site here.

Thanks again for me out shg.

 

[shg]

Good job!

 

[CharlieMode]

Hi guys,

I was just reading about how jobstofmoravia came up with a solution to his/her issue.

Jon Peltier also gives a more general approach about how to prepare solver for its first use (it is detailed in the same page you guys mentioned).

Does anyone know how to modify it so it can run it as a workbook open procedure?

Thanks in advance !