Making charts from arrays in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been trying to take two arrays of numbers in VBA and make a
scatterplot, but have been unsuccessful. It's easy enough to use the
cells in a worksheet as the source for a chart, but I would like to go
directly from data to chart without an intermediate step of printing the
array out to cells in a worksheet.

This is the VBA procedure which shows that it generates points in columns A
and B

Sub Plot()
Dim mu As Single
Dim segma As Single
Dim xFirst As Single
Dim xLast As Single
Dim X As Single
Dim Y As Single
Dim lngRow As Long
Dim StepValue As Single
Dim Steps As String

xFirst = Sheets(1).Range("B1")
xLast = Sheets(1).Range("B2")
Steps = Sheets(1).Range("B3")
mu = Sheets(1).Range("B4")
segma = Sheets(1).Range("B5")

StepValue = (xLast - xFirst) / (Steps - 1)
lngRow = 0
X = xFirst

With Sheets(1).Range("A10")
Do While X <= xLast
.Offset(lngRow, 0) = X
Y = Application.WorksheetFunction.­NormDist(X, mu,
segma,False)
.Offset(lngRow, 1) = Y
X = X + StepValue
lngRow = lngRow + 1
Loop
End With

End Sub

Can anybody tell me how to do it??

Thanks for any help
 
How are you unsuccessful? What doesn't work? What error message, if
any do you get?

You are probably running into the limitation on the length of the
SERIES formula and/or its components.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
The code works with me fine but I don't want the source data to be printed on
the Worksheet.
 
You can populate arrays in vba, then use:

With ActiveChart.SeriesCollection(1)
.XValues = MyXArray
.Values = MyYArray
.Name = MyName
End With

Or you can populate the chart from the worksheet, use this to unlink the
data, then delete the range:

With ActiveChart.SeriesCollection(1)
.XValues = .XValues
.Values = .Values
.Name = .Name
End With

More about delinking charts from their data:

http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top