# 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.

This entry was posted in Excel by Ken Puls. Bookmark the permalink.

## About Ken Puls

I'm the blog owner... My main website is located at www.excelguru.ca, and is devoted to holding tips and tricks on excel development.

## 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. 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"

5. 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!

6. 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.

7. Dick, I've never noticed that before!

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