CODEWITHSUNDEEP
excelvbauser-defined-functions
PeterN
PeterN

Reputation: 23

UDF with count always returns 0, or an error

So, I have a short UDF that takes a table (As Range) for an input, and would return the rows count as an output.

Function CALCROW(mytable As Range) As Double
   Dim rowNumber As Double
   statement
   CALCROW = rowNumber
End Function

For statement, I tried

rowNumber = myTable.Rows.Count
rowNumber = myTable.Count
rowNumber = Application.WorksheetFunction.Count(myTable)

both gave 0, even though the table has 5 rows.

I even tried

rowNumber = myTable.Rows
rowNumber = Application.WorksheetFunction.Rows.Count(myTable)
rowNumber = Application.WorksheetFunction.Rows(myTable)

But for them I got the #VALUE error.

I know that if all I wanted to know the row count in excel i could just use =ROWS(myTable), but it isn't about that...

Upvotes: 1

Views: 51

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Actually the following code (in a module)

Option Explicit

Public Function CALCROW(mytable As Range) As Long
   CALCROW = mytable.Rows.Count
End Function

and this as a formula

=CALCROW(B1:G8)

gives 8 which is correct. Instead of B1:G8 you can even give a named range or ListObject name and it works.

Note that row counts can only be of type Long.

Upvotes: 1

Related Questions