VBA Array Plots

[jimww]

Hello

I have spent hours searching this forum and performing Google searches to find the answer to my question but so far have only found bits and pieces. This is my first attempt to write a VBA macro and I think it is pretty simple however i have hit a roadblock and hope someone can help me out.

I have two arrays numfail and faildata that contain double precision numbers. The task is to plot the faildata array as the x-values and numfail as the y-values in an xy scatterplot.

I have done this before as named ranges in another worksheet but I would like to plot this from VBA subroutines in this worksheet.

I am including all of my code, sorry for the clutter, so that you will know just what I am doing.

The code works until I get to the Sub TTTplotcreate() and I am completely confused on how to get the chart to execute properly.

All help is greatly appreciated.

Thanks
Jim

The initial data is read from the spread sheet from the following address:
numfail from D22:D34
faildata from E22:E34

Also Sheet1 has been renamed to INPUT DATA.

The code I have so for is:

Option Base 1 ' Array elements start at 1 and not 0

Sub SysRel()
Worksheets("Input Data").Activate

Dim i As Integer ' Looping index
Dim firstrow As Integer ' Define first row as 1 to find beginning of numeric data
Dim lastrow As Integer ' Define last row to find end of numeric data
Dim numinputrows As Integer ' Represents the number of data points to be analyzed
Dim numfail(1 To 1000) As Double ' Array containing data for the number of failures
Dim scalednumfail(1 To 1000) As Double ' Array containing the scaled number of failures
Dim failtime(1 To 1000) As Double ' Array containing data for the failure time for each failure
Dim w(1 To 1000) As Double ' Array containing data for TTT plot
Dim scaledw(1 To 1000) As Double ' Array containing scaled w data for the unit TTT plot
Dim duanex(1 To 1000) As Double ' Array containing Ln scale of time values for Duane plots
Dim duaney(1 To 1000) As Double ' Array containing Ln scale of (number of failures)/(time) values for Duane plots


' Loop to find first numeric row that starts failure data portion of input field
firstrow = 1
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

For i = firstrow To lastrow
If Cells(i, 1) = 0 Then
firstrow = i
Else
End If
Next i
'Range("G1") = firstrow


numinputrows = Cells(Cells.Rows.Count, "A").End(xlUp).Row - (firstrow - 1)
' Read in failure data
For i = 1 To numinputrows
numfail(i) = Worksheets("Input Data").Cells(i + firstrow - 1, 1)
failtime(i) = Worksheets("Input Data").Cells(i + firstrow - 1, 2)
Next i

' Code to compute data for scaled TTT plot
' Compute wi's
For i = 1 To (numinputrows - 1)
scalednumfail(i) = numfail(i) / ((numinputrows - 1) - 1)
w(i) = -Log(failtime(numinputrows - i + 1) / failtime(numinputrows))
Next i

' Compute scaled wi's
For i = 1 To (numinputrows - 1)
scaledw(i) = w(i) / w(numinputrows - 1)
Next i

' Compute data for Duane plots
For i = 2 To numinputrows
duanex(i) = Log(failtime(i))
duaney(i) = Log(numfail(i) / failtime(i))
Next i

'*********************************************
' Print computed plot values into spreadsheet*
'*********************************************
For i = 1 To numinputrows
' Print failure data for TTT plot into spreadsheet
Worksheets("Input Data").Cells(i + firstrow - 1, 4) = numfail(i)
Worksheets("Input Data").Cells(i + firstrow - 1, 5) = failtime(i)
Next i

For i = 1 To numinputrows - 1
' Print unit TTT plot data into spreadsheet
Worksheets("Input Data").Cells(i + firstrow - 1, 6) = scalednumfail(i)
Worksheets("Input Data").Cells(i + firstrow - 1, 7) = w(i)
Worksheets("Input Data").Cells(i + firstrow - 1, 8) = scaledw(i)
Next i

For i = 2 To numinputrows
' Print failure data for Duane plot
Worksheets("Input Data").Cells(i + firstrow - 1, 9) = duanex(i)
Worksheets("Input Data").Cells(i + firstrow - 1, 10) = duaney(i)
Next i


TTTplotcreate
End Sub


Sub TTTplotcreate()
Dim TTTChtObj As ChartObject

Set TTTChtObj = ActiveSheet.ChartObjects.Add _
(Left:=586, Width:=400, Top:=250, Height:=300)

TTTChtObj.Chart.ChartType = xlXYScatter

Set TTTChtObj = ActiveSheet.SeriesCollection.NewSeries
With TTTChtObj
.XValues = failtime
.Values = numfail
.Name = TTTplot
End With


'TTTChtObj.Chart.ChartType = xlXYScatter

End Sub

 

[Andrew Poulsom]

You need to use another variable here, declared as Series:

Set TTTChtObj = ActiveSheet.SeriesCollection.NewSeries

 

[jimww]

Thanks so much for the reply Andrew.

Do you mean like the following?

-----------------------------
Sub TTTplotcreate()
Dim TTTChtObj As ChartObject

Set TTTChtObj = ActiveSheet.ChartObjects.Add _
(Left:=586, Width:=400, Top:=250, Height:=300)

TTTChtObj.Chart.ChartType = xlXYScatter

Set newseries = ActiveSheet.SeriesCollection.NewSeries
XValues = failtime
Values = numfail
Name = TTTplot
End Sub
-----------------------------

When I try this an error occurs stating
"Run-time error '438' Object doesn't support this property or method"<!-- / message -->

 

[Andrew Poulsom]

No like this:

Sub TTTplotcreate()
    Dim TTTChtObj As ChartObject
    Dim TTTSeries As Series
    Set TTTChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=586, Width:=400, Top:=250, Height:=300)
    TTTChtObj.Chart.ChartType = xlXYScatter
    Set TTTSeries = TTTChtObj.SeriesCollection.NewSeries
    With TTTSeries
        .XValues = failtime
        .Values = numfail
        .Name = TTTplot
    End With
End Sub

Note that the procedure won't recognise the variables failtime, numfail and TTTplot because they have been declared within your SysRel procedure and are therefore local to it.

 

[jimww]

Andrew,

Thanks so much. I tried running that code and I am getting error

"Run-time error '438': object doesn't support this propery or method"

Do you think it is the way that I am calling the subroutine and passing the arguments?

Here is what I have now:

I call the subroutine like this "Call TTTplotcreate(numfail,failtime)" from SysRel. Just like before but I am passing arguments to subroutine TTTplotcreate.

Then I call the sub that you sent passing the arrays numfail and failtime like this:

Sub TTTplotcreate(numfail, failtime)
Dim TTTChtObj As ChartObject
Dim TTTSeries As Series
Set TTTChtObj = ActiveSheet.ChartObjects.Add _
(Left:=586, Width:=400, Top:=250, Height:=300)
TTTChtObj.Chart.ChartType = xlXYScatter
Set TTTSeries = TTTChtObj.SeriesCollection.NewSeries
With TTTSeries
.XValues = failtime
.Values = numfail
.Name = TTTplot
End With
End Sub

The Debug give me the error at the Set TTTSeries part

Set TTTSeries = TTTChtObj.SeriesCollection.NewSeries

Thanks
Jim

 

[Andrew Poulsom]

Sorry, I missed that:

Set TTTSeries = TTTChtObj.Chart.SeriesCollection.NewSeries

 

[jimww]

Andrew,

That did the trick. Thank you so much for your help. It looks like the subroutine call passed the arguments ok and the plot is coming out with just a few minor changes to go.

It appears that the arrays that i passed through the Subs executed properly. My background is mostly in software lanuages like mathematica, S-plus etc. The lack of syntax knowledge is killing me in Excel. I am trying to come up to speed as fast as I can.

I really appreciate your help. Do you have any suggestions on texts I can buy that will help me learn this type of programming in Excel? the help file supplied with Excel usually leaves me wanting to see more examples of what they are trying to illustrate.

Thanks
Jim

 

[Andrew Poulsom]

I like John Walkenbach's Power programming with VBA books:

http://spreadsheetpage.com/index.php/books/

 

[jimww]

Thanks Andrew, you have been a tremendous help.

Jim