PDA

View Full Version : [SOLVED] Using Refedit with range for sig testing macro



V_F
06-15-2017, 07:20 AM
Hi,
I'm looking for some assistance with my VBA code. I've written a macro to sig test in Excel 2016. The current version of my macro allows the user to select a range of cells as the numerators and denominators for samples 1 and 2. Each of these are summed up in order to give 2 percentages that are then sig tested. I'm now trying to create another version of the significance test where instead of summing these values, I'd like it so that the selected rows are sig testing against one another, but when I remove the 'Application.WorkSheetFunction.Sum' from my code, I'm getting an error. I've included a sample set below along with my current code. When the user selects the column 'Numerator1' as Sample1Numerator, column 'Denominator1' as Sample1Denominator, column 'Numerator2' as Sample2Numerator, and column 'Denominator2' as Sample2Denominator, I'd like each Sample 1 to be sig tested against Sample 2 for EACH row and not as a whole. Like I said, even after I took out the 'Application.WorkSheetFunction.Sum' it wasn't working. It seems like they're not being treated as individual cells of a selected range. Any help is appreciated. VBA code attached19507.





Numerator1

Denominator1


Numerator1

Denominator1



ROW 1

1

5


3

7



ROW 2

2

10


5

8



ROW 3

3

15


1

4



ROW 4

4

7


6

9

Paul_Hossler
06-15-2017, 08:52 AM
You'd do better to attach an Excel Macro workbook with the data, the macros, and the results in the XLSM, not in a Word document

(especially the Expected Results)

V_F
06-15-2017, 01:11 PM
Sure, attached is the macro. I want the user to be able to select the highlighted columns in the respected refedits and then the results to spit out where the user specifies in the output refedit.19518

mdmackillop
06-16-2017, 02:32 AM
This attempts to loop through each row. The result may be wrong, but the code should show the methodology.

Private Sub ComputeButton_Click() Dim A As String, B As String, C As String, D As String, E As String, Num1 As Range, Denom1 As Range, Num2 As Range, Denom2 As Range, Output As Range, Lift As Double, PooledSampleProp As Double, StndError As Double, TestStat As Double, pValue As Double, Incidence1 As Double, Incidence2 As Double, Index As Double
Dim x
A = Sample1Numerator.Value
B = Sample1Denominator.Value
C = Sample2Numerator.Value
D = Sample2Denominator.Value
E = OutputRange.Value
x = Range(A).Cells.Count
For i = 1 To x
If Range(A)(i) <> "" Then
Set Num1 = Range(A)(i)
Set Denom1 = Range(B)(i)
Set Num2 = Range(C)(i)
Set Denom2 = Range(D)(i)
Set Output = Range(E)(1) 'Does not increment
Incidence1 = Num1 / Denom1
Incidence2 = Num2 / Denom2
If Incidence2 <> 0 Then Index = (Incidence1 / Incidence2) * 100 Else Index = 0
Lift = (Incidence1 - Incidence2) * 100
PooledSampleProp = (Application.WorksheetFunction.Sum(Application.WorksheetFunction.Product(De nom1, Incidence1), Application.WorksheetFunction.Product(Denom2, Incidence2))) / Application.WorksheetFunction.Sum(Denom1, Denom2)
StndError = Sqr(Application.WorksheetFunction.Product(Application.WorksheetFunction.Pro duct(PooledSampleProp, (1 - PooledSampleProp)), Application.WorksheetFunction.Sum(1 / Denom1, 1 / Denom2)))
TestStat = (Incidence1 - Incidence2) / StndError
If Lift > 0 Then pValue = 1 - (WorksheetFunction.Norm_S_Dist(Arg1:=TestStat, Arg2:=True)) Else pValue = (WorksheetFunction.Norm_S_Dist(Arg1:=TestStat, Arg2:=True))
Output.Offset(i, 0).NumberFormat = "0.0%"
Output.Offset(i, 1).NumberFormat = "0.0%"
Output.Offset(i, 0) = Incidence1
Output.Offset(i, 1) = Incidence2
Output.Offset(i, 3) = Index
Output.Offset(i, 4) = Lift
Output.Offset(i, 5) = PooledSampleProp
Output.Offset(i, 6) = StndError
Output.Offset(i, 7) = TestStat
Output.Offset(i, 8) = pValue
If pValue < 0.05 Then Output.Offset(i, 9) = "Yes" Else Output.Offset(i, 9) = "No"
If Output.Offset(i, 9) = "Yes" Then Output.Offset(i, 9).Interior.ColorIndex = 35
If Output.Offset(i, 9) = "No" Then Output.Offset(i, 9).Interior.ColorIndex = 38
If pValue < 0.1 Then Output.Offset(i, 10) = "Yes" Else Output.Offset(i, 10) = "No"
If Output.Offset(i, 10) = "Yes" Then Output.Offset(i, 10).Interior.ColorIndex = 35
If Output.Offset(i, 10) = "No" Then Output.Offset(i, 10).Interior.ColorIndex = 38
If pValue < 0.15 Then Output.Offset(i, 11) = "Yes" Else Output.Offset(i, 11) = "No"
If Output.Offset(i, 11) = "Yes" Then Output.Offset(i, 11).Interior.ColorIndex = 35
If Output.Offset(i, 11) = "No" Then Output.Offset(i, 11).Interior.ColorIndex = 38
If pValue < 0.2 Then Output.Offset(i, 12) = "Yes" Else Output.Offset(i, 12) = "No"
If Output.Offset(i, 12) = "Yes" Then Output.Offset(i, 12).Interior.ColorIndex = 35
If Output.Offset(i, 12) = "No" Then Output.Offset(i, 12).Interior.ColorIndex = 38
End If
Next i
Output = "Proportion 1"
Output.Offset(0, 1) = "Proportion 2"
Output.Offset(0, 3) = "Index"
Output.Offset(0, 4) = "Lift"
Output.Offset(0, 5) = "Pooled Sample Proportion"
Output.Offset(0, 6) = "Standard Error"
Output.Offset(0, 7) = "Test Statistic"
Output.Offset(0, 8) = "PValue"
Output.Offset(0, 9) = "(Sig @95%)"
Output.Offset(0, 10) = "Sig @90%)"
Output.Offset(0, 11) = "Sig @85%)"
Output.Offset(0, 12) = "Sig @80%)"
Unload Me


End Sub

V_F
06-16-2017, 06:05 AM
Hi,
Thank you for this. A couple follow up questions. Would I go ahead and create a new variable for each range to loop through? For example 'x = Range(A).Cells.Count', 'y=Range(B).Cells.Count', 'z=Range(C).Cells.Count' and 'zz=Range(D).Cells.Count'? And then after each one of those I would insert the following code, but just replace the A with the respective range? 'For i = 1 To x
If Range(A)(i) <> "" Then
Set Num1 = Range(A)(i)
Set Denom1 = Range(B)(i)
Set Num2 = Range(C)(i)
Set Denom2 = Range(D)(i)
Set Output = Range(E)(1) 'Does not increment
Incidence1 = Num1 / Denom1
Incidence2 = Num2 / Denom2
If Incidence2 <> 0 Then Index = (Incidence1 / Incidence2) * 100 Else Index = 0
Lift = (Incidence1 - Incidence2) * 100
PooledSampleProp = (Application.WorksheetFunction.Sum(Application.WorksheetFunction.Product(De nom1, Incidence1), Application.WorksheetFunction.Product(Denom2, Incidence2))) / Application.WorksheetFunction.Sum(Denom1, Denom2)
StndError = Sqr(Application.WorksheetFunction.Product(Application.WorksheetFunction.Pro duct(PooledSampleProp, (1 - PooledSampleProp)), Application.WorksheetFunction.Sum(1 / Denom1, 1 / Denom2)))
TestStat = (Incidence1 - Incidence2) / StndError
If Lift > 0 Then pValue = 1 - (WorksheetFunction.Norm_S_Dist(Arg1:=TestStat, Arg2:=True)) Else pValue = (WorksheetFunction.Norm_S_Dist(Arg1:=TestStat, Arg2:=True))
Output.Offset(i, 0).NumberFormat = "0.0%"
Output.Offset(i, 1).NumberFormat = "0.0%"
Output.Offset(i, 0) = Incidence1
Output.Offset(i, 1) = Incidence2
Output.Offset(i, 3) = Index
Output.Offset(i, 4) = Lift
Output.Offset(i, 5) = PooledSampleProp
Output.Offset(i, 6) = StndError
Output.Offset(i, 7) = TestStat
Output.Offset(i, 8) = pValue
If pValue < 0.05 Then Output.Offset(i, 9) = "Yes" Else Output.Offset(i, 9) = "No"
If Output.Offset(i, 9) = "Yes" Then Output.Offset(i, 9).Interior.ColorIndex = 35
If Output.Offset(i, 9) = "No" Then Output.Offset(i, 9).Interior.ColorIndex = 38
If pValue < 0.1 Then Output.Offset(i, 10) = "Yes" Else Output.Offset(i, 10) = "No"
If Output.Offset(i, 10) = "Yes" Then Output.Offset(i, 10).Interior.ColorIndex = 35
If Output.Offset(i, 10) = "No" Then Output.Offset(i, 10).Interior.ColorIndex = 38
If pValue < 0.15 Then Output.Offset(i, 11) = "Yes" Else Output.Offset(i, 11) = "No"
If Output.Offset(i, 11) = "Yes" Then Output.Offset(i, 11).Interior.ColorIndex = 35
If Output.Offset(i, 11) = "No" Then Output.Offset(i, 11).Interior.ColorIndex = 38
If pValue < 0.2 Then Output.Offset(i, 12) = "Yes" Else Output.Offset(i, 12) = "No"
If Output.Offset(i, 12) = "Yes" Then Output.Offset(i, 12).Interior.ColorIndex = 35
If Output.Offset(i, 12) = "No" Then Output.Offset(i, 12).Interior.ColorIndex = 38
End If
Next i'?

Sorry, I'm slightly new to VBA and just starting learning a couple days ago.
Thanks again for all of your help!

mdmackillop
06-16-2017, 06:25 AM
The code as written should compare Row 1 values for Sample1 1 & 2, then Row 2 values and so on.
Do you want to compare each Sample 1 against each Sample 2 ie 10 x 10 combinations to produce 100 results?

V_F
06-16-2017, 06:47 AM
Hi,

So I got it to work by doing what I had previously mentioned. Creating the new variable for each loop range and then having an if...end if for each iteration. Works like a charm and the results seem to check out!

Thank you again for all of your help.