Chapter 12: Recording Macros

1. Spreadsheet-Based Decision Support Systems Chapter 12: Recording Macros Prof. Name name@email.com Position (123) 456-7890 University Name

2. Overview • 12.1 Introduction • 12.2 Macros • 12.3 Customizing toolbars and menu options • 12.4 Summary

3. Introduction • Recording a macro. • Writing simple VBA procedures. • Creating event procedures. • Assigning macros to drawing objects in Excel. • Customizing toolbars and menu items by assigning macros.

4. Macros • Recording Macros • The VBA Code • Event Procedures

5. Recording Macros • Macros are technically defined as units of VBA code. • Macros can be thought of as a way to automate a series of actions in a spreadsheet application. • Macros can either be created directly with VBA code in the Visual Basic Editor, or recorded in Excel. • To record a macro, we must know exactly the actions we wish to perform and then use the Macro Recorder.

6. Figure 12.1 • Let us record a macro which copies and pastes data. • A dealer wants to send the information from the first three columns and last column of the data table to the newspaper.

7. Preparation • First we should review/practice the steps we will take when recording the macro • Highlight the first three columns of data with the cursor (C4:E13) • Copy (using CTL+C, right-click and Copy, or the Edit option) • Place the cursor in cell C19 • Paste (using CTL+P, right-click and Paste, or Edit option) • Highlight the last column (K4:K13) • Copy • Place cursor in cell F19 • Paste

8. Record • Now we are ready to record the macro • Tools > Macro > Record New Macro • Record button from VBE Toolbar • When the Record Macro dialog box appears, we enter a name for the macro.

9. Stop • Once you begin recording, notice that the Recordbutton transforms to a Stop button. • After finishing the steps needed to copy and paste the information, you can stop recording. • Tool > Macro > Stop Recording Macro • Stopbutton from the VBE Toolbar

10. Play • Once you have recorded a macro, you can play it to ensure it works correctly. • Tools > Macros > Macros • Playbutton from VBE Toolbar

11. Result • We can see that the macro works correctly.

12. VBA Code • As we learned earlier, each time a macro is recorded in Excel, VBA code is automatically generated. • Let us review what code was generated for this macro • Go the VBE window • A module has been created in the Project Explorer • Select the module to see the code in the Code Window

13. VBA Code (cont) Sub CarCopyPaste() Range("C5:E14").Select Selection.Copy Range("C20").Select ActiveSheet.Paste Range("K5:K14").Select Selection.Copy Range("F20").Select ActiveSheet.Paste Range("A1").Select End Sub • This is the initial statement of every sub procedure. • The Select method of the Range object selects the specified range of cells. • The Copy method is used on the Selection object. • The Paste method is used on the ActiveSheet object. • These steps are repeated to copy and paste the last column. • This last cell selection can be added to avoid leaving the cells highlighted. • This is the last statement of every sub procedure.

14. Starting in VBA • Since we can study the VBA code generated when we record a macro, we can easily learn how to create a similar macro directly from VBA by copying the code generated. • We will now learn how to use Sort and Filter functions in VBA by first recording a macro and then using the generated VBA code to create similar macros starting in VBA.

15. Figure 12.5 • Suppose there is a database for the Miami Airport which lists Flight Destinations, Number of Stops, Class and Price. • We want to be able to filter this information so that we can do the following: • View flights to Beijing • View flights to Hong Kong • View all flights • We also want to be able to sort the data by • Number of Stops • Price

16. Filtering • We will begin by recording the macro to filter the data for viewing Beijing flights only. • To prepare for recording a macro to filter the data table, we review the steps we will perform • Highlight all data (B3:E15) • Data > Filter > AutoFilter • Select the drop-down button for “Destination” column • Filter for Beijing flights • Select cell A1

17. Excel to VBE • We can check this recorded macro to ensure it works. • Now we can go to VBE to view the code generated.

18. Creating New Code • Now that we know the basic structure of the code, we can simply modify it to accomplish the other filtering macros. Sub ViewBeijingFlights() Range("B3:E15").Select Selection.AutoFilter Field:=1, Criteria1:="Beijing" Range("A1").Select End Sub • The Sub titles will change for each new macro • The AutoFilter method will be used on the same selection to generate the filtering drop-down arrows • Field:=1 denotes the “Destination” column, we can modify this value • Criteria1:=“Beijing” is the value which is filtered, we can also modify this value

19. New Macros from VBA • We can now create a macro to view Hong Kong flights and view All flights by using the code from the recorded macro. ------------------------------------------------------ Sub ViewHongKongFlights() Range("B3:E15").Select Selection.AutoFilter Field:=1, Criteria1:="Hong Kong" Range("A1").Select End Sub ------------------------------------------------------ Sub ViewAllFlights() Range("B3:E15").Select Selection.AutoFilter Field:=1 Range("A1").Select End Sub ------------------------------------------------------

20. Sorting • We will now record a macro to sort the data by number of stops. • To prepare for recording a macro to sort the data table, we review the steps we will perform • Highlight all data (B3:E15) • Data > Sort • Select “Stops” from the Sort by list • Select Descending as the order • Select cell A1

21. Excel to VBE • We can check this recorded macro to ensure it works. • Now we can go to VBE to view the code generated.

22. Creating New Code • Now that we know the basic structure of the code, we can simply modify it to accomplish the other sorting macros. Sub SortByStops() Range("B3:E15").Select Selection.Sort Key1:=Range("C4"), Order1:=xlDescending Range("A1").Select End Sub • The Sub titles will change for each new macro • The Sort method will be used on the same selection • Key1:=Range(“C4”) denotes the “Stops” column • Order1:= xlDescending is the order in which the data will be sorted

23. New Macro from VBA • We can now create a macro to sort by price by using the code from the recorded macro. ------------------------------------------------------ Sub SortByPrice() Range("B3:E15").Select Selection.Sort Key1:=Range("E4"), Order1:=xlDescending Range("A1").Select End Sub ------------------------------------------------------

24. Event Procedures • Event Procedures connect events or actions of an Excel object to a specific macro of VBA code. • Click • Change • Activate • To find a list of available/applicable event procedures in VBE, look at the top of the Code Window. • Choose and object from the object list • Choose an event from the event list

25. Event Procedures (cont) • For example, the Activate event can be used on the Worksheet object. • Once an object and event are selected, a sub procedure will be initiated.

26. Control Toolbox • The Control Toolbox is a set of objects to which event procedures are usually assigned. • View > Toolbars > Control Toolbox • Icon from VBA Toolbar • Click and drag from toolbar to spreadsheet • There are many controls available to put on the spreadsheet • Text, labels • Spin buttons, toggle buttons • List box, combo box • Check boxes, option buttons

27. Control Properties • Each control has its own set of properties which can be modified • Name = name of the control, this will be used to identify it in VBA (ex: cmdButton) • Caption = text which appears on the control (ex:“Click Here”) • The Click event procedure can now be associated with this new object by titling are sub procedure • Sub cmdButton_Click()

28. Command Button • Let us add a command button to perform one of the filtering macros we previously wrote in VBA and set the following properties. • Name = cmdHongKong • Caption = “View Hong Kong Flights”

29. Event Procedure • We can now copy and paste the code for this macro to a new sub procedure as follows. Sub cmdHongKong_Click() Range("B3:E15").Select Selection.AutoFilter Field:=1, Criteria1:="Hong Kong" Range("A1").Select End Sub • This title can be automatically generated if we select the command button cmdHongKong from the list of objects and then select Click from the list of events.

30. Drawing Controls • We can also make a button instead of using a command button from the control toolbox. • Create rectangle and then add text to it by right-clicking and choosing Add Text; then format the rectangle as desired.

31. Assigning the Macro • You can now simply right-click on the button you created and select Assign Macro from the drop-down list. • A list of all macros in the open workbooks will appear, select one and your button has become activated.

32. Buttons • We can now create buttons for each of the macros we previously wrote. • Assign a macro to each new button respectively.

33. Customizing Toolbars and Menu Options • Create a toolbar with icons associated with a particular macro. • View > Toolbars > Customize • There are three tabs in the Customize window: • Toolbars = list of all current toolbars, option to create a New toolbar • Commands = list of commands to add to a toolbar, option to assign macros, option to create New Menu • Options = general options

34. Custom Toolbar • Let us create a new toolbar and place some icons to associate with the filtering macros we wrote. • First, choose New from the Toolbars tab of the Customize window. • Enter the name “Filter”

35. Custom Toolbar (cont) • Now go to the Commands tab and find the Macros category. • Since we are creating a toolbar, we select Custom Button. • Drag this button to the new toolbar.

36. Custom Toolbar (cont) • We now use the Modify Selection button to • Name the button • Change the button image • Assign a macro

37. Customizing Menu Options • Customizing a menu option is done in a similar way. • We can ignore the Toolbars tab and move to the Commands tab and choose NewMenu from the Categories list.

38. Custom Menu • Let us create a new menu option for our sorting macros. • We select New Menu and then use Modify Selection to give a name to the new menu item.

39. Custom Menu(cont) • We now again choose Macros from the Commands tab and this time select Custom Menu Item. • Again use Modify Selectionto give a name and new icon to the new menu item, then assign a macro.

40. Summary • Macros are technically defined as units of VBA code. • Macros can either be created directly with VBA code in the Visual Basic Editor, or recorded in Excel. • Steps to Record a Macro: • 1. Choose Tools > Macros > Record New Macro or choose the Record icon from the VBA toolbar; • 2. Perform a sequence of actions in Excel; • 3. Choose Tools > Macros > Stop Recording or choose the Stop icon from the VBA toolbar. • Events are actions that can take place in the Excel window that cause an action to occur in the VBA code. • An event procedure is just like a sub procedure, except that it is associated with a particular action of an Excel object.

41. Additional Links • (place links here)