Excel add-in with a function that can fill multiple cells

Question

Suppose I want to build a Excel add-in which has the same functionality as the BDH function from the Bloomberg add in. Basically the BDH function is a function that can import time series. You can call BDH a cell and then it populates the cells below with a date and observation value for each row.

I can not figure out how to build this basic functionality in an excel add-in. A regular VBA function is not allowed to populate other cells than the cell where it is called from.

A macro may populate other cells, but it can not be called from a cell.

Now I am trying to build an VSTO excel add-in using VB, is there anyone who can point to sample code which builds a function which can populate multiple cells?

Thanks for reading!

EDIT: I am working in an Excel 2019 environment, so I can not use dynamic arrays!

Answer 1

You can build your own array formulas (VBA, VBA + dll, xll via C++ via xlw, ..) that do not return only one value but several (example of array formula is MMULT). In a second step your add-in should then be able to automatic resize your array formula to the desired size.

Answer 2

This is not straightforward without native (Office 365) support and you may wish to consider alternatives. However, you can:

1. Hook the AfterCalculate event
2. Have your array-returning functions write to a queue
3. On AfterCalculate write the returned data to the spreadsheet

In VBA this can be implemented (very inefficiently) as:

Dim PENDING As Variant

Public Function ReturnArray(n As Integer, m As Integer)

    If IsEmpty(PENDING) Then
        Set PENDING = New Scripting.Dictionary
    End If
    
    Dim arr() As Long
    ReDim arr(n - 1, m - 1)
    
    Dim clr: Set clr = Application.caller
    PENDING.Add clr.Address(External:=True), arr
    
    ' Could return entire array here to allow SUM(ReturnArray()) to work
    ReturnArray = arr(0, 0)
    
End Function


Public Sub WritePendingArrays()
    Dim i, j, n, m
    
    If IsEmpty(PENDING) Then Exit Sub
    
    ' Turn updating off whilst we write to the sheet to avoid triggering
    ' a recursive call to ourselves
    Dim currentCalcMode: currentCalcMode = Application.Calculation
    On Error GoTo Cleanup
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Each addr In PENDING.Keys()
        Dim caller As Range: Set caller = Range(addr)
        
        ' <Somehow detect if ReturnArray was the top-level function in the cell>
        
        ' <Somehow do spill detection here>
        
        ' Clear contiguous block starting with top left caller
        ' We assume this is whatever the function wrote last time
        n = IIf(IsEmpty(caller.Offset(1, 0)), 1, caller.End(xlDown).Row - caller.Row + 1)
        m = IIf(IsEmpty(caller.Offset(0, 1)), 1, caller.End(xlToRight).Column - caller.Column + 1)
        For i = 1 To n
            For j = 1 To m
                If i > 1 Or j > 1 Then
                    caller.Cells(i, j).Clear
                End If
            Next
        Next

        ' Now write the array omitting the top left calling cell. This can
        ' be done more efficiently with some range/array gymnastics
        Dim data: data = PENDING(addr)
        For i = 0 To UBound(data, 1)
            For j = 0 To UBound(data, 2)
                If i > 0 Or j > 0 Then
                    caller.Cells(i + 1, j + 1).Value2 = data(i, j)
                End If
            Next
        Next
    Next
    
    PENDING = Empty
    
    ' If original CalculationMode is manual, probably want to do Application.Calculate here
    
Cleanup:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = currentCalcMode
End Sub

Hook WritePendingArrays to the AfterCalculate event by following the recipe here.

Here is a more careful implementation using ExcelDNA. It works differently: it resizes the array formula which nicely addresses the issue of clearing the previous output, but means the function will be evaluated twice.

There are several challenges:

• Spill detection: the above code will overwrite the target cells without possibility of undo. Spill detection should check for obstructions. It is difficult to differentiate between the last output of the function and other data, particularly if the output abuts another non-empty range. You would need to keep track of what data was written by which function and persist this when Excel is restarted.
• Chaining: if you want SUM(ReturnArray()) to work, you need to parse the cell formula to see if the output for ReturnArray is the final output from the cell. This is very hard in full generality. I think BDH does not support this.
• Dependency tracing: Excel's dependent/precedent tracking will not work correctly.
• Calculation: If calculation mode is manual, it make take several calls to Application.Calculate to ensure all cells are calculated
• Performance: the above code is very slow. Because VBA does not support array slicing, doing the fast Range=Array trick isn't available without copying subarrays (although this would be quicker than looping over the range). You may want to look at Excel's C-API, for which I recommend the xlOil wrapper (disclaimer: I wrote it).

Alternatives to dynamic arrays:

• Trimming: Use an overly-large array formula output - Excel will pad it with #N/A. Define an ARRAYTRIM function to take such a range and return an array consisting of just the data. You can then write SUM(ARRAYTRIM(...) as expected.
• Caching: Rather than returning the data to the sheet, return a cache key string. You then write SUM(CACHELOOKUP(...) or may your custom functions cache-string aware. For an example of this see xlOil, if you're familiar with python, this happens seamlessly in xlOil Python.

You mention VSTO for implementation: this does not allow creation of worksheet functions, so isn't sufficient for this task. Either use ExcelDNA (which does work with VB.Net) or Excel's C-API directly or xlOil or other commercial solutions.