VBA UDF gives a different answer when called from Excel vs VBA

Question

The following VBA function counts the number of cells containing formulas in a given range. It works correctly when called from a VBA subband. When called from Excel, it returns the total number of cells in the range.

The call from Excel is =CountFormulas(A1:C7) , which returns 21, although only two cells with formulas are in range.

What causes this mismatch?

 Public Function CountFormulas(ByRef rng As Range) As Long CountFormulas = rng.SpecialCells(xlCellTypeFormulas).Count End Function Public Sub CountFormulasFromSub() Dim rng As Range Dim res As Integer Set rng = Sheet1.Range("a1:c7") res = CountFormulas(rng) End Sub 

Answer 1

It's impossible. The following link has something that won't work inside UDF.
Here - http://support.microsoft.com/kb/170787

EDIT: Manual counting works.

 Public Function CountFormulas(rng As Range) As Integer Dim i As Integer Dim cell As Range For Each cell In rng If cell.HasFormula Then i = i + 1 End If Next CountFormulas = i End Function 

Change Integer to Long if you think it will exceed 32767.

Answer 2

If I were to send worksheets to a function, it would check all the cells on the whole worksheet, quite a lot and rather slowly. Although Excel 2007+ supports lines 16384 * 1048576, only those cells that were actually used are loaded into memory. There was no need to go through all the remaining 17 billion cells for verification. The closest I could determine was to use Worksheet.UsedRange to limit arbitrary input to a range. This is not ideal, although in cases where cells located far from each other were used. For instance. if cells A1 and XFD1048576 contain data, the entire worksheet will be included in UsedRange. Any advice on how to limit the range to actually used cells (just two cells in the above example) would be greatly appreciated.

Using UsedRange, I created a function that I will use if someone else can use it:

 Public Function CountIfFormula(ByRef rng As Range, Optional ByVal matchStr As String) As Long 'Counts the number of cells containing a formula and optionally a specific string (matchStr) in the formula itself. Dim i As Long Dim isect As Range 'Restricts the range to used cells (checks only cells in memory) Set isect = Application.Intersect(rng, rng.Parent.UsedRange) For Each cell In isect If cell.HasFormula Then If InStr(1, cell.Formula, matchStr) Then i = i + 1 End If Next CountIfFormula = i End Function 

Function Usage:

 Sub GetNrOfCells() Dim i As Long Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets i = i + CountIfFormula(ws.Cells, "=SUM(") Next 'i will now contain the number of cells using the SUM function End Sub 

Best regards, and thanks for your reply.

Fossie