# Introduction -Numerical Methods with Excel and Visual Basic by grapieroo9

VIEWS: 135 PAGES: 10

• pg 1
```									 Introduction – Numerical Methods
with Excel and Visual Basic
CE 201 Civil Engineering Computing
Spring 2007

Objectives:
- Learn why numerical methods are necessary in engineering
- Introduce VBA in Excel
- Demonstrate some macros (programs) in Excel

Why numerical methods?
n Many real-world problems must be solved
using numerical solution methods , because
they don’t have analytical solutions. For
example:
–   Flow in pipe networks
–   Indeterminate structural analysis
–   Flow in heterogeneous soils
–   Nonlinear systems/equations of any kind

n In fact, numerical methods are used
routinely in engineering

1
Commercial software
n We have lots of software that does numerical
methods for us… SAP2000, MODFLOW,

But, being able to use a software package does not
imply you know how it works!

Why learn programming?
n Useful and marketable skill

n Requires a logical approach to problem-
solving that applies throughout engineering
– logic must be very carefully thought out, or
program will not do what you want it to!

n Can’t always find software that does exactly
what you need – with programming, you can

2
Numerical methods and computers
n Numerical methods almost always involve
repetitive calculations in small increments, so
they lend themselves well to computers and
programming

Integration
f(x)             of f(x) by the
trapezoidal
method

Example 1- Beam deflection
homogeneous structure               based on integration

3

There is no analytical
solution for this
beam’s deflection, so
we must use
numerical methods!

Tapered beam or
composite beam

Example 2 – Flow through a
stormwater detention basin

4
Stormwater routing through a
detention basin

Qin
S
Qout
Outflow structure with weirs
Qin = inflow ? known as f(time)
Qout = outflow ? unknown
Want to size the basin (S) and outflow weir (Lw ) to allow a
limited Qout

dS
= Qin (t ) − Qout ( t)
dt
equation cannot be solved
dh
Adb ( h)        = Qin (t ) − C w Lw ( h(t ) − ho )1 .5   for h(t) or Qout (t) without
dt
numerical methods!
nonlinear                              nonlinear

Excel and Visual
Basic as Tools
for doing
Numerical Methods

5
Excel Interface

Visual Basic Editor in Excel

(this Code window is
where “macros” are
written for Excel –
these macros are the
same as Visual Basic
programs)

6
Visual Basic
n programming language that combines
syntax of BASIC (Beginners All-purpose
Symbolic Instruction Code) with a graphical
user interface (thus “Visual”)

n Used to enhance the capabilities of Excel
(Visual Basic for Applications (VBA))

n Used to create stand-alone software for
Windows (VB 6 and VB 2005)

7
Example program/macro in Excel
n Excel has lots of built-in math functions… click on the
fx button, and you get:

n But, maybe you have thought “why doesn’t Excel
have a function for x?” … with VBA, you can write
your own functions (termed “user-defined functions”)

Example program in Excel (cont)
n Go to the Visual Basic
Editor and select Insert-
n In the Code window,
write a Function that will
perform what you want
n Now, back in the
Worksheet window, you
can use this function
just like any other built-
in Excel function, e.g.:
=gpmtocfs(A5)
n Note, the function will
not be available to other
Excel files unless this
workbook is open!

8
Another User-Defined Function
n Manning’s equation is used to calculate flow rate in
open channels and pipes flowing by gravity

Note description

Manning’s Equation UDF
1.49 2 / 3 1 / 2               A
Q (cfs) =       Rh S A           Rh =
n                           Pw

Comment line

Now, back in the Worksheet:

=ManningQ(0.013,12,.01) gives 3.6

9
n Useful to have a description of your UDF
n Use Tools-Macro-Macros, then type the name of the
UDF, then click the Options button

Next Time