How Many Formulas In Your File?

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. Dick, I've never noticed that before!

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

10. 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. 🙁

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.