I'd like to make a macro that accesses a value in the more detailed classification matrix resulting from a boosted trees model and uses that value as a variable. Specifically, I'd like to assign the Total Percent that were Predicted Y in my analysis. It comes up as Cell(10,3) in the detailed classification matrix that includes row, column, and total percentages. How do I go about accessing this value?
Here is the portion of the macro that I think holds all the information needed to answer my question above, I just haven't been able to find the answer myself. Before the portion posted below is just a recorded macro of a classification boosted trees analysis:
Dim S1 As Spreadsheet Set S1 = ActiveDataSet
Dim newanalysis2 As Analysis Set newanalysis2 = Analysis (scBoostingTrees, S1) Dim oStaDocs2 As StaDocuments
[Recorded Macro for Boosted Tree Classification goes here]
newanalysis2.Run ' Boosted Trees Results : Data1 Dim oAD3 As STABTrees.BTreesResults Set oAD3 = newanalysis2.Dialog oAD3.ResponseCategory = 0 oAD3.StartTreeNumber = 1 oAD3.EndTreeNumber = 1 oAD3.NumberOfTreesForModel = 12 oAD3.NumberOfMoreTreesToCreate = 200 oAD3.TestDataSet = True oAD3.LiftChartLiftValue = True oAD3.ResponseCategory = 0 oAD3.CumulativeLiftChart = True Set oStaDocs2 = oAD3.PredictedVsObservedByClasses Set AO = newanalysis2.RouteOutput(oStaDocs2) Set PctTot = oStaDocs2(3).Value(10,3)
The highlighted portion is what I need to fill in with code that will access data from the "Predicted vs Observed by Classes" results spreadsheet. From what I've seen in Workbooks after performing this type of analysis, the "Predicted vs Observed by Classes" results seem to be provided in a histogram and 2 spreadsheets, one simple and one more detailed. I want to access the value from a cell in the more detailed spreadsheet, but don't know how to reference it in code. Any help will be greatly appreciated.
You can either grab the text and then manually remove the “%” sign from the text and then convert to a double, or take the numerator and denominator separately from the spreadsheet, convert them to double and perform the division. For example, PctTot = oStaDocs2(3).Text(10,3). From here you can remove % sign via the Left( ) function or Replace ( ) function. The text can then be converted to a double via the CDbl ( ) function.