Download to read offline
More Related Content
Similar to Vba and macro creation (using excel)
Vba and macro creation (using excel)
- 1. VBA and Macrocreation (using Excel) Javier cauna morales 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 1
- 2. Agenda for Today Object-OrientedProgramming Creating Macros with VBA 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 2
- 3. OBJECT ORIENTED PROGRAMMING: VBA 5/29/2020 Javier CaunaMorales UAGRM-IngPetrolera 3
- 4. What is O-Oprogramming? A programming style that uses “objects” to comprise programs. Objects: In a pure O-O language, every thing in the program is an object. An object is a data structure consisting of attributes (data fields) and methods (actions). The attributes of the object hold its current information The methods of the object determine what the object can do 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 4
- 5. Different objects types:classes Each programming environment comes with many different kinds of predefined objects. An object type is called a class. A class is thus some type of object. All objects within a class hold the same kind of information (identical attributes) and can perform the same actions (identical methods). A class is the “cookie cutter”/template you use to create new object instances. Hollywood analogy: in Battlestar Galactica there are 12 “classes” of human robots. One of these 12 model types was the Sharon model. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 5
- 6. Creating and usingobjects You 1st need a class class Sharon Attributes: height, 3Dbodyshape, faceimage, currentlocation, currentlove, pastexperienceslog Methods: imitateHuman, fallInLove(person), doSecretMission(password) You then “declare” a new object and can subsequently assign its attribute value and invoke its methods. Example: declaring a new Sharon object, assigning a value, and calling a method Sharon sharonNo55 sharonNo55.currentlocation = “KA.493.X7.1034” sharonNo55.fallInLove(Helo) 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 6
- 7. Object Oriented ProgrammingFundamentals Appropriate object classes (their attributes and methods) heavily depend on the problem you are working on. Example: A system for a bank: ◦ Objects: Customers, Accounts, etc. ◦ Attributes: First Name, Last Name, SSN, Address, etc. ◦ Methods: Withdraw, Open an account, Deposit, Cash check, etc. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 7
- 8. VBA: Visual Basicfor Applications VBA is a specialized version of Microsoft’s well-known Visual Basic language. VBA is an Object-Oriented language within Microsoft Office suite: ◦ Excel, Word, Access, and Power Point. ◦ We will focus on Excel. ◦ Shortcut to access the VBA interface in Excel: Alt+F11. Excel treats everything in a spreadsheet as objets. So, VBA lets us play with all these objects. ◦ Object that we are interested in: Workbooks, worksheets, cells, rows, ranges. ◦ There are over 200 different class (types of objects) in Excel. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 8
- 9. Coding with VBA Thereare two approaches to write a code in VBA: • Standard Coding • Macro Recording The Macro approach “records” a series of mouse-clicks and keyboard strokes. • Advantage: Corresponding VBA code is created automatically and recorded for you. • Disadvantage: It’s limited. To tackle this limitation, we use a mixed approach: • First, record a simple macro. • Then, tweak it to achieve more complex tasks. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 9
- 10. Problem Definition We havedata spanning 3 columns and 13 rows (C6:F18). ◦ Data should be stored in the given range in order to use it for other application. ◦ Unfortunately, there are some errors in the data entry process, so some of the rows are shifted to the right by one column. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 10
- 11. Declare and namevariables. Create Subroutines and Functions (We’ll learn what they are shortly). Use conditional statements. Create repetitions. Problem Definition We have data spanning 3 columns and 13 rows (C6:F18). ◦ Data should be stored in the given range in order to use it for other application. ◦ Unfortunately, there are some errors in the data entry process, so some of the rows are shifted to the right by one column. Our job is to correct all these mistakes: ◦ First, record a simple macro that correct a specific row (say Row 8). ◦ Then, tweak it to make correction in any given row. ◦ Finally, we let the code check any mistake and correct it. While doing this, we learn how to 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 11
- 12. Our First Macro Download and open VBA_Example_class.xlsm In Excel 2010, we should save the file as a Macro Enabled Workbook (i.e., in the .xlsm format). 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 12
- 13. Our First Macro ◦ ◦ ◦ ◦ Selectthe range D8:F8. Cut the selected cells. Select cell C8. Paste the cut cells. To see the VBA code generated by your actions, go into VBA (Alt+F11) and then near the upper left of the window expand the Modules folder and double-click on Module1. Find the Macros option under the Excel 2010 View tab to record a VBA macro. Let’s call our Macro “Shifter.” We want our macro to do the following: 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 13
- 14. Subroutines Notice that thecode starts with Sub command and ends with End Sub command. Sub refers to the Subroutines. ◦ Subroutine: A portion of code within a larger program that performs a specific task and is relatively independent of the remaining code. ◦ We can use a subroutine in a different subroutine by “calling” it. ◦ e.g., Call Shifter(). 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 14
- 15. Execution of Subroutines Eachline of a subroutine is executed sequentially. Let’s see how our macro is executed. (The first three green lines following each ‘ are simply comments/documentation.) • The real first line is Range("D8:F8").Select, and it selects the cells we want to select. • The second line, Selection.Cut, cuts the selected cells. • The third line, Range("C8").Select, selects the cell C8. • Finally, the last line, ActiveSheet.Paste, pastes the cut values to the selected cell of the worksheet. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 15
- 16. Subroutines with InputArguments The limitation of Shifter is that it only corrects Row 8. We can solve this by creating a subroutine which will take a row number as an input parameter. Sub ShiftOneColumn (RowNum As Integer) CODE BLOCK End Sub ◦ RowNum is the input variable. We name it. ◦ As Integer part declares the data type of our input variable. ◦ Common Data Types: Data Type Boolean Integer Double String Description Holds either TRUE or FALSE value. Integers between -32000 and 32000 Double precision numbers, can hold fractional numbers (The range of double number is very large) A set of characters (like sentences) 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 16
- 17. Shifting Any GivenColumn Sub ShiftOneColumn (RowNum As Integer) Range("D" & RowNum & ":F" & RowNum).Select Selection.Cut Range("C" & RowNum).Select ActiveSheet.Paste End Sub How do we tell VBA to shift the row according to our input, RowNum? ◦ Currently, we select cells D8,E8,F8 by writing "D8:F8". ◦ We will construct that D#:F# syntax, for our row # (i.e., RowNum) Range("D" & RowNum & ":F" & RowNum).Select ◦ In Excel and VBA the & operator simply combines (“concatenates”) text together 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 17
- 18. Shifting Repeatedly We nowhave a subroutine that can correct any given row. We want to apply this subroutine to any rows between 6 and 18. We use a loop (e.g., a FOR-NEXT Loop) for this task: For-Next Loop Syntax For varName=start_val To end_val Step step_size CODE BLOCK Next varName ◦ The above code assigns the value of variable varName to start_val. ◦ Then, executes the code inside the loop. ◦ After that, increases the value of variable varName by step_size ◦ And, runs the code again. ◦ Repeats that until the value of variable varName reaches end_val. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 18
- 19. Shifting Repeatedly Sub ShifterLoop ForRowNum=6 To 18 Step 1 Call ShiftOneColumn(RowNum) Next RowNum End Sub Let’s create our FOR-NEXT Loop. ◦ The variable whose value we change is RowNum. ◦ The Start value is 6. The End value is 18. ◦ And, the Step Size is 1. In each step, we call the subroutine ShiftOneColumn. Notice that we have started to use the value called RowNum that we introduced…. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 19
- 20. Variable Declaration The subroutineShiftOneColumn requires an integer input. We decided to introduce something called RowNum which stores a row number. ◦ We want the data type for RowNum to be Integer. The VBA interpreter will attempt to guess (scary!) the data type if you forget to declare your variables. To avoid that problem, we need to define the variables we use properly: Variable Declaration in VBA Dim varName As dataType Some rules about variable declaration: ◦ Variables names have to start with a letter, and have no spaces. ◦ VBA is not case sensitive (RowNum and rownum are the same). ◦ Names that are more than one word are usually written with the first letter of words capitalized; it’s not required but it’s fairly standard practice). 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 20
- 21. Shifting Repeatedly So, weadd a line before the loop where we declare our variable RowNum. Sub ShifterLoop Dim RowNum As Integer For RowNum=6 To 18 Step 1 Call ShiftOneColumn(RowNum) Next RowNum End Sub 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 21
- 22. Checking If FirstColumn is Empty We need to check whether the first column of a row is empty or not. VBA’s conditional statement IF-THEN-ELSE allows us to achieve this goal. IF-THEN-ELSE Syntax If test_cond Then ... CODE BLOCK executed if test_cond holds ... Else ... CODE BLOCK executed if test_cond fails ... End If 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 22
- 23. A Function toCheck the First Column We could write our conditional statement to check the first column within the subroutine, but we will define a separate “Function” for the checking task. Functions are very similar to the subroutines. Then, why a function? ◦ Unlike a subroutine, but Functions return a value. ◦ Useful for reporting the result of a calculation or other results. ◦ Our function will return… 1 if the first column is empty 0 otherwise. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 23
- 24. More on Functionsvs. Subroutines Functions are not always the right choice: ◦ A function cannot directly write data back to the spreadsheet. ◦ We can write data to the spreadsheet via subroutines. Easiest way: How to write data to cells Cells(row_num,col_num).Value= x ◦ e.g., Cells(10,3).Value= "Ducks Rock" writes Ducks Rock to the cell C10. (Try it!) ◦ In this example, our function doesn’t need to change the value of cell, but our function does return a value. 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 24
- 25. A Function toCheck the First Column Let’s go back to our task: Creating a function to check the first column. We name our function as CheckColOne. What is our input argument? Row Number. Function to check the first column Function CheckColOne (RowNum as Integer) If Cells(RowNum,3).Value="" Then CheckColOne=1 Else CheckColOne=0 End If End Function How do we check if the first column is empty? ◦ ◦ ◦ ◦ We can use Cells(row_num,col_num).Value. Then, check whether it is empty. We know the row number: It’s our input argument, RowNum. The column number is 3 since the first column of data is in Column C. So, our test condition is if Cells(RowNum,3).Value="". 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 25
- 26. Using the CheckFunction in the Main Subroutine Sub ShifterLoop () Dim RowNum As Integer For RowNum=6 To 18 Step 1 If CheckColOne(RowNum) Then Call ShiftOneColumn(RowNum) End If Next RowNum End Sub 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 26
- 27. Practice: Extending ourprogram How would we extend our program if we wanted to have the program highlight each moved row with a yellow background? Remember the approach most business people using VBA take: Perform “Macro Recording” to start Use/modify the resulting code 5/29/2020 Javier Cauna Morales UAGRM-IngPetrolera 27