Any reasons to avoid the Application.Worksheet function?

Question

I am writing a macro to iterate over some entries and would like for a loop to avoid any possibility of an infinite loop, for example:

For i = 0 to COUNT **do stuff with START_CELL.Offset(i,0) Next 

I don’t remember how to make calculations from VBA, so the search sent me here: Use VBA to count non-empty cells in a column . One suggestion was

 n = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count 

It seemed more complicated, so I did a few more digging and decided that I would use:

 COUNT = Application.WorksheetFunction.Count(COUNT_RANGE) 

Another example on this page uses Application.WorksheetFunction.CountA() , but still bothers me (paranoid) now, so I should avoid it. Whether there is a?

Thanks to everyone.

Answer 1

The only reason you can avoid working with tables is because you are concerned about backward compatibility with older versions of Excel. Newer versions of Excel typically introduce new worksheet features. If they are not available in your user's Excel versions, you will run into difficulties.

In addition, and if you know that the functions are available in the version that your users work with, they are usually very efficient and often faster than the equivalent in VBA, especially if they avoid looping through cells, as indicated in the comment.