Generic method for Excel.Workbook and Excel.Worksheet

Question

I'm writing an Excel Add-in and for 1 of the functions I need to calculate some summary statistics. These should be added as names to either the entire Workbook or to a single Worksheet. Right now I have 2 methods that do each thing. However though, both methods look nearly exactly the same with the difference that the 1st one adds the name to the workbook and the 2nd to the worksheet.

Is there a way to make this method more generic so that based on the arguments passed I could check if the provided worksheet is null and if so add to the entire workbook?

    /// <summary>
    /// Generate dynamic summary statistics as <see cref="Name"/>s in the specified <see cref="Workbook"/>.
    /// </summary>
    /// <param name="range">The <see cref="Range"/> of the data that needs summary statistics.</param>
    /// <param name="doCalculate">Defines which variables in the summary have to be calculated.</param>
    public DynamicSummaryStatistics(Range range, SummaryStatisticsBool doCalculate)
    {
        var book = Globals.ThisAddIn.Application.ActiveWorkbook;
        var name = ((Name)range.Name).Name;
        if (doCalculate.Mean) Mean = book.Names.Add(name + "_MEAN", "=AVERAGE(" + name + ")");
        if (doCalculate.Variance) Variance = book.Names.Add(name + "_VAR", "=VAR.S(" + name + ")");
        if (doCalculate.StdDev) StdDev = book.Names.Add(name + "_STDEV", "=STDEV.S(" + name + ")");
        if (doCalculate.Minimum) Minimum = book.Names.Add(name + "_MINIMUM", "=MIN(" + name + ")");
        if (doCalculate.Quartile1) Quartile1 = book.Names.Add(name + "_QUARTILE1", "=QUARTILE.INC(" + name + ",1)");
        if (doCalculate.Median) Median = book.Names.Add(name + "_MEDIAN", "=MEDIAN(" + name + ")");
        if (doCalculate.Quartile3) Quartile3 = book.Names.Add(name + "_QUARTILE3", "=QUARTILE.INC(" + name + ",3)");
        if (doCalculate.Maximum) Maximum = book.Names.Add(name + "_MAXIMUM", "=MAX(" + name + ")");
        if (doCalculate.InterquartileRange) InterquartileRange = book.Names.Add(name + "_IQR", "=" + Quartile3.Name + "-" + Quartile1.Name);
        if (doCalculate.Skewness) Skewness = book.Names.Add(name + "_SKEW", "=SKEW(" + name + ")");
        if (doCalculate.Kurtosis) Kurtosis = book.Names.Add(name + "_KURT", "=KURT(" + name + ")");
        if (doCalculate.MeanAbsDev) MeanAbsDev = book.Names.Add(name + "_AVEDEV", "=AVEDEV(" + name + ")");
        if (doCalculate.Mode)
        {
            Mode = book.Names.Add(name + "_MODE", "=MODE.SNGL(" + name + ")");
            try
            {
                Globals.ThisAddIn.Application.WorksheetFunction.Mode_Sngl(range);
                HasMode = true;
            }
            catch
            {
                HasMode = false;
            }
        }
        if (doCalculate.Range) Range = book.Names.Add(name + "_RANGE", "=" + Maximum.Name + "-" + Minimum.Name);
        if (doCalculate.Count) Count = book.Names.Add(name + "_COUNT", "=COUNT(" + name + ")");
        if (doCalculate.Sum) Sum = book.Names.Add(name + "_SUM", "=SUM(" + name + ")");
    }

    /// <summary>
    /// Generate dynamic summary statistics as <see cref="Name"/>s in the specified <see cref="Worksheet"/>.
    /// </summary>
    /// <param name="sheet">The <see cref="Worksheet"/> on which the <see cref="Name"/>s should be generated.</param>
    /// <param name="range">The <see cref="Range"/> of the data that needs summary statistics.</param>
    /// <param name="doCalculate">Defines which variables in the summary have to be calculated.</param>
    public DynamicSummaryStatistics(Worksheet sheet, Range range, SummaryStatisticsBool doCalculate)
    {
        var name = ((Name)range.Name).Name;
        if (doCalculate.Mean) Mean = sheet.Names.Add(name + "_MEAN", "=AVERAGE(" + name + ")");
        if (doCalculate.Variance) Variance = sheet.Names.Add(name + "_VAR", "=VAR.S(" + name + ")");
        if (doCalculate.StdDev) StdDev = sheet.Names.Add(name + "_STDEV", "=STDEV.S(" + name + ")");
        if (doCalculate.Minimum) Minimum = sheet.Names.Add(name + "_MINIMUM", "=MIN(" + name + ")");
        if (doCalculate.Quartile1) Quartile1 = sheet.Names.Add(name + "_QUARTILE1", "=QUARTILE.INC(" + name + ",1)");
        if (doCalculate.Median) Median = sheet.Names.Add(name + "_MEDIAN", "=MEDIAN(" + name + ")");
        if (doCalculate.Quartile3) Quartile3 = sheet.Names.Add(name + "_QUARTILE3", "=QUARTILE.INC(" + name + ",3)");
        if (doCalculate.Maximum) Maximum = sheet.Names.Add(name + "_MAXIMUM", "=MAX(" + name + ")");
        if (doCalculate.InterquartileRange) InterquartileRange = sheet.Names.Add(name + "_IQR", "=" + Quartile3.Name + "-" + Quartile1.Name);
        if (doCalculate.Skewness) Skewness = sheet.Names.Add(name + "_SKEW", "=SKEW(" + name + ")");
        if (doCalculate.Kurtosis) Kurtosis = sheet.Names.Add(name + "_KURT", "=KURT(" + name + ")");
        if (doCalculate.MeanAbsDev) MeanAbsDev = sheet.Names.Add(name + "_AVEDEV", "=AVEDEV(" + name + ")");
        if (doCalculate.Mode)
        {
            Mode = sheet.Names.Add(name + "_MODE", "=MODE.SNGL(" + name + ")");
            try
            {
                Globals.ThisAddIn.Application.WorksheetFunction.Mode_Sngl(range);
                HasMode = true;
            }
            catch
            {
                HasMode = false;
            }
        }
        if (doCalculate.Range) Range = sheet.Names.Add(name + "_RANGE", "=" + Maximum.Name + "-" + Minimum.Name);
        if (doCalculate.Count) Count = sheet.Names.Add(name + "_COUNT", "=COUNT(" + name + ")");
        if (doCalculate.Sum) Sum = sheet.Names.Add(name + "_SUM", "=SUM(" + name + ")");
    }

You could have a private method which takes a `Func` and you provide `(format) => books.Names.Add(format)` for a workbook and `(format) => sheet.Names.Add(format)` for the worksheet method. So instead of attempting to provide a method of accessing the workbook or worksheet generically provide a method that takes the method of calling the required object. — Stephen Ross, Mar 24 '16 at 12:01
Thank you for your fast reply. Is it possible to provide a little example in pseudo code so I know where to start? I never worked before with what you said. — Krowi, Mar 24 '16 at 12:11

Stephen Ross · Accepted Answer · 2016-03-24T15:59:55.460

Instead of trying to make the access to the Workbook and Worksheet generic I'd say that providing a Func<string, string, Name> that allowed access to call either the Workbook or Worksheet would be a better method.

public DynamicSummaryStatistics(Range range, SummaryStatisticsBool doCalculate)
{
    var functionToRun = (arg1, arg2) => Globals.ThisAddIn.Application.ActiveWorkbook.Names.Add(arg1, arg2);

    this.ComputeDynamicSummaryStatistics(range, doCalculate, functionToRun);
}

public DynamicSummaryStatistics(Worksheet sheet, Range range, SummaryStatisticsBool doCalculate)
{
    var functionToRun = (arg1, arg2) => sheet.Names.Add(arg1, arg2);

    this.ComputeDynamicSummaryStatistics(range, doCalcualte, functionToRun);
}

private void ComputeDynamicSummaryStatistics(Range range, SummaryStatisticsBool doCalculate, Func<string, string, Name> functionToRun)
{
    var name = ((Name)range.Name).Name;
    if(doCalculate.Mean) Mean = functionToRun(name + "_MEAN", "=AVERAGE(" + name + ")");
    if(doCalculate.Variance) Variance = functionToRun(name + "_VAR", "=VAR.S(" + name + ")");
    // etc. etc.
}

So this is saying in the function ComputeDynamicSummaryStatistics every-time that I call functionToRun I am either calling sheet.Names.Add(...) or Globals.ThisAddIn.Application.ActiveWorkbook.Names.Add(...) based on which constructor was called.

The accessing of either the Workbook or the Worksheet is totally unknown to the method and all it is now concerned with is providing the name to add.

This is working like a charm. I only had to change the function initializer to `Func funcAdd = (name, refersTo) => sheet.Names.Add(name, refersTo);` to handle the 2 `string` arguments and the return type `Name`. Thank you a lot. — Krowi, Mar 24 '16 at 15:51
Ah sorry I had completely missed that you pass in two string arguments, I'll update my answer to show what it should be. — Stephen Ross, Mar 24 '16 at 15:58
I think there has to be a 3rd argument `TResult` which is `Name` here. — Krowi, Mar 26 '16 at 14:28

Generic method for Excel.Workbook and Excel.Worksheet

1 Answers1