CODEWITHSUNDEEP
excelvba
Fossie
Fossie

Reputation: 45

VBA UDF gives different answer when called from Excel vs VBA

The following VBA function counts the number of cells containing formulas in a given range. It works correctly when called from a VBA sub. 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 even though only two cells with formulas are in the range.

What is causing this discrepancy?

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

Upvotes: 4

Views: 460

Answers (2)

Fossie
Fossie

Reputation: 45

If I were to send worksheet.cells to the function, it would check all cells in the entire worksheet, quite many and quite slow. Although Excel 2007+ supports 16384*1048576 rows, only cells which have actually been used are loaded to memory. There would be no need to go through all the other 17 billion cells to check. The closest I could get to identifying these was using Worksheet.UsedRange to restrict an arbitrary range input. It is not perfect though, in cases when cells far apart have been used. E.g. if cells A1 and XFD1048576 contain data, the entire worksheet would be included in UsedRange. Any tips on how to restrict the range to actually used cells (merely two cells in the above example) would be greatly appreciated.

Utilizing UsedRange I built a function which I'll share in case anyone else can make use of 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

Use of the function:

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 thank you for your reply.

Fossie

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33476

This isn't possible. The following link has the things that won't work inside of a UDF.
Here - http://support.microsoft.com/kb/170787

EDIT: A manual way of counting works though.

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.

Upvotes: 3

Related Questions