Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charts won't update | Charts and Charting in Excel | |||
Excel Charts Linked to Spreadsheets | Charts and Charting in Excel | |||
making charts based on text categories | Excel Discussion (Misc queries) | |||
linkingof charts in worksheets to cells | Excel Discussion (Misc queries) | |||
Can't create dynamic charts | Charts and Charting in Excel |