Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|