Excel UDF to return formula count

coolactuary

New Member
Joined
Apr 17, 2016
Messages
5
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
 

Some videos you may like

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.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Apr 17, 2016
Messages
5

ADVERTISEMENT

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
Joined
Apr 17, 2016
Messages
5
Thanks. Is that not extrememly similar to my Function HowManyFormulasFixedSheetALT() above - or have I missed your point?
 

coolactuary

New Member
Joined
Apr 17, 2016
Messages
5

ADVERTISEMENT

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
Joined
Aug 23, 2010
Messages
16,393
About post 5
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
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,584
Messages
5,523,720
Members
409,532
Latest member
Lmfacc

This Week's Hot Topics

Top