Implement Time Series Models in EXCEL-VBA

Implement Time Series Models in EXCEL-VBA

Introduction

In the past, even mildly complicated engineering calculations have not mixed well with spreadsheets because of the very strong tendency to wind up with nearly-impossible-to-debug "spaghetti code". While most students seem to enjoy using spreadsheets, instructors recognize that other than by finding the “correct” answer printed somewhere, it is next to impossible to grade assignments or to help the occasional student debug them. However, by using Visual Basic for Applications (VBA) in conjunction with the Excel spreadsheet, the user has the convenience of a spreadsheet for neatly-formatted input/output and for graphical display of results, i.e., to function as a graphical user interface (GUI).

Meanwhile well-structured, readable, line-oriented code can be used for the more complicated calculations. This appendix will review a few general aspects of spreadsheets, while providing a brief introduction to the use of functions and subroutines written in VBA. Many books about Excel do not address VBA at all, and if they do, the coverage is limited to a few page

s. In the early days of spreadsheets, a macro was just a recording of a series of keystrokes; that can still be done. (In fact, as will be noted later, recording a series of keystrokes and mouse clicks and then viewing the resulting VBA code generated is a great way to learn the language.) But VBA, which has been available since Excel 5.0 and is a subset of Visual Basic tm , is a fullfeatured, structured programming language and thus far more powerful. Indeed, because VBA allows the programmer to program and thus control Excel's rich collection of drawing and chart objects, it might be considered more potent than VB itself. Consider drawing a rendered cylinder on the screen. From scratch this would be a very formidable job in VB (including hidden surface removal, shading, etc.).

With VBA you can just grab a cylinder from the “Autoshapes” collection and manipulate it at will using VBA statements. In addition you don't have to buy another package. Those who have taken a course using any modern structured language like Java, C++, Fortran 90, Pascal, etc. can pick up VBA syntax with very little coaching. Indeed, those students who ordinarily will do anything to avoid writing a traditional computer program find VBA an attractive and enjoyable addition to their skill set.

Discussion

The vertical velocity distribution of fluids in unidirectional, open-channel flows resembles the classical loglaw distribution of flows over fixed walls. It is a function of variables such as the fluid dynamic viscosity (m) and density (r), the roughness (k) and slope (S) of the boundary, as well as the flow depth (d) (Bagnold, 1966). Velocity profiles are also influenced by the presence of bedforms and sediments in motion. At the boundary itself the flow velocity (U) is zero, from where it increases upward to a maximum just below the surface. The exact nature of the velocity distribution furthermore depends on various forms of the Reynolds number (Re), given ...