CountIf visible cells only using udf

[YasserKhalil]

Hello everyone
I have tried the following udf to use countif for visible cells only

Code: Select all

Function CountVisible(rg As Range, cn As Range) As Long
    Dim rng As Range, r As Range, cnt As Long
    Set rng = rg.SpecialCells(xlCellTypeVisible)
    For Each r In rng.Areas
        cnt = cnt + Application.WorksheetFunction.CountIf(r, cn.Value)
    Next r
    CountVisible = cnt
End Function

But when testing the udf, I found that it deals with the whole range not only the visible cells when I filtered another range

[HansV]

How about

Code: Select all

Function CountVisible(rg As Range, cn As Range) As Long
    Dim ar As String
    Dim ar1 As String
    Dim r1 As Long
    Dim ac As String
    ar = rg.Address(External:=True)
    ar1 = rg(1, 1).Address(External:=True)
    r1 = rg.Row
    ac = cn.Address(External:=True)
    CountVisible = Evaluate("=SUMPRODUCT((" & ar & "=" & ac & ")*" & _
        "(SUBTOTAL(103,OFFSET(" & ar1 & ",ROW(" & ar & ")-" & r1 & ",0))))")
End Function

[YasserKhalil]

Thank you very much. This returns a VALUE error.

[HansV]

It works correctly in my test workbook. Could you attach a sample workbook demonstrating the problem?

[YasserKhalil]

Here's a sample file

[HansV]

The formulas in your sample workbook work in my Excel (2021).
Here is a version using an ordinary formula.

Sample.xlsm

[YasserKhalil]

Thank you very much my tutor. The formula works fine but what about the udf! Why Evaluate doesn't work within the udf?

[HansV]

Apparently Evaluate is less powerful in older versions of Excel.
Why do you need a UDF if a standard formula will work?

[YasserKhalil]

I am working on Office 365 (not older version) and I need UDF (just a matter of curious why not working within the udf)

[HansV]

That's strange - it the UDF works in Excel in Office 2021, it should work in Excel in Micrsoft 365 too...

[DocAElstein]

Hi

I can’t help much on this one as I haven’t really got a clue what that formula is doing, or what the workings or thoughts are behind it are: I am not good at all with formulas or the built in spreadsheet functions.
But

_ 1 In my Excel 2007, the Evaluate and the formula in the following all give the same results.

Code: Select all

 '    http://www.eileenslounge.com/viewtopic.php?f=30&t=37732
Sub Testit()
 Debug.Print Range("F1").FormulaArray ' =SUMPRODUCT(($C$5:$C$194=E1)*SUBTOTAL(103,OFFSET($C$5,ROW($C$5:$C$194)-ROW($C$5),0)))
 Let Range("I1").FormulaArray = "=SUMPRODUCT(($C$5:$C$194=E1)*SUBTOTAL(103,OFFSET($C$5,ROW($C$5:$C$194)-ROW($C$5),0)))"
Dim vTemp
 Let vTemp = Evaluate("=SUMPRODUCT(($C$5:$C$194=E1)*SUBTOTAL(103,OFFSET($C$5,ROW($C$5:$C$194)-ROW($C$5),0)))")
End Sub

_._______________________________________________

_2 A complete long shot in ignorance of really knowing what you are doing at all …

Could those (External:=True) be giving problems?
_ I don’t understand at all why you have them,
and even if you do need them,
_ they might cause the final formula in the evaluate to be bigger than 255 characters,
or
_ if you have some strange non English worksheet names, then that might give issues:

StrangeExternalAddressWorksheetNamesIssues.JPG

I could be totally wrong here if Yasser's Excel and VBA somehow supports his strange characters: My German Excel and VBA does support the few German keyboard characters, ö ü ä , but I am not sure if VBA is capable of handling the more exotic squiggles of Yasser’s language. It certainly isn’t on any of my Excels or VBAs. But it might be in his: Clearly his Excel handles the language, but I don't know if that always means that VBA does as well.


Alan

[HansV]

Based on Alan's remarks: leaving out External:=True would cause the UDF to return incorrect results if the formula refers to cells on another sheet, and might cause problems if the formula gets recalculated while another sheet is the active sheet. But you might try it:

Code: Select all

Function CountVisible(rg As Range, cn As Range) As Long
    Dim ar As String
    Dim ar1 As String
    Dim r1 As Long
    Dim ac As String
    ar = rg.Address
    ar1 = rg(1, 1).Address
    r1 = rg.Row
    ac = cn.Address(External:=True)
    CountVisible = Evaluate("=SUMPRODUCT((" & ar & "=" & ac & ")*" & _
        "(SUBTOTAL(103,OFFSET(" & ar1 & ",ROW(" & ar & ")-" & r1 & ",0))))")
End Function

Sample.xlsm

[YasserKhalil]

Thanks a lot, Mr. Alan and Mr. Hans
Now the UDF works fine and without any problems at all.
Best Regards for both of you