I googled up this...
'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
So for your sub...
Sub Power_Analysis()
Dim pA, pB, nB, beta As Double, qnorm1a, qnorm2a, Z, pnorm1a, pnorm2a, Power
pA = A
pB = B
nB = C
beta = D
'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
On Error GoTo ErrHandler
qnorm1a = Application.WorksheetFunction.Norm_S_Inv(1 - Alpha / 2)
If nB = 0 Then
qnorm2a = Application.WorksheetFunction.Norm_S_Inv(1 - beta)
nB = (pA * (1 - pA) / Kappa + pB * (1 - pB)) * ((qnorm1a + qnorm2a) / (pA - pB)) ^ 2
E = WorksheetFunction.Ceiling_Math(nB)
PowerAnalysisPrompt.nB.Value = Format(E, "0.00")
Else
Z = (pA - pB) / (Sqr(pA * (1 - pA) / nB / Kappa + pB * (1 - pB) / nB))
pnorm1a = Application.WorksheetFunction.Norm_S_Dist(Z - qnorm1a, True)
pnorm2a = Application.WorksheetFunction.Norm_S_Dist(-Z - qnorm1a, True)
Power = pnorm1a + pnorm2a
beta = 1 - Power
E = beta
PowerAnalysisPrompt.PowerBox.Value = Format(E, "0.00")
End If
Exit Sub
ErrHandler:
On Error GoTo 0
MsgBox "Norm_S_Inv Error"
End Sub
I'm not sure that it matters. U have made a number of revisions to the code that are causing errors...
For example my code for the refedit input named "pa" on the PowerAnalysisPrompt form produces a double precision output..
A = CDbl(PowerAnalysisPrompt.pA.Value
)
Your code produces an error...
A = Range(PowerAnalysisPrompt.pA.Value)
A is declared as a double and I don't think that a range output is even possible. U have completely changed most of your inputs to this same format? As I said, I supplied U with a working version of the program and I don't understand why U made these kind of changes? I'm not sure that extra time on my hands was well spent? Dave