Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Variable array in Excel VBA charting
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. -- Ho-Shu |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Variable array in Excel VBA charting
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" wrote in message ... 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. -- Ho-Shu |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Variable array in Excel VBA charting
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? -- Ho-Shu "Peter T" wrote: 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" wrote in message ... 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. -- Ho-Shu |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Variable array in Excel VBA charting
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...00906ACNJ.html _______ "hsPipe" wrote in message ... 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? -- Ho-Shu "Peter T" wrote: 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" wrote in message ... 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. -- Ho-Shu |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Variable array in Excel VBA charting
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 "hsPipe" wrote in message ... 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? -- Ho-Shu "Peter T" wrote: 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" wrote in message ... 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. -- Ho-Shu |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Variable array in Excel VBA charting
"Peter T" <peter_t@discussions wrote in message ... 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...00906ACNJ.html _______ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Variable array in Excel VBA charting
Hi Jon,
How to do a simple little thing like format the line weight 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" wrote in message ... "Peter T" <peter_t@discussions wrote in message ... 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...00906ACNJ.html _______ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Variable array in Excel VBA charting
"Peter T" <peter_t@discussions wrote in message ... Hi Jon, How to do a simple little thing like format the line weight 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 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. The macro recorder is totally busted for shapes, and pretty much busted when it comes to formatting chart elements 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...00906ACNJ.html _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
charting variable line data | Excel Discussion (Misc queries) | |||
charting variable length line data | Charts and Charting in Excel | |||
Charting a variable range | Charts and Charting in Excel | |||
defining a variable-size worksheet area for charting | Excel Worksheet Functions | |||
How do I Add a variable in the legend without charting the data? | Charts and Charting in Excel |