Search code examples
excelvbaintegrationworksheet-function

WorksheetFunction.Sum with For loop

Screenshot

Hi! I'm posting this question in the hope it hasn't already been answered!

As you can see in the image, I would like to get the area under the curve using VBA. The results of the trapezoid method using the simple formula (B4 - B3) * (C4 + C3) / 2 are given in the column E (light yellow shade) and the sum is given in the cell F4, which is the area I want.

To test wether my VBA code works, I limited it to calculate the sum of the first two trapezoids, but it gives me the surface of the second trapezoid instead:

Sub Integral()

Dim i As Integer
Dim Integral As Double

For i = 1 To 2

    Integral = Application.WorksheetFunction.Sum((Cells(3 + i, 2) - Cells(2 + i, 2)) * (Cells(3 + i, 3) + Cells(2 + i, 3)) / 2)

Next i

Sheet1.Cells(4, 7) = Integral

End Sub

I believe the solution is simple, but at the moment, nothing comes to my mind! I would appreciate it if someone showed me how to solve this! Thank you in advance!

Solution

  • You dont have an accululative sum after each iteration of the loop

    Sub Integral()

Dim i As Integer
Dim Integral As Double
Dim Sum_Area as Double 

For i = 1 To 2

    Integral = (Cells(3 + i, 2) - Cells(2 + i, 2)) * (Cells(3 + i, 3) + Cells(2 + i, 3)) / 2
    Sum_Area = Sum_Area + Integral
Next i

Sheet1.Cells(4, 7) = Sum_Area

End Sub