3

I would like to write a VBA function, which outputs a list of all the single formulas and array formulas of a worksheet. I want an array formula for a range to be printed for only one time.

If I go through all the UsedRange.Cells as follows, it will print each array formula for many times, because it covers several cells, that is not what I want. 

 For Each Cell In CurrentSheet.UsedRange.Cells
     If Cell.HasArray Then
        St = Range(" & Cell.CurrentArray.Address & ").FormulaArray = " _
                & Chr(34) & Cell.Formula & Chr(34)
     ElseIf Cell.HasFormula Then
        St = Range(" & Cell.Address & ").FormulaR1C1 = " _
                & Chr(34) & Cell.Formula & Chr(34)
     End If
     Print #1, St
 Next

Does anyone have a good idea to avoid this?

Improve this question
1
  • If you look on my profile, you will see my Mappit addin. This addin produces a list of all unique formulae per sheet - as well as a map - identifying the unique formulae Commented Jul 14, 2013 at 2:07

3 Answers 3

Reset to default
2

You basically need to keep track of what you've already seen. The easy way to do that is to use the Union and Intersect methods that Excel supplies, along with the CurrentArray property of Range.

I just typed this in, so I'm not claiming that it's exhaustive or bug-free, but it demonstrates the basic idea:

Public Sub debugPrintFormulas()
    Dim checked As Range

    Dim c As Range
    For Each c In Application.ActiveSheet.UsedRange
        If Not alreadyChecked_(checked, c) Then
            If c.HasArray Then
                Debug.Print c.CurrentArray.Address, c.FormulaArray

                Set checked = accumCheckedCells_(checked, c.CurrentArray)
            ElseIf c.HasFormula Then
                Debug.Print c.Address, c.Formula

                Set checked = accumCheckedCells_(checked, c)
            End If
        End If
    Next c
End Sub

Private Function alreadyChecked_(checked As Range, toCheck As Range) As Boolean
    If checked Is Nothing Then
        alreadyChecked_ = False
    Else
        alreadyChecked_ = Not (Application.Intersect(checked, toCheck) Is Nothing)
    End If
End Function

Private Function accumCheckedCells_(checked As Range, toCheck As Range) As Range
    If checked Is Nothing Then
        Set accumCheckedCells_ = toCheck
    Else
        Set accumCheckedCells_ = Application.Union(checked, toCheck)
    End If
End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I was about to write a comment about a dictionary being more efficient... but I take it back. Very clever
@Pynner, thanks. For my use case, performance just isn't important. I've only ever done this kind of thing when writing out formulas to text files for version control purposes, so of course processing the worksheet wouldn't be the bottleneck assuming any reasonable algorithm. However, although I don't know for sure, I suspect that Range is actually quite efficient behind the scenes. (I suppose you could optimize my example a bit by only accumulating array formula ranges. And, as @Andy G noted, by only looking at cells with formulas.)
2

The following code produces output like:

$B$7 -> =SUM(B3:B6)
$B$10 -> =AVERAGE(B3:B6)
$D$10:$D$13 -> =D5:D8
$F$14:$I$14 -> =TRANSPOSE(D5:D8)

I'm using a collection but it could equally well be a string.

Sub GetFormulas()
    Dim ws As Worksheet
    Dim coll As New Collection
    Dim rngFormulas As Range
    Dim rng As Range
    Dim iter As Variant

    Set ws = ActiveSheet
    On Error Resume Next
    Set rngFormulas = ws.Range("A1").SpecialCells(xlCellTypeFormulas)
    If rngFormulas Is Nothing Then Exit Sub 'no formulas
    For Each rng In rngFormulas
        If rng.HasArray Then
            If rng.CurrentArray.Range("A1").Address = rng.Address Then
                coll.Add rng.CurrentArray.Address & " -> " & _
                    rng.Formula, rng.CurrentArray.Address
            End If
        Else
            coll.Add rng.Address & " -> " & _
                rng.Formula, rng.Address
        End If
    Next rng
    For Each iter In coll
        Debug.Print iter
        'or Print #1, iter
    Next iter
    On Error GoTo 0     'turn on error handling
End Sub

The main difference is that I am only writing the array formula to the collection if the current cell that is being examined is cell A1 in the CurrentArray; that is, only when it is the first cell of the array's range.

Another difference is that I am only looking at cells that contain formulas using SpecialCells, which will be much more efficient than examining the UsedRange.

Improve this answer

6 Comments

I started with a Collection as I was hoping to not add items if they were already in the collection, but this proved to be a little tricky. The alternative would be to use a Dictionary.
an odd thing is, when there is no formula in a sheet, ws.Cells.SpecialCells(xlCellTypeFormulas) raises an error...
@SoftTimur Yes, and On Error Resume Next doesn't work when SpecialCells fails. I'm investigating a way around this.
Actually, On Error Resume Next does work, it's just that I had "Break on All Errors" set. I've added the error checking to my answer.
Your second version is too "efficient". It assumes formulas are unique to Range areas, but that will often not be the case. For example, put two different formulas in adjacent cells, like this: A1: =42 and A2: =99. The output will be "$A$1:$A$2 -> =42". I don't see any choice other than to iterate every cell that has a formula, as you do in your first version.
|
0

The only reliable solution I see for your problem is crosschecking each new formula against the ones already considered to make sure that there is no repetition. Depending upon the amount of information and speed expectations you should rely on different approaches.

If the size is not too important (expected number of records below 1000), you should rely on arrays because is the quickest option and its implementation is quite straightforward. Example:

Dim stored(1000) As String
Dim storedCount As Integer

Sub Inspect()

 Open "temp.txt" For Output As 1
 For Each Cell In CurrentSheet.UsedRange.Cells
     If Cell.HasArray Then
        St = Range(" & Cell.CurrentArray.Address & ").FormulaArray = " _
                & Chr(34) & Cell.Formula & Chr(34)
     ElseIf Cell.HasFormula Then
        St = Range(" & Cell.Address & ").FormulaR1C1 = " _
                & Chr(34) & Cell.Formula & Chr(34)
     End If
     If(Not alreadyAccounted(St) And storedCount <= 1000) Then
        storedCount = storedCount + 1
        stored(storedCount) = St
        Print #1, St
     End If
 Next
 Close 1
End Sub

Function alreadyAccounted(curString As String) As Boolean
    Dim count As Integer: count = 0

    Do While (count < storedCount)
        count = count + 1
        If (LCase(curString) = LCase(stored(count))) Then
            alreadyAccounted = True
            Exit Function
        End If
    Loop
End Function

If the expected number of records is much bigger, I would rely on file storage/checking. Relying on Excel (associating the inspected cells to a new range and looking for matches in it) would be easier but slower (mainly in case of having an important number of cells). Thus, a reliable and quick enough approach (although much slower than the aforementioned array) would be reading the file you are creating (a .txt file, I presume) from alreadyAccounted.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.