Countif

[Charles]

Hi
Is it possible to use the function, Countif in a macro?
I get a function not defined error when trying:
res=Countif(range,k)

Charles

 

[mangesh_yadav]

Use as follows:

ans = WorksheetFunction.CountIf()


Mangesh

 

[Jerry W. Lewis]

Answer Depends on your version of Excel:
Application.Countif() works on all versions that support VBA
WorksheetFunction.Countif() works on versions since 97
You could combine them
Application.WorksheetFunction.Countif()
(sort of like wearing suspenders and a belt) but I know of no advantages
that offset the extra typing.

WorksheetFunction will give you function names in the Intellisense
dropdown and it give you an argument list for the function you type in
manually. Some functions may behave slightly differently when the call
fails,
http://groups-beta.google.com/group...cf3b8/e69b15d92b5caacb?hl=en#e69b15d92b5caacb
and WorksheetFunction may be as much as 20% faster
http://groups-beta.google.com/group...aca23/05dbe6e736626911?hl=en#05dbe6e736626911
would be a place to start on the differences.

Another option for calling worksheet functions from VBA is
Evaluate("COUNTIF()")
It doesn't matter with COUNTIF, but Evaluate() will treat its argument
as an array formula.

Jerry