Excel VBA I.pdf

Spreadsheet-Based Decision Support Systems for Business Excel VBA I  Visual Basic Editor  The Visual Basic Editor (VBE) is the environment in which you work with VBA programming code.  Visual Basic icon on the Developer Ribbon  ALT + F11  Three main windows in the V...

Spreadsheet-Based Decision Support Systems for Business Excel VBA I  Visual Basic Editor  The Visual Basic Editor (VBE) is the environment in which you work with VBA programming code.  Visual Basic icon on the Developer Ribbon  ALT + F11  Three main windows in the VBE  Project Explorer  Properties  Code  Two other windows in VBE, used for debugging  Immediate  Watch Excel VBA I 2  Visual Basic Editor (cont)  The Visual Basic Editor Code Project Window Explorer Properties Window Watch Window Immediate Window Excel VBA I 3  Project Explorer  Lists all projects in any open workbook.  Each workbook has a project, and each project can have several parts  Workbook and worksheets  Modules  Forms  Use Insert > Module or Insert > User Form to add a new module or form to the current project; can also use icon from standard toolbar. Excel VBA I 4  Properties Window  Contains detailed information about any selected part of a project in the Project Explorer.  Some basic naming and formatting properties can be modified for worksheets and workbooks.  Properties are very important for user forms  Formatting  Position  Picture  Scrolling  Behavior Excel VBA I 5  Code Window  Displays the VBA code for the highlighted part of a project in the Project Explorer.  When macros are recorded they are simultaneously created as VBA code in the Visual Basic Editor.  There are three types of macros or procedures  Sub procedures  Function procedures  Event procedures Excel VBA I 6  Immediate and Watch Windows  Both of these windows are used for debugging.  Immediate Window executes whatever is entered immediately.  Watch Window displays values of inserted variables.  Use the View menu option to view or hide any window. Excel VBA I 7  Object Browser  Provides a list of properties and methods of all Excel objects which may be manipulated in VBA.  There are larger categories into which various objects are grouped  Classes = groups of related objects  Libraries = collections of VBA and Excel object classes  Members = properties, methods, and constants of a selected class of objects  View > Object Browser or icon from standard toolbar. Excel VBA I 8  Object Browser (cont’d)  All classes containing the searched object are shown in the top window.  All members of a selected class are shown below. Excel VBA I 9  Quick Review  Objects  VBE has three main windows  Project Explorer  Properties  Code  Two debugging windows  Immediate  Watch  Object Browser Excel VBA I 10  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. Excel VBA I 11  Ensure the Developer Ribbon is Attached  Select the File Ribbon (top left-hand corner)  Click Options.  In the Customize Ribbon.  Under Customize the Ribbon and under Main Tabs, select the Developer check box.  Click OK.  The Developer Ribbon will now be visible in Excel. Excel VBA I 12  Attach the Developer Ribbon in Excel 2007  Select the Office Button (top left-hand corner)  Click Excel Options.  In the Popular tab, check Show Developers tab in the Ribbon and click OK.  The Developer Ribbon will now be visible in Excel. Excel VBA I 13  Example 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. Excel VBA I 14  Example 1: 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 (C5:E13)  Copy (using CTL+C, right-click and Copy, or the Edit option)  Place the cursor in cell C20  Paste (using CTL+V, right-click and Paste, or Edit option)  Highlight the last column (K5:K13)  Copy  Place cursor in cell F20  Paste  Place cursor in cell A1  Hit the key Excel VBA I 15  Example 1: Record  Now we are ready to record the macro  Record Macro button from Code are of the Developer Ribbon  When the Record Macro dialog box appears, we enter a name for the macro. Excel VBA I 16  Example 1: Stop  Once you begin recording, notice that the Record Macro button transforms to a Stop Recording button.  After finishing the steps needed to copy and paste the information, you can stop recording.  Stop Recording on the Developer Ribbon Excel VBA I 17  Example 1: Play  Once you have recorded a macro, you can play it to ensure it works correctly.  Macros from the Developer Ribbon  Select the desired Macro and click Run Excel VBA I 18  Example 1: Result  We can see that the macro works correctly. Excel VBA I 19  Example 1: 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 Excel VBA I 20  Example 1: VBA Code (cont) Sub CarCopyPaste()  This is the initial statement of every sub procedure.  The Select method of the Range("C5:E14").Select Range object selects the Selection.Copy specified range of cells. Range("C20").Select  The Copy method is used on ActiveSheet.Paste the Selection object.  The Paste method is used on Range("K5:K14").Select the ActiveSheet object. Selection.Copy  These steps are repeated to Range("F20").Select copy and paste the last column. ActiveSheet.Paste  This last cell selection can be added to avoid leaving the Range("A1").Select cells highlighted.  This is the last statement of every End Sub sub procedure. Excel VBA I 21  VBA Syntax Programming Language English language – VBA VS  Objects  Nouns  Properties  Adjectives  Methods  Verbs  Arguments  Adverbs Excel VBA I 22  Objects  Objects in Excel include  Workbooks and worksheets  Cells and ranges of cells  Charts  There is an object hierarchy which orders these objects  Workbooks contain worksheets  Worksheets contain ranges  Ranges contain cells A cell: Range(“A1”) Objects are like nouns, an A worksheet: object is a thing. Worksheets(“Example 1”) Sheet1 A workbook: Workbooks(“Movies.xlsx”) Excel VBA I 23  Properties  Like the way an adjective describes a noun in English, a property in VBA describes a characteristic of an object.  in VBA, the property comes after the object, and is separated by a period.  Syntax: Object.Property Example: Range(“A1”).Value = 5 Range(“F10”).Font.Name = “Arial” Range(“F10”). Font. Size = 14 Excel VBA I 24  Methods  In VBA, actions are performed with methods. Each executed method affects an object.  VBA syntax requires that you first list the object, then a period, and then the method.  Syntax: Object.Method Example: Range(“C5”).Select ActiveWorkbook.Save Range(“A1:F10”). Clear Excel VBA I 25  Arguments  In VBA, an argument describes how a method is to be executed.  Syntax1: Object.Method ArgumentName := Value  Syntax2: Object.Method Value Example: ActiveWorkbook.SaveAs FileName := “D:\Data\Test1.xlsx” ActiveWorkbook.SaveAs “D:\Data\Test1.xlsx” Selection.AutoFilter Field:=1, Criteria1:="Hong Kong" Excel VBA I 26  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. Excel VBA I 27  Example 2  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 Excel VBA I 28  Example 2: 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 (or Home) Ribbon > Sort & Filter (Editing)> Filter  Select the drop-down button for “Destination” column  Filter for Beijing flights  Select cell A1 Excel VBA I 29  Example 2: Excel to VBE  We can check this recorded macro to ensure it works.  Now we can go to VBE to view the code generated. Excel VBA I 30  Example 2: 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()  The Sub titles will change for each new macro Range("B3:E15").Select  The AutoFilter method will be Selection.AutoFilter Field:=1, used on the same selection to Criteria1:="Beijing" generate the filtering drop-down arrows Range("A1").Select  Field:=1 denotes the “Destination” column, we can End Sub modify this value  Criteria1:=“Beijing” is the value which is filtered, we can also modify this value Excel VBA I 31  Example 2: 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 ------------------------------------------------------ Excel VBA I 32  Example 2: 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 Largest to Smallest (i.e. Descending) as the order  Select cell A1 Excel VBA I 33  Example 2: Excel to VBE  We can check this recorded macro to ensure it works. Excel VBA I 34  Example 2: Excel to VBE  Now we can go to VBE to view the rather bloated code generated by the recorder. Excel VBA I 35  Example 2: Excel to VBE  This code can be modified to the following to produce exactly the same sorted outcome. Excel VBA I 36  Example 2: 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()  The Sub titles will change for each new macro Range("B3:E15").Select  The Sort method will be used on Selection.Sort the same selection Key1:=Range("C4"),  Key1:=Range(“C4”) denotes the Order1:=xlDescending “Stops” column Range("A1").Select  Order1:= xlDescending is the order in which the data will be sorted End Sub Excel VBA I 37  Example 2: 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 ------------------------------------------------------ Excel VBA I 38  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 an object from the object list  Choose an event from the event list Excel VBA I 39  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. Excel VBA I 40  Control Toolbox  Developer>Controls>Insert contains a set of objects to which event procedures can assigned.  Developer>Controls>Insert  Icon from VBA Toolbar  Click and drag from toolbar to spreadsheet Excel VBA I 41  Control Properties  There are many controls available to put on the spreadsheet  Text, labels, Spin buttons, toggle buttons  List box, combo box, Check boxes, option buttons  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 our sub procedure  Sub cmdButton_Click() Excel VBA I 42  Example 2, 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”  TakeFocusOnClick= False Excel VBA I 43  Example 2, Event Procedure  We can now copy and paste the code for this macro from the Module to a new sub procedure in the Worksheet for Example 2 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. Excel VBA I 44  Drawing Controls  We can also make a button instead of using a command button from the control toolbox. Excel VBA I 45  Drawing Controls  For Example: Developer > Shapes > Rectangle  Create rectangle and then add text to it by right-clicking and choosing EditText; then format the rectangle as desired. Excel VBA I 46  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. Excel VBA I 47  Example 2: Buttons  We can now create buttons for each of the macros we previously wrote.  Assign a macro to each new button respectively. Excel VBA I 48  Quick Review  Recording Macros  Record, Stop, Play, VBA code is generated  Copy and Paste methods  Sub Procedure basics  Some Objects / Methods / Arguments  Range / Select  Selection / AutoFilter / Field, Criteria  Selection / Sort / Key, Order  Event Procedures and Control Toolbox  Drawing controls Excel VBA I 49