Week 5

1. Week 5 ABC-BSM By: Aurino Djamaris

2. Tips use of comments: • Briefly describe the purpose of each Sub or Function procedureyou write. • Use comments to keep track of changes you make to a procedure. • Use a comment to indicate that you’re using a function or a constructin an unusual or nonstandard manner. • Use comments to describe the variables you use, especially if you don’tuse meaningful variable names. • Use a comment to describe any workarounds you develop to overcomebugs in Excel. • Write comments as you develop code, instead of saving the task for afinal step.

3. Sub Procedure • Using Comments in Your VBA Code SubCommentsDemo() ‘ This procedure does nothing of value x = 0‘x represents nothingness ‘Display the result MsgBox x End Sub Bad commented Vba

4. VBA Rules regarding VariableNames: x = 1 InterestRate = 0.075 LoanPayoffAmount = 243089 DataEntered = False x = x + 1 UserName = “Bob Johnson” DateStarted = #3/14/2004# MyNum = YourNum * 1.25 • You can use letters, numbers, and some punctuation characters, but thefirst character must be a letter. • You cannot use any spaces or periods in a variable name. • VBA does not distinguish between uppercase and lowercase letters. • You cannot use the following characters in a variable name: #, $, %, &, or !. • Variable names can be no longer than 255 characters. Of course, you’reonly asking for trouble if you use variable names 255 characters long. Variable names readableuse mixed case (for example, InterestRate) or the underscore character (interest_rate). Don’t use: Sub, Dim, With, End, For, If, Then

5. VBA’s data types Letting VBA handle your data typing resultsin slower execution and inefficient memory use. For small applications, thisusually doesn’t present much of a problem. But for large or complex applications,which may be slow or need to conserve every last byte of memory, youneed to be on familiar terms with data types.

6. Declaring and scoping variables To force to declare all the variables, include the following asthe first statement in your VBA module: Option Explicit Procedure-only variables only k is declared to be an Integer; or

7. Declaring and scoping variables (cont) Module-only variables: a variable to be available to all procedures in a module. Public variables: a variable available to all the procedures in all your VBAmodules in a workbook Public CurrentRate As Long Static variables: retain their value even when the procedure ends.

8. Constants • a value or string that never changes — a constant. A constant is a namedelement whose value doesn’t change. • Like variables, constants have a scope. Keep these points in mindTo make: • constant available within only a single procedure, declare theconstant after the procedure’s Sub or Function statement. • constant available to all procedures in a module, declare theconstant in the Declarations section for the module. • constant available to all modules in the workbook, use thePublic keyword and declare the constant in the Declarations section ofany module.

9. Constants (c0nt.) • The following simple procedure uses a built-in constant (xlCalculationManual)to change the Calculation property of the Application object. (In other words,this changes the Excel recalculation mode to manual.) “Microsoft Excel Constants.” : Find it on Microsoft help and type constants

10. Examples of Constants

11. Strings Text is often referred to as a string. You canwork with two types of strings in VBA: • ixed-length strings are declared with a specified number of characters.The maximum length is 65,526 characters. As a point of reference, thischapter contains about half that many characters. • Variable-length strings theoretically can hold as many as two billioncharacters. F When declaring a fixed-length string, do not use a comma in the number that specifies the string size. In fact, never use commas when entering a numeric value in VBA.

12. Dates • A variable defined as a date uses eight bytes of storage and can hold datesranging from January 1, 0100 to December 31, 9999. That’s a span of nearly10,000 years.Use the date data type to work with time data. • These examples declare variables and constants as a date data type:

13. Assignment Statements expression as “. . . a combination of keywords, operators, variables, and constants thatyields a string, number, or object. An expression can be used to performa calculation, manipulate characters, or test data.” • expressions are to theright of the equal sign: increasing the value of x by 1.

14. Operators Examples:

15. Arrays • declare an array of100 integers • Statementsdeclare the same 101-element array • declaring a 100-element array: • declares a 100-integer array with two dimensions: • assigns 125 to the elementlocated in the 3rd row and 4th column of the matrix

16. Dynamic Arrays • A dynamic array doesn’t have a presetnumber of elements. Declare a dynamic array with a blank set of parentheses: • change the number of elementsin a dynamic array. It assumes that the NumElements variable contains avalue • To preserve an array’s values when you redimension the array use:

17. Range Objects • Range object, the address is always surrounded bydouble quotes Or • reference with a worksheet name fromthe active workbook: • to refer to a range in a different workbook • refer to an entire row (in this case, row 3) by using syntax like this: • refer to an entire column (column 4 in this example) like this: • refers to a two-areanoncontiguous range Notice that a comma separates the two areas.

18. The Cells property • Cells property takes two arguments: row and column. For example, thefollowing expression refers to cell C2 on Sheet2: • use the Cells property to refer to a multicell range. Refer to A1:J10 • set theValue property of the Range object

19. The Offset property • refers to a cell 1 row below cell A1 and 2 columns to the right of cell A1. In other words, this refers to the cell C2 • A negative column offset refers to a columnto the left of the range. The following example refers to cell A1: • use 0 as one or both of the arguments forOffset. The following expression refers to cell A1: • refer to a range that consists of one or more entire columns,you can use an expression like the following: • refer to one or more complete rows, use an expression

20. Range Object Properties • The Value property represents the value contained in a cell. • change the Value property for a range of any size. √ X

21. The Text and Count property • Supposethat cell A1 contains the value 12.3 and is formatted to display two decimalsand a dollar sign ($12.30). The following statement displays a messagebox containing $12.30: • But the next statement displays a message box containing 12.3: • Count property returns the number of cells in a range(all cells, not just • the nonblank cells).

22. The Column and Row properties • The Column property returns the column number of a single-cell range; theRow property returns the row number of a single-cell range. Both are readonlyproperties. For example, the following statement displays 6 because thecell is in the sixth column: MsgBox Sheets(“Sheet1”).Range(“F3”).Column • The next expression displays 3 because cell F3 is in the third row: MsgBox Sheets(“Sheet1”).Range(“F3”).Row • If the Range object consists of more than one cell, the Column propertyreturns the column number of the first column in the range, and the Rowproperty returns the row number of the first row in the range.

23. The Other property • The Address property a read-only property, displays the cell address for a Range objectin absolute notation (a dollar sign before the column letter and before therow number). • The HasFormula property (which is read-only) returns True if the single-cellrangecontains a formula. • Font Property To changesome aspect of a range’s font, you must first access the range’s Font objectand then manipulate the properties of that object.

24. The Other property (cont.) • The Interior property - changes the Color property of theInterior object contained in the Range object: • The Formula property • The NumberFormat property represents the number format

25. Range Object Methods • The Select method: • The Copy and Paste methods • The Clear methoddeletes the contents of a range and all the cell formatting. or or or

26. Range Object Methods (cont.) • delete a range Excelshifts the remaining cells around to fill up the range you deleted. • Delete method uses an argument that indicates how Excel should shift • the remaining cells. In this case, we use a built-in constant (xlToLeft) for theargument. We could also use xlUp, another named constant. deletes a range and then fills the resulting gap byshifting the other cells to the left

27. Using VBA and WorksheetFunctions • What Is a Function? • A function essentially performs a calculation and returns a single value. TheSUM function, of course, returns the sum of a range of values. The sameholds true for functions used in your VBA expressions:Each function does itsthing and returns a single value. • The functions you use in VBA  from 3 sources: • Built-in functions provided by VBA • Worksheet functions provided by Excel • Custom functions that you (or someone else) write, using VBA

28. Use Functions Examples (1) Sub UseFunction() Dim myRange As Range Set myRange =Worksheets("Sheet1").Range("A1:C10") answer =Application.WorksheetFunction.Min(myRange) MsgBox answer End Sub The above Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it is set to range A1:C10 on Sheet1. Another variable, answer, is assigned the result of applying the Min function to myRange. Finally, the value of answer is displayed in a message box.

29. Use Function example 2 • uses VBA’s Date function to display the current system datein a message box: • procedure uses the VBA Len function, which returns the lengthof a string

30. Use Function example 3 • If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. For example, you can use the Match worksheet function to search a range of cells. In a worksheet cell, you would enter a formula such as MATCH(9,A1:A10,0). However, in a Visual Basic procedure, you would specify a Range object to get the same result. Sub FindFirst() myVar = Application.WorksheetFunction _ .Match(9, Worksheets(1).Range("A1:A10"), 0) MsgBox myVar End Sub

31. Displaying the integer part of a number • The following procedure uses the Fix function, which returns the integer portionof a value — the value without any decimal digits: In this case, the message box displays 123. VBA has a similar function called Int. The difference between Int and Fix ishow each deals with negative numbers. • Int returns the first negative integer that’s less than or equal to the argument(-123.456 turns into -124). • Fix returns the first negative integer that’s greater than or equal to theargument (-123.456 turns into -123).

32. Determining a file size • Sub procedure displays the size, in bytes, of the Excel executablefile. It finds this value by using the FileLen function. Sub GetFileSize() Dim TheFile As String TheFile = Application.Path & “\EXCEL.EXE” MsgBox FileLen(TheFile) End Sub Please Refer to Chapter 9: Using VBA and Worksheet Functions page 129 for another functions. Try it !!!

33. VBA functions that do morethan return a value

34. Worksheet function examples in your book Try these on the Lab

35. More about Using Worksheet Functions • The WorksheetFunction object contains the worksheet functions available toVBA procedures. To see a list of these functions, you can use the ObjectBrowser, as shown in Figure 9-3. Follow these steps to display a complete listof worksheet functions available in VBA: • In the VBE, press F2.The Object Browser appears. • In the Project/Library drop-down list (the one in the upper-left cornerof the Object Browser), select Excel. • In the list labeled Classes, select WorksheetFunction.

36. Using Custom Functions • a simple Function procedure and then using itin a VBA Sub procedure: Function Sub Procedure that Call/use the function

37. Controlling Program Flow andMaking Decisions Programming Constructs for Making Decisions

38. The GoTo Statement

39. The If-Then structure If-Then structure without the optional Else clause: The routine has to test for all conditions, be it morning, afternoon, or evening.

40. An If-Then-Else-ElseIf example

41. If-Then example Make the flow chart !!!

42. The Select Case structure • The syntax for the Select Case structure follows:

43. The Select Case Example or

44. A nested Select Case example Try This !! The answer depend on ActiveCells content

45. Looping • Bad looping True False This routine works as intended, so why is it an example of bad looping?

46. Using GoTo statements to perform looping • Is contrary to the concept of structured programming. (See the sidebarearlier in this chapter, “What is structured programming? Does it matter?”) • Makes the code more difficult to read. • Is more prone to errors than using structured looping procedures. • VBA has enough structured looping commands that you almost never have torely on GoTo statements for your decision making.

47. For-Next loops • syntax for : For – Next • Example:

48. For-Next Example Will looping until number that user inputing to NumTo Fill

49. For-Next example with an Exit For statement

50. A nested For-Next example See - Chapter 10: Controlling Program Flow and Making Decisions -153- for 3 dimensional