VBA (arrays problem)

[Ali Baba]

hi

I wrote this code to help me to plot a normal distribuion curve.

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

As you can see that the procedure generates points in columns A and B to be
used to create the chart. Is it possible to generate the graph without
seeing these points on the sheet. I mean that you have an array.

Plz help!!

 

[RB Smissaert]

This I think is what you want:

Sub Plot()

Dim X As Single
Dim Y As Single
Dim lngRow As Long
Dim StepValue As Single

Dim arrValues(1 To 5) As Double

arrValues(1) = 2
arrValues(2) = 3
arrValues(3) = 5
arrValues(4) = 2
arrValues(5) = 1

StepValue = (arrValues(2) - arrValues(1)) / (arrValues(3) - 1)
lngRow = 0
X = arrValues(1)

With Sheets(1).Range("A10")
Do While X <= arrValues(2)
.Offset(lngRow, 0) = X
Y = Application.WorksheetFunction.NormDist(X, _
arrValues(4), _
arrValues(5), _
False)
.Offset(lngRow, 1) = Y
X = X + StepValue
lngRow = lngRow + 1
Loop
End With

End Sub


RBS

 

[Ali Baba]

What I actually want is to take two arrays of numbers in VBA and make a
scatterplot. The procedure I gave shows that the cells in the worksheet are
used as the source for the 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.

 

[RB Smissaert]

OK, then put your resulting X and Y in a 2-D array and use this array as the
source of the chart.

RBS

 

[Ali Baba]

well, i don't know how to do it. cn you show me?

 

[RB Smissaert]

OK, then it will be something like this:

Sub Plot()

Dim X As Single
Dim Y As Single
Dim lngRow As Long
Dim StepValue As Single

Dim arrValues(1 To 5) As Double
Dim arrResults(1 To 5, 1 To 2) As Double

arrValues(1) = 2
arrValues(2) = 3
arrValues(3) = 5
arrValues(4) = 2
arrValues(5) = 1

StepValue = (arrValues(2) - arrValues(1)) / (arrValues(3) - 1)
X = arrValues(1)

Do While X <= arrValues(2)
lngRow = lngRow + 1
arrResults(lngRow, 1) = X
arrResults(lngRow, 2) = _
Application.WorksheetFunction.NormDist(X, _
arrValues(4), _
arrValues(5), _
False)
X = X + StepValue
Loop

End Sub


I haven't looked at the logic of your Sub, so you will have to figure out
the first dimension of the array arrResults.
This is now 1 to 5, but this may have to be different.
Your chart can now be based on arrResults.

RBS

 

[Peter T]

Ali Baba,

Try adding the following after "Loop" in RBS's routine

Dim vX, vY
Dim chObj
vX = Application.Index(arrResults, , 1)
vY = Application.Index(arrResults, , 2)
On Error Resume Next
Set chObj = ActiveSheet.ChartObjects("TestChart")
'On Error GoTo 0
If chObj Is Nothing Then
With ActiveSheet.ChartObjects.Add(10, 10, 400, 200)
.Chart.ChartType = xlXYScatter
With .Chart.SeriesCollection.NewSeries
.XValues = vX
.Values = vY
End With
.Chart.ChartArea.Font.Size = 10
.Name = "TestChart"
.Select
End With
End If

It would be easier to use separate arrays for your X & Y values instead of
splitting the 2D array with Index as in the above. Assuming the chart
already exists, in practice probably something like

With .SeriesCollection(n)
.XValues = arrXvalues
.Values = arrYvalues
End With

Be aware there is a 1024 string limit for the entire series formula, which
could be exceeded with many values.

Regards,
Peter T