VBA Power Analysis Macro help

I'm trying to create a Power Analysis macro that I use as an add-in in excel. I've previously done this successfully for a significance testing macro, but this one seems to be giving me issues. the prompt asks for 2 proportions, a kappa, an alpha, power and sample size. The point of the macro is to estimate one of the last 2. In different instances the power is known and sample size is unknown, there are also instances where sample size is known and power is unknown. Whichever is unknown is what's being estimated. Any advice would be appreciated. I've attached what I currently have.

There's qite a bit likely to be wrong (one thing is you're using Set when you shouldn't be in some instances, another is you've Dimmed D as Double but it takes values from a textbox which are always strings), but it would be a lot easier to analyse if you provided some dummy data for the computation, and explain as if to a 5-year old what needs to go in which userform fields, single cells/multiple cells, values, and finally if you could give some expected results. It would be even better if you stepped us through both scenarios of which values are known, with expected results for both.

I don't see anay relation between the calculation in mOduel1 and the code in the userform.

You migth reduce all code in the userform to:

Code:

---

Private Sub ComputeButton_Click()
  If PowerBox <> "" Then
    C = (pA.Value * (1 - pA.Value) / KappaBox + pB.Value * (1 - pB.Value)) * ((Application.NormSInv(1 - AlphaBox / 2) + Application.NormSInv(D)) / (pA.Value - pB.Value)) ^ 2 And E.Offset(0, 0) = "Sample Size (nB)" And E.Offset(0, 1) = C
  Else
    Z = (pA.Value - pB.Value) / Sqr(((pA.Value * (1 - pA.Value)) / (nB.Value * KappaBox)) + ((pB.Value * (1 - pB.Value)) / nB.Value))
    D = Application.NormSDist(Z - Application.NormSInv(1 - AlphaBox / 2)) + Application.NormSDist(-Z - Application.NormSInv(1 - AlphaBox / 2)) And E.Offset(0, 0) = "Power (1-Beta)" And E.Offset(0, 1) = D
  End If
End Sub

Private Sub CancelButton_Click()
  Unload Me
End Sub

---

Attached is an updated macro that still doesn't work. I've also included some data. In the first scenario, nB (sample size) is missing and with the other values selected via the user prompt, it needs to compute sample size (should be 553.66). In the second scenario the Beta is missing and needs to be computed (should be 0.2). I've used R in the past, but all of my data is in excel format and switching back and forth can be a pain, especially when trying to perform multiple analyses. The following R code accomplishes what needs to be done:

pA=0.234
pB=0.144
kappa=.4
alpha=0.05
beta=0.20
(nB=(pA*(1-pA)/kappa+pB*(1-pB))*((qnorm(1-alpha/2)+qnorm(1-beta))/(pA-pB))^2)
ceiling(nB) # 70
z=(pA-pB)/sqrt(pA*(1-pA)/nB/kappa+pB*(1-pB)/nB)
(Power=pnorm(z-qnorm(1-alpha/2))+pnorm(-z-qnorm(1-alpha/2)))

The nB calculates the sample size and the power effectively calculates beta (power=1-beta).

Test thoroughly the attached.
No checks for only one of beta/sample size data entered, nor for neither of them entered.

Perfect. Everything works but I do get an error when I try to close out of the add-in, any thoughts on how to fix this? Currently if I click the add-in and the prompt comes up, if I click cancel, the prompt closes, but if I click 'X' (top right), I get a runtime error.

Try this version attached.

The prompt closes, but now when I try to actually compute the numbers using the macro, I get a blank output/nothing is computed. It seems that the prompt cancels out once the 'compute' button is clicked.

I'm very sorry, this message seem to have passed me by.
Change the queryClose code to:

Code:

---

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then bCanceled = True
End Sub

---