PDA

View Full Version : Solver in VBA- Project Dependencies



~!@#$%
04-27-2013, 02:54 AM
Hi Guys, i new to VBA but so far i like it and have learnt alot by viewing forums, but this has me stumped especially as i'm not good at arrays yet, i'm learning Project Management and trying to come up with my own wee Portfolio selector that i can use to find the optimum Portfolio of projects in solver,
i can use Solver's menu to do dependencies like has to be binary wher Xj=1 (do project j) Xj=0(don't do j)
and the dependencies Xj=1 (mandatory) , Xj >= Xi Project (j depends on project i) etc
ok in VBA
and Solver i have done Mandatory the easiest way i could figure out:
i set up solver and then


For Each b In Range("Dependencies").Rows
If b.Text = "Mandatory" Then
b.Cells.Select
b.Offset(0, -2).Select
temp = ActiveCell.Address
SolverAdd cellRef:=Range(temp), _
relation:=2, _
formulaText:="One"
End If
Next

it works well strangely enough i know i'ts not the best, but now i'm stuck and cant figure out multiple dependencies i've tried arrays but it won't let me store cell addresses in them , my ranges the user would choose the dependency type in dropdown then the projects it relies on which is a multi-pick dropdown.
Any ideas appreciated thanks for all the reading

SamT
04-27-2013, 02:24 PM
Sub test(Project As String)
Dim arDep() As Variant
For Each b In Range("Dependencies").Rows
If b.Text = Project Then
arDep(UBound(arDep)) = b.Offset(0, -2)
ReDim Preserve arDep(UBound(arDep) + 1)
End If
Next b
'I don't have Solver, so you'll have to play with this
SolverAdd cellRef:=arDep, _
relation:=2, _
formulaText:="One"

End Sub

~!@#$%
04-28-2013, 01:25 PM
:banghead:
Thanks ,thats what i tried to do in my head but seemed muddled up doing it, ill test it and post back
cheers again!