Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA (arrays problem)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default VBA (arrays problem)
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!!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default VBA (arrays problem)
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" wrote in message
...
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!!

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default VBA (arrays problem)
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" wrote:

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" wrote in message
...
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!!


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default VBA (arrays problem)
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" wrote in message
...
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" wrote:

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" wrote in message
...
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!!



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default VBA (arrays problem)
well, i don't know how to do it. cn you show me?

"RB Smissaert" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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!!




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default VBA (arrays problem)
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


"Ali Baba" wrote in message
...
well, i don't know how to do it. cn you show me?

"RB Smissaert" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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!!





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with SUMPRODUCT and Arrays [email protected] Excel Worksheet Functions 2 January 13th 06 09:55 PM
Problem with plotting a chart when using arrays as Values and Xvalues [email protected] Charts and Charting in Excel 3 August 19th 05 09:05 PM
Elusive Problem with Arrays [email protected] Excel Programming 3 April 29th 05 12:50 AM
Arrays Kenny Excel Programming 5 August 26th 04 09:47 AM
Arrays David Excel Programming 5 January 10th 04 05:09 AM


All times are GMT +1. The time now is 05:08 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement