Custom function problem

**JKCincy** · 03-10-2010, 01:34 PM

Hello,
I'm fairly new to VBA programming, and very new to creating custom functions. The function I'm trying to create would apply the TDIST worksheet function to the result of a series of calculations. Four arguments would be needed to feed the series of calculations.

The code I have been using below results in the #VALUE! error.

As far as I can tell, the formulas for the interim calculations are correct, although there could very well be errors in my code that attempts to make those calculations.

Any help would be appreciated. Thanks in advance.

Code:

Function INDPROPS(pct1, pct2, base1, base2)


Dim diff As Variant
Dim pbar As Variant
Dim sediffs As Variant
Dim zstat As Variant
Dim ABSZSTAT As Variant

diff = Abs(pct1 - pct2)
pbar = (pct1 * base1 + pct2 * base2) / (base1 + base2)
sediffs = Application.WorksheetFunction.Sqrt(pbar * (1 - pbar)) * (1 / base1 + 1 / base2)
zstat = (diff - 0.5 * (1 / base1 + 1 / base2)) / sediffs
ABSZSTAT = Abs(zstat)

   INDPROPS = 0.995 - Application.WorksheetFunction.TDist(ABSZSTAT, 100000000, 2)

End Function

**shg** · 03-10-2010, 02:04 PM

100,000,000 sounds like a lot of degrees of freedom. Have you tried doing the same thing with worksheet functions?

Sqr is a native function in VBA.

Code:

Function INDPROPS(pct1 As Double, pct2 As Double, _
                  base1 As Double, base2 As Double) As Double
    Dim diff        As Double
    Dim pbar        As Double
    Dim sediffs     As Double
    Dim zstat       As Double

    diff = Abs(pct1 - pct2)
    pbar = (pct1 * base1 + pct2 * base2) / (base1 + base2)
    sediffs = Sqr(pbar * (1 - pbar)) * (1 / base1 + 1 / base2)
    zstat = Abs((diff - 0.5 * (1 / base1 + 1 / base2)) / sediffs)

    INDPROPS = 0.995 - WorksheetFunction.TDist(zstat, 100000000, 2)
End Function

**JKCincy** · 03-10-2010, 02:41 PM

Yes, the 100000000 degrees of freedom works fine in the worksheet function; it's basically just saying that we are sampling from a very large population (the formula is computing statistical testing diagnostics in a survey research setting).

Thanks for the note about Sqr. I think I had it that way originally, but as part of my (unsuccessful) troubleshooting, I tried calling the worksheet function. That doesn't appear to be the problem.