Error 1004 setting xValues repeatedly

[Martin Harmelin]

Have bumped into a problem using VBA to set xValues in bubble charts in
volume (Excel 2003 SP2 in WinXP). Have a query sheet attached to an Analysis
Services cube. From that I generate 13 bubble charts with approx 10 data
series in each. The user can rerun the query for different sets of data and
regenerate the charts. Works OK the first couple of times but eventually it
crashes with error 1004 unable to set xValues. It was stopping at different
places when I had different amounts of source data, so it was not
data-related. I was originally deleting the series and creating new ones each
time, so I tried just replacing the existing series values - made no
difference. I eventually stripped it back to a loop just resetting the
xvalues over and over again from the same source. Eventually it always
crashes. Then I stripped out everything else. All I have left is a single set
of data with one chart and one data series, no external queries or anything
else. Set up the following loop:

Dim ii As Integer
Dim jj As Integer
Dim Ser As Series
Set Ser = Worksheets("Sheet2").ChartObjects(1).Chart.SeriesCollection(1)
For jj = 1 To 500
For ii = 1 To 25000
Ser.XValues = Worksheets("Sheet1").Range("D2")
Next ii
Next jj

and eventually it falls over (after a lot more iterations this time with all
the other memory freed up).

Does anyone know of a workaround? Advice would be much appreciated.

Cheers