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?

 

[CharlieMode]

I'm talking about this code:

Function CheckSolverIntl() As Boolean
'' Adjusted for Application.Run() to avoid Reference problems with Solver
'' Adjusted for international versions of Excel
'' Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
'' Returns True if Solver can be used, False if not.


Dim bSolverInstalled As Boolean
Dim bAddInFound As Boolean
Dim iAddIn As Long
Const sAddIn As String = "solver.xla"


'' Assume true unless otherwise
CheckSolverIntl = True


On Error Resume Next
' check whether Solver is installed
bSolverInstalled = IsInstalled(sAddIn)
Err.Clear


If bSolverInstalled Then
' uninstall temporarily
bAddInFound = AddInInstall(sAddIn, False)
' check whether Solver is installed (should be false)
bSolverInstalled = IsInstalled(sAddIn)
End If


If Not bSolverInstalled Then
' (re)install Solver
bAddInFound = AddInInstall(sAddIn, True)
' check whether Solver is installed (should be true)
bSolverInstalled = IsInstalled(sAddIn)
End If


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


If CheckSolverIntl Then
' initialize Solver
Application.Run "Solver.xla!Solver.Solver2.Auto_open"
End If


On Error GoTo 0


End Function


Function IsInstalled(sAddInFileName As String) As Boolean
Dim iAddIn As Long

IsInstalled = False

For iAddIn = 1 To Application.AddIns.Count
With Application.AddIns(iAddIn)
If LCase$(.Name) = LCase$(sAddInFileName) Then
If .Installed Then
IsInstalled = True
End If
Exit For
End If
End With
Next

End Function


Function AddInInstall(sAddInFileName As String, bInstall As Boolean) As Boolean
Dim iAddIn As Long

For iAddIn = 1 To Application.AddIns.Count
With Application.AddIns(iAddIn)
If LCase$(.Name) = LCase$(sAddInFileName) Then
If .Installed <> bInstall Then
.Installed = bInstall
End If
AddInInstall = True ' True = add-in is listed
Exit For
End If
End With
Next

End Function