Consulting

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

Thread: VBA Userform Precedence

  1. #1

    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  2. #2
    You'd better use 1 Userform containing a multipage with 2 tabs:

    - PowerAnalysisPrompt
    - KappaForm
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    Last edited by snb; 01-12-2018 at 10:16 AM.

  3. #3
    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  4. #4
    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  5. #5
    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
    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  7. #7
    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
    
    
    Formatting tags added by mark007
    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 
    
    
    Formatting tags added by mark007
    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 
    
    
    Formatting tags added by mark007
    )
    Your code produces an error...
    A = Range(PowerAnalysisPrompt.pA.Value) 
    
    
    Formatting tags added by mark007
    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
    Do not use:

    Application.WorksheetFunction.Norm_S_Inv 
    
    
    Formatting tags added by mark007
    but

    Application.Norm_S_Inv 
    
    
    Formatting tags added by mark007
    instead

  9. #9
    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
    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
    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 
    
    
    Formatting tags added by mark007
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    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
    This seems to work for the data provided. HTH. Dave
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  13. #13
    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
    Paul, apologies but for this macro I need to keep it in userforms.

  15. #15
    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
    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
    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
     '#VALUE! – Occurs if the supplied probability argument is non-numeric
    qnorm1a = Application.Norm_S_Inv(1 - Alpha / 2) 
    
    
    Formatting tags added by mark007
    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 
    
    
    Formatting tags added by mark007
    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
    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
    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 
    
    
    Formatting tags added by mark007
    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
  •