VBA Excel - Solver in the loop returns error on the reference to changing cells and conditions

Question

I am trying to loop a solver command for VBA Excel, and getting the error message regarding the variables and conditions in the model. The idea of the model is to find a range of debt and equity financing so that the cash balance is zero, and the debt and equity components meet certain covenants (that work as bounds in the solver run).

Here is my code:

Sub Debt_Capital_Balancing()

  Application.ScreenUpdating = False

  Dim Early_Repmnt As String, CashBeforeSolver As Variant, CED As Variant, _
  DR As Variant, CC As Variant, TW As Single, NDE As Single, DE As Single, W As Single

  K = Range("Forecast_periods").Count
  Range("Debt_Received, Debt_Early_Repayment, RE_Distribution, _ CC_APIC_Change").ClearContents

  For i = 1 To K

    CashBeforeSolver = Abs(Range("Cash_Excess_Deficit").Cells(1, i).Value)
    CED = Range("Cash_Excess_Deficit").Cells(1, i).Value
    DR = Range("Debt_Received").Cells(1, i).Value
    CC = Range("CC_APIC_Change").Cells(1, i).Value
    TW = Range("Target_WACC").Cells(1, i).Value
    NDE = Range("Net_Debt_To_EBITDA").Cells(1, i).Value
    DE = Range("D_E").Cells(1, i).Value
    W = Range("WACC").Cells(1, i).Value

    SolverReset
    SolverOk SetCell:=CED, MaxMinVal:=3, ValueOf:=0, ByChange:="DR,CC", Engine:=3, EngineDesc:="Evolutionary"

    SolverAdd cellRef:=DR, Relation:=3, FormulaText:=0
    SolverAdd cellRef:=CC, Relation:=3, FormulaText:=0
    SolverAdd cellRef:=DR, Relation:=1, FormulaText:=CashBeforeSolver
    SolverAdd cellRef:=CC, Relation:=1, FormulaText:=CashBeforeSolver
    SolverAdd cellRef:=NDE, Relation:=1, FormulaText:="Target_Net_Debt_To_EBITDA"
    SolverAdd cellRef:=DE, Relation:=1, FormulaText:="Target_D_E_Ratio"
    SolverAdd cellRef:=W, Relation:=1, FormulaText:=TW

    SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.00001, _
    Convergence:=0.0001, StepThru:=False, Scaling:=True, AssumeNonNeg:=False, Derivatives:=1

    SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, _
    Multistart:=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
    IntTolerance:=0.1, SolveWithout:=False, MaxTimeNoImp:=200

    SolverSolve

  Next i

End Sub

I guess I incorrectly refer to the variables in the solver pattern. Can someone please help me with that? May be there are some other ways to refer to the changing cells and dynamic conditions?

Many thanks,

Yury

Single-Step through the code: 1. On What line does it error? 2: What is the error (as of Dy.Lee as well) — user2261597
– user2261597, Commented Aug 20, 2017 at 11:59

Yury G · Accepted Answer · 2017-08-24 17:43:19Z

Ok, I've found a solution, may be not optimal but it works. Here is the code:

Sub Debt_Capital_Balancing()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

Dim InitCashBalance As Variant
Dim InitCashBalance2 As Variant
Dim TargetCell As Variant
Dim DebtReceivedChangeCell As Variant
Dim DebtPaidChangeCell As Variant
Dim REChangeCell As Variant
Dim CapitalChangeCell As Variant
Dim DEConstr As Variant
Dim WACCConstr As Variant
Dim DtoEBITDAConstr As Variant
Dim TargWACC As Variant
Dim TargDE As Variant
Dim TargDtoEBITDA As Variant
Dim DebtcfConstr As Variant
Dim EquitycfConstr As Variant
Dim MinDE As Variant

Range("Debt_Received, Debt_Early_Repayment, Dividends, _
RE_Distribution, CC_APIC_Change").ClearContents

K = Range("Forecast_periods").Count

Set InitCashBalance = Range("Cash_Excess_Deficit").Cells(1, 1)
Set TargetCell = Range("Cash_Excess_Deficit").Cells(1, 1)
Set DebtReceivedChangeCell = Range("Debt_Received").Cells(1, 1)
Set DebtPaidChangeCell = Range("Debt_Early_Repayment").Cells(1, 1)
Set REChangeCell = Range("RE_Distribution").Cells(1, 1)
Set CapitalChangeCell = Range("CC_APIC_Change").Cells(1, 1)
Set DtoEBITDAConstr = Range("Net_Debt_To_EBITDA").Cells(1, 1)
Set DebtcfConstr = Range("Debt_cf").Cells(1, 1)
Set EquitycfConstr = Range("Equity_cf").Cells(1, 1)
Set TargDtoEBITDA = Range("Target_Net_Debt_To_EBITDA")

For i = 1 To K

InitCashBalance2 = Abs(InitCashBalance)

SolverReset
SolverOk SetCell:=TargetCell.Address, MaxMinVal:=3, ValueOf:=0, _
ByChange:=DebtReceivedChangeCell.Address & "," & CapitalChangeCell.Address,_
Engine:=3, EngineDesc:="Evolutionary"

SolverAdd cellRef:=DebtReceivedChangeCell.Address, Relation:=3, FormulaText:=0
SolverAdd cellRef:=CapitalChangeCell.Address, Relation:=3, FormulaText:=0
SolverAdd cellRef:=DebtReceivedChangeCell.Address, Relation:=1, FormulaText:=InitCashBalance2
SolverAdd cellRef:=CapitalChangeCell.Address, Relation:=1, FormulaText:=InitCashBalance2
SolverAdd cellRef:=DtoEBITDAConstr.Address, Relation:=1, _ FormulaText:=TargDtoEBITDA
SolverAdd cellRef:=DebtcfConstr.Address, Relation:=3, FormulaText:=0
SolverAdd cellRef:=EquitycfConstr.Address, Relation:=3, FormulaText:=0

SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.00001, _
Convergence:=0.0001, StepThru:=False, Scaling:=True, _
AssumeNonNeg:=False, Derivatives:=1

SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, _
Multistart:=False, RequireBounds:=True, MaxSubproblems:=0, _
MaxIntegerSols:=0, IntTolerance:=0.1, SolveWithout:=False, MaxTimeNoImp:=200

SolverSolve userFinish:=True 
SolverFinish KeepFinal:=1 

Set InitCashBalance = InitCashBalance.Offset(0, 1)
Set TargetCell = TargetCell.Offset(0, 1)
Set DebtReceivedChangeCell = DebtReceivedChangeCell.Offset(0, 1)
Set DebtPaidChangeCell = DebtPaidChangeCell.Offset(0, 1)
Set REChangeCell = REChangeCell.Offset(0, 1)
Set CapitalChangeCell = CapitalChangeCell.Offset(0, 1)
Set DtoEBITDAConstr = DtoEBITDAConstr.Offset(0, 1)
Set DebtcfConstr = DebtcfConstr.Offset(0, 1)
Set EquitycfConstr = EquitycfConstr.Offset(0, 1)

Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Collectives™ on Stack Overflow

VBA Excel - Solver in the loop returns error on the reference to changing cells and conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related