# Excel UDF to return formula count

#### coolactuary

##### New Member
The examples I've checks, including from Excel MVPs have used Subs rather than Functions. I can get a Sub to work and I can get a Function to debug.print the right result, but when I call the function from a sheet I get the full UsedRange count, rather than the function count - see code below. Apologies if I'm being naive:

Code:
``````Function HowManyFormulasFixedSheet()
' Called from Sheet2, this *incorrectly* returns 6 formulas i.e. the full used range on Sheet1
CountFormulas = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeFormulas).Count
Debug.Print "HowManyFormulasFixedSheet count: " & CountFormulas
HowManyFormulasFixedSheet = CountFormulas
End Function

Sub HowManyFormulasFixedSheetSub()
' This *correctly* prints formula count of 1.
CountFormulas = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeFormulas).Count
Debug.Print "HowManyFormulasFixedSheetSub count: " & CountFormulas
End Sub

Function HowManyFormulasFixedSheetALT()
' This correctly returns 1 formula
CountFormulas = 0
For Each cell In Sheets("Sheet1").UsedRange.Cells
CountFormulas = CountFormulas + Abs(cell.HasFormula)
Next cell

Debug.Print "HowManyFormulasFixedSheetALT count: " & CountFormulas
HowManyFormulasFixedSheetALT = CountFormulas

End Function``````

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### coolactuary

##### New Member
... I get the full UsedRange count, rather than the function count

Sorry I meant formula count of course.

#### Rick Rothstein

##### MrExcel MVP
You could write your UDF this way...
Code:
``````Function HowManyFormulasFixedSheet()
Dim Cell As Range
For Each Cell In Sheets("Sheet1").UsedRange
HowManyFormulasFixedSheet = HowManyFormulasFixedSheet - Cell.HasFormula
Next
End Function``````
Note, though, that this UDF includes the formula containing this UDF function in its count.

#### coolactuary

##### New Member

Thanks. Reviewing the Decision Models bullets on the page you reference, I was aware of a function not being able to alter t[FONT=&quot]he value or formula or properties of another cell (that's what a Sub is for!) but not that a function can't use "[/FONT][FONT=&quot]FIND, SpecialCells, CurrentRegion, CurrentArray, GOTO, SELECT, PRECEDENTS etc : although you can use Range.End."

What misled me - and I confess to remaining somewhat confused / surprised - is that my first function [/FONT]
HowManyFormulasFixedSheet [FONT=&quot]managed to successfully use [/FONT]Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeFormulas).Count [FONT=&quot]when I look at the function in VBE and run it using F5.

Any ideas why this should be?

Thanks again.

[/FONT]

#### coolactuary

##### New Member
Thanks. Is that not extrememly similar to my Function HowManyFormulasFixedSheetALT() above - or have I missed your point?

#### coolactuary

##### New Member

You could write your UDF this way...

Apologies Rick, I ended I replying to my own post rather than your repsonse. Trying again...

Thanks. Is that not extrememly similar to my Function HowManyFormulasFixedSheetALT() above - or have I missed your point?

#### Marcelo Branco

##### MrExcel MVP
Yes, the function works if it is called by a Sub, for example:

Code:
``````Sub Main()
Dim MyVar As Long

MyVar = HowManyFormulasFixedSheet()
Debug.Print "MyVar= " & MyVar
End Sub

Function HowManyFormulasFixedSheet()
Dim CountFormulas As Long

CountFormulas = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeFormulas).Count
Debug.Print "HowManyFormulasFixedSheet count: " & CountFormulas
HowManyFormulasFixedSheet = CountFormulas
End Function``````

But doesn't work correctly if it's called by a formula in a cell
=HowManyFormulasFixedSheet()

M.

Last edited:

#### Rick Rothstein

##### MrExcel MVP
Apologies Rick, I ended I replying to my own post rather than your repsonse. Trying again...

Thanks. Is that not extrememly similar to my Function HowManyFormulasFixedSheetALT() above - or have I missed your point?
Similar, yes, but more compact.

Replies
4
Views
93
Replies
2
Views
85
Replies
5
Views
49
Replies
3
Views
30
Replies
13
Views
109