Life Data Analysis in Microsoft Excel - Explanation

This article explains some of the API calls in the Life Data Analysis in Microsoft Excel example file. For more examples, please see the Example section of the API Reference.

CalculateWeibull Subroutine
First, we will walk through the CalculateWeibull subroutine:



Initialize a globally scoped WeibullDataSet object. Note that this variable is declared outside the CalculateWeibull function so that it will be maintained later for use by the Plot subroutine.

Set mWDataSet = New WeibullDataSet

Specify analysis settings. Note that here we’re saying to run calculations using MLE to estimate parameters on a 2-parameter Weibull distribution. 

mWDataSet.AnalysisSettings.Analysis = WeibullSolverMethod_MLE mWDataSet.AnalysisSettings.Parameters = WeibullSolverNumParameters_MS_2Parameter mWDataSet.AnalysisSettings.Distribution = WeibullSolverDistribution_Weibull

Next we load the failures from the spreadsheet into the WeibullDataSet object. To begin, we setup a loop to iterate through rows in the spreadsheet. We begin the loop with 3 to skip over our two rows of headers. We end the loop dynamically based on the last populated row of data. This is accomplished by asking Excel to search upward from the last row in the sheet by calling  to give the last row in the 1st column that has data entered into it. Inside the loop, we call AddFailure to add the failure time and number in group for each of the rows on the spreadsheet. 

Dim i As Integer For i = 3 To ActiveSheet.Cells(ActiveSheet.Rows.count, 1).End(xlUp).Row - 1 Call mWDataSet.AddFailure(Val(ActiveSheet.Cells(i, 1).Value), CLng(ActiveSheet.Cells(i, 2).Value)) Next

Now that we’ve defined the WeibullDataSet, we call the data set’s calculate subroutine. 

mWDataSet.Calculate

After calculations are complete, we copy the parameters out of the WeibullDataSet object into the results section of the spreadsheet. The parameters are stored in the FittedModel inside the WeibullDataSet. To begin, we pull out the ParamValues array that contains pairs of ParamName and Value.

Dim params As ParamValues params = mWDataSet.FittedModel.Parameters

Note that our spreadsheet only has 6 rows in a section called "Results." Three of these rows will be taken up by the "Reliability," "Probability of Failure" and "Failure Rate" results. This then leaves up to 3 rows available for any parameters contained in the FittedModel's ParamValues array, so we will ignore any additional parameters that might be in that array.

Dim maxRowIndex As Integer maxRowIndex = UBound(params) If maxRowIndex > 2 Then maxRowIndex = 2 Dim curRow As Integer curRow = 8

We define a variable called CurRow to iterate through the available spreadsheet rows (beginning with the first row below the “Results” header row). We iterate through the ParamValues array and increment this variable as we read out up to 3 parameters. For i = 0 To maxRowIndex ActiveSheet.Cells(curRow, 4) = params(i).ParamName ActiveSheet.Cells(curRow, 5) = params(i).Value curRow = curRow + 1 Next

Next we read the "At Time" and copy the results from the FittedModel's "Reliability," "Unreliability," and "FailureRate" functions to the results section of the sheet.

Dim timeVal As Double timeVal = Val(ActiveSheet.Range("D3").Value) ActiveSheet.Cells(11, 4) = "Reliability (t=" & timeVal & ")" ActiveSheet.Cells(11, 5) = Format$(mWDataSet.FittedModel.Reliability(timeVal)) ActiveSheet.Cells(12, 4) = "Prob. Failure (t=" & timeVal & ")" ActiveSheet.Cells(12, 5) = Format$(mWDataSet.FittedModel.Unreliability(timeVal)) ActiveSheet.Cells(13, 4) = "Failure Rate (t=" & timeVal & ")" ActiveSheet.Cells(13, 5) = Format$(mWDataSet.FittedModel.FailureRate(timeVal))



Plot Subroutine
After the WeibullDataSet is calculated, clicking the Plot button will be able to update the plot region of the sheet. Upon clicking the button, the Plot subroutine is called via the cmdPlot_Click handler.

Let’s walk through the Plot subroutine:

 Check that the WeibullDataSet that will be referenced has been initialized. If it hasn’t, then call the CalculateWeibull subroutine first so that we have something to plot.</li>

If mWDataSet Is Nothing Then Call CalculateWeibull

Next we initialize a WAPlot object and add the WeibullDataSet. Note that if the WeibullDataSet still isn’t initialized, then there is an error in the data set.</li>

If Not mWDataSet Is Nothing Then Dim Plot As New SynthesisAPI.WAPlots Call Plot.AddDataset(mWDataSet)

Now that the WAPlot object has been initialized with the WeibullDataSet, we can get an image of a plot and display it in the spreadsheet’s plot image area. Note that the GetSelectedPlotType function is called to translate the selected item in the spreadsheet list-box to a WAPlotType to be passed to the Plot object’s CreatePlotVB6 function. </li>

Set imgWPlot.Picture = Plot.CreatePlotVB6(GetSelectedPlotType)

If an error occurred, then we hide the plot image and display a message</li>

imgWPlot.Visible = Not Plot.ErrorHappened MsgBox Plot.ErrorMessage End If

</ol>

Supporting Functions
In addition, there are some supporting functions to improve the usability of the sheet:
 * Worksheet_Activate – calls the FillCommandListBox function
 * FillCommandListBox – initializes the spreadsheet list box with a list of available plot types
 * GetSelectedPlotType – returns the appropriate WAPlotType based on the index selected in the spreadsheet list box
 * Worksheet_Change – clears the results section when any cell in the results or input sections of the sheet is changed. This is accomplished by calling ClearResults.  Note that Worksheet_Change is called even when the sheet is being updated by the calculate subroutine, so we have a flag variable (called mClearResultsWhenChanged) that is set before updating the sheet programmatically.
 * ClearResults – clears the values from the results section of the sheet and hides the plot image.
 * lstProcessCmd_Change – updates the plot image based on the newly selected plot type.