Making charts from arrays in VBA
 

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=20
any do you get?

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

--=20
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,=20
says...
I have been trying to take two arrays of numbers in VBA and make a=20
scatterplot, but have been unsuccessful. It's easy enough to use the=20
cells in a worksheet as the source for a chart, but I would like to go=20
directly from data to chart without an intermediate step of printing the=
=20
array out to cells in a worksheet.
=20
This is the VBA procedure which shows that it generates points in columns=
A=20
and B
=20
Sub Plot()=20
Dim mu As Single=20
Dim segma As Single=20
Dim xFirst As Single=20
Dim xLast As Single=20
Dim X As Single=20
Dim Y As Single=20
Dim lngRow As Long=20
Dim StepValue As Single=20
Dim Steps As String=20
=20
xFirst =3D Sheets(1).Range("B1")=20
xLast =3D Sheets(1).Range("B2")=20
Steps =3D Sheets(1).Range("B3")=20
mu =3D Sheets(1).Range("B4")=20
segma =3D Sheets(1).Range("B5")=20
=20
StepValue =3D (xLast - xFirst) / (Steps - 1)=20
lngRow =3D 0=20
X =3D xFirst=20
=20
With Sheets(1).Range("A10")=20
Do While X <=3D xLast=20
.Offset(lngRow, 0) =3D X=20
Y =3D Application.WorksheetFunction.=C2=ADNormDist(X, mu,=
=20
segma,False)=20
.Offset(lngRow, 1) =3D Y=20
X =3D X + StepValue=20
lngRow =3D lngRow + 1=20
Loop=20
End With=20
=20
End Sub=20
=20
Can anybody tell me how to do it??
=20
Thanks for any help=20
=20
=20

The code works with me fine but I don't want the source data to be printed on
the Worksheet.



"Tushar Mehta" wrote:

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

In article ,
says...
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

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/ChartsH...ChartData.html

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


Ali Baba wrote:

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