The euler's spreadsheet calculator using visual basic programming for solving ordinary differential equations

VOL. 11, NO. 20, OCTOBER 2016 I SSN 1819- 6608 ARPN Jour nal of Engineering and Applied Sciences © 2006- 2016 Asian Resear ch Publishing Net wor k ( ARPN) . All right s reser ved. www.arpnjournals.com THE EULER’S SPREADSHEET CALCULATOR USING VISUAL BASIC PROGRAMMING FOR SOLVING ORDINARY DIFFERENTIAL EQUATIONS 1Department Kim Gaik Tay1, Sie Long Kek2, Tau Han Cheong3 and Rosmila Abdul-Kahar2 of Communication Engineering, Faculty of Electrical and Electronic Engineering, Universiti Tun Hussein Onn Malaysia 2Department of Mathematics & Statistics, Faculty of Science, Technology and Human Development, Universiti Tun Hussein Onn Malaysia 3Faculty of Education, Universiti Teknologi Mara, Malaysia E-Mail: tay@uthm.edu.my ABSTRACT Motivated by the works of a Richardson’s Extrapolation spreadsheet calculator for differentiation, we have developed the Euler’s spreadsheet calculator using Visual Basic (VBA) programming to solve ordinary differential equations (ODEs). Users simply need to enter the independent and dependent variables used, a starting value and ending value for the independent variable, an initial value for the dependent variable, the step size, the ODE and exact function for the ODE. Lastly click the APPLY button which is associated with the VBA programming written to solve the ODEs by the Euler’s method, and finally its full solution is automatically calculated and displayed. Hopefully, this Euler’s ODEs spreadsheet calculator can help educators to prepare their marking scheme easily and assist students in checking their answers. Keywords: Excel spreadsheet, ordinary differential equations, spreadsheet calculator, Euler’s method. INTRODUCTION The computing approaches of the ordinary differential equations (ODEs) can be roughly divided into the exact solution method and the numerical method. Since the use of the exact solution method is limited to the linear ODEs, the application of the numerical method is seen to be practical in solving engineering problems. This is because the exact solution of the nonlinear ODEs is difficult to be analysed and its existence might be questioned. In view of these, the applications of the numerical method could approximate the solution of the ODEs, particularly for the nonlinear ODEs. As such, it is necessary to develop a tool in order to solve the ODEs easily. A series of papers [1-3] working on Excel spreadsheet calculators focused on numerical differentiation. None of the literature works have dealt with Excel spreadsheet calculator for solving ODEs yet. Tay et. al [4] demonstrated on how to solve a system of two first-order ODEs via RK4 method using Microsoft Excel features namely relative rows, relative columns, fixed rows and fixed columns in a standard spreadsheet interface without using VBA programming. Hence paper [4] did not include development of a spreadsheet calculator. Each time a new system of ODEs need to be solved, users have to retype all commands in a standard spreadsheet interface. Tips in [4] are suitable in a step-by step teaching and learning environment, where users can experience the RK4 algorithm themselves from scratch in a standard spreadsheet interface. The work of [5] demonstrates how to solve an initial-value problem of first order differential equation, dy  x  y, y (0)  1 using Excel spreadsheet. In this dx dy and new approximate y are put dx in columns B, C, D and E respectively. The initial values are put in row 5. The new approximate y is solved using Euler’s method. The work of [6] displays how to solve an initialvalue problem of first order differential equation, dy  ry , y (0)  2 with exact solution y  Aerx using dx Excel spreadsheet. In this paper, variables x, exact y and y are put in columns B, C, D respectively, while initial conditions are put in row 1. New approximate y is solved starting from Row 2 using Euler’s method. The Youtube video [7] demonstrates how to solve an initial-value problem of first order differential equation, dy  x 2  y 2 , y (0)  0.5 using Excel spreadsheet. In dx dy this video, variables xn , yn , xn 1 ,  f ( xn , yn ) and new dx approximate yn , are put in columns A, B, C, D and E respectively. The initial values are put in row 6. The new approximate yn , is solved using Euler’s method. The paper, variables x, y, work of [7] is similar to the work of [5] except for the extra variables xn 1 column. The work of [8] shows how to solve an initialvalue problem of second order differential equation of d 2 g    0 via Euler simple pendulum equation, dt 2 L method by using Excel spreadsheet. In order to solve this, d variables time t, position , velocity and acceleration dt 11819 VOL. 11, NO. 20, OCTOBER 2016 I SSN 1819- 6608 ARPN Jour nal of Engineering and Applied Sciences © 2006- 2016 Asian Resear ch Publishing Net wor k ( ARPN) . All right s reser ved. www.arpnjournals.com d 2 are put into columns A, B, C and D respectively in dt 2 Excel spreadsheet. Later, the initial conditions are placed in row 4. The subsequent time step solutions are solved using Euler’s method. The works of Euler’s method for solving ODEs using spreadsheets found in [5-8] focus on an intuitive way to implement the Euler’s method for ODEs using spreadsheet without using VBA programming. For a new ODE, users have to re-implement the Euler’s algorithm and re-drag and drop (to copy down the formula implemented in spreadsheet) which is inconvenient for users already familiar with Euler’s methods for ODEs who just require its full solutions. Their works are suitable in teaching and learning environment where students have to experience implementation of Euler’s algorithm in spreadsheet intuitively instead of coding Euler’s algorithm in the old-fashioned computer programs such as in C programming, Matlab, Maple and etc. Their works did not include development of spreadsheet calculator. In our definition, a spreadsheet calculator is easy to use without having to type any commands in the spreadsheet. Users only need to input the required information, and then its full solution will automatically be calculated. The calculation is done via the numerical algorithm coded in VBA programming. Spreadsheet calculator is suitable for repetitive needs of its full numerical solution without coding its numerical algorithm by the users. Thus, the aim of this paper is to develop the Euler’s spreadsheet calculator using VBA programming to solve ODEs for users who need its full solution directly without implementing its numerical algorithm. Euler’s method for ordinary differential equations Consider a general form of the first-order ordinary differential equation given below: dy  f ( x, y ) dx (1) with the initial value y( x0 )  y0 for the interval x0  x  xn . Here, x is the independent variable, y is the dependent variable, n is the number of point values, and f is the function of the derivation. The aim is to determine the unknown function y ( x ) whose derivative satisfies Equation. (1) and the corresponding initial value. In doing so, the interval x0  x  xn is discretized to be x0 , x1  x0  h, x2  x0  2h, ..., xn  x0  nh (2) where h is the fixed step size. On this basis, the unknown function y ( x ) can be written as the first-order Taylor series approximation at the corresponding points as given in Equation. (2), given below: y ( x0 )  y0 y ( x1 )  y ( x0 )  hf ( x0 , y ( x0 )), h  x1  x0 y ( x2 )  y ( x1 )  hf ( x1 , y ( x1 )), h  x2  x1  (3) y ( xn 1 )  y ( xn  2 )  hf ( xn  2 , y ( xn  2 )), h  xn 1  xn  2 y ( xn )  y ( xn 1 )  hf ( xn 1 , y ( xn 1 )), h  xn  xn 1 Notice that Equation. (3) can simply be formulated as yi 1  yi  hf ( xi , yi ), i  0,1, 2, n (4) Hence, the Euler’s method, which is defined by Equation. (4), gives the numerical solution of Equation. (1) in order to determine the unknown function y ( x ) . NUMERICAL EXAMPLE For illustration, consider the RC-circuit as shown in Figure-1 given by [9]. E(t) = sin100t V  C=0.1 F R= 5 Figure-1. RC-circuit. The governing first-order ordinary differential equation is given by q Ri   E (t ) C 1 Ri   i dt  E (t ) C R di i dE (t )   dt C dt (5) where R is the resistance (ohms), i is the current (ampere), q is the electric charge (coulomb), C is the di is capacitance (farads), E (t ) is the voltage (volts), dt dE (t ) rate of change of current and is rate of change of dt voltage. ohms, Given E (t )  sin(100t ) volts, R  5 C  0.1 farads and at the initial time t  0 the initial current is i = 0. The differential Equation. (5), which is the RC-circuit ODE, for time interval 0  t  5 seconds with the time step size t  0.01 seconds is solved by using the Euler’s method. If the exact solution is given by 11820 VOL. 11, NO. 20, OCTOBER 2016 I SSN 1819- 6608 ARPN Jour nal of Engineering and Applied Sciences © 2006- 2016 Asian Resear ch Publishing Net wor k ( ARPN) . All right s reser ved. www.arpnjournals.com i 10 500 10 2t cos100t  sin100t  e , 2501 2501 2501 (6) then the absolute errors are calculated at each iteration. The computation procedure of the Euler’s method is summarized as follows: Euler’s solution method Step 1: Rewrite the ODE in (5) by substituting the given values. That is, di 100 cos100t i    20 cos100t  2i  f (t , i ) 5 5(0.1) dt (7) Step 2: By using the Euler’s method, formulate Equation. (7) into the form of Equation. (4), given by ik 1  ik  hii  ik  hf (tk , ik ) ik 1  ik  0.01(20 cos100t  2ik ) with h  t  0.01 and i  0 when t  0 Step 3: Apply the Euler’s spreadsheet calculator, which is discussed in the next section, to obtain the numerical solution. The Euler’s spreadsheet calculator for solving odes In this section, the use of the Euler’s spreadsheet calculator is discussed. Figure-2 illustrates the Euler’s spreadsheet calculator. To use this Euler’s spreadsheet calculator, firstly, users enter the independent and dependent variables into the cells C4 and D4 respectively. Secondly, the initial values for both of the mentioned variables are entered into the cells B6 and C6 respectively, whereas the ending value of the independent variable is entered into the cell G6. Thirdly, the step size h is entered into the cell H6. Fourthly, the ODE given by (7) is entered into the cell D7 naturally via programming syntax or mathematical form instead of using Excel command. Fifthly, the exact solution of the ODE, which is given by (6), is entered into the cell D6. Users can select the desired accuracy upon the number of decimal places, which are ranged from one to nine decimal places from the drop down menu in cell I10. Finally, click the APPLY button in the cell D10, which is associated with the VBA programming for the computation of the Euler’s method. Then, the numerical solution of the ODE will be shown at the bottom of the spreadsheet as given in Figure-2. For other ODEs, the same steps are repeated. Figure-2. The Euler’s spreadsheet calculator for solving ODEs. CONCLUSIONS In this paper, a spreadsheet calculator, which applies the Euler’s method for solving the ODEs, was developed. In this spreadsheet calculator design, the utility of the VBA programming is employed to simplify the use of the spreadsheet calculator. This spreadsheet calculator is very user friendly since users only need to enter relevant information to compute the full solution of the ODEs which will then be displayed to the users. In future, a spreadsheet calculator for solving ODEs using other numerical methods will be developed. This spreadsheet calculator can serve as a tool for educators and students who need its full solution of the ODEs using the Euler’s method. ACKNOWLEDGEMENTS This work is fully supported by UTHM Multi Disciplinary Research grant MDR vote 1109. REFERENCES [1] Tay K. G., Kek S. L. Abdul-Kahar R., Azlan M. A., and Lee M. F. 2013. A Richardson’s Extrapolation Spreadsheet Calculator for Numerical Differentiation, Spreadsheets in Education (eJSiE). 6(2): 1-5. [2] Tay K. G., Kek S. L. Abdul-Kahar R. 2008 Numerical Differentiation Spreadsheet Calculator, Proceedings of the National Symposium on Application of Science Mathematics 2008 (SKASM 2013). 111-120. [3] Tay K. G., Kek S. L. Abdul-Kahar R. 2014. Improved Richardson’s Extrapolation Spreadsheet Calculator for Numerical Differentiation," AIP Conference Proceedings 1605: 740-743. 11821 VOL. 11, NO. 20, OCTOBER 2016 I SSN 1819- 6608 ARPN Jour nal of Engineering and Applied Sciences © 2006- 2016 Asian Resear ch Publishing Net wor k ( ARPN) . All right s reser ved. www.arpnjournals.com [4] Tay K. G., Kek S. L. Abdul-Kahar R. 2012. A Spreadsheet Solution of a System of Ordinary Differential Equations Using the Fourth-Order RungeKutta Method, Spreadsheets in Education (eJSiE). 5(2): 1-10. on [5] Information http://www.mathcs.richmond.edu/~jad/232s07/euler_l ab3.pdf [6] Information http://www.instructables.com/id/SpreadsheetCalculus-Eulers-Method/ on on [7] Information https://www.youtube.com/watch?v=CxONzd1XdyY on [8] Information http://www.esm.psu.edu/courses/emch12/IntDyn/cour se-docs/Euler-tutorial/ [9] Tay K. G., Kek S. L. Abdul-Kahar R. 2013. Numerical Methods with Excel. UTHM Publisher. Batu Pahat, Malaysia. pp. 155. 11822