Life Data Analysis in Microsoft Excel - Explanation

From ReliaWiki
Jump to navigation Jump to search



SynAPI.png
Main Page

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:

  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.
    2. '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.
    3. '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 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 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.
    4. '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 - 1 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.
    5. '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 inside the WeibullDataSet. To begin, we pull out the ParamValues array that contains pairs of ParamName and Value.
    6. '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.
    7. '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 be able to update the plot region of the sheet. Upon clicking the Plot 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.
  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.
  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.
  4. If an error occurred, then we hide the plot image and display a message
Retrieved from "https://www.reliawiki.com/index.php?title=Life_Data_Analysis_in_Microsoft_Excel_-_Explanation&oldid=52009"