Excel UDF to return formula count

[coolactuary]

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:

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

 

[coolactuary]

... I get the full UsedRange count, rather than the function count

Sorry I meant formula count of course.

 

[Marcelo Branco]

The SpecialCells method doesn't work in UDFs, that is when the function is called from a worksheet cell.

See the topic UDF action being ignored in
http://www.decisionmodels.com/calcsecretsj.htm

M.

 

[Rick Rothstein]

You could write your UDF this way...

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]

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]

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

 

[coolactuary]

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]

About post 5
Yes, the function works if it is called by a Sub, for example:

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.

 

[Rick Rothstein]

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.