Outline

1. Excel – Basic Elements Using Macros Excel VBA Basics Excel VBA Advanced Outline

2. Module– collection of logically related procedures grouped together Procedure– a group of ordered statements enclosed by Sub and End Sub Function– the same as a procedure, but also returns some value and is closed between Function and End Function key words Modules & Procedures

3. Sub ShowTime()Range("C1") = Now() End Sub Function sumNo(x, y) sumNo = x + y End Function Procedure & Function Examples The procedure places the current time inside cell C1 The function returns sum of two input numbers, whose values are in the parameter variables x & y

4. Sub z(a) MsgBox a End Sub Sub x()Call z("ABC") End Sub Sub y()z "ABC“ End Sub Calling procedures vs. calling functions If there are few sumNo functions, the full name of the function is needed Sub ShowSum()MsgBox _ Module1.sumNo(3,5) End Sub Function sumNo(x, y) sumNo = x + y End Function

5. Passing arguments by reference – • Is the VBA default • Means, if any changes happened to the argument variables, they will be preserved after the function/procedure finishes • Passing arguments by value – • Is possible in VBA (by explicit definition) • Means, the pre-calling state of the argument variables will be preserved after the procedure/function finishes Passing Arguments by Value or by Reference

6. Sub TestPassing1() Dim y As Integer y = 50 AddNo1 y MsgBox y AddNo2 y MsgBox y End Sub Sub AddNo1(ByRef x As Integer) x = x + 10 End Sub Sub AddNo2(x As Integer) x = x + 10 End Sub Arguments by Ref/by Val. Examples public Sub TestPassing2() Dim y As Integer y = 50 AddNo3 y MsgBox y End Sub private Sub AddNo3(ByVal x _ As Integer) x = x + 10 End Sub

7. Use public to allow any module to call the function/procedure Use private to make limited access to the function/procedure (only from the owning module) Functions/Procedure Scope

8. A variable is used to store temporary information within a Procedure, Module… • A variable name • Must start with letter and can’t contain spaces and special characters (such as “&”, “%”, “\”) • Can’t be any excel keyword (“if”, “while”…) • Can’t have identical name to any existing class (“Wroksheet”, “Workbook”…) VBA Variables

9. Byte – positive integer numbers (0:255) Integer – integers (-32,768 : 32,767) Long – 4-byte integer Currency – for fixed-point calculations Single – 2-byte floating-point numbers VBA Data Type

10. Double – double-precision floating-point numbers Date – used to store dates and times as real numbers. String – contains a sequence of characters VBA Data Type

11. Sub NoVariable() Range("A1").Value = _ Range("B2").Value Range("A2").Value = _ Range("B2").Value * 2 Range("A3").Value = _ Range("B2").Value * 4 Range("B2").Value = _ Range("B2").Value * 5 End Sub The Variables Advantage by Example In VB the end of statement is in the end of line. To write the same statement in few lines use “_” at the end of line! Sub WithVariable() Dim _ iValue as Integer iValue = _ Range("B2").Value Range("A1").Value = _ iValue Range("A2").Value = _ iValue * 2 Range("A3").Value = _ iValue * 4 Range("B2").Value = _ iValue * 5 End Sub

12. Declaring Variables • Format: Dim varibaleName AS dataType • Examples: • Dim myText As String • Dim myNum As Integer • Dim myObj As Range • The default value of • any numeric variable is zero • any string variable –“” (empty string) • an Object variable – is nothing (still the declaration will store space for the object!!!) Using Variables

13. In VB you don’t have to declare variable before its usage • Then, VB will by itself declare such variable as “Variant” • You can also declare variable as “Variant” • Dim myVar as Variant • Variant – means that the variable may contain any data type • The price is very high!!! – any time VB access such variable, it will spend time on “deciding” what is its “current” type! Variant “Data Type”

14. To assign a value to a Numeric or String type Variable, you simply use your Variable name, followed by the equals sign (=) and then the String or Numeric To assign an Object to an Object type variable you must use the key word "Set" Variables Assignment

15. Sub ParseValue() Dim sWord as String Dim iNumber as Integer Dim rCell as Range Set rCell = Range("A1") sWord = Range("A1").Text iNumber = Range("A1").Value End Sub Variables Assignment – cont.

16. The scope & lifecycle of a variable defines the code where the variable can be accessed and time when the stored data is kept inside the variable • Procedure-Level • Variables defined inside procedures • Can be accessed only inside the procedure and keep their data until the End statement of the procedure • Module-Level • Defined in the top of a Module • Any procedure inside the Module can access the variable • The variable retains the values unless the Workbook closes • Project-Level, Workbook Level, or Public Module-Level • Defined as “Public” in the top of a Module • Can be accesses by any procedure in any module • The variable retains the values unless the Workbook closes VBA Variables Scope & Lifecycle

17. Sub scopeExample() Dim x as Integer x = 5 End Sub Dim y as Integer ‘all the module procedures are here… Public z as Integer ‘all the module procedures are here… VBA Variables Scope & Lifecycle – cont. Procedure level variables Module level variables Project level variables

18. Workbook: the class represents an Excel file Worksheet: represents a single worksheet Sheet: represents a single worksheet or chartsheet Cell: represents a single cell Basic Excel Classes

19. VBA Entities by Example A Current Workbook A Range C4:D7 A Cell A current Worksheet

20. Workbooks: a collection of objects of class “Workbook” Worksheets: a collection of objects of class “Worksheet” Sheets: a collection of Sheet objects Range: a range of objects of class Cell Excel Containers

21. Sub Test1() Worksheets("Sheet1").Range("A10", "B12") = "Hello“ Worksheets(1).Range("A13,B14") = "World!" End Sub Referencing the Objects - Examples This will take the whole square between the two cells Two equal ways to refer Sheet1 The range of two cells

22. The Output Which Workbook was Used?

23. Sub ShowWorkSheets()Dim mySheet As WorksheetFor EachmySheetInWorksheetsMsgBox mySheet.NameNextmySheet End Sub What does this procedure do?

24. The Output! How many times the user will click on the button?

25. Cells indexing format: • Cells(row, column), where both row and column are given as integers (starting from 1) • Cells(index) –see the next slide • Following expressions are equivalent and refer to the cell A1 in the currently active sheet: • ActiveSheet.Range.Cells(1,1) • Range.Cells(1,1) • Cells(1,1) Referencing Cells

26. Range(“B1:F5”).Cells(12) = “XYZ” Referencing Cells with Offset See how we calculate cell 12 In the given range!

27. ActiveCell.Offset(4, 5) = 1 Referencing Cells with Offset – cont. This is the currently active cell The assignment result

28. Workbooks.Close – closes the active workbook Workbooks.Count – returns the number of currently open workbooks Range(“A1”) is the same as Range(“A1”).Value Worksheets(1).Column(“A:B”).AutoFit Worksheets(1).Range(“A1:A10”).Sort_ Workbooks.Open fileName:=“Hello.xls”, password:=“kukuriku” Few methods/properties of Excel Classes

29. Dim myRange as Range Set myRange = Range(“A1:A10”) Defining and Assigning a new Object of type Range

30. Suppose, we want to keep a collection of all the books that we loan, • Or we want to keep lists of tasks for all the days of the week • The naïve solution is to keep a lot of variables • Another solution is to create array keeping the whole collection together VBA Arrays

31. Dim LoanBooks(3) LoanBooks(1) = “Winnie The Pooh” LoanBooks(2) = “Adventures of Huckleberry Finn” LoanBook(3) = “Frankenstein” Declaring object of type Array The array declaration. The size must be defined here!

32. Dim WeekTasks(7,2) WeekTasks(1,1) = “To buy milk” WeekTasks(7,1) = “To dance” … MsgBox WeekTasks(1,1) & ”” & WeekTasks(1,2) _ & vbCrLf & WeekTasks(2,1)… Multidimensional Arrays What will the code print?

33. There are two ways to resize the existing array: • ReDim LoanBooks(7) – will erase the old values • ReDim Preserve LoanBooks(7) – will preserve values in indexes 1-3 Resizing the Arrays

34. Dim A(1 To 100, 0 To 3, -3 To 4) • UBound(A, 1) – will return “100” • UBound(A, 2) – will return “3” • UBound(A, 3) – will return “4” • LBound(A, 1) – will return “1” • LBound(A, 2) – will return “0” • LBound(A, 3) – will return “-3” • Write code calculating the size of each one of the sub-arrays Upper & Lower Index Bounds of an Array

35. If Age >= 18 Then Status = "Adult" End If If Age >=18 Then Status = “Adult” Vote = “Yes” Else Status = “Child” Vote = “No” End If VBA Control Structures - If

36. IfAge >= 18 Then MsgBox "You can vote" ElseIfAge >=22 and Age < 62 Then MsgBox “You can drive” End If VBA Control Structures - If

37. Select Case Grade Case Is >= 90 LetterGrade = "A" Case Is >= 80 LetterGrade = "B" Case Is >= 70 LetterGrade = "C" Case Is >= 60 LetterGrade = "D" Case Else LetterGrade = “E"End Select VBA Control Structures – Select

38. For i = 10 to 1 Step -2 Cells(i, 1) = “AB” Next i i = 1 Do While i =< 10Cells(i, 1) = ii = i + 1 Loop i = 1 Do Cells(i, 1) = i i = i + 1 Loop While i < 11 VBA Control Structures – Loops

39. Sub CellsExample()For i = 1 To 5For j = 1 To 5Cells(i, j) = "Row " & i & " Col " & jNext jNext iEnd Sub Test yourself! What does the procedure do?