Visual Basic for Applications

1. Visual Basic for Applications • Introduction • The Control Toolbox View►Toolboxs► Control Toolbox • Command Buttons View►Toolboxs► Command Buttons

2. Visual Basic for Applications • Visual Basic Editor (VBE) Menu Bar Toolbar Project explorer window Code window Properties window • A Simple Program Loan Amortization Table Using PMT Function(Lecture 8) Create your first Macro

3. Visual Basic for Applications • Running a Program • Press the shortcut key, if there is one. • Select Tools►Macro►Macros and click the name of macros in the macro dialogue box. • Press Alt+F8 and click the name of macros in the macro dialogue box. • Click the Run button, ►, press [F5], or go to Run..Run Sub/UserForm to run the program • Create a control bottom (or a command bottom) • Enter the function name and arguments

4. Visual Basic for Applications • Modules and Procedures A module is a container for procedures. A procedure is a unit of code enclosed either between the Sub and End Sub statement or between the Function and End Function statements.

5. Visual Basic for Applications • Module Functions Sub Statement (NB: Subs do not return values, Functions do) • Sub name [(arguments list)] • [statements] • [Exit Sub] • [statements] • End Sub

6. Visual Basic for Applications • Module Functions Function Statement • Function name [(arglist)] [As type] • [statements] • [name = expression] • [Exit Function] • [statements] • [name = expression] • End Function

7. Visual Basic for Applications • Module Functions Call Sub Procedure • Call Sub name [(argument list)] Call Function Procedure • Call Function name (argument list) • Object.Value = Function name (argument list) Sometime you have to add Worksheet name or Modules name before the procedure.

8. Visual Basic for Applications • Passing Argument by Reference The procedure access to the actual variable in memory. As a result, the variable's value can be changed by the procedure. Passing by reference is the default in VBA.

9. Visual Basic for Applications • Passing Argument by Value It will pass the value to the corresponding variable, not the memory address.

10. Visual Basic for Applications • Scope of Modules and Procedures Procedures in Visual Basic can have either private or public scope. Procedure with private scope A procedure with private scope is only accessible to the other procedures in the same module

11. Visual Basic for Applications • Scope of Modules and Procedures Procedure with public scope A procedure with public scope is accessible to all procedures in in every module in the workbook in which the procedure is declared, and in all workbooks that contain a reference to that workbook.

12. Visual Basic for Applications • Objects and Collections • Objects are the fundamental building blocks of Visual Basic. • An object is a special type of variable that contains both data and codes. • A collection is a group of objects of the same class.

13. Visual Basic for Applications • Objects and Collections • The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range. • Workbooks is a collection of all Workbook objects. • Worksheets is a collection of Worksheet objects.

14. Visual Basic for Applications • Objects and Collections • The Workbook object represents a workbook. • The Worksheet object represents a worksheet. • The Sheet object represents a worksheet or chartsheet. • The Range object represents a range of cells

15. Visual Basic for Applications • Workbook and Worksheet Object • A workbook is the same as an Excel file. • The Workbook collection contains all the workbooks that are currently opened. • Inside of a workbook contains at least one worksheet.

16. Visual Basic for Applications • Workbook and Worksheet Object • In VBA, a worksheet can be referenced as followed:Worksheets("Sheet1") • Worksheets("Sheet1") is the worksheet that named "Sheet1." • Another way to refer to a worksheet is to use number index like the following:Worksheets(1)The above refers to the first worksheet in the collection.

17. Visual Basic for Applications • Workbook and Worksheet Object To refer sheets (or other objects) with the same name, you have to qualify the object. For example:Workbooks("Book1").Worksheets("Sheet1")Workbooks("Book2").Worksheets("Sheet1")

18. Visual Basic for Applications • Range Object and Cells Property Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range

19. Visual Basic for Applications • Range Object and Cells Property • Range object has a Cells property. • The Cells property takes one or two indexes as its parameters Cells(index) or Cells(row, column)

20. Visual Basic for Applications • Range Object and Cells Property • ActiveSheet.Range.Cells(1,1)Range.Cells(1,1) Cells(1,1) • Range("A1") = 123 and Cells(1,1) = 123 • Cells(12) = "XYZ"puts "XYZ" on Cells(1,12) or Range("L1") assume cell A1 is the current cell • Range("B1:F5").cells(12) = "ZYZ"

21. Visual Basic for Applications • Methods and Properties • A Property represents a built-in or user-defined characteristic of the object. • A method is an action that you perform with an object. Below are examples of a method and a property for the Workbook Object.

22. Visual Basic for Applications • Methods and Properties • Workbooks.Close Close method close the active workbook • Workbooks.CountCount property returns the number of workbooks that are currently opened

23. Visual Basic for Applications • Methods and Properties • Methods That Take No Arguments:Worksheets("Sheet").Column("A:B").AutoFit • Methods That Take Arguments:Worksheets("Sheet1").Range("A1:A10").Sort _Worksheets("Sheet1").Range("A1")

24. Visual Basic for Applications • Methods and Properties

25. Visual Basic for Applications • Variables Dimxas Type Data Types • Integer (2 bytes) • Long (long integer, 4 bytes) • Single (single-precision floating-point, 4 bytes) • Double (double-precision floating-point, 8 bytes) • Currency (scaled integer, 8 bytes) • Decimal (14 bytes) • Date (8 bytes) • String (variable-length, 10 bytes) • Array(arglist)

26. Visual Basic for Applications • Array(arglist) Before signing values to an array, the array needs to be created. You can declare the array by using the Dim statement. Dim Array_name(Begin to End, Begin to End)

27. Visual Basic for Applications • Comments ‘ This is a comment, not part of the program

28. Visual Basic for Applications • Input and Output (I/O) Input • From Cells • Set x = Range(“A1”) • From Screen • x = InputBox(Prompt:="Please enter a number.") Output • To Cells • Range(“A1”) = x • To Screen • The Message Box MsgBox“The value of x is” & Str(x)

29. Visual Basic for Applications • Arithmetic Operators Power/Exponent^ Operator Multiplication* Operator Division/ Operator Addition+ Operator Subtraction- Operator

30. Visual Basic for Applications • Concatenation Operators Concatenate Strings& Operator (String)

31. Visual Basic for Applications • Type Conversion Functions Change expression to Currency CCur(expression) Change expression to DateCDate(expression) Change expression to DoubleCDbl(expression) Change expression to DecimalCDec(expression)

32. Visual Basic for Applications • Type Conversion Functions Change expression to Integer CInt(expression) Change expression to LongCLng(expression) Change expression to SingleCSng(expression) Change expression to StringCStr(expression)

33. Visual Basic for Applications • Type Conversion Functions Change text to number VALUE(text) Change value to textTEXT(value,format_text) Change number to String STR(number)

34. Visual Basic for Applications • Control Functions If, Then If expression Then statements [Else [elsestatements]] End If

35. Visual Basic for Applications • Control Functions Do, Until Do Until x = y statements Loop

36. Visual Basic for Applications • Control Functions Do, While Do While x = y statements Loop

37. Visual Basic for Applications • Control Functions For, Next For counter = start To end [Step step] [statements] [Exit For] [statements] Next [counter]

38. Visual Basic for Applications • Logical Functions Logical ANDAND(logical1,logical2, ...) Logical OR OR(logical1,logical2,...)

39. Visual Basic for Applications • Mathematical Functions Count COUNT(value1,value2,...) Natural Logarithm LN(number) Maximum of a Series MAX(number1,number2,...) Minimum of a Series MIN(number1,number2,...) Power/Exponent POWER(number,power) Product of a Series PRODUCT(number1,number2,...) Round ROUND(number,num_digits) Sum of a Series SUM(number1,number2, ...)

40. Visual Basic for Applications • Statistical Functions Correlation Coefficient CORREL(array1,array2) Covariance COVAR(array1,array2) Kurtosis KURT(number1,number2,...) Lognormal Distribution LOGNORMDIST(x,mean,standard_dev) Mean AVERAGE(number1,number2,...) Median MEDIAN(number1,number2,...) Mode MODE(number1,number2,...) Normal Distribution NORMDIST(x,mean,standard_dev,cumulative)

41. Visual Basic for Applications • Statistical Functions Percentile Rank PERCENTRANK(array,x,significance) Percentile PERCENTILE(array,k) Quartile QUARTILE(array,quart) Rank RANK(number,ref,order) Skewness SKEW(number1,number2,...) Slope SLOPE(known_y's,known_x's) Standard Deviation STDEV(number1,number2,...) Standard Normal Distribution NORMSDIST(z) Variance VAR(number1,number2,...)

42. Visual Basic for Applications • Financial Functions Depreciation DDB(cost, salvage, life, period[, factor]) Depreciation, Straight Line SLN(cost, salvage, life) Depreciation, Sum-Of-Years' Digits SYD(cost, salvage,life, period) Future Value FV(rate, nper, pmt[, pv[, type]]) Interest Payment IPmt(rate, per, nper, pv[, fv[, type]]) Interest Rate Rate(nper, pmt, pv[, fv[, type[, guess]]])

43. Visual Basic for Applications • Financial Functions Internal Rate of Return IRR(values()[, guess]) Modified Internal Rate of Return MIRR(values(), finance_rate, reinvest_rate) Net Present Value NPV(rate, values()) Number of Periods (Annuity) NPer(rate, pmt, pv[, fv[, type]]) Present Value PV(rate, nper, pmt[, fv[, type]]) Principle Payment PPmt(rate, per, nper, pv[, fv[, type]])