Excel VBA I PDF Tutorial

Summary

This is a tutorial on using Visual Basic for Applications (VBA) in Microsoft Excel. It explains how to use the Visual Basic Editor (VBE), create and use macros. The document includes various examples to illustrate the concept of programming in Excel.

Full Transcript

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

Use Quizgecko on...
Browser
Browser