Getting a list of excel formula arrays in VBA

[enzovitale]

Dear all,
I need to get a list of all formula arrays in the current sheet. I'd like to do this in VBA.

It would be very nice if Microsoft provided an 'Arrays' property of the Workbook and Worksheet objects, like they do for e.g. the 'Names' collection. Then, I could just query this property.

Since to my knowledge they don't, I wrote the code below, which works nicely but is quite SLOW. Any suggestions?

Enzo



Dim UsedRange As Excel.Range
Set UsedRange = ActiveSheet.UsedRange

Dim DictionaryArrays As Scripting.Dictionary
Set DictionaryArrays = New Scripting.Dictionary

' Build up a collection of all distinct array ranges
' by looping over all cells in the used range.
If Not UsedRange.HasArray = False Then
For Each iCell In UsedRange
If iCell.HasArray Then
Set iArray = iCell.CurrentArray
If Not DictionaryArrays.Exists(iArray.Address) Then
Call DictionaryArrays.Add(iArray.Address, iArray)
End If
End If
Next
End If

 

[Sektor]

Try to set Application.ScreenUpdating = False in the beginning of procedure.

 

[enzovitale]

no difference

 

[Richard Schollar]

You can restrict your search to just formula cells in the sheet too by using the SpecialCells method:

For Each iCell In UsedRange.SpecialCells(xlCellTypeFormulas)

 

[enzovitale]

thanks a lot Richard!

Indeed there's no array in Excel without a formula attached to it, right? I mean, even array constants (which I'd like to capture in my routine) are defined through formulas. Hence, your approach should just work!

Enzo

 

[SydneyGeek]

You can test for an array once you're in the cell:

Sub ShowArrays()
    Dim c As Range
    
    For Each c In Cells.SpecialCells(xlCellTypeFormulas)
        If c.HasArray Then
            Debug.Print c.Address & "; " & c.FormulaArray
        End If
    Next c
End Sub

Denis