Thanks, Bob, this is a really exhausting explanation. Some of it should be
involved in the Help text similarly to some other references to backward
compatibility. I'll use WorksheetFunction method in the future (just like in
the past) but being aware of going on the right way.

Stefi


€žBob Phillips€� ezt Ã*rta:

Stefi,

The whole truth, or just part of the truth?

Both methods (Application and WorksheetFunction) will work. Application was
the pre Excel 97 way of doing it and is retained for compatibility, but
there are some nuances in the way they work.

There were problems with some functions in Excel97 and 2000 using the
WorksheetFunction method, which did not surface using Application .

WorksheetFunction (and Application.WorksheetFunction) supports the "auto
list members" option, whereas Application alone does not.
In Excel 97 it was necessary to use Application.WorksheetFunction to get
this auto list, but in later versions WorksheetFunction alone works.

Not all worksheet functions are exposed to VBA. Functions not exposed by the
WorksheetFunction object usually have a
VBA equivalent (e.g., Left, Year), but be aware, they do not necessarily
work in exactly the same way..

Functions within Add-ins, such as the Analysis Toolpak, cannot be called
with Application or WorksheetFunction.
Errors are handled differently. When a function is called directly using
Application (e.g., Application.VLookup) , the result is a variant containing
an error. When called directly using WorksheetFunction (e.g.,
WorksheetFunction .Â*VLookup), the function will raise an error. Both can be
managed, but in different ways

Dim ans As String

On Error Resume Next
ans = WorksheetFunction.VLookup("value", table_array, 2, False)
On Error GoTo 0
If ans = "" Then
MsgBox "not found using WorksheetFunction"
End If


If IsError(Application.VLookup("value", table_array, 2, False)) Then
MsgBox "not found using Application"
End If


WorksheetFunction is faster, by an order of circa 20%.


--

HTH


RP
(remove nothere from the email address if mailing direct)


"Stefi" wrote in message
...
Bob,

I'm interested in an other aspect of this topic: How can one use CountIf
worksheet function without the WorksheetFunction. qualifier?
In the VB Help topic "Using Microsoft Excel Worksheet Functions in Visual
Basic" we are explicitly told that "In Visual Basic, the Microsoft Excel
worksheet functions are available through the WorksheetFunction object.",
and
also the examples consistently use the WorksheetFunction. qualifier.
What is the truth?
Regards,
Stefi




"Bob Phillips" ezt Ã*rta:

Jean-Pierre,

This works for me

With ActiveSheet 'Worksheets("deelnemers")
cohort = Application.CountIf(.Range("D3:D992"), "<" & stap + 5) - _
Application.CountIf(.Range("D3:D992"), "< " & stap)
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi Niek,

your suggestion doesn't work....

this is my new function:

cohort =
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<"&stap&"+5") -
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992")
, "<stap")

I get a "compile error" in your suggestion

I adapted your suggestion to:

cohort =
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" &
stap & "+5") -
Application.CountIf(Worksheets("deelnemers").Range ("D3:D992"),
"<" & stap)

This generates no error message but now it seems that it generates the
number
of items in the list that do not meet the criteria instead of those
that
do...

with step = 25 i get a respons of 927 while there ar only a few people
between 25-30

any ideas?

I get the message:
Niek Otten wrote:
"<"&stap&"+5"

Hi,

[quoted text clipped - 21 lines]
goes wrong.
any adeas ?


--
Message posted via http://www.officekb.com