Customizing output in a recorded macro

A recorded analysis macro will route all its output to whatever your current output mode is by calling the RouteOutput function. For example, if your output mode is set to workbook, then a macro's output will go into a new workbook. To override this behavior, look for RouteOutput in your macro and change it to directly access the generated documents. Consider the following code generated from a Descriptive Statistics analysis:

Option Base 1
Sub Main
Dim newanalysis As Analysis
Set newanalysis = Analysis (scBasicStatistics, ActiveDataSet)
With newanalysis.Dialog
    .Statistics = scBasDescriptives
End With
newanalysis.Run
With newanalysis.Dialog
    .Variables = "3 6 9"
    .PairwiseDeletionOfMD = True
    .DisplayLongVariableNames = False
    .ExtendedPrecisionCalculations = False
    .PlotMedianQuartileRange = False
    .PlotMeanSEAndSD = False
    .PlotMeanSD196TimesSD = True
    .PlotMeanSE196TimesSE = False
    .UserDefinedPercentiles = False
    .ValidN = True
    .Mean = True
    .Median = False
    .Mode = False
    .GeometricMean = False
    .HarmonicMean = False
    .ConfLimitsForMeans = False
    .Sum = False
    .StandardDeviation = True
    .Variance = False
    .StandardErrorOfMean = False
    .MinimumMaximum = True
    .LowerUpperQuartiles = True
    .Range = False
    .QuartileRange = False
    .Skewness = False
    .Kurtosis = False
    .StandardErrorOfSkewness = False
    .StandardErrorOfKurtosis = False
    .UseNumberOfIntervals = True
    .NumberOfIntervals = 10
    .NormalExpectedFrequencies = False
    .KSAndLillieforsTestForNormality = True
    .ShapiroWilkWTest = False
    .ConfidenceIntervalForMeansPlot = 95
    .CompressedStemAndLeaf = False
End With
newanalysis.RouteOutput(newanalysis.Dialog.Summary).Visible = True
newanalysis.RouteOutput(newanalysis.Dialog.Histograms).Visible = True
End Sub

1. Find the calls to RouteOutput in your macro:

   newanalysis.RouteOutput(newanalysis.Dialog.Summary).Visible = True
   newanalysis.RouteOutput(newanalysis.Dialog.Histograms).Visible = True

2. Instead of routing the output to the default location, we will directly retrieve them from the analysis. To have a place to put this output, we need to create some document objects. In this case, we know that we will have one spreadsheet and three graphs, so enter this above the calls to RouteOutput:

   Dim SummarySpr As Spreadsheet
   Dim Histograms As StaDocuments
   newanalysis.RouteOutput(newanalysis.Dialog.Summary).Visible = True
   newanalysis.RouteOutput(newanalysis.Dialog.Histograms).Visible = True

3. Remove the calls to RouteOutput so that the code looks like this:

   Dim SummarySpr As Spreadsheet
   Dim Histograms As StaDocuments

4. Now set the document objects to the documents in the analysis. The analysis contains its results as a collection. To retrieve a particular result, we set a document object to that item in the collection. For example, to retrieve the summary table we will set our spreadsheet document to the first item in the Summary collection by adding these lines:

   Set SummarySpr = newanalysis.Dialog.Summary(1)
   SummarySpr.Visible = True

   Likewise, to retrieve the three histograms, set the StaDocuments object to the graphs in the analysis's Histograms collection by adding the following lines:

   Set Histograms = newanalysis.Dialog.Histograms
   Histograms(1).Visible = True
   Histograms(2).Visible = True
   Histograms(3).Visible = True

   Note: We assign the analysis's histograms to a StaDocuments object for efficiency purposes. When the Histograms property of the analysis is called, all the histograms are generated. If we were to call this property for each histogram, then all three histograms would be generated three times. So calling this once, assigning its results to a StaDocuments object, and then accessing the histograms from there will prevent the histograms from being created more than once.

   From here, we can customize the spreadsheet and graphs. For example, add a footnote to the first graph by adding the following line:

   Histograms(1).Titles.Add(scgFootnote, "Dayton office.")

   The final macro will look like this:

   Option Base 1
   Sub Main
   Dim newanalysis As Analysis
   Set newanalysis = Analysis (scBasicStatistics, ActiveDataSet)
   
   With newanalysis.Dialog
       .Statistics = scBasDescriptives
   End With
   newanalysis.Run
   
   With newanalysis.Dialog
       .Variables = "3 6 9"
       .PairwiseDeletionOfMD = True
       .DisplayLongVariableNames = False
       .ExtendedPrecisionCalculations = False
       .PlotMedianQuartileRange = False
       .PlotMeanSEAndSD = False
       .PlotMeanSD196TimesSD = True
       .PlotMeanSE196TimesSE = False
       .UserDefinedPercentiles = False
       .ValidN = True
       .Mean = True
       .Median = False
       .Mode = False
       .GeometricMean = False
       .HarmonicMean = False
       .ConfLimitsForMeans = False
       .Sum = False
       .StandardDeviation = True
       .Variance = False
       .StandardErrorOfMean = False
       .MinimumMaximum = True
       .LowerUpperQuartiles = True
       .Range = False
       .QuartileRange = False
       .Skewness = False
       .Kurtosis = False
       .StandardErrorOfSkewness = False
       .StandardErrorOfKurtosis = False
       .UseNumberOfIntervals = True
       .NumberOfIntervals = 10
       .NormalExpectedFrequencies = False
       .KSAndLillieforsTestForNormality = True
       .ShapiroWilkWTest = False
       .ConfidenceIntervalForMeansPlot = 95
       .CompressedStemAndLeaf = False
   End With
   
   'NEW CODE THAT WE ADDED IN THIS EXAMPLE
   Dim SummarySpr As Spreadsheet
   Dim Histograms As StaDocuments
   Set SummarySpr = newanalysis.Dialog.Summary(1)
   SummarySpr.Visible = True
   Set Histograms = newanalysis.Dialog.Histograms
   Histograms(1).Visible = True
   Histograms(2).Visible = True
   
   Histograms(3).Visible = True
   Histograms(1).Titles.Add(scgFootnote, "Dayton office")
   End Sub