Chapter 9

1. Chapter 9 Macros And Visual Basic For Applications

2. Personal Macro Workbook • A workbook that opens automatically whenever Excel is opened • Any macro stored in this workbook can be used by any open workbook

3. Macro • Macro storing Excel commands (written in Visual Basic for Applications) • Visual Basic Editor (VBE) creating, displaying, running, and debugging macro commands • Project Explorer locating macro modules • Code window displaying micro statements

4. Macro Statements • Sub (subroutine) statementfollowed by the name of the macro • Defining the beginning of the macro • Comments begin with apostrophe (’) • Providing documentation • End Sub statement • Defining the end of the macro

5. The With Block • Including With and End With • Performing multiple actions on the same object

6. Absolute Cell References • Referring to a specific cell • Referred to by cell address or by range name • Syntax: Range(“A1”).Select • Always selecting cell A1

7. Relative Cell Reference (I) • Referring to a cell by its position relative to the active cell • Offset: giving the position relative to the active cell • First number: indicating the offset in rows • A positive number: rows below the active cell • Second number: indicating the offset in columns • A positive number: columns to the right of the active cell • Offset (2, 3) always refers to a cell two rows below and three columns to the right of the active cell

8. Relative Cell Reference (2) • Range: dimensions of cell(s) • Range(“A1”): a single cell • Range(“A1:A3”): a range three rows by one column • Syntax: ActiveCell.Offset(2,1).Range(“A1”). Select • Always selecting a single cell two rows below and one column to the right of the active cell

9. Data Management Macro • Automating data list management tasks • Prompting user for input to populate a criteria range • Filtering list or displaying database functions based on those criteria • Use range name for the list or the criteria range

10. Visual Basic for Applications • Tool menu, Macro, Visual Basic Editor • Or Alt+F11 • VBA: a subset of Visual Basic • Macro converted to VBA programs • Creating macro using macro recorder • Using Visual Basic Editor to modify

11. Custom Button – Standard Toolbar • Creating custom button: • Tools menu, Customize, Command tab, selecting Macro for the category, dragging custom button to the standard toolbar, selecting Modify Selection button, typing the name to replace “&name button” (do not press enter”, selecting Assign Macro to assign a macro, selecting This Workbook for Micro in, selecting Close • Deleting a micro and its custom button: • Tools menu, Micro, selecting micro name, clicking Delete button • Dragging the corresponding custom button from the standard toolbar to the worksheet area

12. Custom Button – Worksheet • Creating: • View Menu, Toolbars, Forms, clicking Button tool, drawing a button, assigning a associated macro, typing the name for the button • Selecting • Ctrl key and clicking the button (releasing the Ctrl key right away) • Coping • Holding Ctrl key and clicking the button (+ sign) • Deleting the macro and its corresponding micro • Tools menu, Macro, selecting the macro name, clicking Delete button, OK (performing this first) • Selecting the custom button, right clicking the mouse, selecting Cut

13. Common VBA statements • Underscore and ampersand: continuation • MsgBox statement • Displaying information to the user • InputBox statement • Accepting information from the user • Storing information in a cell or memory for future usage in the program

14. Decision Making • “If-Then-Else-End If” statement • If testing a condition (true or false) • “Then” clause including a series of commands to execute for the true condition • Then clause is necessity • “Else” clause including commands to execute for the false condition • Else clause is optional • Ends with End If statement

15. Loops • Do Until -Loop statement • Executing statements repeatedly • Begin with Do Until statement • Including a true or false condition after the Until for evaluating • End with Loop statement • Executing between Do and Loop statements repeatedly until the condition is true

16. Points to Remember • Personal Macro workbook • Macro • Visual Basic Editor • VBA statements • Information: MsgBox and InputBox • Decision making: If • Repeating: Do

17. Assignment • Practice exercises 7 and 8 • Due date: