Straight Line is not straight

[klm]

The following macro is supposed to generate a rotating straight line, but it
is not.
Please note that "circular reference" FunctionKey(F9) is used here.
Can you explain why and how to fix it, if you can, please.


Sub RotatingLine()
' I want to creat a rotating line by changing the slope in cell(G4) with
incremental change in cell(G6).
' But the line is not straight.
With Application
.Iteration = True
.MaxIterations = 1
.MaxChange = 0.001
End With
Range("E3") = "x"
Range("F3") = "y"
Range("G3") = "m"
Range("H3") = "b"
Range("E4") = "12"
Range("F4") = "40"
Range("G4") = "=G4+G6"
Range("H4") = "=F4-E4*G4"
Range("G5") = "incr m chng"
Range("G6") = "-15"
Range("A1") = 1
Range("A1:A20").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
Range("B1") = "=A1*$G$4+$H$4"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B20"), Type:=xlFillDefault
Range("A1:B20").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B20"),
PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.HasLegend = False
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = -1000
.MaximumScale = 1000
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub

 

[JE McGimpsey]

XL is calculating by rows, so B1:B4 are calculated using the value in G4
when F9 is pressed.

After B4, G4 is recalculated (decremented by 15, in this case), and
B5:B20 are then calculated using that value of G4.

If Iterations weren't set, when G4's value changed, that would trigger
XL to recalculate all its dependents (including B1:B4). But with
iteration set to 1, that recalculation doesn't occur.

The simplest change would be to move your data range to A5:B24

 

[klm]

Hi JE,
Great explanation,
Thank you very much.
note: I can relocate G4:H4 to another sheet if data range is not movable.