Object Oriented Programming

1. Object Oriented Programming A programming concept which views programs as objects with properties and ways to manipulate the object and the properties.

2. Object Noun Part of the application Property Adjective Action to do something Method Verb Visual Basic Objects / Properties / Methods Attribute

3. Visual Basic Range (“A3”).select Rangeis an object (“A3”)is a modifier for the object selectis amethod Note that objectseparated from methodby a.

4. Visual Basic Activesheet.name Activesheet is an object name is a property The result is the name of the active sheet

5. Procedures Sub procedures return no values Function procedures return a value

6. Function Procedures Function Grade (exam1,exam2,exam3) as String Sum = exam1+exam2+exam3 If sum > 95 then Grade = “A” Else if sum > 80 Grade = “B” Else Grade = “C” End if End Function The function name must be assigned the value to be returned!

7. Arguments to a function must be in the order specified in the function description: = Grade (90, 80, C5) Function Grade (exam1,exam2,exam3) as String Note the “as String” to declare the type of value returned by the function.

8. Sub Procedures Sub Gc() ‘ Lines beginning with ‘ are comments ' Gc Macro ' Puts gc in active cell & units in adjacent cell to right ' Keyboard Shortcut: Ctrl+g ' ActiveCell.FormulaR1C1 = "32.174" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "ft-lbm/lbf-s^2" End Sub This was a recorded macro – macros are “Sub” procedures.

9. Review of some Excel Basics Cell references of two types: A1Columns are alphabetic, rows are numeric R1C1 R number Column number B2 and R2C2 refer to the same cell Can be set by the Tools / Options menus (Note that the two methods are transposed – A1 – column first, then row R1C1 – row first, then column )

10. Formulae in Excel (A1 style) A1 is a relative address --- it changes when the formula is copy /pasted $A$1 is an absolute address --- it does not change via copy / paste And can use mixed mode: A$1 – A is relative, $1 is absolute $A1 – A is absolute, 1 is relative

11. Formulae in Excel (R1C1 style) R1C1 is an absolute address – it does not change under copy / paste R[1]C[1] is a relative address – it does change under copy /paste And can use mixed mode: R1C[1] – R1 is absolute, C[1] is relative

12. In VBA, can use either or both A1 and R1C1 styles A1 style tends to be absolute A1 style used with the “Range” property Range(“A4”) Can refer to individual cells with the “Cells” Property, which uses an R1C1 style Cells(4,1)

13. In Excel, cells & ranges can be named Insert / name menu These names can be used in Formulae VBA

14. VBA Variable Types String A sequence of bytes containing characters Integer 2 byte integer Long 4 byte integer Single 4 byte real number Double 8 byte real number Variant Can hold anything (but “expensive” to use) “Object” A class of data types used by Excel/VBA

15. Characters & Stings For Excel, anything that is not a number or a formula is a string. If want a set of digits as a string, need to enclose in quote or quotation marks. For VBA, need to define variable that will hold strings as string data types Good programming practice is to declare all variables and their types via the “Dim” statement.

16. Visual Basic Sub NameIt() Dim newname as String newname = InputBox(“Enter a name for the worksheet”) ActiveSheet.Name=newname End Sub

17. “Object” data type Many of the objects in Excel have their own data type: Dim r as Range Dim q as Worksheet Dim z as Chart About the only one we will use is the “Range” data type.

18. Option Explicit Forces you to ‘Dim’ all variables Helps prevent typos Can set this as the default through the Tools/ Options menu. Require variable declaration check box)

20. Visual Basic All objects have properties Most objects have methods Will work with only a few of the many objects, methods and properties To get a list of objects and properties, invoke the Object Browser

21. Visual Basic Some of the objects Workbook Worksheet ActiveSheet Range Form Chart Note --- a “Cell” is not an object, but is a type of range object

22. Visual Basic • And to further confuse the issue • objects can have objects as properties • And objects can be grouped into collections • Workbook = collection of worksheets

23. Visual Basic Object Browser --- Just for a quick look In VBA, press F2 or “View / Browser” Select Excel from library list Enter an object in the second list box Use “F1” to get help on an object

24. Program module (Sub or Function) is made up of a series of steps to accomplish a task. Five major categories of steps: Assignment statements A = 5 Conditional statements If ( A > 5) then …. Calls to other modules A = sqrt (12) Iteration Statements For I = 1 to 6 … Next i Input /Output operations (Which are really calls to other modules) Read 5, A

25. Expression evaluation inVBA Operators in priority order Left to right Operator Priority List ( ) ^ - (unary minus) * / + - Comparison Operators (>, < …) Logical Operators (NOT, AND, OR…)

26. Test = 2 + 3 ^2 > 5 AND (3-7)*2 > 6 (3-7) -4 3^2 9 -4*2 -8 9+2 11 11 > 5 True -8 > 6 False True AND FalseFalse Thus, Test has the value “False”

27. Conditional Expressions If / then / else end if Select case …. End Case

28. If (expression) then One or more expressions else One or more expressions end if Simple form If (expression ) then (expression)

29. Select Case Statement Like a complex If / Then / ElseIf … / EndIf Select Casetestexpression[Caseexpressionlist-n[statements-n]]...[Case Else[elsestatements]] End Select We will not use it

30. Iteration Statements Used to execute a number of statements repeatedly Two major types --- For / Next Do / Loop

31. Forcountertoupperlimt stepsize Statements Next counter For Each cellIn Selection Statements cell.value =expression Nextcell Note that “value” is a property of the “cell” object and is the contents of the cell

32. Do while expression_is_true Statements Loop Do Statements Loop while expression_is_true

33. Do Until expression_is_true Statements Loop Do Statements Loop Until expression_is_true

34. To add develop a function (or sub) First determine what function (or sub ) is to do, the steps to do it, and the needed arguments. In VB, Insert Module Insert Procedure Select desired type Type name Add arguments to argument list

35. To add develop a function (or sub) Dim all variables Write the code Return to Excel & test

36. Some Simple Examples • Compute the factorial of number • Compute the sine of an angle from the • series expansion

37. Simple function to compute n!. There is a worksheet function to do this (Fact(n), but calling it is complicated. Public Function NFact(N) As Double ‘ Assumes N is an integer > 1 Dim I As Integer NFact = 1 I = 1 Do While I <= N NFact = NFact * I I = I + 1 Loop End Function

38. Function to compute the sine of an angle (in radians) Use a Taylor series expansion about 0 radians Sine = Function will take two argument – the angle and n, then number Of terms in the series. It will return the approximate value of the sine. (In theory, n should be ∞ )

39. Public Function MySine(angle As Single, nterms As Integer) As Double ‘ Declare the variables. Dim J As Integer ‘ Initialize the value to zero MySine = 0.0 ‘ Compute the value by evaluating each term in the sum J = 1 Do While J <= nterms MySine = MySine + ((-1) ^ (J + 1)) * (angle ^ (2 * J - 1)) / (NFact(2 * J - 1)) J = J + 1 Loop End Function