[Contribution] Use Excel regular formulas in VBA

Because I got a lot of help from here, I will contribute my knowledge

PHP Code: 

Dim i As Integer
Dim LastRow As Integer



Dim avReturn As Double
Dim stDev As Double
Dim vrnc As Double
Dim Average As Double
Dim dblAverage As Double



If Sheets("Sheet1").Range("B6") = Sheets("Sheet1").Range("G10") Then Sheets("Data").Range("A1:G5536").Copy Destination:=Sheets("Index").Range("A:G")

If Sheets("Sheet1").Range("B6") = Sheets("Sheet1").Range("G10") Then Sheets("Sheet1").Range("G11") = Sheets("Data").Range("B2")



 
 


LastRow = Sheets("Data").UsedRange.Row - 2 + Sheets("Data").UsedRange.Rows.Count

dblAverage = Application.WorksheetFunction.Average(Worksheets("Data").Range("B1:B8000"))
hgAverage = Application.WorksheetFunction.Average(Worksheets("Data").Range("C1:C8000"))
lwAverage = Application.WorksheetFunction.Average(Worksheets("Data").Range("D1:D8000"))
CLAverage = Application.WorksheetFunction.Average(Worksheets("Data").Range("E1:E8000"))

CLMax = Application.WorksheetFunction.Max(Worksheets("Data").Range("E1:E8000"))
OPMax = Application.WorksheetFunction.Max(Worksheets("Data").Range("B1:B8000"))
HgMax = Application.WorksheetFunction.Max(Worksheets("Data").Range("C1:C8000"))
LwMax = Application.WorksheetFunction.Max(Worksheets("Data").Range("D1:D8000"))
CLMin = Application.WorksheetFunction.Min(Worksheets("Data").Range("E1:E8000"))
OPMin = Application.WorksheetFunction.Min(Worksheets("Data").Range("B1:B8000"))
HgMin = Application.WorksheetFunction.Min(Worksheets("Data").Range("C1:C8000"))
LwMin = Application.WorksheetFunction.Min(Worksheets("Data").Range("D1:D8000"))
GLP = (Sheets("Data").Range("E" & LastRow) - Sheets("Data").Range("B2")) / Sheets("Data").Range("B2")



CLStd = Application.WorksheetFunction.StDev_P(Worksheets("Data").Range("E1:E8000"))
OPStd = Application.WorksheetFunction.StDev_P(Worksheets("Data").Range("B1:B8000"))
HgStd = Application.WorksheetFunction.StDev_P(Worksheets("Data").Range("C1:C8000"))
LwStd = Application.WorksheetFunction.StDev_P(Worksheets("Data").Range("D1:D8000"))
AHAO = (Sheets("Sheet1").Range("B17") - Sheets("Sheet1").Range("B16")) / Sheets("Sheet1").Range("B16")
ALAO = (Sheets("Sheet1").Range("B18") - Sheets("Sheet1").Range("B16")) / Sheets("Sheet1").Range("B16")
ACAO = (Sheets("Sheet1").Range("B19") - Sheets("Sheet1").Range("B16")) / Sheets("Sheet1").Range("B16")

avvl = Application.WorksheetFunction.Average(Worksheets("Data").Range("F1:F8000"))
Skew = Application.WorksheetFunction.Skew(Worksheets("Data").Range("E1:E8000"))
Kurt = Application.WorksheetFunction.Kurt(Worksheets("Data").Range("E1:E8000"))
Suml = Application.WorksheetFunction.Sum(Worksheets("Data").Range("F1:F8000"))




Sheets("Data").Range("w1") = "Weighted average"
Sheets("Data").Range("X1") = "# Data"


Sheets("Data").Range("X2") = LastRow



For i = 3 To LastRow
    Sheets("Data").Range("V" & i) = (Sheets("Data").Range("E" & i - 1) - Sheets("Data").Range("E" & i)) / Sheets("Data").Range("E" & i - 1)
Next i















Sheets("Sheet1").Range("B16") = dblAverage
Sheets("Sheet1").Range("B17") = hgAverage
Sheets("Sheet1").Range("B18") = lwAverage
Sheets("Sheet1").Range("B19") = CLAverage
Sheets("Sheet1").Range("B23") = CLMax
Sheets("Sheet1").Range("B20") = OPMax
Sheets("Sheet1").Range("B21") = HgMax
Sheets("Sheet1").Range("B22") = LwMax
Sheets("Sheet1").Range("D23") = CLMin
Sheets("Sheet1").Range("D20") = OPMin
Sheets("Sheet1").Range("D21") = HgMin
Sheets("Sheet1").Range("D22") = LwMin
Sheets("Sheet1").Range("D16") = GLP
Sheets("Sheet1").Range("F23") = CLStd
Sheets("Sheet1").Range("F20") = OPStd
Sheets("Sheet1").Range("F21") = HgStd
Sheets("Sheet1").Range("F22") = LwStd
Sheets("Sheet1").Range("F16") = AHAO
Sheets("Sheet1").Range("F17") = ALAO
Sheets("Sheet1").Range("F18") = ACAO

Sheets("Sheet1").Range("h16") = avvl
Sheets("Sheet1").Range("G8") = Sheets("Data").Range("E" & LastRow)
Sheets("Sheet1").Range("G7") = Sheets("Data").Range("B2")
Sheets("Sheet1").Range("h20") = Skew
Sheets("Sheet1").Range("h21") = Kurt
Sheets("Data").Range("AG2") = Suml



Sheets("Sheet1").Range("h25") = Sheets("Data").Range("N" & LastRow) 


Hi Joejang,

Thanks to sharing you knowledge with us!!

Can you please share with us Excel File as well. Also please provide us a small description so that we will be able to understand this coding easily (eg. That what we can get by using this VBA Code, how we should apply etc.)

Thanks
Nisha