VBA Userform Precedence

[V_F]

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.

[snb]

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

- PowerAnalysisPrompt
- KappaForm

[Dave]

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

[V_F]

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.

[Paul_Hossler]

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

[V_F]

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.

[Dave]

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

[snb]

Do not use:


Application.WorksheetFunction.Norm_S_Inv

but


Application.Norm_S_Inv

instead

[Dave]

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

[V_F]

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.

[Paul_Hossler]

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


21376




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

[Dave]

This seems to work for the data provided. HTH. Dave

[V_F]

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?

[V_F]

Paul, apologies but for this macro I need to keep it in userforms.

[Paul_Hossler]

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?

[V_F]

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.
21402

[V_F]

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.

[Dave]

'#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

[V_F]

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.

[Dave]

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

[Paul_Hossler]

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.


That's what I was doing to test

You can take a look at the XLSM attachment. I simplified the code a little and move some things around

Saved it as XLAM, and added 'Start' to the ribbon

Seems to work