Consulting

Results 1 to 9 of 9

Thread: VBA Power Analysis Macro help

  1. #1
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location

    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.
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    Last edited by p45cal; 01-05-2018 at 07:04 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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:

    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

  4. #4
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location

    New attachments added

    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).
    Attached Files Attached Files

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Test thoroughly the attached.
    No checks for only one of beta/sample size data entered, nor for neither of them entered.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    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.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Try this version attached.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    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.
    Last edited by V_F; 01-10-2018 at 10:18 AM.

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I'm very sorry, this message seem to have passed me by.
    Change the queryClose code to:
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then bCanceled = True
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •