VBA question!?
 

Hi everyone,

I have this piece of vba code:

Option Explicit
Option Base 1

Dim Budget(1) As Single

Sub Sensitivity()
Dim cell As Range, Mult As Variant, ModelCounter As Integer, _
IncludeConstraint As Boolean
Application.ScreenUpdating = False
SaveOriginalValues
ModelCounter = 0
For Each cell In Range("Multiples")

ModelCounter = ModelCounter + 1
Mult = cell.Value
If IsNumeric(Mult) = True Then IncludeConstraint = True
ChangeModel Mult
RunSolver IncludeConstraint
StoreResults ModelCounter
Next
RestoreOriginalValues
End Sub

Sub SaveOriginalValues()
Dim i As Integer
For i = 1 To 1
Budget(i) = Range("Budget").Cells(i)
Next
End Sub

Sub ChangeModel(Mult As Variant)
Dim i As Integer
If IsNumeric(Mult) = True Then
For i = 1 To 1
Range("Budget").Cells(i) = Mult * Budget(i)
' MsgBox Mult * Budget(i)
Next
End If
End Sub

Sub RunSolver(IncludeConstraint As Boolean)
SolverReset
SolverOk SetCell:=Range("TotProj"), MaxMinVal:=1,
ByChange:=Range("Picked")
SolverAdd CellRef:=Range("Spending"), Relation:=1,
FormulaText:="Budget"

If IncludeConstraint = True Then _
SolverAdd CellRef:=Range("Picked"), Relation:=5
', FormulaText:="Budget"
SolverOptions AssumeLinear:=True, AssumeNonNeg:=True
SolverSolve UserFinish:=True
End Sub

Sub StoreResults(ModelCounter As Integer)
Dim i As Integer
With Range("G2")
For i = 1 To 30
.Offset(i, ModelCounter) = Range("Picked").Cells(i)

Next
' .Offset(i, ModelCounter) = Range("TotProj")
End With
End Sub

Sub RestoreOriginalValues()
Dim i As Integer
For i = 1 To 1
Range("Budget").Cells(i) = Budget(i)
Next
RunSolver True
End Sub


NOW, the results (either 1 or 0) should come out like this:

1 1 0 1
0 1 1 0
1 0 0 1

and so on. Instead I get things like thise:

1
0 1 0 1
1 1 1 0

and so on.

As you see in the first row, I get after the 1st value nothing but
blank. That means somehow my counter is NOT positioned correctly!?

Any help is appreciated....

Thanks,
Mike

VBA question!?
 

you don't give the start values nor do you describe what is is the code is
supposed to do. I don't understand why you'd create an array for one value,
nor a loop for i = 1 to 1 ? Doesn't make sense. I don't see any 2 dim
arrays, so Its pretty hard to see how you'd get any output at all.



"Mike" wrote:

Hi everyone,

I have this piece of vba code:

Option Explicit
Option Base 1

Dim Budget(1) As Single

Sub Sensitivity()
Dim cell As Range, Mult As Variant, ModelCounter As Integer, _
IncludeConstraint As Boolean
Application.ScreenUpdating = False
SaveOriginalValues
ModelCounter = 0
For Each cell In Range("Multiples")

ModelCounter = ModelCounter + 1
Mult = cell.Value
If IsNumeric(Mult) = True Then IncludeConstraint = True
ChangeModel Mult
RunSolver IncludeConstraint
StoreResults ModelCounter
Next
RestoreOriginalValues
End Sub

Sub SaveOriginalValues()
Dim i As Integer
For i = 1 To 1
Budget(i) = Range("Budget").Cells(i)
Next
End Sub

Sub ChangeModel(Mult As Variant)
Dim i As Integer
If IsNumeric(Mult) = True Then
For i = 1 To 1
Range("Budget").Cells(i) = Mult * Budget(i)
' MsgBox Mult * Budget(i)
Next
End If
End Sub

Sub RunSolver(IncludeConstraint As Boolean)
SolverReset
SolverOk SetCell:=Range("TotProj"), MaxMinVal:=1,
ByChange:=Range("Picked")
SolverAdd CellRef:=Range("Spending"), Relation:=1,
FormulaText:="Budget"

If IncludeConstraint = True Then _
SolverAdd CellRef:=Range("Picked"), Relation:=5
', FormulaText:="Budget"
SolverOptions AssumeLinear:=True, AssumeNonNeg:=True
SolverSolve UserFinish:=True
End Sub

Sub StoreResults(ModelCounter As Integer)
Dim i As Integer
With Range("G2")
For i = 1 To 30
.Offset(i, ModelCounter) = Range("Picked").Cells(i)

Next
' .Offset(i, ModelCounter) = Range("TotProj")
End With
End Sub

Sub RestoreOriginalValues()
Dim i As Integer
For i = 1 To 1
Range("Budget").Cells(i) = Budget(i)
Next
RunSolver True
End Sub


NOW, the results (either 1 or 0) should come out like this:

1 1 0 1
0 1 1 0
1 0 0 1

and so on. Instead I get things like thise:

1
0 1 0 1
1 1 1 0

and so on.

As you see in the first row, I get after the 1st value nothing but
blank. That means somehow my counter is NOT positioned correctly!?

Any help is appreciated....

Thanks,
Mike

VBA question!?
 

Pat,

This is a vba code for using excel optimizer. I agree it is confusing.
However, I removed the un-ncessary pieces. The 1st sub is the driver.
The "StoreResults" sub is the one that is writing results. The whole
code runs an excel optimizer known as "SOLVER" which tries to find x
values and f objective function value. It does several runs of course
and writes down x values of each run in one column. If you an email
address, I can email you the whole code to see?


Dim Budget(1) As Single

Sub Sensitivity()
Dim cell As Range, Mult As Variant, ModelCounter As Integer, _
IncludeConstraint As Boolean
Application.ScreenUpdating = False
SaveOriginalValues
ModelCounter = 0
For Each cell In Range("Multiples")

ModelCounter = ModelCounter + 1
Mult = cell.Value
If IsNumeric(Mult) = True Then IncludeConstraint = True
ChangeModel Mult
RunSolver IncludeConstraint
StoreResults ModelCounter
Next
RestoreOriginalValues
End Sub

Sub StoreResults(ModelCounter As Integer)
Dim i As Integer
With Range("G2")
For i = 1 To 30
.Offset(i, ModelCounter) = Range("Picked").Cells(i)


Next

End With
End Sub


Sub RestoreOriginalValues()
Dim i As Integer
For i = 1 To 1
Range("Budget").Cells(i) = Budget(i)
Next
RunSolver True
End Sub

VBA question!?
 

Pat,

Forgot to say that you can email me at

Then, I can email you the whole code with the application?

Thanks,
Mike