Just random mumbling: Automating Anderson Darling Test using VBA

Showing posts with label Automating Anderson Darling Test using VBA. Show all posts

Wednesday, February 20, 2019

Automating Anderson Darling Test using VBA,VBA Teacher Sourav,Kolkata 08910141720

Sub anderson()
'
' Macro1 Macro
'

'

Sheets("anderson").Select

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Enter the data into column E"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Average"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C[3]:R[198]C[3])"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Sigma"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=STDEV(R[-2]C[3]:R[197]C[3])"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "n"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(R[-3]C[3]:R[196]C[3])"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "S"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-5]C[9]:R[194]C[9])"
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "AD"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=-R[-3]C-R[-1]C/R[-3]C"
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "AD*"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C*(1+0.75/R[-4]C+2.25/R[-4]C^2)"
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "p Value"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "=MAX(R[5]C:R[8]C)"
    Range("A14").Select
    ActiveCell.FormulaR1C1 = "p Value Calculations"
    Range("A15").Select
    ActiveCell.FormulaR1C1 = "p"
    Range("A16").Select
    ActiveCell.FormulaR1C1 = "p"
    Range("A17").Select
    ActiveCell.FormulaR1C1 = "p"
    Range("A18").Select
    ActiveCell.FormulaR1C1 = "p"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(R[-6]C<13>= 0.6),EXP(1.2937-5.709*R[-6]C+0.0186*R[-6]C^ 2),0)"
    Range("B16").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(R[-7]C<0 .6="">=0.34),EXP(0.9177-4.279*R[-7]C-1.38*R[-7]C^2),0)"
    Range("B17").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(R[-8]C<0 .34="">=0.2),1-EXP(-8.318+42.796*R[-8]C-59.938*R[-8]C^2),0)"
    Range("B18").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(R[-9]C<0 .2="" br="">    Range("B19").Select

    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Value"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "i"
   'macro4

   Range("F1").Select

    ActiveCell.FormulaR1C1 = "i"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",1)"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",R[-1]C+1)"
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F153")

    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Sorted"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-2]), NA(),SMALL(R2C[-2]:R201C[-2],RC[-1]))"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G151")

    Range("H1").Select
    ActiveCell.FormulaR1C1 = "F(Xi)"
    With ActiveCell.Characters(Start:=1, Length:=3).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(Start:=4, Length:=2).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = True
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-3]),"""",NORMDIST(RC[-1], R3C2, R4C2, TRUE))"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H153")

    Range("I1").Select
    ActiveCell.FormulaR1C1 = "1-F(Xi)"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(Start:=6, Length:=2).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = True
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-4]), """", 1-RC[-1])"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I153")

    Range("J1").Select
    ActiveCell.FormulaR1C1 = "1-F(Xn-i+1)"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveCell.Characters(Start:=6, Length:=6).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = True
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-5]),"""",SMALL(R2C[-1]:R201C[-1],RC[-4]))"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J153")
    Range("J2:J153").Select
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "S"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-6]),"""",(2*RC[-5]-1)*(LN(RC[-3])+LN(RC[-1])))"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K153")
    Range("K2:K153").Select
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "z"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-7]="""",NA(),NORMSINV((RC[-6]-0.3)/(R5C2+0.4)))"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L151")
    Range("L2:L151").Select
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M4").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M5").Select

    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("M4").Select
    ActiveCell.FormulaR1C1 = "=RC[1]*R4C2+R3C2"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "-3"
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("N4").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("N5").Select

    Dim ch As Shape
   Range("G2:G201,L2:L201").Select
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
     Set ch = ActiveSheet.Shapes(1)
    ch.Name = "Chartanderson"


    Application.CommandBars("Format Object").Visible = False
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    With ActiveChart
'chart name
.HasTitle = True
.ChartTitle.Characters.Text = "Normal Probability Test"
'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X"
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Z"
End With

Range("A1").Select

End Sub