Straight Line is not straight
 

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 Whe=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

Straight Line is not straight
 

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


In article <TZcGi.4308$Z33.3633@trndny08, "klm" wrote:

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 Whe=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

Straight Line is not straight
 

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


"JE McGimpsey" wrote in message
...
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


In article <TZcGi.4308$Z33.3633@trndny08, "klm" wrote:

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 Whe=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