How to loop through FormulaArrays in an Excel Sheet

Question

Excel 2010. I need to find out where the FormulaArrays are in a sheet. In particular I need to get their address.

I am not after knowing if a given (1x1) cell is "part of" a FormulaArray but rather know where the FormulaArray "blocks" are located. Eg if a FormulaArray is entered in A1:B2, I'd like to get the information that the FormulaArray area is over the range A1:B2, as opposed to knowing that the individual cells A1, A2, B1, B2 are part of "a" FormulaArray.

Is this possible?

Answer 1

This wasn't as easy as I hoped. Excel doesn't appear to expose a collection of array formulas for you to loop over. That means you have to check every cell. At a high level this code:

1. Scans every cell.

2. If the cell contains an array formula it extracts the address.

3. If the address is new, it is added to the addresses variable.

4. Outputs all address to the screen.

    ' Returns the address of array formula in the current worksheet.
    Sub GetArrayFormulaRangeAddresses()
        Dim r As Range              ' Used to loop over active cells.
        Dim addresses() As String   ' Holds each found address.
        Dim address As String       ' Used to avoid duplicate entries.
        Dim foundCount As Integer   ' Count of found array formulas.
   
        ' Initialise vars.
        foundCount = -1
   
        ' Check every active cell, in currently selected tab.
        ' Ideally you would loop over a formula collection but
        ' the Excel object model does not appear to expose this.
        For Each r In ActiveSheet.UsedRange.Cells
   
            ' Found one.
            ' WARNING: Array formula contains values,
            ' even when cell is not part of an array formula.
            If r.FormulaArray Like "={*}" Then
   
                ' WARNING: Cannot pass array until after firt redim statement.
                ' To avoid check found count, then addresses array.
                If foundCount = -1 Then
   
                    ' Not found, add to list.
                    foundCount = foundCount + 1
                    ReDim Preserve addresses(foundCount)
                    addresses(foundCount) = r.CurrentArray.address
   
                Else
                    ' Check if address already found.
                    If Not CheckArrayContains(addresses, r.CurrentArray.address) Then
   
                        ' Not found, add to list.
                        foundCount = foundCount + 1
                        ReDim Preserve addresses(foundCount)
                        addresses(foundCount) = r.CurrentArray.address
                    End If
                End If
            End If
        Next
   
   
        ' TODO: What do you want to do with found addresses?
        For foundCount = LBound(addresses) To UBound(addresses)
            MsgBox addresses(foundCount)
        Next
    End Sub
   
    ' Returns true if the passed array contains a value.
    ' Otherwise returns false.
    Public Function CheckArrayContains(ByRef CheckArray() As String, ByVal CheckValue As String) As Boolean
        Dim i As Integer        ' Counter, used to check each element of the array.
   
        ' Check existing members.
        For i = LBound(CheckArray) To UBound(CheckArray)
            If CheckArray(i) = CheckValue Then
   
                ' Match found.
                CheckArrayContains = True
                Exit Function
            End If
        Next
   
        ' No match found.
        CheckArrayContains = False
    End Function
   

I think this code could be improved:

1. Redim is expensive. It would probably be better to resize the array in batches of 1000.
2. The Excel Object Model might expose a collection you could loop over.
3. For larger workbooks, it would be faster to extract all addresses and then deduplicate the results.