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


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




                                                          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
 A simple loading pattern &          An analytical solution
 homogeneous structure               based on integration




                                                                3
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




                                                          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-
    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.:
       =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
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:
     “Root-Finding”
   n Will do with several different methods in
     Excel Worksheet
   n Will implement the “interval-halving”
     method as a VBA macro in Excel




                                                         10

								
To top