Runge Kutta in Excel 5

Using Runge Kutta in Microsoft Excel 5.0

Here we will learn how to use Excel macros to solve initial value problems. The simplest macro takes an initial value for a single step of a 4th order Runge Kutta scheme and returns the end value of the dependent variable. We can then simply call this macro repeatedly in a spreadsheet to generate a full solution to an ordinary differential equation. The macro would usually appear as:

Function f(t, y, dt)
f = 2 * y * t
End Function

Function rk(t, y, dt)
    k1 = dt * f(t, y)
    k2 = dt * f(t + dt / 2, y + k1 / 2)
    k3 = dt * f(t + dt / 2, y + k2 / 2)
    k4 = dt * f(t + dt, y + k3)
    y = y + (k1 + 2 * (k2 + k3) + k4) / 6
End Function

In the spreadsheet, we simply place the initial values for t and y, choose a time step, and make repeated calls to this function (rk) to produce a solution.

A second approach is to use subroutines (Subs), rather than functions. A Sub is much like a function macro, but it does not return a value to the spreadsheet. We will have to write the results directly back to the spreadsheet.

Before we use a Sub to solve differential equations, we'll practice with something similar. We will look at an example that writes a two-column spreadsheet which evaluates and lists a function of some variable X. The Sub below will accomplish this. Look at it first, and then read the explanation of how it works.

Function f(x)
    f = x ^ 2 + 3
End Function

Sub writeit()
    NumPoints = 21
    XNot = 0
    dX = 0.1
    ActiveCell.Value = "X"
    ActiveCell.Offset(0, 1).Value = "F(X)"
    x = XNot
    For i = 1 To NumPoints
        fnc = f(x)
        ActiveCell.Offset(i, 0).Value = x
        ActiveCell.Offset(i, 1).Value = fnc
        x = x + dX
    Next
End Sub

The first three lines set up the parameters. They indicate that we will plot 21 points starting with X = 0 and incrementing by 0.1. The next two lines write labels for X and for the function F(X). Note that ActiveCell refers to the cell that is active in the spreadsheet when the macro (Sub) is run. Also, ActiveCell.Offset(0, 1) refers to the cell just to the right of the active cell because it offsets one cell from the active cell in the column direction. The Sub then sets the initial X value and loops through all the points to calculate and write results for F(X).

To run this macro, type it into a macro sheet, click on a cell in a worksheet, and click Tools/Macro. This will bring up a dialog box that contains the name of your macro. Double-click on that macro name and it will execute.

Sometimes we would rather have the macro always start in the same cell, rather than starting in the active cell. To accomplish this, we select a cell by inserting a command such as [A12].Select in the macro. This will make cell A12 the active cell.

One more variation is to add a button in the spreadsheet to run the macro. To do this, you

Click View/Toolbars and tell the dialog box to show you the Forms Toolbar.
Activate a spreadsheet (not a macro module).
On the Forms Toolbar, click on the Button button (second row, second column).
Go to the spreadsheet and trace out the button. You can double click on the button's label if you want to change it.
When you define the button you'll be asked to attach a macro to it. This macro will be run when the button is pressed.

Using these techniques to solve ordinary differential equations: Here we'll write a macro that will solve the whole ODE at once. The techniques are the same as above, but we have to adapt the Sub from evaluating a function to stepping through the solution of an ODE. We will have to input the initial value for y, the time at which we wish to stop, and the time step (or number of steps). To accomplish this, we write a subroutine that looks something like the following:

Sub RungeKutta ()
first set constants
then loop through all steps, calling rk(h, y, t) in each step
then write the results back to the spreadsheet
End Sub

You'll have to figure out how to do the internals. For the loop, you can use any of a variety of loops. Choose whichever you prefer. I would suggest that you do this for a single first order ODE and then progress to doing systems of ODE's.

One thing to note is that the rk routine is now probably best rewritten as a Sub, rather than as a Function. For the second-order equations, this routine might look something like:



Sub rk2(t, y, z, dt)
    k1 = dt * f(t, y, z)
    l1 = dt * g(t, y, z)
    k2 = dt * f(t + dt / 2, y + k1 / 2, z + l1 / 2)
    l2 = dt * g(t + dt / 2, y + k1 / 2, z + l1 / 2)
    k3 = dt * f(t + dt / 2, y + k2 / 2, z + l2 / 2)
    l3 = dt * g(t + dt / 2, y + k2 / 2, z + l2 / 2)
    k4 = dt * f(t + dt, y + k3, z + l3)
    l4 = dt * g(t + dt, y + k3, z + l3)
    y = y + (k1 + 2 * (k2 + k3) + k4) / 6
    z = z + (l1 + 2 * (l2 + l3) + l4) / 6
End Sub

This is particularly useful because the subroutine can return values for both y and z, allowing a less cumbersome treatment of the boundary conditions. To call this routine from within the main routine, use a statement like Call rk2(t, y, z, dt) . This will send values for all the arguments and return new values for y and z.

Getting constants from the spreadsheet: One final item. It would be nice to set all the constants within the spreadsheet. This can be done by having the macro get the constants off the sheet. To accomplish this,

Give a name to the cells you'll want to access in the macro. This is done with Insert/Name/define. We'll assume the cell is named TimeStep.
Now in the macro you can do something like h=Range("TimeStep").Value in your macro. This sets the value of h to whatever was in the cell on the spreadsheet.
You can do the same with all the variables.

© 1997-98 by James Blanchard, University of Wisconsin-Madison. This site was supported by Hewlett Packard and by the CIC.