How to Integrate a Series

[elliott10]

hi, I am new to coding/programming. How do I write a macro to integrate a series of n data points using an unknown function y=f(x)?
criteria includes the following:

Show a button to run your macro
Assume there are ten x/y pairs in the spreadsheet, starting with the first x value in cell A1, and
the first y value in cell B1, and the remaining data points organized vertically downward, with all
x-values in column A and y values in column B.
The x-values are equidistant (h= 1,2,....,etc I'm guessing)
All x and y values are equal or larger than zero. There are no empty spreadsheet cells or
irregular entries (letters and such) among the data set.
To approximate the integral, calculate and sum the areas of rectangles of area A=yi * deltaX
where yi is the right hand boundary of each interval bounded by two x-values (see posted
pictures of algorithm). deltaX is the distance between two adjacent x-values.
Output the value of the integral via a message box after the integration

I started with the following:

Function Unknown(x)

y = f(x)

End Function

(new module)

Option Explicit
Sub Eurler()
'This sets the integration limit from 1 to 10

xi = 0
xf = 10
h = 0.1

'This is the number of caluclations it will run

a = (xf - xi) / h





End Sub

 

[CSmith]

No pics?

 

[elliott10]

No pics?

Picture of what?

 

[elliott10]

No pics?

Option Explicit
Sub eulerint()
Dim xold, yold, ynew, slope, step As Single, k As Integer

Sheet1.Activate


xold = 0
yold = 2
step = 1
'k is a counter for the output row
k = 1


While xold < 11



'calculate the slope at the current x-value
slope = xold ^ 3


'calculate the new y-value by adding the rise to the old y value
ynew = yold + slope * step
'output to Column B aka Column 2
Cells(k, 2) = ynew
Range("A1:A11") = xold + step


'determine New Values before executing the loop again
k = k + 1
yold = ynew
xold = k + 1

Wend


End Sub


This is what I have so far. It doesn't make a ton of sense to me, though. I don't really know what it should look like on excel. Any insight would help tons. Thanks!

 

[CSmith]

Looks about right for what a search on euler's method shows...
I would try, and it should build out columns A & B

Range("A" & k) = xold + step

 

[elliott10]

Looks about right for what a search on euler's method shows...
I would try, and it should build out columns A & B

Range("A" & k) = xold + step

Is this how you would integrate y=f(x)?

Looks about right for what a search on euler's method shows...
I would try, and it should build out columns A & B

Range("A" & k) = xold + step

It said there was an overflow when I added that code in replace of xold = k + 1. Also- how would I add a message box that would show the last output for the series?

 

[elliott10]

No pics?

 

[CSmith]

Was there a particular line you got the overflow error?

 

[CSmith]

Oooo missed it... STEP is VB keyword use a different variable name

 

[CSmith]

Sub eulerint()
  Dim xold, yold, ynew, slope, step As Single, k As Integer

  Sheets("Euler").Activate

  xold = 0
  yold = 2
  h = 1
  'k is a counter for the output row
  k = 1

  While xold < 11

    'calculate the slope at the current x-value
    slope = xold ^ 3

    'calculate the new y-value by adding the rise to the old y value
    ynew = yold + slope * h
  
    'output to Column B aka Column 2
    Cells(k, 2) = ynew
    Range("A" & k) = xold + h

    'determine New Values before executing the loop again
    k = k + 1
    yold = ynew
    xold = k + 1
  Wend
End Sub

Book1
	A	B
1	1	2
2	4	29
3	5	93
4	6	218
5	7	434
6	8	777
7	9	1289
8	10	2018
9	11	3018
Euler