Computers in Civil Engineering CEE3100 Spring 2002

1. Computers in Civil EngineeringCEE3100 Spring 2002 Lecture #4 Programming in Excel with VBA I

2. Visual Basic for Applications (VBA) • A complete programming language residing with Microsoft applications such as Excel, Word, and Access • Why do we need VBA with Excel? • Create graphical user interfaces (GUI) • Make decisions, control operations, store values • VBA programs are typically written for special applications that require speed to handle lots of calculations, complex structures with decision making scenarios • Downside: programming requires much more effort

3. Formulas, Functions, Macros, VBA Programs • Formulas work directly with data on spreadsheet, uses simple math operations • Functions accept arguments, perform complex math operations, and return a result • Macros commonly used for repeated sequences of commands, often non-mathematical, do not accept arguments, do not return a value • VBA Programs offer most flexibility and power, create GUI, standard programming methods, make decisions, control operations, store values

4. Starting Visual Basic for Applications Tools / Macro / Visual Basic Editor • Modules and Subs from the VBA menu: Insert / Module Insert / Procedure

5. Public Sub quad_root() ‘VBA sub to compute roots of a quadratic eq. Dim a As Single, b As Single, c As Single, d As Single, x1 As Single, x2 As Single a = 1 b = 3000.001 c = 3 d = (b * b - 4 * a * c) ^ 0.5 x1 = (-b + d) / (2 * a) x2 = (-b - d) / (2 * a) MsgBox (“Solution of 2nd order equation:") MsgBox ("first root=" + Str(x1)) MsgBox ("second root=" + Str(x2)) End Sub

6. Data type Description Range Byte 1-byte binary data 0 to 255. Integer 2-byte integer  – 32,768 to 32,767. Long 4-byte integer  – 2,147,483,648 to 2,147,483,647. Single 4-byte floating-point number – 3.402823E38 to  – 1.401298E – 45 (negative values). Double 8-byte floating-point number 4.94065645841247E – 324 to 1.79769313486231E308 ( same for negative). String String of characters Zero to approximately two billion characters. Boolean 2 bytes True or False. Variables and Data Types

7. Declaring a Constant • You declare a constant for use in place of a literal value by using the Const statement Const MyVar = 459 Const MyDouble As Double = 3.4567 Const MyStr = "Hello" Public Const MyString = "HELP" Private Const MyInt As Integer = 5 • If you don't specify scope, the constant has private scope by default. • If you don't explicitly specify a data type when you declare a constant, Visual Basic gives the constant the data type that best matches the expression assigned to the constant.

8. Declaring a Variable • You declare a variable by using the Dim, Private, or Public, keyword. Use the As keyword to explicitly specify a data type for the variable Dim I As Integer Dim Amt As Double, X as Integer Private J As Integer • If you don't specify scope, the constant has private scope by default.

9. Declaring an Array and a Dynamic Array • You must explicitly declare an array before you can use it; you cannot implicitly declare an array: Dim counters(14) As Integer To refer to a certain element i in array: counters(i):e.g., counters(4) You declare a dynamic array just as you would declare a fixed­size array, but without specifying dimension sizes: Dim DynArray() As Integer • Somewhere in a procedure, allocate the actual number of elements with a ReDim statement: ReDim DynArray(X + 1) • Use the Preserve keyword to change the size of an array without losing the data in it. You can enlarge an array by one element without losing the values of the existing elements: ReDim Preserve myArr (UBound(myArr)+1)

10. Declaring a Multi-Dimensional Array • Dim a(10, 10) As Single To refer to a certain element : a(i, j), e.g., a(1,4)