Rijnsent
Well-known Member
- Joined
- Oct 17, 2005
- Messages
- 1,483
- Office Version
- 365
- Platform
- Windows
Hi there,
I've got the following issue: I'm updating a chart on a worksheet. For this updating, I need to move the data shown in the chart 1 cel down (e.g. first it was C16:C25, next step would be C17:C26). The chart itself has 8 series in it (7 lines, 1 bar).
The macro I have takes the formula of the seriescollection (.formulaR1C1), replaces parts of that string (so the series moves 1 down) and puts it back into the .formulaR1C1. So far, so good, but the weird thing is that the code gets really slow:
While debugging I timed the macro, and it might take Excel 1.2 seconds to update the chart. That is: at random any of the series seems to take that long, the others go in 0.00 seconds... See the debugging below, I looped several times through the series for illustration purposes.
One time it's series 1, the next time series 6 that causes the slowdown. Anybody a clue what might be the cause of this (and how to speed it up)?
Thanks in advance,
Koen
I've got the following issue: I'm updating a chart on a worksheet. For this updating, I need to move the data shown in the chart 1 cel down (e.g. first it was C16:C25, next step would be C17:C26). The chart itself has 8 series in it (7 lines, 1 bar).
The macro I have takes the formula of the seriescollection (.formulaR1C1), replaces parts of that string (so the series moves 1 down) and puts it back into the .formulaR1C1. So far, so good, but the weird thing is that the code gets really slow:
While debugging I timed the macro, and it might take Excel 1.2 seconds to update the chart. That is: at random any of the series seems to take that long, the others go in 0.00 seconds... See the debugging below, I looped several times through the series for illustration purposes.
One time it's series 1, the next time series 6 that causes the slowdown. Anybody a clue what might be the cause of this (and how to speed it up)?
Thanks in advance,
Koen
Code:
Application.Calculation = xlCalculationManual
Set grafiek = Worksheets("Grafiek").ChartObjects("Chart1").Chart
T0 = Time()
For Tel = 1 To grafiek.SeriesCollection.Count
serierange = grafiek.SeriesCollection(Tel).FormulaR1C1
'some stuff to determine the rows, finally adding up to the new string that's supposed to go into the seriescollection(n).formulaR1C1:
serierange = ....
Debug.Print "Chrt-" & Tel & "-" & Time - T0
grafiek.SeriesCollection(Tel).FormulaR1C1 = serierange
Next Tel
Code:
Debug results:
Chrt-1-2,31
Chrt-2-2,31
Chrt-3-2,31
Chrt-4-2,31
Chrt-5-2,31
Chrt-6-3,47
Chrt-7-3,47
Chrt-8-3,47
Chrt-1-3,47
Chrt-2-3,47
Chrt-3-4,62
Chrt-4-4,62
Chrt-5-4,62
Chrt-6-4,62
Chrt-7-4,62
Chrt-8-4,62
Chrt-1-5,78
Chrt-2-5,78
Chrt-3-5,78
Chrt-4-5,78
Chrt-5-5,78
Chrt-6-5,78
Chrt-7-5,78