How Many Formulas In Your File?

The other day I was working on a financial model. During a break I saw on Twitter that @JesseH77 posted the following:

My budget files have 180,850 and 160,191 formulas and 11 lines of code to count the formulas.

This got me curious about my own model, so I knocked up some quick and dirty code, just to see:

Sub CountFormulas()

Dim cl As Range, ws As Worksheet, fcount As Long, ftcount As Long

 

For Each ws In ThisWorkbook.Worksheets

For Each cl In ws.UsedRange

If cl.HasFormula Then fcount = fcount + 1

Next cl

Debug.Print ws.Name & ":" & fcount

ftcount = ftcount + fcount

fcount = 0

Next ws

 

Debug.Print "Total Formulas in Model: " & ftcount

End Sub

The results for the model I just built were:

0.0 TOC:37

1.0 Neighbourhood Plan Map:3

1.1 Unit Mix Calculations:389

1.2 Unit Mix Summary:355

1.3 Historical Projects:398

1.4 Site Quality:1457

1.5 Lot Cost & Revenue:2216

1.6 Revenue Timing:6278

2.0 Vertical Construction:0

2.1 Building Mix:806

2.2 Building Costs:598

2.3 Development Charges:34

3.0 Land & Infrastructure:836

3.1 Infrastructure Notes:14

3.2 Global Factors:16

3.3 Infrastructure Timing:24230

4.0 P&L - Standard:676

4.1 P&L - Contribution:1306

5.0 Cashflow-Consol By Sector:2079

5.1 Cashflow-Consol By Product:2079

5.2 Cashflow-Detail By Sector:2537

5.3 Cashflow-Detail By Product:2681

6.0 Notes:10

Total Formulas in Model: 49035

Doesn't really mean anything, except that it's a big workbook, but I still found it to be an interesting exercise.

11 thoughts on “How Many Formulas In Your File?

  1. Hi Ken,

    You can use SpecialCells to count the formulas and eliminate a loop. Doing that would have saved you at least 5 seconds when analyzing all those workbooks.

    I'm sure you knew that, but forgot about it. Just wait until you get to be my age. Then you'll forget things you never even knew in the first place.

    Hey, if they're expecting a big Excel turnout for the next Summit, let me know. I might head up there and have some beers with you guys. I miss those Summits.

  2. Ken, I had to add one more line to skip chart sheets so in the end it was 12 lines.

    Honestly out of all the reports, dashboards, and code I've written this impressed my boss most of all. He went back and checked all his budget files for the last 7 years and charted the results.

    Thanks for the tip John, I'll have a look at that.

  3. @John,
    Yeah, you're right. Not sure why I didn't think of that up front, but the loop jumped straight in there. Maybe I'm starting to get old...? Revised code would look something like this:

    Sub CountFormulas()
    Dim ws As Worksheet, fcount As Long, ftcount As Long
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
    fcount = ws.Cells.SpecialCells(xlCellTypeFormulas).Count
    If Err.Number <> 0 Then
    Err.Clear
    fcount = 0
    Else
    ftcount = ftcount + fcount
    End If

    Debug.Print ws.Name & ": "& fcount
    Next ws
    Debug.Print "Total Formulas: " & ftcount
    On Error GoTo 0
    End Sub

    Not sure yet who is coming to the summit. I'll put out some feelers and see if what I can find out. Maybe you can crash the party like Zack did a few years back. 🙂

    @Jesse, I've never been too worried about hte number of lines of code to get something done. I usually prefer to make sure it works, and then second is trying to make it run as quickly as possible. (Not evident in my original code!) You'll notice that the code above avoids chart sheets as well.

    Funny re the boss though! 🙂

  4. Pingback: Tweets that mention The Ken Puls Blog » Blog Archive » How Many Formulas In Your File? -- Topsy.com

  5. I love the idea, but I can't get the second code to work, I don't typically use the immediate window, so this may be user error. I get a syntax error on this row...

    "Debug.Print ws.Name & “: “; fcount"

  6. Hi Jon,

    Nope, I managed to get a typo in there somehow. (Odd, because I tested it!) I've updated the code in the example, but the line you looked at should have read:

    Debug.Print ws.Name & “: “& fcount

    Thanks for pointing it out!

  7. FYI: You didn't type the second ampersand in your original code. When you forget to type an ampersand (and probably some other stuff) in a debug.print statement, it throws a semi-colon in there because it assumes you want them out in columns. Why that gives a compile error, I don't know. Maybe Jon has regional setting where semi-colons are different.

  8. I'd go for a range to simplify the coding

    Set Rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
    If Not Rng Is Nothing Then
    fcount = Rng.Cells.Count

    Regarding this line
    “Debug.Print ws.Name & “: “; fcount”
    I needed to replace the double quotes with my own.

  9. LOL! Fair enough Malcolm. Maybe we should have a contest to see just how short we can make this. 🙂

    Re the quotes, yep. WordPress seems to convert to those stupid "smart" quotes. They don't copy into VBA all that well. 🙁

  10. What you also forgot is If you are using a specialcells method prior to Excel 2010, and if you have "big" sheets - you got to be careful
    You will get a run time error 6 - overflow if you have more than 8192 areas for the below code

    fcount = ws.Cells.SpecialCells(xlCellTypeFormulas).Count

    To test Put 1 in cell A1, and =1 in cell a2. drag down the block till row 20,000

    If which case you got to either loop or resize the range

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *