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

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

VBA solver add-in function

Hi,

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"
.SolverSolve
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()

Create_New_Instance_of_Excel
Workbook_Open
runMe

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"
.SolverSolve

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

MsgBox "runMe end"

End Sub
0
mcgerks
Asked:
mcgerks
1 Solution
 
Brendt HessSenior DBACommented:
Try this version:

Dim vBuffer As Variant

vBuffer = .Application.Run("Solver.xla!
SolverOK", "$A$4", 3, 0, "$A$3")
0
 
cerebralpcCommented:
mcgerks,
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 ! --

Thanks,

cerebralpc
EE Cleanup Volunteer

http://www.experts-exchange.com/Q_20556582.html
0

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