Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

Thread: VBA Userform Precedence

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

    VBA Userform Precedence

    I have a macro used as an add-in that has 3 userform prompts. When the user selects the add-in, I would like a specific prompt to show, which from there, the user selects which prompt they will see next, but currently the wrong userform is showing first, how do I correct this? Attached is my macro, any help/explanations would be appreciated. I want the General form to show first, then once they select an option and 'Compute', that prompt closes and the correct one opens. I currently have them linked and when I run it from that prompt by pressing F5 it works, but from the initial startup of the add-in, the wrong userform is displayed.
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,400
    You'd better use 1 Userform containing a multipage with 2 tabs:

    - PowerAnalysisPrompt
    - KappaForm
    Attached Files Attached Files
    Last edited by snb; 01-12-2018 at 10:16 AM.

  3. #3
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    559
    Location
    Too much time on my hands. I debugged and made your file operational. Seems to produce some output... I have no idea if its correct? I made some changes that seem right but may be wrong dependent upon what U want to do. I'm guessing SNB's approach will be superior but I thought I'd post my efforts anyways. By the way, what does this program actually do? HTH. Dave
    Attached Files Attached Files

  4. #4
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    Thank you Dave. I modified what you had provided and I'm still getting an error. This macro will be used as an add-in. When I run the macro from the edit screen starting from the 'generalform' prompt, everything works. But as soon as I save it as an add-in and try to run it, I'm getting the error "Run-time error '1004': Unable to get the Norm_S_Inv property of the WorksheetFunction class". The main modification that I did to what you provided was I separated out the Kappas. The kappa form and the poweranalysisprompt should be completely separated, meaning the sample sizes entered in the kappaform gives an output to the selected output range, and not filled in the poweranalysisprompt. I've attached my most recent version.

    This macro should do a few different things. If it's 'Pre' it can either estimate the sample size needed to show statistical significance when running a 2 sample proportion z test, or it can estimate the power necessary. If it's 'Post', it can estimate kappa just by selecting the 2 known sample sizes.
    Attached Files Attached Files

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,920
    Location
    What would be helpful is a workbook with data to play with, and instructions as to what number or range goes into which control on the various user forms
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    Attached is some some data. What's highlighted in yellow is what the output results should be. Scenario 1 estimates sample size (nB), scenario 2 estimates Beta, scenario 3 estimates Kappa. Scenarios 1 & 2 would be input into the 'pre-campaign'/poweranalysisprompt userform. Scenario 3 would be input into the kappaform.
    Attached Files Attached Files

  7. #7
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    559
    Location
    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

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,400
    Do not use:

    Application.WorksheetFunction.Norm_S_Inv
    but

    Application.Norm_S_Inv
    instead

  9. #9
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    559
    Location
    Whoops I see why U changed the code to range. Change your variable declaration to reflect range (I think I changed it to double). Apologies. Dave

  10. #10
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    with the variable declaration changed and the 'worksheetfunction' taken out, I'm not getting an error saying 'type mismatch' pointed at the 'qnorm1a = Application.Norm_S_Inv(1 - Alpha /2)' line. Although I don't see where the mismatch is since both qnorm1a and alpha should be type double.

  11. #11
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,920
    Location
    Seems more complicated that it needs to be

    If you don't need to use a UserForm, just use a user defined function for the 3 options


    Capture1.JPG


    Option Explicit
    
    Function CalcSampleSize(pA As Double, pB As Double, Beta As Double, Kappa As Double, Optional alpha As Double = 0.05) As Variant
        Dim qNorm1a As Double, qNorm2a As Double, nB As Double
        
        qNorm1a = Application.WorksheetFunction.Norm_S_Inv(1 - alpha / 2)
        qNorm2a = Application.WorksheetFunction.Norm_S_Inv(1 - Beta)
        
        nB = (pA * (1 - pA) / Kappa + pB * (1 - pB)) * ((qNorm1a + qNorm2a) / (pA - pB)) ^ 2
        CalcSampleSize = WorksheetFunction.Ceiling_Math(nB)
    End Function
    
    Function CalcBeta(pA As Double, pB As Double, nB As Double, Kappa As Double, Optional alpha As Double = 0.05) As Variant
        Dim qNorm1a As Double, qNorm2a As Double, Z As Double, pNorm1a As Double, pNorm2a As Double, Power As Double
        qNorm1a = Application.WorksheetFunction.Norm_S_Inv(1 - alpha / 2)
        
        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
        CalcBeta = 1 - Power
    End Function
    
    Function CalcKappa(nA As Double, nB As Double) As Variant
        CalcKappa = nA / nB
    End Function
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    559
    Location
    This seems to work for the data provided. HTH. Dave
    Attached Files Attached Files

  13. #13
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    It seems that it's working when I run it from the editor, but when I save as an add-in, it errors out. Do you experience this as well?

  14. #14
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    Paul, apologies but for this macro I need to keep it in userforms.

  15. #15
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    5,920
    Location
    Quote Originally Posted by V_F View Post
    Paul, apologies but for this macro I need to keep it in userforms.
    NP

    Why do you want to make it an addin (XLAM)? How will you run the .Show to display the user form from the add in?

    Could you just put it in XLSTART as a hidden workbook and run the .Show driver using the QAT or via F8?
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    An add-in would make it easier for the people who would be using it. That's currently the format that is used for the other macros that are used. Below is a screenshot of what the ribbon looks like now.
    Excel_Ribbon.jpg

  17. #17
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    I'm still experiencing the issue with getting an error saying 'type mismatch' pointed at the 'qnorm1a = Application.Norm_S_Inv(1 - Alpha /2)' line. Although I don't see where the mismatch is since both qnorm1a and alpha should be type double. The alpha comes from the PowerAnalysisPrompt.AlphaBox.Value Textbox and I've tried using CDbl, VAL, and a few other various things I've found online, but nothing seems to get rid of this error.

  18. #18
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    559
    Location
    '#VALUE! – Occurs if the supplied probability argument is non-numeric
      qnorm1a = Application.Norm_S_Inv(1 - Alpha / 2)
    Seems like the Alpha value has to be the problem. U can trial the following and/or add the previously posted error management code.
    If Not IsNumeric(PowerAnalysisPrompt.AlphaBox.Value) Or _
    PowerAnalysisPrompt.AlphaBox.Value = vbNullString Then
      PowerAnalysisPrompt.AlphaBox.Value = 0.05
      Alpha = 0.05
    Else`
      Alpha = Val(PowerAnalysisPrompt.AlphaBox.Value)
    End If
    I have never got this error. When does it occur? Could U provide the steps etc. that are required to make it an addin. Dave

  19. #19
    VBAX Regular
    Joined
    Jun 2017
    Posts
    22
    Location
    Dave,
    Save the file in .xlam, go to developer -> Excel Add-ins -> Browse -> select the .xlam file you just saved -> click ok. Right click your ribbon -> customize the ribbon -> change popular commands to macros and select the add-in you just enabled and add it where ever you want it to show up on the ribbon -> click ok. From here, when you click to open the add-in, the error appears. I know it's a lot of work, but it's easy access for people to use once enabled.

  20. #20
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    559
    Location
    Thanks for that and good news I've generated the same error as U. U are using the Sub Power_Analysis() to start the addin. U need to do this and select it as the macro for the addin. No errors and operational. Place this in module code.
    Public Sub Start()
    GeneralForm.Show
    End Sub
    For XL2016 to make an addin, save the file as an addin. Goto file options>addins>Go>check your addin box. Go to the ribbon, right click, select customize the ribbon, select macros in the choose commands scroll box, select your addin, select addins in the main tab list, select new tab, select add. You can then change the name of the new tab or the addin and symbol by right clicking on the new tab or addin and selecting rename. Click Ok and the addin tab is added to the ribbon. Thanks V_F for the info, HTH, Dave

Posting Permissions

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