Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 998
  • Last Modified:

VBA solver add-in function


I am working on VBA code in MS Project and I'm trying to add a reference to the Solver add-in to an excel sheet without manually having to add it by going to tools referneces. Everyting seems to work ok, the sover reference is added to the VBA project in excel, but VBA doesn't like the line:

with objExcel
.SolverOk SetCell:="$A$4", MaxMinVal:=3, ValueOf:="0", ByChange:="$A$3"
end wtih

I get the error message: Run-time error '438'. Object doesn't support this property or method.

I was wondering if there is any way of calling the solverOK function through MS project using an excel sheet as an object.

Here is the code I have:

Option Explicit
Dim objExcel As Object

Sub test()


End Sub

Sub Create_New_Instance_of_Excel()

Set objExcel = CreateObject("Excel.sheet")
Exit Sub

End Sub

Sub Workbook_Open()

Dim SolverPath As String

objExcel.Application.Visible = True
objExcel.Application.Cells(1, 1).Value = "Opened"
MsgBox "Workbook Opened"

SolverPath = "C:\Program Files\Microsoft Office\Office\Library\Solver\Solver.xla"
objExcel.VBProject.References.AddFromFile SolverPath

objExcel.Application.Cells(1, 1).Value = "Solver Added"
MsgBox "Solver Added"

End Sub

Sub runMe()

MsgBox "runMe"

With objExcel
.SolverOk SetCell:="$A$4", MaxMinVal:=3, ValueOf:="0", ByChange:="$A$3"

.Application.Cells(1, 1).Value = "runMe"
End With

MsgBox "runMe end"

End Sub
1 Solution
Brendt HessSenior DBACommented:
Try this version:

Dim vBuffer As Variant

vBuffer = .Application.Run("Solver.xla!
SolverOK", "$A$4", 3, 0, "$A$3")
No comment has been added lately (141 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to bhess1 http:#8170046

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --


EE Cleanup Volunteer


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now