有什么理由可以避免Application.Worksheetfunction吗? [英] Any reason to avoid Application.Worksheetfunction?

问题描述

我正在编写一个宏以遍历某些记录，并希望进行for循环，以避免在循环时出现无限可能，例如:

I am writing a macro to iterate over some records and wanted to a for-loop to avoid any chance of infinite while looping like:

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

我不记得如何从VBA进行计数，因此搜索将我发送到这里:

I couldn't remember how to do a count of things from VBA so a search sent me here: Use VBA to Count Non Blank Cells in a Column. One suggestion was

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

这似乎过于复杂，所以我做了一些进一步的挖掘，并决定要使用:

This seemed over complicated, so I did some more digging and decided I was going to use:

COUNT = Application.WorksheetFunction.Count(COUNT_RANGE)

该页面上的另一个示例使用了Application.WorksheetFunction.CountA()，但是现在我仍然很担心(偏执狂)，我有理由避免使用它.有吗?

Another example on that page used Application.WorksheetFunction.CountA(), but still now I am concerned (paranoid) there is a reason I should avoid it. Are there any?

谢谢.

推荐答案

避免工作表功能的唯一阻碍因素是，如果您担心与旧版本Excel的向后兼容性.新版本的Excel通常会引入新的工作表功能.如果您的用户版本的Excel中没有这些功能，您将遇到困难.

The only show-stopper reason to avoid worksheet functions is if you are worried about backwards compatibility with older versions of Excel. New versions of Excel generally introduce new worksheet functions. If these aren't available in your users' versions of Excel you will run into difficulties.

除此之外，如果您知道用户使用的版本上提供的功能，则它们通常非常有效，并且通常比VBA中的等效功能更快，尤其是如果他们避免循环访问单元格，请参见评论.

Other than that, and if you know the functions are available on the version your users are working with, they are generally very efficient and often quicker than the equivalent in VBA, especially if they avoid looping through cells, as mentioned in the comments.

这篇关于有什么理由可以避免Application.Worksheetfunction吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！