Life Data Analysis in Microsoft Excel - Explanation

This article explains some of the API calls and code structure 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:

1. 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.

 'Setup a WeibullDataSet object 
 Set mWDataSet = New WeibullDataSet

2. Specify analysis settings. Note that here we're saying to run calculations using MLE to estimate parameters on a 2-parameter Weibull distribution.
'Set analysis settings mWDataSet.AnalysisSettings.Analysis = WeibullSolverMethod_MLE mWDataSet.AnalysisSettings.Parameters = WeibullSolverNumParameters_MS_2Parameter mWDataSet.AnalysisSettings.Distribution = WeibullSolverDistribution_Weibull
3. Next, we load the failures from the spreadsheet into the WeibullDataSet object. To begin, we set up 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 ActiveSheet.Cells(ActiveSheet.Rows.count, 1).End(xlUp).Row 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.
'Read the Grouped Time to Failures data into the WeibullDataSet Dim i As Integer For i = 3 To ActiveSheet.Cells(ActiveSheet.Rows.count, 1).End(xlUp).Row Call mWDataSet.AddFailure(Val(ActiveSheet.Cells(i, 1).Value), CLng(ActiveSheet.Cells(i, 2).Value)) Next
4. Now that we've defined the WeibullDataSet, we call the data set's calculate subroutine.
'Calculate the WeibullDataSet mWDataSet.Calculate
5. 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 property of the WeibullDataSet. To begin, we pull out the ParamValues array that contains pairs of ParamName and Value.
'Get the parameter array for the fitted model 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. 'The spreadsheet only has room for 3 parameters, so limit to 3 max (index 0 to 2) 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
6. 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.
'Populate time-specific values 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 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:

1. 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.
'If we haven't calculated, then go ahead and do that now If mWDataSet Is Nothing Then Call CalculateWeibull
2. 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.
'If still not calculated, then there is an error, so exit sub If Not mWDataSet Is Nothing Then 'Add data set to the plot Dim Plot As New SynthesisAPI.WAPlots Call Plot.AddDataset(mWDataSet)
3. 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.
'Update the plot image Set imgWPlot.Picture = Plot.CreatePlotVB6(GetSelectedPlotType)
4. If an error occurred, then we hide the plot image and display a message
'Handle errors by hiding the plot and showing a message imgWPlot.Visible = Not Plot.ErrorHappened MsgBox Plot.ErrorMessage End If

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 this function 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.