"Unable to get the NormSInv property of the WorksheetFunction clas

[David Roodman]

Hello.
I am writing a function in Excel 2002. In context, the following watch
expression works fine:
Application.WorksheetFunction.NormSInv((1)
But this one:
Application.WorksheetFunction.NormSInv(1)
generates the value:
<Unable to get the NormSInv property of the WorksheetFunction class>

Both NormSInv and NormSInv are listed in my object browser window, and as
taking one argument. Does anyone have any idea what's going on here? I'll be
grateful for any help you can offer.
David Roodman
Center for Global Development
Washington, DC

 

[Jerry W. Lewis]

Neither expression works. The first has unmatched parentheses, but
otherwise both are identical. If the following does not answer your
question, repost without the typos.

In this case the "Unable to get ... property ..." error is telling you
that NormSInv() is not returning a number. That is to be expected,
since NormSInv(1) is infinite, as is NormSInv(0). Correspondingly, in a
worksheet cell,
=NORMSINV(1)
and
=NORMSINV(0)
will return #NUM!

Since Excel 97, VBA has required explicit error handling for calls to
worksheet functions. To use
Application.WorksheetFunction.NormSInv(p)
you either need to ensure that 0<p<1, or else have an On Error statement

Jerry