Excel – MacroProgrammingStudent ManualJeff Turner PPDMacro Programming Using Visual BasicLesson 1: Introduction to Visual Basic for Excel ..................................................... 5 Objectives ........................................................................................................................ 5 Running a Macro ....................................................................................................... 5 Displaying the Visual Basic Toolbar and Setting Security Levels .............. 5 Running Macros from Buttons and Shortcut Keys ........................................ 6 Running Macros from the Macro Dialog Box ................................................ 6 Running the Create_Worksheet Macro .......................................................... 6 Recording a Macro................................................................................................... 7 Test the Macro ........................................................................................................ 8 Viewing the VBA Code ............................................................................................ 8 Examining and Running the Recorded Macro ............................................. 8 Assigning Shortcut Keys to Macros ....................................................................... 9 Test the Macro ...................................................................................................... 10 Examining Module Design ................................................................................. 10 VBA Code .................................................................................................................. 10 Sub Procedures .................................................................................................... 11 Navigating in the VBA Module ............................................................................ 11 Accessing Help for VBA Code ............................................................................. 11 Lesson 2: Assigning Macros ....................................................................................... 13 Absolute Recording ................................................................................................ 13 Recording a Macro by Using Absolute References .................................. 14 Relative Recording .................................................................................................. 15 Examining the Difference between Code with Relative and Absolute References ................................................................................................................. 16 Assigning Macros to Menus, Buttons, and Toolbars ....................................... 17 Assigning Macros to Menus .............................................................................. 17 Adding a Menu Item to a Menu; Assigning it to a Macro ....................... 17 Assigning a Macro to a Button on a Sheet .................................................. 18 Assigning a Macro to a Toolbar and Deleting a Button from a Toolbar ................................................................................................................................... 19 Lesson 3: Creating Decision-Making Code .......................................................... 20 The If…Then Decision Structure............................................................................ 20 Creating and If…Then Sub Procedure and Testing the If…Then Sub Procedure .............................................................................................................. 21 Creating an If…Then…Else Sub Procedure and Testing the If…Then…Else Sub Procedure .......................................................................................................... 22 Lesson 4: Using Look Structures ................................................................................ 24 Using Loops ................................................................................................................ 24 Creating a Do Until…Loop Structure .............................................................. 24 Lesson 5: Macro Management ............................................................................... 26 Creating a Personal Macro Workbook.............................................................. 26 Assigning Sub Procedures to Run Automatically............................................ 27 3Macro Programming Using Visual BasicAppendix A................................................................................................................ 28 Using a For Each…Next Statement................................................................. 28 For Each…Next Loop .......................................................................................... 28 Creating a For Each…Next Structure............................................................. 29 Appendix B ................................................................................................................ 30 Copying Macros from One Workbook to Another .................................... 30 Appendix C ............................................................................................................... 31 Using a Personal Macro Workbook ................................................................ 31 Appendix D................................................................................................................ 32 Recording Code into the Personal Macro Workbook .................................. 324Macro Programming Using Visual BasicLesson 1: Introduction to Visual Basic for ExcelObjectivesTo understand the fundamentals of a Visual Basic module, you will: • • • Run a pre-recorded macro. Record a macro. Examine a Visual Basic module and identify the various components.Running a MacroVisual Basic for Applications is a programming environment and language that enables you to automate and customize your Office 2000 applications. For example, you can record macros to perform repetitive tasks. Or you can create interactive dialog boxes and wizards to make your work easier.Displaying the Visual Basic Toolbar and Setting Security Levels1. Locate and start Excel. 2. Right click on the toolbar at the top of the screen. 3. Choose Visual Basic.4. Click Security 5. Select Low 6. Click OK 7. Close & Reopen Excel5Macro Programming Using Visual BasicRunning Macros from Buttons and Shortcut KeysA macro is a group of user-created instructions that automates one or more operations. Macros can be used for common tasks, such as formatting or printing or for repetitive tasks, such as entering a series of labels. 1. Open the file VBA Overview and view the workbook. 2. Click Insert New Sales Rep. 3. Click OK 4. Select the range A6:D6 and press [Ctrl]D. 5. Type Jones and click OK. 6. Type 5 and click OK. 7. Type 450 and click OK.Running Macros from the Macro Dialog BoxThe dialog box displays the names of all the macros that are stored in open workbooks. From this dialog box, you can also: • • • Step through selected code, running the macro one line at a time. Access and edit the VBA code. Delete the selected macro from the open workbook. Change various options of the selected macro.•Running the Create_Worksheet Macro1. Look at the Sales Figures sheet. 2. Click the Run Macro button.6Macro Programming Using Visual Basic3. Select Create_Worksheet and click Run. 4. Click OK. 5. Enter the following data into cell C6. Cell C6: 400 Cell C7: 450 Cell C8: 385 Cell C9: 500 Cell C10: 465 6. Look at Sheet 1. The macro added this sheet, and it added the Insert New Sales Rep. button. Cell C3 displays the current month and year.Recording a MacroThe Macro Recorder works like a tape recorder. It records the actions that you perform, when you use the keyboard or the mouse. 1. Select cell C6. 2. Click the Record Macro button.3. In the Macro Name text box, type Dollar and press[Tab]. 4. Type f and press [Tab]. 5. Look at the Store Macro In drop-down list box. 6. Press [Tab] to move to the Description box. 7. Type “To format the numbers in the selected range as currency with zero decimal places.7Macro Programming Using Visual Basic8. Click OK. 9. Choose Format→Cells. On the Number tab, select Currency, Set Decimal to ‘0’ and click OK> 10. Click the Stop Recording button.Test the Macro1. Select the cells C7:C10. 2. Press [Ctrl]F to run the macro on cells C7 through C10.Viewing the VBA CodeAfter recording a macro, you can run the macro (execute the recorded code) and examine the specific code.Examining and Running the Recorded Macro1. Click the Run Macro button.8Macro Programming Using Visual Basic2. Select Dollar and click Edit to view the VBA code.3. Click the Project Explorer button. 4. Look at the VBA code for the Dollar macro. Note the following: • • • The name of the Sub procedure is the name you typed in the Macro dialog box. The shortcut key and description you entered are displayed in the module. Selection.NumberFormat = “$#,##0” is the code that formats the text in the selected cell.5. Choose File→ Close 6. Select the range D6:D10 and click the Run Macro button. 7. Select Dollar and click Run. 8. Save the file as My VBA Overview.Assigning Shortcut Keys to MacrosExcel has built-in shortcut keys, such as [Ctrl]N for File→ New and [Ctrl]S for File→Save. Any shortcut key you assign overrides Excel’s built-in shortcut keys.9Macro Programming Using Visual BasicTherefore if you assign [Ctrl]S to a macro, it will no longer work to save the current file.1. Choose Tools→ Macro→Macros. 2. Look at the available macros. You can assign shortcut keys to any macro. 3. Select Create_Worksheet and click Options. 4. In the Shortcut Key text box, type the letter w. Click OK. 5. Click Cancel to close the Macro dialog box. 6. Press [Ctrl]W to run the Create Worksheet macro and click OK.Test the Macro1. Enter the values of your choice in cells C6:C10 2. Format the range C6:D10 using the Dollar macro.Examining Module DesignUp until now, the term macro has been used to describe the VBA code. The code is contained in structures called Sub procedures.VBA CodeVBA code consists of statements and comment text. Statements are instructions executed when the macro or Sub procedure is run. Comment text, which begins with a single apostrophe, is just a comment ~ and is not executed. Statements consist of:•Keyword: displayed as blue text. For example Sub and End Sub are keywords that mark the beginning and end of a Sub procedure. Operator: can be arithmetic (+,-), concatenation (&), logical (ND, Or), or comparison(=,>,<).•10•Variable: Storage locations that you designate with a unique name.•Procedure Call: a Sub procedure inside another Sub procedure. Sub ProceduresSub Procedure names:•Must begin with an alphabetic character.•Cannot contain embedded periods or spaces.•Must be unique within a module.Navigating in the VBA Module1.Click the Visual Basic Editor button.2.Double-click on Modules.3.Double-click on Module 1.4.Maximize the Module1 code window.5.Click the Object Browser button.6.Select VBA Overview.xls from the Project/Library drop-down list.7.Double-click on DataEntry in the Members Of list box.8.Select Insert_New_Data and click View Definition button.Accessing Help for VBA Code1.Press [Ctrl][Home]2.Scroll down3.Double-click on ActiveCell4.Press [F1] and Read the Help screen.5.Close the Microsoft Visual Basic Help window.6.Close the Visual Basic Editor then save and close the workbookLesson 2: Assigning MacrosObjectivesTo learn about relative and absolute recorded macros and to assign these macros to various objects, you will:•Identify the differences between a macro recorded by using relative references and a macro recorded by using absolute references.•Assign a macro to a menu, a button on a sheet, and a toolbar buttonSpecifying the Location of Recorded MacrosWhen you record a macro, its code is recorded in a module in the active workbook. You can use the Visual Basic Editor to view and edit modules. If you want to store a macro in a module other than the one in which it was recorded, you can move the macro by cutting it from one module and pasting it in another module.Absolute RecordingBy default, the macro recorder records cell references absolutely. This means that when you set the reference type to Absolute before you select cell B4, the macro will always select cell B4. You should record macros absolutely when you want the macro to always act upon the same range, no matter which cell is active when the macro runs.Recording a Macro by Using Absolute References1.Open Assign Macros.2.Click the Record Macro button.3.Type Absolute_Title to name the macro.4.Press [Tab] and type t to assign a shortcut key5.Press [Tab] and in the Description box type: Creates and formats a title….6.In cell C1, enter ABC Company. In cell C2, type Sales Figures.7.Select the range C1:C2. Click the Bold button and click the Center button.8.Click the Stop recording button.9.Choose Tools→Macro→Macros and select Absolute_Title. Click Edit10.Look at the code in the Code window.11.Place the insertion point at the end of the line “Created and formats atitle…” and press [Enter]. ‘Type recorded using absolute reference.12.[Alt][TAB]13.Go to Sheet214.Run the Absolute_Title macro.15.Make sure that the range C1:C2 is selected and choose Edit→Clear→All.Relative RecordingTo set the macro to record cell references relatively, you must change the default: on the Stop Recording toolbar, click the Relative Reference button. For instance, if you select cell A1, start recording with Reference Type set to Relative, and then select cell B2, the macro will select a cell one cell to the right and one cell down from the active cell. You should record macros relatively when you want the macro to begin its routine at the active cell.Recording a Macro by Using Relative References1.On sheet2, select cell C1.2.Click the Record Macro button and type Relative_Title.3.Enter a description and click OK.4.Click the Relative Reference button to record the macro.5.In cell C1, type ABC Company; in cell C2, type Sales Figures. Select therange C1:C2. Format the range to be centered and bolded. Click theRelative Reference button.6.Click the Stop Recording button.7.Click the Run Macro button, select Relative_Title and click Edit.8.Place the insertion point at the end of the comment line and press [Enter].Type ‘Recorded using Relative References’.9.Examine the code.10.Choose File→Close and Return to Microsoft Excel11.On Sheet3, select cell D4 and run the Relative_Title macro.12.Save the file as My Assign Macros.Check your Macro SkillsCreate a macro to clear out the contents of selected cells using Ctrl + EExamining the Difference between Code with Relative and Absolute References1.Click the Visual Basic Editor button.2.Double-click on the modules folder and double-click on Module1.3.Press [Ctrl][Home] to move to the top of the code.4.In the Absolute_Title Sub procedure, look at the 3rd line of code.5.Press [Page Down] to display the Relative_Title code.6.Examine the 2nd line of code.7.Examine the 4th line of code.8.Return to the Excel worksheet (press [Alt][Tab]).Assigning Macros to Menus, Buttons, and ToolbarsOnce you record a macro, you can make it more accessible by assigning the macro to a menu, a button on a worksheet, or a button on a toolbar. Assigning Macros to MenusIt may be difficult to remember all the shortcut keys if you have designed a number of macros. In this case it may be easier to create a new menu choice and attach the macro to the menu item.Adding a Menu Item to a Menu; Assigning it to a Macro1.Choose Tools→Customize and select the Commands tab.2.In the Categories list box, select Macros.3.In the Commands list box, select Custom Menu Item.4.Drag Custom Menu Item to the bottom of the Format menu.5.Click Modify Selection.6.Select the text in the Name text box and type &Title.7.Select Assign Macro.8.Select Relative_Title.9.Click OK and click Close.10.On Sheet1, select the range C1:C211.Select cell and choose Format.12.Choose Title to run the Relative_Title macro.13.Press[Ctrl]E to clear the contents and formats of the selected cells.Assigning a Macro to a Button on a SheetAfter you record a macro, you can make it more accessible by creating a button on a worksheet and attaching the macro to the button.Creating and Using a Button1.Select Sheet1.2.Right-click the toolbar.3.Choose Forms.4.Click the Button button.5.Drag to the right corner of where you want the button displayed.6.Release the mouse button.7.In the Macro Name list box, select Absolute_Title.8.Click OK.9.Type Create Title to replace the default button text.10.Deselect the button11.Close the Forms toolbar.12.Select any cell on the worksheet13.Click Create Title to run the macro.Assigning a Macro to a Toolbar and Deleting a Button from a Toolbar1.Right-click on the toolbar and choose Customize.2.Select the category Macros.3.Select the Custom Button command.4.Drag the selected button to the toolbar, and drop it to the right of theUnderline button on the Formatting toolbar.5.Click Modify Selection and choose Assign_Macro6.Select Absolute_Title and click OK.7.Click Modify Selection, change button image…8.Select sheet2 and clear the contents of the range C1:C2.9.Select cell A1.10.Place the mouse pointer over the new button on the toolbar.11.Click the new button.12.Save the file.13.Right-click and choose customize14.Drag the custom button off the toolbar.15.Close the Customize dialog box.16.Close the file.Lesson 3: Creating Decision-Making CodeObjectivesTo be able to write procedures that test conditions and then run only certain statements based on the results, you will:•Create and identify the syntax of If…Then statements.•Create and identify the syntax of If…Then…Else statements.The If…Then Decision StructureDecision-making procedures enable you to perform one calculation if a certain condition is true and a different calculation if that condition is false. Conditional StatementsConditional statements examine a certain condition and then determine what to do based on that condition.Symbol Operator______________________________________________< Less than<= Less than or equal to> Greater than>= Greater than or equal to= Equal to<> Not equal to______________________________________________If….ThenThe single line syntax or the multiple-line block syntax can be used for If…Then statements.Single-line syntax Block syntaxIf condition Then statement If condition ThenStatement 1Statement 2End ifCreating and If…Then Sub Procedure and Testing the If…Then Sub Procedure1.Open the file Decision Making2.Select cell D5.3.Click the Record Macro button. In the Macro Name text box, type IfThen.In the Description text box, type An example of an If…Then structure and click OK.4.Click the Bold button.5.Click the Stop Recording button.6.Display the code for the IfThen macro (choose Tools→Macro→Macros;select IfThen; click Edit)7.Look at the code.8.Place the insertion point above the line Selection.Font.Bold=True. Press[Enter], press [Tab]. Type If ActiveCell>600 Then.9.Place the insertion point at the end of the line Selection.Font.Bold=True.Press [Enter], type End If, and press [Enter].10.Return to the spreadsheet (press [Alt][Tab] and on the Sales Figures sheet,select cell D6.11.Run the IfThen macro. Cell D6 is bold.12.Select cell D7.13.Run the IfThen macro. Cell D7 should not be bold.14.Save the file as My Decision Making.The If…Then…Else Decision StructureWhen you use the If…Then…Else statements, one of the conditions is always run. You can use the single-line syntax or the multiple-line block syntax to create If…Then…Else statements:Single-line syntax Block syntaxIf condition If condition Then statement1statement2 Else Then statement else statement elsestatement1elsestatement2End if______________________________________________________________________________ Use the Elself statement if you need to check several conditions. The blocksyntax would look like the following example:If condition 1 Thenstatement AElseIf condition 2 Thenelseifstatement Aelseifstatement Belseifstatement CElseelseifstatement AEnd IfCreating an If…Then…Else Sub Procedure and Testing the If…Then…Else Sub Procedure1.Display the code for the IfThen macro (press [Alt][Tab]). Place the insertionpoint at the beginning of the line Selection.Font.Bold=True. Press [Tab], to indentthe line of code.2.Place the insertion point at the end of the line Selection.Font.Bold=True.Press [Enter], to start a new line in the Sub procedure.3.Type Else: Selection.Font.Italic=True.The Finished ProductSub IfThen ()'' IfThen Macro' An example of an If…Then Structure.'If ActiveCell > 600 ThenSelection.Font.Bold = TrueElse: Selection.Font.Italic = TrueEnd If4.Return to the spreadsheet.5.Make sure that cell D7 is selected on the Sales Figure sheet.6.Run the IfThen macro.7.Select cell D8.8.Run the IfThen macro.Check Your SkillsUse the IfThen macro to finish formatting the numbers in column D.Lesson 4: Using Loop StructuresObjectivesTo learn how to execute one or more lines of code repetitively, you will:•Learn the structure of and create a For…Next Loop.•Learn the structure of and create a Do…Loop.•Learn the structure of and create a For Each…Next Loop.Using LoopsLoops enable VBA to perform designated tasks repetitively until certain conditions are met. There are two general types of loops that are commonly used in VBA: For loops and Do loops.Creating a Do Until…Loop Structure1.Open Loops2.Press [Alt][F11] and select Sheet1.3.In the Module1 code window, in Section II, position the insertion point atthe end of the line. Sub DoUntilLoop()and press [Enter].4.Type ‘ A Do Loop that runs until a blank cell is selected. Press [Enter] andtype Do Until ActiveCell =””5.Now Copy & Paste IF routine from Section 1 above6.To move down one cell Type: ActiveCell.Offset(1, 0). Range(“A1”).Select7.In the line before End Sub, type the following code: Loop ‘Ending the Loop.You can place comment text on the same line as other code, as long ayou precede the comment with an apostrophe(‘).Final CodeSub DoUntilLoop()' A Do Loop that runs until a blank cell is selectedDo Until ActiveCell = ""' A decision structure that bold or italicizes numbers'depending on their valueIf ActiveCell > 600 ThenSelection.Font.Bold = TrueElse: Selection.Font.Italic = TrueEnd IfActiveCell.Offset (1, 0) . Range (“A1”) . SelectLoop 'Ending the LoopEnd Sub 'Ending the procedure8.Return to Sheet1 and select cell D5. Run the DoUntil Loop macro. The loop is executed and stops when it reaches the blank cell D16.7.Save file as My_LoopsLesson 5: Macro ManagementObjectivesTo organize you macros for easy access, you will:•Create a Personal Macro Workbook.•Assign Sub Procedures to run automatically.Personal Macro WorkbookFor a recorded macro to be available at all times, you must store it in the Personal Macro Workbook, a hidden workbook that is always open unless you specify otherwise. Because the Personal Macro Workbook is always open, the macros are always available through the Macro dialog box. There are two way to create the Person Macro Workbook:•Record a new macro and select Personal Macro Workbook from the Store Macro In drop-down list.•Save a workbook as Personal.xls in the startup subdirectory (XLstart) in the Office directoryCreating a Personal Macro Workbook1.Click the New button.2.Choose File→Save As. Save this file in the XLStart folder (C: drive/ProgramFiles/Microsoft Office/Office(#)/XLSTART it Personal.4.Click Save5.Click Window / Hide6.Close and reopen ExcelAssigning Sub Procedures to Run AutomaticallyAn automatic procedure is stored on a module sheet in a workbook and runs automatically at a designated time, such as when the workbook is opened or closed. Each workbook can contain only one Auto_Open and one Auto_Close procedure.Creating an Auto_Open Sub Procedure and Running the Auto_Open Sub Procedure; Exiting ExcelObjective: To change the name of the Sub procedure so that it isexecuted when the workbook in which it is located opens.1.Open the file Automatic Procedures.2.Run the macro Create_Worksheet and click OK. A new worksheet isinserted before the Sales Figures sheet. Notice the current month andyear are displayed in cell C3.3.Display the code for the Create_Worksheet macro.4.Change the name of the Sub procedure Create_Worksheet to Auto_Open.5.Save the workbook as My Automatic Procedures.6.Close the workbook.7.Open My Automatic Procedures.8.Click OK.9.Close the file without saving changes.Appendix AUsing a For Each…Next StatementWhereas a For…Next loop repeats a statement block a specified number of times, a For Each…loop repeats a statement block for each element in a collection of objects.Objects in VBAAn object in VBA is something you name, control, and manipulate in procedures. On of the most common objects assigned to a variable is a reference to a cell or a range of cells.For Example, in the statement:Set C = ActiveCellC is the variable name, and ActiveCell is a keyword referring to the active cellon the worksheetSetting Variables1.Display the code for Module2. The Sub procedure ForEachNext is missingcertain statements.2.Place the insertion point on the line after the comment that begins with‘ Set the variable C.3.Type C = ActiveCell.Sub ForEachNext()‘Set the variable C to the active cell in the selectionSet C = ActiveCell‘The For Each…Next statementFor Each…Next LoopThe basic syntax for the For Each…Next loop is:For Each element In groupStatement blockNext ElementThe argument element is a variable name used to cycle through the objects of the collection. The argument group is an object collection. When VBA executes For Each…Next loop, it performs the following steps:1.Defines element to be the first element in group.2.Runs the statements.3.Tests to see if element is the last element in group. If so, the loop is exited.4.Defines element to be the next element in group.5.Repeats steps 2 though 4.Creating a For Each…Next StructureObjective: To set up a loop structure to perform the If…Then…Elsestatement only on the cells in a selected range.1.In the Module2 code window, place the insertion point on the line afterthe comment ‘The For Each_Next statement and press [tab].2.Type For Each C in the Selection - Variable C refers to argument element3.Place the insertion point on the line above the End Sub line and press [Tab]4.Type Next C.Sub ForEachNext()‘Set the variable C to the active cell in the selectionSet C = ActiveCell‘The For Each…Next statementFor Each C In Selection‘A decision structure that bolds or depending on their valueIf C > 600 ThenC.Font.Bold = TrueElse: C.Font.Italic = TrueEnd IfNext CEnd Sub。
