Introduction -Numerical Methods with Excel and Visual Basic by grapieroo9

VIEWS: 135 PAGES: 10

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

   - 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
    –   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

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

     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
    design your own software

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

                              f(x)             of f(x) by the

Example 1- Beam deflection
 A simple loading pattern &          An analytical solution
 homogeneous structure               based on integration

What about this cantilever beam?

    Nonuniform loading pattern

                                 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

Stormwater routing through a
detention basin

                                                                          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

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

     Excel and Visual
      Basic as Tools
        for doing
    Numerical Methods

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

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)

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-
    Module from the menus
  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.:
  n Note, the function will
    not be available to other
    Excel files unless this
    workbook is open!

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

Adding a description to your macro/UDF
   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

   n Start with a classical numerical method:
   n Will do with several different methods in
     Excel Worksheet
   n Will implement the “interval-halving”
     method as a VBA macro in Excel


To top