Using Variable array in Excel VBA charting

[hsPipe]

I am trying to plot the generated data (over 1000 x-y scatter points) from
the variable arrays directly rather than depositing the array to the spread
sheet and than plotting it. This is in the hope that it will reduce the time
required to plot the chart.
I need help on how to specify the series using arrays rather than cell
ranges.

Than you.

 

[Peter T]

Indeed you can make charts with no source data in cells, but with your 1000
x-y points it's not straightforward and would require quite a lot of work
(it entails writing the data to named "vertical" arrays). If the only reason
is to save time, writing data to 1000x2 cells is barely an eye-blink if you
do it in one go -

ReDim arr(1 To 1000, 1 To 2) As Double
populate the array with x-y values
Range("a1").Resize(UBound(arr), UBound(arr, 2)).Value = arr

Regards,
Peter T

 

[hsPipe]

Thanks for the response. Transferring original input to variable arrays,
process them and writing them back to the spread sheet using the method you
suggested is what I had done. It took about 10 seconds to process a
tremendous amount of data.
However, when I tred to plot the data in a series of graphs in an active
sheet, the process slows down to 70 seconds. I was wondering whether it it
the time consumed in getting the data from the spread sheet that slows down
the plotting. And hence the idea of plotting directly from the variable
arrays.
May be I have to accept the delay from 30 seconds to 70 seconds for the
graphs?

 

[Jon Peltier]

Are you using Excel 2007? It is rather slow about charting a series with
more than a trivial number of points. 2003 and earlier would do this
instantly.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Peter T]

I doubt getting data from cells is what's slowing things down. Following
makes a chart with 5 x 1000xy series (ie 10k data cells) pretty quickly,
even in Excel 2007 it took less than 0.5 sec (in an old system), and most of
that time was running the last line to reset screenupdating.

Option Explicit
Sub SpiralTest()
Dim xA As Double, yA As Double
Dim xK As Double, yK As Double
Dim pts As Long
Dim rad As Double, rdn As Double
Dim nS As Long
Dim arr() As Double
Dim rng As Range
Dim cht As Chart

On Error Resume Next
' << just for testing >>
ActiveSheet.ChartObjects.Delete
ActiveSheet.UsedRange.ClearContents
On Error GoTo 0

Application.ScreenUpdating = False

Set cht = ActiveSheet.ChartObjects.Add(10, 10, 300, 600).Chart
cht.ChartType = xlXYScatterSmoothNoMarkers
cht.HasLegend = False

rdn = Application.WorksheetFunction.Pi / 180
pts = 1080
rad = 1

ReDim arr(1 To pts, 1 To 2)

For nS = 1 To 5

If nS = 1 Then
xA = 0: yA = -40: xK = 3: yK = 6
ElseIf nS = 2 Then
xA = 0: yA = 0: xK = 6: yK = 6
ElseIf nS = 3 Then
xA = 0: yA = -20: xK = 6: yK = 4
ElseIf nS = 4 Then
xA = 0: yA = 20: xK = 6: yK = 18
ElseIf nS = 5 Then
xA = 0: yA = 40: xK = 6: yK = 30
End If

MakeSpiral rdn, rad, pts, xA, yA, xK, yK, arr

Set rng = ActiveSheet.Cells(1, nS * 2 - 1).Resize(pts, 2)
rng.Value = arr

With cht.SeriesCollection.NewSeries
If Val(Application.Version) >= 12 Then
.ChartType = xlXYScatterSmoothNoMarkers
.Border.Weight = xlThin
End If
.XValues = "=" & Application.ConvertFormula( _
rng.Columns(1).Address(external:=True), xlA1, xlR1C1)
.Values = "=" & Application.ConvertFormula( _
rng.Columns(2).Address(external:=True), xlA1, xlR1C1)
End With
Next

Application.ScreenUpdating = True

End Sub

Function MakeSpiral(rdn As Double, rad As Double, pts As Long, _
xA As Double, yA As Double, _
xK As Double, yK As Double, _
arr() As Double)
Dim i As Long
For i = 1 To pts
arr(i, 1) = xA + (Cos(i * xK * rdn) * rad * i * 0.01)
arr(i, 2) = yA + (Sin(i * yK * rdn) * rad * i * 0.01)
Next
End Function

Jon - so what's up with the the 2007 chart macro recorder. How to do a
simple little thing like format the line weight (or rather points width).

Regards,
Peter T

 

[Jon Peltier]

Jon - so what's up with the the 2007 chart macro recorder. How to do a
simple little thing like format the line weight (or rather points width).

They broke it. The macro recorder is totally busted for shapes, and pretty
much busted when it comes to formatting chart elements (which are shapes). I
think they didn't have time to integrate the new office shapes into the
macro recorder (and I suspect there are holes in the object model, but it
just might be that I haven't messed with it enough).

The solution is to keep Office 2003 installed somewhere. Recorded code from
previous versions works in 2007. It doesn't take full advantage of the new
shapes formatting options, but that's not necessarily a bad thing. If you
need the newer options, you'll have to rely on the Object Browser and
IntelliSense. The online help has examples, but they were so poorly
conceived that it's impossible to learn anything from them. Basically they
repeat syntax without showing how to apply anything meaningful.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Peter T]

Hi Jon,

which in previous versions the macro recorder would give
sr.Border.Weight = xlThin

you'll have to rely on the Object Browser and IntelliSense.

Border (for Series) has curiously become a "hidden member" in 2007

Ah, with an educated guess looks like this is it
sr.Format.Line.Weight

The macro recorder is totally busted for shapes, and pretty much busted
when it comes to formatting chart elements

That's progress for you!

Regards,
Peter T

 

[Jon Peltier]

Hi Jon,


which in previous versions the macro recorder would give
sr.Border.Weight = xlThin


Border (for Series) has curiously become a "hidden member" in 2007

Just means it's not the "official" way to do something, but it's not
completely deprecated. A lot of things we do in 97-2003 make use of hidden
leftovers from Excel 5.

Ah, with an educated guess looks like this is it
sr.Format.Line.Weight

Andy Pope has figured out much of the formatting OM. Check his web site, and
hunt for newsgroup responses he's authored.

That's progress for you!

That's spending all of your time and resources on partial development of an
untenable UI, and not having enough left to make real improvements
elsewhere.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______