I run a large VBA macro on Excel, consisting of a large Sub routine, containing a Do Loop statement, performing periodical calculations and updating a Chart at the end of every recalculation process.
When I call the following routine at the end of every recalculation, the chart updates to reflect the new Recalculated scenario:
Sub Chart_Chart1_Rebuild()
Sheets("Chart1").Select
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=Monitor!$" & Range("ask_h_Column") & "$" & Range("Engine_Top_Row") & ":$" & Range("ask_h_Column") & "$" & Range("Engine_Bottom_Row")
ActiveChart.SeriesCollection(2).Values = "=Monitor!$" & Range("bid_l_Column") & "$" & Range("Engine_Top_Row") & ":$" & Range("bid_l_Column") & "$" & Range("Engine_Bottom_Row")
ActiveChart.SeriesCollection(3).Values = "=Monitor!$" & Range("Level_Up_Column") & "$" & Range("Engine_Top_Row") & ":$" & Range("Level_Up_Column") & "$" & Range("Engine_Bottom_Row")
ActiveChart.SeriesCollection(4).Values = "=Monitor!$" & Range("Level_Dw_Column") & "$" & Range("Engine_Top_Row") & ":$" & Range("Level_Dw_Column") & "$" & Range("Engine_Bottom_Row")
ActiveChart.SeriesCollection(5).Values = "=Monitor!$" & Range("Pointer") & "$" & Range("Engine_Top_Row") & ":$" & Range("Pointer") & "$" & Range("Engine_Bottom_Row")
ActiveChart.SeriesCollection(1).XValues = "=Monitor!$" & Range("X_Axis") & "" & Range("Engine_Top_Row") & ":$" & Range("X_Axis") & "$" & Range("Engine_Bottom_Row")
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = Range("X_Axis_Min_Value_Trade") - 0.0001
ActiveChart.Axes(xlValue).MaximumScale = Range("X_Axis_Max_Value_Trade")
'Range("Show_Pointer").Select
'ActiveWindow.ScrollColumn = 1
End Sub
• This Sub routine works perfectly on a Windows 7 based computer.
• On a Widows 11 based computer, however, the same macro does not work, and the Chart does not refresh. The Chart refreshes only after the Sub containing the Do Loop statement has been stopped.