HOW TO: Automatically make an array formula occupy the right number of cells

How can I make my add-in function automatically occupy the right number of cells for the array that it returns?

Issue

My add-in function returns an array. The users of the function have difficulty in creating the correct array formula for it, either because they do not know the number of cells that will be returned, or because they have difficulties with array formulae. I would like the formula array to automatically resize itself to display the entire array.

Summary

An Excel worksheet function is not allowed to change the contents of cells, so this solution must use an event handler and a macro, as follows:

1. The add-in contains an instance of the helper class, CResizeResultManager, which manages the behavior.

   // Include the behavior manager header file, and declare a single global instance
   #include "ResizeResultManager.h"
   CResizeResultManager theResizeResultInstance;
   

2. The worksheet function returns an array result to Excel as usual, but just before it returns, it calls CResizeResultManager::AddToQueueIfRequired().

       xloResult.AllocArray(rows, cols);
       // omitted ... populate array ...
   
       // Register this result with the behavior manager
       theResizeResultInstance.AddToQueueIfRequired(xloResult);
   
       // Return to Excel as usual
       return xloResult.Ret();
   

3. The CResizeResultManager inspects the result and compares it to the calling range. If the result is too large to be displayed in the calling range, then the result/calling range pair is added to a queue.

4. The CResizeResultManager will wait for the calculation loop to finish, and will then trigger an event handler macro. This macro will work through each unique item in the queue and, if possible, expand the formula in the calling range to fit the entire result array.

5. If the range is recalculated at a later time, but the result has not changed size, then no further action will be taken. However, if the result has expanded, then the process will be repeated and the range will be expanded again.

Problems

There are three problems with this behavior:

1. Excel's Undo stack is cleared whenever the resizing behavior occurs. This is a fixed characteristic of Excel: whenever a user-defined macro is run, whether in VBA or from an XLL, the Undo stack is cleared.

2. When the range is resized, the formula is re-evaluated, and the add-in function is therefore called for a second time. If this presents a performance problem, a results cache should be used.

3. There will be times when the behavior is not required, for example when the function is called within a reducing formula such as:

   =SUM(MyArrayFunction(1,2,3))

   In this case, it is clear that, although an array is returned by our add-in function, the result of the entire formula should be displayed in a single cell. You can handle this issue by adding an optional boolean argument resize to the function, with a default value of TRUE. If the value is provided and is false, then the add-in function should not call CResizeResultManager::AddToQueueIfRequired().

Solution

A header file containing the helper class CResizeResultManager can be downloaded here.

The code for the class is listed below.

Example

You can find an example project that implements this behavior here:

• Sample for Visual Studio 2010
• Sample for Visual Studio 2012
• Sample for Visual Studio 2013
• Sample for Visual Studio 2015

Once you've built the project and opened the add-in in Excel, take a look at the behavior of the GetCurve() function.

• Non-array formulae. You can type a formula, such as =GetCurve(.5,3,4,TRUE) into a cell and just press Enter. The formula will automatically be converted to an array formula occupying 3 rows and 4 columns.

• Array formulae of insufficient size. Select a range of 2x2 cells, type in =GetCurve(.5,3,4,TRUE) and press SHIFT+CTRL+Enter. The array formula will automatically be expanded to 3x4.

• Array formulae of sufficient size. Select a range of 6x6 cells, type in =GetCurve(.5,3,4,TRUE) and press SHIFT+CTRL+Enter. The array formula will be left unchanged, since enough spacve is available. As is usual with Excel add-in functions, the excess cells wil be filled with #N/A.

The important parts of the code are these:

• Include the header and declare an instance of CResizeResultManager

  #include "ResizeResultManager.h"
  CResizeResultManager theResizeResultInstance;
  

• In your add-in function, add an optional boolean argument, resize. You can decide whether the default value should be TRUE or FALSE.

• In your add-in function, call CResizeResultManager::AddToQueueIfRequired() just before the function returns its result.

      xloResult.AllocArray(rows, cols);
      // omitted ... populate array ...
  
      // Register this result with the behavior manager
      if (resize)
      {
          theResizeResultInstance.AddToQueueIfRequired(xloResult);
      }
  
      // Return to Excel as usual
      return xloResult.Ret();
  

CResizeResultManager class

/*-------------------------------------------------------------------------*\
|                                                                           |
|   FILE:       ResizeResultManager.h                                       |
|   PURPOSE:    Declarations and definitions of classes to add a resize     |
|               behavior to functions that return array results.            |
|   DATE:       7 February 2013                                             |
|   VERSION:    7.0.7                                                       |
|   COPYRIGHT:  (c) Planatech Solutions Ltd, 2013                           |
|                                                                           |
|   This software is the property of Planatech Solutions Ltd.  It is        |
|   licensed for the use of the purchaser only, and no license is granted   |
|   for the further distribution of this source code whether for sale or    |
|   otherwise, whether in its original form or amended. Any binary files    |
|   generated from this source code can be distributed for sale to third    |
|   parties only with the express written permission of Planatech Solutions |
|   Ltd. Planatech Solutions Ltd and their agents accept no liability for   |
|   losses incurred of any kind through the use of this software.           |
\*-------------------------------------------------------------------------*/
/*
    The class offers a behavior such that array formulae are resized after 
    the Calculate event if the calling range is too small to hold the 
    array result.
    To use this class, declare a single instance at global scope, e.g:
    #include "ResizeResultManager.h"
    CResizeResultManager theResizeResultInstance;
    In each function that needs the behavior, make sure that the behavior 
    is optional, either (i) by including an extra boolean argument, typically 
    named resize or (ii) by having two versions of the function one which 
    resizes and another wihch does not. 
    
    This is important, because often the behavior will not be wanted, for 
    example when an array function is called within a function that produces 
    a scalar result, e.g. "=SUM(MyArrayFn(...))".
    To request the behavior, add a call to AddToQueueIfRequired() at the end 
    of the add-in function, e.g.:
    CXlOper* MyArrayFn_Impl(..., BOOL resize)
    {
        // Assign xloResult to an array
        xloResult.AllocArray(rows, cols);
        ...
        
        if (resize)
        {
            theResizeResultInstance.AddToQueueIfRequired(xloResult);
        }
        return xloResult.Ret();
    }
    AddToQueueIfRequired() will compare the size of the caller to the size
    of the array in xloResult and will add it to a queue if the caller 
    is not large enough to hold the result. 
    After the next Calculate event, ProcessQueue() will be called and each 
    item in the queue will cause a resize behavior if this is possible.
*/
#ifndef __RESIZE_RESULTMANAGER_H__
#define __RESIZE_RESULTMANAGER_H__
#include <XlFuncs.h>
#include <XlpShared.h>
#ifndef __CPPLOG_H__
#include <cpplog_off.h>
#endif
class CResizeResultArgs
{
public:
    CResizeResultArgs(const CXlOper& xloCaller, 
        size_t nCallerRows, size_t nCallerCols, 
        size_t nRequiredRows, size_t nRequiredCols)
        : m_xloTopLeft(xloCaller.Resize(1, 1)), 
          m_nCurrentRows(nCallerRows), m_nCurrentCols(nCallerCols),
          m_nRequiredRows(nRequiredRows), m_nRequiredCols(nRequiredCols)
    {
        m_nNewRows = max(m_nRequiredRows, m_nCurrentRows); 
        m_nNewCols = max(m_nRequiredCols, m_nCurrentCols);
    }
public:
    CXlOper m_xloTopLeft;
    size_t m_nCurrentRows, m_nCurrentCols;
    size_t m_nRequiredRows, m_nRequiredCols;
    size_t m_nNewRows, m_nNewCols;
    CString m_strFormula;
};
class CResizeResultManager : public CXlLockable
{
private: 
    typedef std::vector<CResizeResultArgs> queue_t;
public:
    CResizeResultManager() 
    {
        m_calculateObserver.SetInstance(this);
    }
    void ProcessQueue()
    {
        LOG_TRACE(g_logger, "CResizeResultManager::ProcessQueue()");
        queue_t queue;
        MoveQueue(queue);
        for (size_t i = 0; i < queue.size(); i++)
        {
            Resize(queue[i]);
        }
    }
    void MoveQueue(queue_t& queue)	// Thread-safe
    {
        CXlLock lock(*this, true);
        queue = m_queue;
        m_queue.clear();
    }
    bool AddToQueueIfRequired(const CXlOper& xloResult)
    {
        LOG_TRACE(g_logger, "AddToQueueIfRequired(" << log(xloResult) << "): starts");
        CXlOper xloCaller;
        size_t nRequiredRows, nRequiredCols;
        size_t nCallerRows, nCallerCols;
        if (!xloResult.IsArray())
            return false;
        xloResult.GetDims(nRequiredRows, nRequiredCols);
        if (xloCaller.GetCaller() != 0 || !xloCaller.IsRef()) 
            return false;
        xloCaller.GetDims(nCallerRows, nCallerCols);
        if (nCallerRows < nRequiredRows || nCallerCols < nRequiredCols)
        {
            AddToQueue(xloCaller, nCallerRows, nCallerCols, nRequiredRows, nRequiredCols);
            return true;
        }
        else
        {
            LOG_TRACE(g_logger, "AddToQueueIfRequired(" << log(xloResult) << "): no action required");
        }
        return false;
    }
    void AddToQueue(const CXlOper& xloCaller, size_t nCallerRows, size_t nCallerCols, size_t nRequiredRows, size_t nRequiredCols) // Thread-safe
    {
        CXlLock lock(*this, true);
        m_queue.push_back(CResizeResultArgs(xloCaller, nCallerRows, nCallerCols, nRequiredRows, nRequiredCols));
        LOG_TRACE(g_logger, "AddToQueue(" << log(xloCaller) << ", " << nRequiredRows << ", " << nRequiredCols << ")");
    }
    void Resize(CResizeResultArgs& args)
    {
        LOG_TRACE(g_logger, "Resize starts: " << log(args.m_xloTopLeft) << " to [" << args.m_nNewRows << "," << args.m_nNewCols << "]");
        try
        {
            ResizeImpl(args);
#ifdef __CPPLOG_H__
            LOG_TRACE(g_logger, "Resized formula [" << args.m_strFormula << "] to [" << args.m_nNewRows << "," << args.m_nNewCols << "]");
#endif
        }
        catch(CXlRuntimeException& ex)
        {
#ifdef __CPPLOG_H__
            LOG_ERROR(g_logger, "Resize to [" << args.m_nNewRows << "," << args.m_nNewCols << "] failed : " << ex.what());
            if (args.m_strFormula.size() > 0)
                LOG_ERROR(g_logger, "Formula: [" << args.m_strFormula << "]");
#endif
        }
    }
    static CString log(const CXlOper& xlo)
    {
        if (xlo.IsRef())
        {
            return "CXlOper ref[" + xlo.GetRef().ToString() + "]";
        }
        else if (xlo.IsScalar())
        {
            return xlo.ToString();
        }
        else if (xlo.IsArray())
        {
            CString strBuf;
            size_t nRows, nCols;
            xlo.GetArraySize(nRows, nCols);
            strBuf.Format("CXlOper array[%lu rows x %lu cols]", nRows, nCols);
            return strBuf;
        }
        else
        {
            return "[CXlOper]";
        }
    }
protected:
    void ResizeImpl(CResizeResultArgs& args)
    {
        // If change is not required, quit
        if ((args.m_nCurrentRows >= args.m_nNewRows)
         && (args.m_nCurrentCols >= args.m_nNewCols))
        {
            return;
        }
        int rc;
        bool bFormula;
        size_t r, c;
        
        // Get formula from top left cell
        if (!args.m_xloTopLeft.IsRef())
            throw CXlRuntimeException("Expected a reference");
        if ((rc = CXlMacros::GetCellIsFormula(bFormula, args.m_xloTopLeft)) != 0)
            throw CXlApiException("GetCellIsFormula", rc);
        if (!bFormula)
            throw CXlRuntimeException("Expected a reference to a cell containing a formula");
        if ((rc = CXlMacros::GetFormula(args.m_strFormula, args.m_xloTopLeft)) != 0)
            throw CXlApiException("GetFormula", rc);
        // Check that new cells in range are empty
        for (r = args.m_nCurrentRows; r < args.m_nNewRows; r++)
            for (c = 0; c < args.m_nNewCols; c++)
                CheckCellIsEmpty(args.m_xloTopLeft, r, c);
        for (c = args.m_nCurrentCols; c < args.m_nNewCols; c++)
            for (r = 0; r < args.m_nCurrentRows; r++)
                CheckCellIsEmpty(args.m_xloTopLeft, r, c);
        // Apply formula to new range
        CXlOper xloNewRange = args.m_xloTopLeft.Resize((RW12)args.m_nNewRows, (COL12)args.m_nNewCols);
        if ((rc = CXlMacros::FormulaArray(args.m_strFormula, xloNewRange)) != 0)
        {
            throw CXlApiException("FormulaArray", rc);
        }
    }
    void CheckCellIsEmpty(const CXlOper& xloTopLeft, size_t r, size_t c)
    {
        CString strFormula;
        BOOL bIsArray;
        CXlOper xloCell = xloTopLeft.Offset((RW12)r, (COL12)c);
        if (xloCell.GetFormula(strFormula, bIsArray)
         && !strFormula.IsEmpty())
        {
            CString strMessage;
            strMessage.Format("cell [%ld, %ld] is not empty", r, c);
            throw CXlRuntimeException(strMessage);
        }
    }
private:
    queue_t m_queue;
private:
    // Events
    class CalculateObserver : public CXlCalculateEventStaticObserver
    {
    public:
        CalculateObserver() : m_instance(0) { }
        virtual void Update(CXlWorksheetEventArgs* e)
        {
            if (m_instance)
                m_instance->ProcessQueue();
        }
        void SetInstance(CResizeResultManager* instance) { m_instance = instance; }
    private:
        CResizeResultManager* m_instance;
    };
    CalculateObserver m_calculateObserver;
};
#endif // __RESIZE_RESULTMANAGER_H__